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
2
votes
2 answers

Create RSQLite from csv containing umlauts - then read in using dplyr

I have a large csv file containing umlauts (Ä/Ö) that I need to transform to a RSQLite database to analyse in parts using dplyr. However, when I read in data from the database, the umlauts do not reproduce correctly. Ä becomes and Ö becomes…
Maarölli
  • 375
  • 1
  • 3
  • 13
2
votes
3 answers

When does dplyr's mutate function work with a database?

I try to use dplyr's mutate() function on a SQL like database. I don't understand why it work sometimes and doesn't other times. Here is one example with the stantard example from…
PAC
  • 5,178
  • 8
  • 38
  • 62
2
votes
1 answer

How to perform parameter substitution in SQL queries using R?

Programming SQL-specific stuff in Python I've got used to always use parameter substitution when executing plain SQL queries, like this: # Never do this -- insecure! symbol = 'RHAT' c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol) #…
R Kiselev
  • 1,124
  • 1
  • 9
  • 18
2
votes
0 answers

Writing arbitrary R objects to SQLite database

I'm trying to store large list objects created in R to an SQLite database via RSQLite. Since These list objects contain several 2d and 3d matrices, I'd like store them as individual entries. I read serializing these and storing them as blobs does…
user3641187
  • 405
  • 5
  • 10
2
votes
0 answers

RSQLite DB for a Large CSV Dataset

I have a CSV file that is structured as follows: Date (mm/dd/yyyy) Experiment Number (1) Header (several lines of text) colN1 colN2 colN3 ... data11 data12 data13 ... data21 data22 data23 ... ... Repetitive up to experiment 52. Now, the number…
Kimlotte
  • 55
  • 6
2
votes
1 answer

Error: protect(): protection stack overflow while writing file with dbWriteTable (RSQLite)

I attempt to write a data.frame with 285,476 rows and 17 columns into the SQlite database using dbWriteTable from RSQLite package. I get a following error: Error: evaluation nested too deeply: infinite recursion / options(expressions=)? I have…
Martin Bulla
  • 41
  • 1
  • 3
2
votes
0 answers

Data frame with different encodings in R

Here is a snippet from data encoding in R memory. The CSV file was read with encoding "Latin-1" using data.table::fread. As this piece suggests, the data is stored with different encodings, which is not desirable because I'll leave the data in a…
Marie-Eve
  • 565
  • 4
  • 15
2
votes
0 answers

rsqlite_query_send: could not execute1: unable to open database file

I'm hitting this weird database connection issue with RSQLite that I need to work around somehow. I have documented how to reproduce this issue at the link below: https://github.com/rstats-db/RSQLite/issues/101 > dbSendQuery(db, "create table hfp…
Anthony Damico
  • 5,779
  • 7
  • 46
  • 77
2
votes
0 answers

dbWriteTable Load Failure

I have been observing strange behavior from DBI:::dbWriteTable which seems to be connected to the occurrences of single quotes in input fields of the files I am attempting to read in. Here is a minimally reproducible example: library(RSQLite) con <-…
mlegge
  • 6,763
  • 3
  • 40
  • 67
2
votes
1 answer

Loading SQLite table in R with RSQLite

I had this function I used to load a SQLite table sqLiteConnect <- function(database, table) { library(DBI) library(RSQLite) con <- dbConnect("SQLite", dbname = database) query <- dbSendQuery(con, paste("SELECT * FROM ", table, ";", sep=""))…
CptNemo
  • 6,455
  • 16
  • 58
  • 107
2
votes
0 answers

CSV to SQLite to R: too many quotes for character columns

I'm trying to create a workflow where I Create a SQLite database using R Load some (potentially large -- larger than can fit in memory) CSV files into the database as tables Do analysis with dplyr It seems like something below is very close to…
kevinykuo
  • 4,600
  • 5
  • 23
  • 31
2
votes
0 answers

Importing SQLite integer column which is > 2^31-1

I have a table in a SQLite database DB that I want to import in R. Yet one column integer of table is a SQLite integer datatype that might assume values > 2^31-1, then more than what supported by the R integer dataype. Yet RSQLite will proceed to…
CptNemo
  • 6,455
  • 16
  • 58
  • 107
2
votes
1 answer

How to add a SQLite temp table from an R dataframe?

I have an SQLite database connection to a database file. I want to extract some data from one of the tables, do some processing in R and then create a temporary table on the same connection from the processed data. It needs to be a temp table…
dspringate
  • 1,805
  • 2
  • 13
  • 20
2
votes
3 answers

Appending to existing SQLite table when addition has fewer columns, without reading database into R

Is there some simple way, either on the SQL side or on the R side, to append a data.frame to an existing table that has more columns? The missing columns should just be filled in with NAs. Bonus points if it gracefully handles table 2 having more…
Ari B. Friedman
  • 71,271
  • 35
  • 175
  • 235
2
votes
2 answers

Selecting only 500 rows from table in R

I am using RSQLite package for database operations. For selecting data from table, I used the following code, but I got only 500 rows. Actually this table contains 1200+ rows for that query. What is the cause of this problem? library('RSQLite') lite…
Dinoop Nair
  • 2,663
  • 6
  • 31
  • 51
1 2 3
…
14 15