Stack Overflow
Stack Overflow

Questions tagged [rsqlite]

An R package for interfacing with SQLite databases.

RSQLite is an package providing an interface to databases. RSQLite uses the interface which is also used by the packages , , , and .

Repositories

Other resources

Related tags

219 questions
9
votes
1 answer

Update a table using subquery in SQLite

I want to add a column to my table using ALTER TABLE and UPDATE statements not to recreate the full table. When using a subquery in my UPDATE statement I don't get the output I expect. build reproducible…
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
7
votes
2 answers

How do I limit the memory usage of duckdb in R?

I have several large R data.frames that I would like to put into a local duckdb database. The problem I am having is duckdb seems to load everything into memory even though I am specifying a file as the location. Also, it isn't clear to me the…
Kevin
  • 107
  • 5
7
votes
0 answers

Listing database tables for sqlite in R

I am unable to list tables for the sqlite database I am connecting to from R. I setup the tables in my database (WBS_test1.db) using "DB Browser" https://sqlitebrowser.org/ Looking at this db in the command window, I am able to list the tables via…
dphuber
  • 71
  • 1
  • 3
7
votes
1 answer

Adding column to sqlite database

I am trying to add a vector which I generated in R to a sqlite table as a new column. For this I wanted to use dplyr (I installed the most recent dev. version along with the dbplyr package according to this post here). What I tried:…
Alex
  • 4,925
  • 2
  • 32
  • 48
6
votes
3 answers

Error with R package biomaRt and This dependency RSQLite

I have a problem to install biomaRt with bioconductor. I have already install this package without error in Rstudio with R 3.6 but with R 4.0 in conda specific environment container, I have an error with RSQLite. Here this message…
William Jarassier
  • 81
  • 3
6
votes
2 answers

How do I "flush" data to my RSQLite disk database?

I'm creating a database using R package dbplyr, using RSQLite, but my database is zero-bytes in size on disk despite my writing (and reading back) a table. Here is my script: library("RSQLite") library("dbplyr") library("dplyr") data(mtcars) con…
Thomas Browne
  • 23,824
  • 32
  • 78
  • 121
6
votes
1 answer

What happens to date data in a database when using RSQLite?

I've started working with RSQLite and dplyr to efficiently process large datasets. However, I haven't been able to reconcile how to get RSQLite to format dates or what best practices are here. The example below should illustrate where the process…
boshek
  • 4,100
  • 1
  • 31
  • 55
6
votes
2 answers

sqldf : create table from data frame error: "no such table". and two tables created instead of one

I've recently upgraded R, RSQLite, and sqldf (versions below). Normally: sqldf('create table foo as select * from bar', db = 'test.db') should create a table called 'foo' in an attached sqlite database, using data frame 'bar' if it exists to load…
Nathan Siemers
  • 423
  • 4
  • 8
6
votes
2 answers

RSQLite query with user specified variable in the WHERE field

I am using a the RSQLite library in R in to manage a data set that is too large for RAM. For each regression I query the database to retrieve a fiscal year at a time. Now I have the fiscal year hard-coded: data.annual <- dbGetQuery(db, "SELECT *…
Richard Herron
  • 9,760
  • 12
  • 69
  • 116
5
votes
1 answer

RSQLite: How to increase number of columns and parameters?

I often work with big CSV files (>50GB) with >10,000 columns. I'm thinking of reading the information into RSQLite so that I can do easy querying and subsetting. ISSUE SQLite has a limit of 2000 fields and 999 host parameters in a single SQL (see…
NoviceProg
  • 815
  • 1
  • 10
  • 22
5
votes
1 answer

How to check if a "Send Query" is over?

I need to check if my res from dbSendQuery() is over. My code is like this: db <- dbConnect(drv=SQLite(),flags=SQLITE_RW,dbname="db.sqlite",synchronous = "off") dbBegin(db) res <- dbSendQuery(db,"Update Operation SET Name = 'teste' where Id =…
Walercy Rodrigues de Oliveira
  • 51
  • 3
5
votes
1 answer

Disconnect DBI / RSQLite within a function in R

I'm building a package for internal usage and attempting to abstract away all possible database interaction from the users. I need to connect to the database and disconnect from the database within the function (I think). However, the disconnect…
Scott Stoltzman
  • 363
  • 1
  • 15
5
votes
3 answers

Adding value to existing database table in RSQLite

I am new to RSQLite. I have an input document in text format in which values are seperately by '|' I created a table with the required variables (dummy code as follows) db<-dbconnect(SQLite(),dbname="test.sqlite") dbSendQuery(conn=db, "CREATE…
Sanju
  • 169
  • 2
  • 8
4
votes
0 answers

RSQLite why and when to use disconnect

I'm building a shiny app that connects to a SQLite database. It enables the user to see tables and update and insert data. Now my question is whether it is better to connect once at the start of the app and disconnect once when closing it. Or…
Johannes69
  • 41
  • 1
4
votes
2 answers

Use RSQLite to manipulate data frame in r directly using SQL

I have a data set of the form that I would like to change to this form below in R using SQL. I know that I could do this daily simply with dplyr but the point here is to learn to use SQL to create and manipulate a small relational database. Price…
user849541
  • 176
  • 8
1
2 3
…
14 15