How to parse the data frame values into postgresql statement?
0
How to pass the data frame d values in to postgresql statement inside like below :
a<-data.frame(b=sample(letters),c=1:26)
d<-a%>%filter(c>15)%>%select(b)
e<-paste("select date,amount from table where id in ('", d,"')")
dbGetQuery(con,e)
Error mesg:
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR: invalid input syntax for integer: "c("m","w"...)
LINE 1: ...type,id from table where id in ('c("m","w"...
getting error for the above query.Suggest me if i was wrong.
r postgresql
asked Jan 18 at 12:28
datascientistdatascientist
32
New contributor
datascientist is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
How to pass the data frame d values in to postgresql statement inside like below :
a<-data.frame(b=sample(letters),c=1:26)
d<-a%>%filter(c>15)%>%select(b)
e<-paste("select date,amount from table where id in ('", d,"')")
dbGetQuery(con,e)
Error mesg:
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR: invalid input syntax for integer: "c("m","w"...)
LINE 1: ...type,id from table where id in ('c("m","w"...
getting error for the above query.Suggest me if i was wrong.
r postgresql
asked Jan 18 at 12:28
datascientistdatascientist
32
New contributor
datascientist is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
@Ronak Shah can u help me out?
– datascientist
Jan 18 at 12:45
add a comment |
0
0
0
How to pass the data frame d values in to postgresql statement inside like below :
a<-data.frame(b=sample(letters),c=1:26)
d<-a%>%filter(c>15)%>%select(b)
e<-paste("select date,amount from table where id in ('", d,"')")
dbGetQuery(con,e)
Error mesg:
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR: invalid input syntax for integer: "c("m","w"...)
LINE 1: ...type,id from table where id in ('c("m","w"...
getting error for the above query.Suggest me if i was wrong.
r postgresql
asked Jan 18 at 12:28
datascientistdatascientist
32
New contributor
datascientist is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
How to pass the data frame d values in to postgresql statement inside like below :
a<-data.frame(b=sample(letters),c=1:26)
d<-a%>%filter(c>15)%>%select(b)
e<-paste("select date,amount from table where id in ('", d,"')")
dbGetQuery(con,e)
Error mesg:
Error in postgresqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not Retrieve the result : ERROR: invalid input syntax for integer: "c("m","w"...)
LINE 1: ...type,id from table where id in ('c("m","w"...
getting error for the above query.Suggest me if i was wrong.
r postgresql
r postgresql
asked Jan 18 at 12:28
datascientistdatascientist
32
New contributor
datascientist is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jan 18 at 12:28
datascientistdatascientist
32
New contributor
datascientist is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jan 18 at 12:28
datascientistdatascientist
32
New contributor
datascientist is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jan 18 at 12:28
datascientistdatascientist
32
asked Jan 18 at 12:28
datascientistdatascientist
32
32
New contributor
datascientist is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
datascientist is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
datascientist is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
@Ronak Shah can u help me out?
– datascientist
Jan 18 at 12:45
add a comment |
@Ronak Shah can u help me out?
– datascientist
Jan 18 at 12:45
@Ronak Shah can u help me out?
– datascientist
Jan 18 at 12:45
@Ronak Shah can u help me out?
– datascientist
Jan 18 at 12:45
add a comment |
1 Answer
1
active
oldest
votes
0
This is what you pass to dbGetQuery:
paste("select date,amount from table where id in ('", d,"')")
[1] "select date,amount from table where id in (' u ')" "select date,amount from table where id in (' i ')"
[3] "select date,amount from table where id in (' s ')" "select date,amount from table where id in (' t ')"
[5] "select date,amount from table where id in (' k ')" "select date,amount from table where id in (' l ')"
[7] "select date,amount from table where id in (' y ')" "select date,amount from table where id in (' v ')"
[9] "select date,amount from table where id in (' n ')" "select date,amount from table where id in (' z ')"
[11] "select date,amount from table where id in (' b ')"
You need to "collapse" the d variable to get something like ('value1', 'value2', ...):
sprintf("select date, amount from table where id in ('%s')", paste(d, collapse = "', '"))
[1] "select date, amount from table where id in ('u', 'i', 's', 't', 'k', 'l', 'y', 'v', 'n', 'z', 'b')"
answered Jan 18 at 13:25
MRauMRau
1668
how to add the numeric values instead of character type?
– datascientist
Jan 18 at 13:58
for the same query i want to get the numeric values in the same place of characters..but getting error due to the "c" character coming before starting the values
– datascientist
Jan 18 at 14:01
i am getting the error at "c" for numeric values :('c("3249", "3266", "3553",")')"
– datascientist
Jan 18 at 14:19
I am not sure if I understand your problem, but if you want to pass numeric values, then you have to get rid of':sprintf("select date, amount from table where id in (%s)", paste(d, collapse = ", "))
– MRau
Jan 18 at 14:27
getting the same error
– datascientist
Jan 18 at 15:41
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
datascientist is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54254043%2fhow-to-parse-the-data-frame-values-into-postgresql-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
This is what you pass to dbGetQuery:
paste("select date,amount from table where id in ('", d,"')")
[1] "select date,amount from table where id in (' u ')" "select date,amount from table where id in (' i ')"
[3] "select date,amount from table where id in (' s ')" "select date,amount from table where id in (' t ')"
[5] "select date,amount from table where id in (' k ')" "select date,amount from table where id in (' l ')"
[7] "select date,amount from table where id in (' y ')" "select date,amount from table where id in (' v ')"
[9] "select date,amount from table where id in (' n ')" "select date,amount from table where id in (' z ')"
[11] "select date,amount from table where id in (' b ')"
You need to "collapse" the d variable to get something like ('value1', 'value2', ...):
sprintf("select date, amount from table where id in ('%s')", paste(d, collapse = "', '"))
[1] "select date, amount from table where id in ('u', 'i', 's', 't', 'k', 'l', 'y', 'v', 'n', 'z', 'b')"
answered Jan 18 at 13:25
MRauMRau
1668
how to add the numeric values instead of character type?
– datascientist
Jan 18 at 13:58
for the same query i want to get the numeric values in the same place of characters..but getting error due to the "c" character coming before starting the values
– datascientist
Jan 18 at 14:01
i am getting the error at "c" for numeric values :('c("3249", "3266", "3553",")')"
– datascientist
Jan 18 at 14:19
I am not sure if I understand your problem, but if you want to pass numeric values, then you have to get rid of':sprintf("select date, amount from table where id in (%s)", paste(d, collapse = ", "))
– MRau
Jan 18 at 14:27
getting the same error
– datascientist
Jan 18 at 15:41
add a comment |
0
This is what you pass to dbGetQuery:
paste("select date,amount from table where id in ('", d,"')")
[1] "select date,amount from table where id in (' u ')" "select date,amount from table where id in (' i ')"
[3] "select date,amount from table where id in (' s ')" "select date,amount from table where id in (' t ')"
[5] "select date,amount from table where id in (' k ')" "select date,amount from table where id in (' l ')"
[7] "select date,amount from table where id in (' y ')" "select date,amount from table where id in (' v ')"
[9] "select date,amount from table where id in (' n ')" "select date,amount from table where id in (' z ')"
[11] "select date,amount from table where id in (' b ')"
You need to "collapse" the d variable to get something like ('value1', 'value2', ...):
sprintf("select date, amount from table where id in ('%s')", paste(d, collapse = "', '"))
[1] "select date, amount from table where id in ('u', 'i', 's', 't', 'k', 'l', 'y', 'v', 'n', 'z', 'b')"
answered Jan 18 at 13:25
MRauMRau
1668
how to add the numeric values instead of character type?
– datascientist
Jan 18 at 13:58
for the same query i want to get the numeric values in the same place of characters..but getting error due to the "c" character coming before starting the values
– datascientist
Jan 18 at 14:01
i am getting the error at "c" for numeric values :('c("3249", "3266", "3553",")')"
– datascientist
Jan 18 at 14:19
I am not sure if I understand your problem, but if you want to pass numeric values, then you have to get rid of':sprintf("select date, amount from table where id in (%s)", paste(d, collapse = ", "))
– MRau
Jan 18 at 14:27
getting the same error
– datascientist
Jan 18 at 15:41
add a comment |
0
0
0
This is what you pass to dbGetQuery:
paste("select date,amount from table where id in ('", d,"')")
[1] "select date,amount from table where id in (' u ')" "select date,amount from table where id in (' i ')"
[3] "select date,amount from table where id in (' s ')" "select date,amount from table where id in (' t ')"
[5] "select date,amount from table where id in (' k ')" "select date,amount from table where id in (' l ')"
[7] "select date,amount from table where id in (' y ')" "select date,amount from table where id in (' v ')"
[9] "select date,amount from table where id in (' n ')" "select date,amount from table where id in (' z ')"
[11] "select date,amount from table where id in (' b ')"
You need to "collapse" the d variable to get something like ('value1', 'value2', ...):
sprintf("select date, amount from table where id in ('%s')", paste(d, collapse = "', '"))
[1] "select date, amount from table where id in ('u', 'i', 's', 't', 'k', 'l', 'y', 'v', 'n', 'z', 'b')"
answered Jan 18 at 13:25
MRauMRau
1668
This is what you pass to dbGetQuery:
paste("select date,amount from table where id in ('", d,"')")
[1] "select date,amount from table where id in (' u ')" "select date,amount from table where id in (' i ')"
[3] "select date,amount from table where id in (' s ')" "select date,amount from table where id in (' t ')"
[5] "select date,amount from table where id in (' k ')" "select date,amount from table where id in (' l ')"
[7] "select date,amount from table where id in (' y ')" "select date,amount from table where id in (' v ')"
[9] "select date,amount from table where id in (' n ')" "select date,amount from table where id in (' z ')"
[11] "select date,amount from table where id in (' b ')"
You need to "collapse" the d variable to get something like ('value1', 'value2', ...):
sprintf("select date, amount from table where id in ('%s')", paste(d, collapse = "', '"))
[1] "select date, amount from table where id in ('u', 'i', 's', 't', 'k', 'l', 'y', 'v', 'n', 'z', 'b')"
answered Jan 18 at 13:25
MRauMRau
1668
answered Jan 18 at 13:25
MRauMRau
1668
answered Jan 18 at 13:25
MRauMRau
1668
answered Jan 18 at 13:25
MRauMRau
1668
1668
how to add the numeric values instead of character type?
– datascientist
Jan 18 at 13:58
for the same query i want to get the numeric values in the same place of characters..but getting error due to the "c" character coming before starting the values
– datascientist
Jan 18 at 14:01
i am getting the error at "c" for numeric values :('c("3249", "3266", "3553",")')"
– datascientist
Jan 18 at 14:19
I am not sure if I understand your problem, but if you want to pass numeric values, then you have to get rid of':sprintf("select date, amount from table where id in (%s)", paste(d, collapse = ", "))
– MRau
Jan 18 at 14:27
getting the same error
– datascientist
Jan 18 at 15:41
add a comment |
how to add the numeric values instead of character type?
– datascientist
Jan 18 at 13:58
for the same query i want to get the numeric values in the same place of characters..but getting error due to the "c" character coming before starting the values
– datascientist
Jan 18 at 14:01
i am getting the error at "c" for numeric values :('c("3249", "3266", "3553",")')"
– datascientist
Jan 18 at 14:19
I am not sure if I understand your problem, but if you want to pass numeric values, then you have to get rid of':sprintf("select date, amount from table where id in (%s)", paste(d, collapse = ", "))
– MRau
Jan 18 at 14:27
getting the same error
– datascientist
Jan 18 at 15:41
how to add the numeric values instead of character type?
– datascientist
Jan 18 at 13:58
how to add the numeric values instead of character type?
– datascientist
Jan 18 at 13:58
for the same query i want to get the numeric values in the same place of characters..but getting error due to the "c" character coming before starting the values
– datascientist
Jan 18 at 14:01
for the same query i want to get the numeric values in the same place of characters..but getting error due to the "c" character coming before starting the values
– datascientist
Jan 18 at 14:01
i am getting the error at "c" for numeric values :('c("3249", "3266", "3553",")')"
– datascientist
Jan 18 at 14:19
i am getting the error at "c" for numeric values :('c("3249", "3266", "3553",")')"
– datascientist
Jan 18 at 14:19
I am not sure if I understand your problem, but if you want to pass numeric values, then you have to get rid of
': sprintf("select date, amount from table where id in (%s)", paste(d, collapse = ", "))– MRau
Jan 18 at 14:27
I am not sure if I understand your problem, but if you want to pass numeric values, then you have to get rid of
': sprintf("select date, amount from table where id in (%s)", paste(d, collapse = ", "))– MRau
Jan 18 at 14:27
getting the same error
– datascientist
Jan 18 at 15:41
getting the same error
– datascientist
Jan 18 at 15:41
add a comment |
datascientist is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
datascientist is a new contributor. Be nice, and check out our Code of Conduct.
datascientist is a new contributor. Be nice, and check out our Code of Conduct.
datascientist is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54254043%2fhow-to-parse-the-data-frame-values-into-postgresql-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
@Ronak Shah can u help me out?
– datascientist
Jan 18 at 12:45