Thanks for the response Gabor. Looks like the below example will work when
using SQLite, but in my case I'm just creating a dataframe in R and trying
to update it using sqldf as below and it doesn't seem to work ...
con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
sqldf()
sqldf(c("pragma count_changes = 1", "update con set V1 = 0 where
V1 > 5 "))
ans <- sqldf("select * from main.con")
sqldf()
-----Original Message-----
From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com]
Sent: Thursday, June 11, 2020 9:12 AM
To: Ravi Jeyaraman <ravi76 at gmail.com>
Cc: r-help at r-project.org
Subject: Re: [R] sqldf and number of records affected
Here is an example. Ignore the warning or use the workaround discussed here
https://github.com/ggrothendieck/sqldf/issues/40
to avoid the warning.
library(sqldf)
sqldf() # use same connection until next sqldf()
sqldf(c("pragma count_changes = 1", "update BOD set demand = 99
where Time > 4"))
sqldf("select * from main.BOD")
sqldf()
On Thu, Jun 11, 2020 at 9:01 AM Ravi Jeyaraman <ravi76 at gmail.com>
wrote:>
> Hello all, When I execute a SQL using SQLDF, how do I get the number
> of records affected? I mean, if I run an UPDATE on a data frame, it
> doesn't tell me if and how many records got updated. I've read
> through the documentation and there don't seem to be a way to get this
> info unless it's done on a database. Any ideas?
>
> Thanks
> Ravi
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
--
This email has been checked for viruses by AVG.
https://www.avg.com
There is no real difference between your example and the example I provided. Both use a data.frame in R and both work for me under R 3.5 with RSQLite 2.2.0 See log below. Note that there is a bug in R 4.0 related to tcltk that could possibly affect sqldf as it uses tcltk. A fix has been announced for R 4.0.2.> library(sqldf)Loading required package: gsubfn Loading required package: proto Loading required package: RSQLite> con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) > sqldf()<SQLiteConnection> Path: :memory: Extensions: TRUE> sqldf(c("pragma count_changes = 1", "update con set V1 = 0 where V1 > 5 "))rows updated 1 5 Warning message: In result_fetch(res at ptr, n = n) : SQL statements must be issued with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery().> ans <- sqldf("select * from main.con") > sqldf()NULL> ansV1 1 1 2 2 3 3 4 4 5 5 6 0 7 0 8 0 9 0 10 0> R.version.string[1] "R version 3.5.3 (2019-03-11)"> packageVersion("sqldf")[1] ?0.4.11?> packageVersion("RSQLite")[1] ?2.2.0?> packageVersion("DBI")[1] ?1.1.0? On Thu, Jun 11, 2020 at 10:06 AM Ravi Jeyaraman <ravi76 at gmail.com> wrote:> > Thanks for the response Gabor. Looks like the below example will work when using SQLite, but in my case I'm just creating a dataframe in R and trying to update it using sqldf as below and it doesn't seem to work ... > > con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) > sqldf() > sqldf(c("pragma count_changes = 1", "update con set V1 = 0 where V1 > 5 ")) > ans <- sqldf("select * from main.con") > sqldf() > > -----Original Message----- > From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com] > Sent: Thursday, June 11, 2020 9:12 AM > To: Ravi Jeyaraman <ravi76 at gmail.com> > Cc: r-help at r-project.org > Subject: Re: [R] sqldf and number of records affected > > Here is an example. Ignore the warning or use the workaround discussed here > https://github.com/ggrothendieck/sqldf/issues/40 > to avoid the warning. > > library(sqldf) > sqldf() # use same connection until next sqldf() > sqldf(c("pragma count_changes = 1", "update BOD set demand = 99 where Time > 4")) > sqldf("select * from main.BOD") > sqldf() > > > On Thu, Jun 11, 2020 at 9:01 AM Ravi Jeyaraman <ravi76 at gmail.com> wrote: > > > > Hello all, When I execute a SQL using SQLDF, how do I get the number > > of records affected? I mean, if I run an UPDATE on a data frame, it > > doesn't tell me if and how many records got updated. I've read > > through the documentation and there don't seem to be a way to get this > > info unless it's done on a database. Any ideas? > > > > Thanks > > Ravi > > > > > > -- > > This email has been checked for viruses by AVG. > > https://www.avg.com > > > > ______________________________________________ > > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > > https://stat.ethz.ch/mailman/listinfo/r-help > > PLEASE do read the posting guide > > http://www.R-project.org/posting-guide.html > > and provide commented, minimal, self-contained, reproducible code. > > > > -- > Statistics & Software Consulting > GKX Group, GKX Associates Inc. > tel: 1-877-GKX-GROUP > email: ggrothendieck at gmail.com > > > -- > This email has been checked for viruses by AVG. > https://www.avg.com >-- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com
You're correct. It does work. I was looking at some other result printed.
My bad.
Looks like we can also get the same result using 'SELECT changes()'.
Approach 1:
con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
sqldf()
suppressWarnings(sqldf(c(" update con set V1 = 0 where V1 > 5 ",
"select changes()")))
sqldf("select * from main.con")
sqldf()
Approach 2:
con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
sqldf()
suppressWarnings(sqldf(c("pragma count_changes = 1", "update con
set V1 = 0 where V1 > 5 ")))
sqldf("select * from main.con")
sqldf()
-----Original Message-----
From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com]
Sent: Thursday, June 11, 2020 10:30 AM
To: Ravi Jeyaraman <ravi76 at gmail.com>
Cc: r-help at r-project.org
Subject: Re: [R] sqldf and number of records affected
There is no real difference between your example and the example I provided.
Both use a data.frame in R and both work for me under R 3.5 with RSQLite 2.2.0
See log below.
Note that there is a bug in R 4.0 related to tcltk that could possibly affect
sqldf as it uses tcltk. A fix has been announced for R 4.0.2.
> library(sqldf)
Loading required package: gsubfn
Loading required package: proto
Loading required package: RSQLite> con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
> sqldf()
<SQLiteConnection>
Path: :memory:
Extensions: TRUE> sqldf(c("pragma count_changes = 1", "update con set V1 = 0
where V1 >
> 5 "))
rows updated
1 5
Warning message:
In result_fetch(res at ptr, n = n) :
SQL statements must be issued with dbExecute() or dbSendStatement() instead of
dbGetQuery() or dbSendQuery().> ans <- sqldf("select * from main.con")
> sqldf()
NULL> ans
V1
1 1
2 2
3 3
4 4
5 5
6 0
7 0
8 0
9 0
10 0> R.version.string
[1] "R version 3.5.3 (2019-03-11)"> packageVersion("sqldf")
[1] ?0.4.11?> packageVersion("RSQLite")
[1] ?2.2.0?> packageVersion("DBI")
[1] ?1.1.0?
On Thu, Jun 11, 2020 at 10:06 AM Ravi Jeyaraman <ravi76 at gmail.com>
wrote:>
> Thanks for the response Gabor. Looks like the below example will work when
using SQLite, but in my case I'm just creating a dataframe in R and trying
to update it using sqldf as below and it doesn't seem to work ...
>
> con <- data.frame(V1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
> sqldf()
> sqldf(c("pragma count_changes = 1", "update con set V1 = 0
where V1 >
> 5 ")) ans <- sqldf("select * from main.con")
> sqldf()
>
> -----Original Message-----
> From: Gabor Grothendieck [mailto:ggrothendieck at gmail.com]
> Sent: Thursday, June 11, 2020 9:12 AM
> To: Ravi Jeyaraman <ravi76 at gmail.com>
> Cc: r-help at r-project.org
> Subject: Re: [R] sqldf and number of records affected
>
> Here is an example. Ignore the warning or use the workaround
> discussed here
> https://github.com/ggrothendieck/sqldf/issues/40
> to avoid the warning.
>
> library(sqldf)
> sqldf() # use same connection until next sqldf()
> sqldf(c("pragma count_changes = 1", "update BOD set demand
= 99 where Time > 4"))
> sqldf("select * from main.BOD")
> sqldf()
>
>
> On Thu, Jun 11, 2020 at 9:01 AM Ravi Jeyaraman <ravi76 at gmail.com>
wrote:
> >
> > Hello all, When I execute a SQL using SQLDF, how do I get the number
> > of records affected? I mean, if I run an UPDATE on a data frame, it
> > doesn't tell me if and how many records got updated. I've
read
> > through the documentation and there don't seem to be a way to get
> > this info unless it's done on a database. Any ideas?
> >
> > Thanks
> > Ravi
> >
> >
> > --
> > This email has been checked for viruses by AVG.
> > https://www.avg.com
> >
> > ______________________________________________
> > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> > https://stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide
> > http://www.R-project.org/posting-guide.html
> > and provide commented, minimal, self-contained, reproducible code.
>
>
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com
>
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com