sqldf R Error create a table

Question

I am doing some experiments with SQL in R using the sqldf package. I am trying to test some commands to check the output, in particular I am trying to create tables.

Here the code:

sqldf("CREATE TABLE tbl1 AS
       SELECT cut
       FROM diamonds")

Very simple code, however I get this error

sqldf("CREATE TABLE tbl1 AS
+ SELECT cut
+       FROM diamonds")
data frame with 0 columns and 0 rows
Warning message:
In result_fetch(res@ptr, n = n) :
Don't need to call dbFetch() for statements, only for queries

Why is it saying the the table create as 0 columns and 0 rows? Can someone help?

Thanks Mike, like that it works perfectly, I wanted to understand why the CREATE TABLE instruction does not. — Marco De Virgilis
– Marco De Virgilis, Commented Jun 13, 2018 at 14:03
I thought CREATE TABLE was something from SQL itself w3schools.com/sql/sql_create_table.asp — Marco De Virgilis
– Marco De Virgilis, Commented Jun 13, 2018 at 14:04
Interesting I never used that in SQL before, not sure why it wouldn't work — Mike
– Mike, Commented Jun 13, 2018 at 14:33

G. Grothendieck · Accepted Answer · 2018-06-13 14:40:49Z

1

That is a warning, not an error. The warning is caused by a backward incompatibility in recent versions of RSQLite. You can ignore it since it works anyways.

The sqldf statement that is shown in the question

creates an empty database
uploads the diamonds data frame to a table of the same name in that database
runs the create statement which creates a second table tbl1 in the database
returns nothing (actually a 0 column 0 row data frame) since a create statement has no value
destroys the database

When using sqldf you don't need create statements. It automatically creates a table in the backend database for any data frame referenced in your sql statement so the following sqldf statement

sqldf("select * from diamonds")

will

create an empty database
upload diamonds to it
run the select statement
return the result of the select statement as a data frame
destroy the database

You can use the verbose=TRUE argument to see the individual calls to the lower level RSQLite (or other backend database if you specify a different backend):

sqldf("select * from diamonds limit 3", verbose = TRUE)

giving:

sqldf: library(RSQLite)
sqldf: m <- dbDriver("SQLite")
sqldf: connection <- dbConnect(m, dbname = ":memory:")
sqldf: initExtension(connection)
sqldf: dbWriteTable(connection, 'diamonds', diamonds, row.names = FALSE)
sqldf: dbGetQuery(connection, 'select * from diamonds limit 3')
sqldf: dbDisconnect(connection)
  carat     cut color clarity depth table price    x    y    z
1  0.23   Ideal     E     SI2  61.5    55   326 3.95 3.98 2.43
2  0.21 Premium     E     SI1  59.8    61   326 3.89 3.84 2.31
3  0.23    Good     E     VS1  56.9    65   327 4.05 4.07 2.31

Suggest you thoroughly review help("sqldf") as well as the info on the sqldf github home page

edited Jun 13, 2018 at 14:40

answered Jun 13, 2018 at 14:22

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marco De Virgilis Over a year ago

Thank you for your explanation. one thing, why do you say that returns nothing since a create statement has no value? and how can I create the desired output using CREATE?

G. Grothendieck Over a year ago

sqldf(c("create table X as ...", "select * from X")) will return X. Please read the documentation cited at the end of my answer. It's all discussed. It may be that you want to use the lower level RSQLite package directly rather than the higher level sqldf.

Collectives™ on Stack Overflow

sqldf R Error create a table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related