[R] RSQLite to input dataframe

[Michael Bedward]

Hi Amy,

I'm not sure if I understand your question correctly so let me know if
the following is off track.

Starting with your example, here is how to create a data.frame and
write it to a new table in a new database file...

my.data = data.frame(X = c("US", "UK", "Canada", "Australia",
"Newzealand"), Y = c(52, 36, 74, 10, 98))

drv <- dbDriver("SQLite")
con <- dbConnect(drv, "myfilename.db")
dbWriteTable(con, "sometablename", my.data)

To verify that the table is now in the file...

dbListTables(con)

To check the fields in the table (should match the colnames in your
data.frame)...

dbListFields(con, "sometablename")

To read the whole table into the workspace as a new data.frame

my.data.copy <- dbReadTable(con, "sometablename")

If you have data in a CSV file, and the contents are small enough to
read in one go, you would use the read.csv function to read the
contents of the file into a data.frame and then use dbWriteTable to
transfer this to your database.

Hope this helps,

Michael


On 4 January 2011 21:43, Amy Milano <milano_amy at yahoo.com> wrote:
> Dear r helpers,
>
> At first, I apologize for raising a query which seems to be a stupid interpretation on my part. I am trying to learn SQLite.
>
>
>
> Following is an example given in the RSQLite.zip file (Page # 4)
>
> drv <- dbDriver("SQLite")
> tfile <- tempfile()
> con <- dbConnect(drv, dbname = tfile)
> data(USArrests)
> dbWriteTable(con, "arrests", USArrests)
>
>
> On the similar line I am trying to read my data.
>
> Suppose I have a dataframe as given below.
>
> DF = data.frame(X = c("US", "UK", "Canada", "Australia", "Newzealand"), Y = c(52, 36, 74, 10, 98))
>
> drv <- dbDriver("SQLite")
> tfile <- tempfile()
> con <- dbConnect(drv, dbname = tfile)
> data(DF)
> dbWriteTable(con, ......., .......) # Didn't know what to write here.
>
> I understand I have raised a query in a stupid manner. I need to understand is there any way I can use SQLite to read
>  dataframe or for that matter any csv file say e.g. 'DF.csv'.
>
> Please enlighten me.
>
> Amy
>
>
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> 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.
>