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
2
votes
1 answer
Update SQLite database without downloading data locally
I have the following 2 dataframes:
library(tidyverse)
library(RSQLite)
df1 <- data.frame(user_id=c("A","B","C"),
transaction_date=c("2019-01-01","2019-01-01","2019-01-01"))
df2 <- data.frame(user_id=c("C","D","E"),
…
Rez99
- 359
- 1
- 4
- 15
2
votes
1 answer
RSQLite parametrised query with vectors as parameters
I am new to SQL and its syntax, and I cannot understand how to pass multiple values (e.g. vector or list) to a single parameter in a parametrised query in R using RSQLite.
I have a two tables database (myTCGA) with data coming from RNASeq data. The…
fra
- 832
- 6
- 14
2
votes
1 answer
Session specific RSQLite table
I am writing an app using shiny where users can add, edit or copy data. The idea is that after they have entered there data in the temporary table it is uploaded to the main database and the table is reset.
I have made a table using RSQLite with…
Nivel
- 629
- 4
- 12
2
votes
1 answer
enabling SQLite's generate_sequence to be used in R's sqldf library under MS windows
I am hoping to use SQLite3's generate_series Table-Valued Function from within R's sqldf library, which in turn uses the RSQLite library. I expect once installed/configured, I will be able to call it as:
sqldf('SELECT value FROM…
malcook
- 1,686
- 16
- 16
2
votes
1 answer
rsqlite table and lubridate
I tried to deal with the table in r database. The code I wrote is as follows.
con <- dbConnect(RSQLite::SQLite(), "flights.sqlite")
airports1 <- dplyr::tbl(con, "airports")
flights1 <- dplyr::tbl(con, "flights")
airportcounts <- flights1 %>%…
shark
- 21
- 1
2
votes
0 answers
Error in connecting to database using RSQLite
I encountered an error while I was trying to connect to database using RSQLite.
The following is my code:
library(DBI)
library(RSQLite)
con = dbConnect(SQLite(), dbname="C:/database/Forms/20183.sqlite")
dbSendQuery(conn=con,
…
C. Bae
- 21
- 1
2
votes
1 answer
RSQLite Warning: "Too many SQL variables"
I was having an issue getting the following error with RSQLite and was having trouble diagnosing the problem:
Error in result_create(conn@ptr, statement) : too many SQL variables
The database showed the correct, fixed number (24) of columns and…
chasemc
- 849
- 6
- 12
2
votes
1 answer
Using Datatypes with RSQLite
I create a database in language R using package RSQLite with the following code.
con <- dbConnect(RSQLite::SQLite(), "TEST.db")
dbSendQuery(con, "CREATE TABLE TabA (C1 INTEGER NOT NULL, C2 INTEGER NOT NULL, C3 REAL, PRIMARY KEY…
Joe
- 21
- 1
2
votes
2 answers
Appending new data to sqlite db in R
I have created a table in a sqlite3 database from R using the following code:-
con <- DBI::dbConnect(drv = RSQLite::SQLite(),
dbname="data/compfleet.db")
s<- sprintf("create table %s(%s, primary key(%s))", "PositionList",
…
Dhiraj
- 1,650
- 1
- 18
- 44
2
votes
0 answers
check Primary key in sqldf package
While creating a database using the sqldf package, I created a table named Company Master. I want to link it to different tables but before doing that i wanted to check if the format is correct it or not. In MySQL it tells immediately if the format…
mrigank shekhar
- 544
- 3
- 15
2
votes
2 answers
storing a vector in a relational database
In my calculations I get some results which are stored in a vector. And because these calculations are executed repeatedly I will have some vectors to be stored in my database.
In my database table data_storage in each row a result vector should be…
tueftla
- 369
- 1
- 3
- 16
2
votes
2 answers
RSQLite Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) : namespace ‘DBI’ 0.4-1 is being loaded, but >= 0.8 is required
When I load sqldf package, I got error message below. How can I fix this?
install.packages("sqldf", dep = TRUE)
library(sqldf)
RSQLite
Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) :
namespace ‘DBI’ 0.4-1 is being…
melik
- 1,268
- 3
- 21
- 42
2
votes
3 answers
Is it possible to run SQL query directly on class tbl_sql (or tbl_dbi)?
Example code:
library(DBI)
library(RSQLite)
# will require more details (like user, password, host, port, etc.)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
data(USArrests)
dbWriteTable(con, "USArrests", USArrests)
dbListTables(con)
d0 <-…
Tal Galili
- 24,605
- 44
- 129
- 187
2
votes
3 answers
Loading data with RSQLite which has quoted values
I am trying to load a large-ish csv file into a SQL lite database using the RSQLite package (I have also tried the sqldf package). The file contains all UK postcodes and a variety of lookup values for them.
I wanted to avoid loading it into R and…
Tumbledown
- 1,887
- 5
- 21
- 33
2
votes
2 answers
Changing sqlite temp folder using RSQLite
Using RSQLite in RStudio I run into the following error when running large queries
Error in rsqlite_send_query(conn@ptr, statement) :
database or disk is full
as the underlying process works on /dev/xvda1. I've got plenty of space on /mnt/tmp,…
user2105469
- 1,413
- 3
- 20
- 37