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
3
votes
1 answer
How to fix "'blob' package not found" when installing RSQLite?
I want to use the RSQLite package, but when I try to use the library, I get this error:
Error: package or namespace load failed for ‘RSQLite’ in loadNamespace(j <- i[[1L]], c(lib.loc, .libPaths()), versionCheck = vI[[j]]): there is no package called…
Malik Williams
- 31
- 2
3
votes
1 answer
Create variable in database using ALTER TABLE and UPDATE
I have a 50GB SQLite database file and I want to calculate and add new variables. Can you leverage Moody_Mudskipper's function or something using ALTER TABLE and UPDATE to create the variable instead of an entire table?
library(dbplyr)
…
Geet
- 2,515
- 2
- 19
- 42
3
votes
4 answers
R dbGetQuery with dynamic string
From This post and This post, I got a way to write an rsqlite dynamic command. However, it doesn't work for me. My data looks like:
Id <- c(34, 22, 86)
sqlcmd <- paste("select col1, col2 from DB where ItemId =", Id, sep="")
Df <- dbGetQuery(conn,…
Mr369
- 384
- 4
- 17
3
votes
1 answer
How to convert data table into SQLite table using RSQLite?
I am very comfortable using R, but I've never used RSQLite or even SQLite, so I have a question on how to best use RSQLite to add data from a data frame in R to an SQLite table.
I understand that I can create tables and add data like so:
db <-…
J0HN_TIT0R
- 323
- 1
- 13
3
votes
0 answers
In R how can I use multiple prepared statements with DBI + RSQLite simultaneously?
Suppose you're trying to do a typical insert-or-update loop with RSQLite. I would expect the following to work:
library(DBI)
testdb <- dbConnect(RSQLite::SQLite(), "test.sqlite")
dbExecute(testdb, "CREATE TABLE spray_count (spray TEXT, count…
Tavin
- 390
- 2
- 13
3
votes
1 answer
Import SQLite database from GitHub
How can I import a SQLite database from a GitHub repository into my R environment?
If I have a SQLite database on my local hard I can do the following, I would like to generalize this to SQLite DB's stored on GitHub:
library("RSQLite")
db <-…
Cyrus Mohammadian
- 4,982
- 6
- 33
- 62
3
votes
0 answers
R sqldf fails with "rsqlite_query_fetch: failed first step: database or disk is full"
I'm trying to select some subsets from the dataframe in R using sqldf. So my code looks like:
library("sqldf")
...
usecase <- as.data.frame(use_case_list[[i]])
user_day_view <- sqldf("select distinct targetuser, day, count(*) from usecase group by…
Andrey Sapegin
- 454
- 8
- 33
3
votes
1 answer
Datetime values chopped in RSQLite depending on null values
I am confronted with a weird phenomenom when extracting data out of a SQLite 3 database using the RSQLite 1.0.0 package. All except the year of the datetime value gets truncated!
See an example:
Following I am extracting a DATETIME…
Alexander Meyer
- 31
- 2
3
votes
1 answer
Cannot get results from sqlite database with opencpu
I have a relatively simple function to extract some data from an sqlite database:
library(RSQLite)
db.select <- function(table="mydata", vars, rows=c()) {
vars <- paste(unlist(vars), collapse=", ")
q <- paste("SELECT ", vars, " FROM ", table,…
Bertjan Broeksema
- 1,541
- 17
- 28
3
votes
2 answers
How to store R objects (lists) in an SQLite database?
I was just wondering whether it is possible to store R lists directly in a SQLite database (and of course retrieve them) via sqldf or RSQLite.
jack
- 73
- 1
- 7
2
votes
2 answers
Fastest way to manipulate bigdata in R
I have 40+ CSV files with each being around 400MB. What I need to do is to read these 40+ big csv files, do some manipulation and formatting on them (such as commonize date formats, separating dates to months,day,etc..), and combine them in a single…
Cindy Burker
- 117
- 9
2
votes
1 answer
Can I attach one SQLite database to another with the DBI library in R?
I am trying to attach one SQLite database to another. On the terminal, I can do this pretty simply with the ATTACH command, but I am working in an R script and I want to get it to work through a DBI connection. I have been able to attach with system…
Lia_G
- 145
- 1
- 8
2
votes
1 answer
Copy Multiple data frames to SQLite db in R
I have ~ 250 csv files I want to load into SQLite db. I've loaded all the csv into my global environment as data frames. I'm using the following function to copy all of them to db but get Error: df must be local dataframe or a remote…
mr_puddles
- 97
- 7
2
votes
1 answer
RSQLite parametrized query
I am using the library RSQLite and I am struggling to understand how to use the params argument of the dbGetQuery() function.
Following the documentation, I am able to parametrize the WHERE statement of the query, but I am unable to do the same for…
davide
- 315
- 3
- 10
2
votes
2 answers
reading in many tables from SQLlite databases and combining in R
I'm working with a program that outputs a database of results. I have hundreds of these databases that are all identical in structure and I'd like to combine them into ONE big database. I'm mostly interested in 1 table from each database. I don't…
Sarah
- 65
- 4