An R package for interfacing with SQLite databases.
RSQLite is an r package providing an interface to sqlite databases. RSQLite uses the r-dbi interface which is also used by the packages rsqlite, rpostgresql, rjdbc, and roracle.
Questions tagged [rsqlite]
219 questions
4
votes
2 answers
Save RSQLite Query Results to csv Without Reading them into R
I am working with a large SQLite database, and I am using RSQLite and Rstudio to run many different queries at the same time. Many times the results of the queries are quite large, and I don't want to waste resources by reading them into R and then…
jamesguy0121
- 1,124
- 11
- 28
4
votes
0 answers
dbWriteTable date format
Halo,
im importing tables from MS-Access into R via
channel <- odbDriverConnect(...)
foo_import <-sqlFetch(channel,"foo")
now Im using the RSQLite for SQL Querys and other stuff
dbWriteTable(conn, "foo_table", foo_import)
R_Table <-…
Leo96
- 489
- 3
- 12
4
votes
0 answers
Speeding up SQL query in R for time-series data
I have a database which contains historic river levels and rainfall data. There is a 'Levels' table which has attributes including the date at which a measurement is taken, and the river level on that date. And also a 'Rainfall' table which,…
Jemma
- 112
- 6
4
votes
3 answers
R: sqlAppendTable only works with numbers?
I am unable to get the R language DBI::sqlAppendTable function to work with anything other than numbers. Below is a bit of code illustrating the problem. I suspect the problem is that sqlAppendTable does not quote data. Any fix or workaround would…
Argent
- 885
- 2
- 9
- 18
4
votes
1 answer
Parallel query of SQLite database in R
I have a large database (~100Gb) from which I need to pull every entry,
perform some comparisons on it, and then store the results of those comparisons. I have attempted to run parallel queries within a single R sessions without any success. I can…
alexvpickering
- 632
- 1
- 8
- 20
4
votes
1 answer
Bulk-Update an SQLite column in R using RSQLite and bind.data
I am using R in combination with SQLite using RSQLite to persistate my data since I did not have sufficient RAM to constantly store all columns and calculate using them. I have added an empty column to the SQLite database using:
dbGetQuery(db,…
Sebastian Hätälä
- 955
- 12
- 34
4
votes
2 answers
RSQLite: binding sets and scalars in the same select query
How can I pass both a scalar and a set of values in the bind.data parameter of dbGetQuery() for an SQL statement like
select * from tst where x = ? and y in (?)
Here's what I tried:
> library("RSQLite")
> c <- dbConnect (SQLite())
> dbGetQuery(c,…
dan3
- 2,528
- 22
- 20
4
votes
2 answers
Factors in RSQLite
I have been unable to find documentation on how RSQLite handles factors. From a quick test (see below), it looks like they are converted to character.
Question 1: Is there any way to preserve them as factors? I can think of some kludgy ways…
Ari B. Friedman
- 71,271
- 35
- 175
- 235
3
votes
1 answer
Execute multiple SQL statements (read from file) in R using DBI and RSQLite
I have the following example code:
library(DBI)
library(RSQLite)
conn <- DBI::dbConnect(RSQLite::SQLite(), "test.sqlite" )
########
# ## This is the original code when reading from the file which is
# ## for convenience commented out here.
# sql…
Rainer
- 8,347
- 1
- 23
- 28
3
votes
1 answer
Creating an SQLite DB in R from an CSV file: why is the DB file 0KB and contains no tables?
I have a 9GB .csv file and would like to convert it to an sqlite data base.
I have followed https://datacarpentry.org/R-ecology-lesson/05-r-and-databases.html#Connecting_to_databases and it works on my local machine but on a server it says the…
HCAI
- 2,213
- 8
- 33
- 65
3
votes
1 answer
Create SQLite table with primary key in R
I am trying to create a table with a primary key for an SQLite database. Is there a way to do this with DBI? I have looked in the documentation but cannot see any examples. I am trying something like the example…
rjss
- 935
- 10
- 23
3
votes
1 answer
Extremely slow simple SQLite queries from external drive
I have a SQLite database on an external hard drive, and I would like to analyze a couple thousand subsets of it in R (or Python). The data are about 110 million observations of 13 variables, and it is stored (~10.5 GBs) on an external drive because…
kwinsler
- 31
- 4
3
votes
1 answer
R dbReadTable returns no such table error
I ran this bit of code yesterday successfully, but today, I am getting a 'no such table' error. I am trying to pull data in a table (named tblLatjamInputs) from an SQLite database into R using DBI::dbReadTable(), but it is acting as though the table…
Laura
- 119
- 1
- 9
3
votes
0 answers
Reading data directly from Binary Large OBject (BLOB)
I'm trying to create a SQLite database to store (XLS) files for my Shiny app. Idea is that people would not have to re-upload their content each session - it will be saved and waiting for them. I want to store raw files - currently I am using only…
Kamil Sijko
- 63
- 6
3
votes
1 answer
How to make a good reproducible SQL database example for RSQLite in R
I need to ask a question about RSQLite but cannot share my database. How can I make a good reproducible SQLite database example within R?
Say a table of this shape:
df<-data.frame(
date=as.POSIXct(c("12/11/2019 12:00","12/11/2019…
Nakx
- 1,460
- 1
- 23
- 32