sqldf and number of records affected

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

sqldf and number of records affected

Ravi Jeyaraman
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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: sqldf and number of records affected

Gabor Grothendieck
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 <[hidden email]> 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
>
> ______________________________________________
> [hidden email] 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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: sqldf and number of records affected

Ravi Jeyaraman
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:[hidden email]]
Sent: Thursday, June 11, 2020 9:12 AM
To: Ravi Jeyaraman <[hidden email]>
Cc: [hidden email]
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 <[hidden email]> 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
>
> ______________________________________________
> [hidden email] 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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: sqldf and number of records affected

Gabor Grothendieck
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@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 <[hidden email]> 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:[hidden email]]
> Sent: Thursday, June 11, 2020 9:12 AM
> To: Ravi Jeyaraman <[hidden email]>
> Cc: [hidden email]
> 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 <[hidden email]> 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
> >
> > ______________________________________________
> > [hidden email] 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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: sqldf and number of records affected

Ravi Jeyaraman
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:[hidden email]]
Sent: Thursday, June 11, 2020 10:30 AM
To: Ravi Jeyaraman <[hidden email]>
Cc: [hidden email]
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@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 <[hidden email]> 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:[hidden email]]
> Sent: Thursday, June 11, 2020 9:12 AM
> To: Ravi Jeyaraman <[hidden email]>
> Cc: [hidden email]
> 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 <[hidden email]> 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
> >
> > ______________________________________________
> > [hidden email] 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

______________________________________________
[hidden email] 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.