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
1
vote
0 answers
RSQLite dbWriteTable not working on large data
Here is my code, where I am trying to write a data from R to SQLite database file.
library(DBI)
library(RSQLite)
library(dplyr)
library(data.table)
con <- dbConnect(RSQLite::SQLite(), "data.sqlite")
### Read the file you want to load to the…
Geet
- 2,515
- 2
- 19
- 42
1
vote
1 answer
R MYSQL Write table deletes ccertain columns
I have the following table in MySQL with the following columns
tbl= ID(Integer(11)) Keyword(Varchar(55)) Estimate(DECIMAL(10,4))
Error(DECIMAL(10,4)) CreateDate(DateTime)
Of these variables ID and CreateDate are system generated namely I…
Raghavan vmvs
- 1,213
- 1
- 10
- 29
1
vote
1 answer
Error in rsqlite_send_query(conn@ptr, statement) : near "(": syntax error
The following line of code:
sqldf("UPDATE q1_sql_1 SET MONTH_YEAR = RIGHT(MONTH_YEAR, LEN(MONTH_YEAR) - 4)")
Shows this error:
Error in rsqlite_send_query(conn@ptr, statement) : near "(": syntax error
Aneesh
- 63
- 2
- 12
1
vote
3 answers
R: How to remove values from a table that appear in another table?
I have data that looks like this:
> head(dbGetQuery(mydb, 'SELECT * FROM geneExpDiffData WHERE significant = "yes"'))
gene_id sample_1 sample_2 status value_1 value_2 log2_fold_change test_stat p_value q_value significant
1 XLOC_000219 …
user974887
- 2,309
- 3
- 17
- 18
1
vote
2 answers
Write table to multiple database connections using pipes
I've created two sqlite databases, and I'd like to use the magrittr/dplyr piping convention to write a single data frame to each dabatabse.
conn1 <- DBI::dbConnect(drv = RSQLite::SQLite(), dbname = "db1")
conn2 <- DBI::dbConnect(drv =…
Danny Morris
- 82
- 1
- 6
1
vote
1 answer
RSQLite - Create databse journal file in /var/tmp
I have a sqlite db where I create tables and append new results on daily basis. This process was manual by going in RStudio and hit run!
I decided to schedule my scripts using Jenkins and for the last 5 hours I could not run it as I kept getting the…
Altons
- 1,422
- 3
- 12
- 23
1
vote
1 answer
Error in rsqlite_send_query(conn@ptr, statement) : duplicate column name: Ret
I have a bunch of sql queries that worked fine but now, for some reason, do not work any more. The data has not changed. The code has not changed.
I keep getting this error message:
Error in rsqlite_send_query(conn@ptr, statement) : duplicate…
FG74
- 61
- 1
- 6
1
vote
0 answers
SQLite: Is it possible to change the typeof(A VALUE) from 'integer' to 'text'?
Background: I find that when I imported a *.csv file into SQLite (using RSQLite) a few values in a text affinity column got stored as 'integer' values. Unfortunately the value of the first row of that column is one of those "integer" values. Now,…
Larry Hunsicker
- 406
- 5
- 12
1
vote
1 answer
R Multiline statement when using RSQLite
I am having a transaction that needs to run on sqlite. The Transaction includes several queries. If built separately (line by line) it works but it requires the call of dbSendQuery n-times. It slows down the code quite a bit.
The ideal code should…
Michael
- 1,759
- 4
- 19
- 29
1
vote
1 answer
Create Database with ready Data
I want store my Images in Database. I know How create this database, but How I can insert Images in it?
I wanted save them in Resources and then add in database, but then I can;t delete them.
I want hold Images in android app, insert them in…
Giorgi Gusakov
- 37
- 7
1
vote
1 answer
How to match tokens in document term matrix to a separate data frame (of POS codes)
Basically I have my bag of words:
source <- VectorSource(text)
corpus <- Corpus(source)
corpus <- tm_map(corpus, content_transformer(tolower))
dtm <- DocumentTermMatrix(cleanset)
etc etc.
And I have a data frame consisting or just two columns which…
ALW94
- 23
- 2
1
vote
1 answer
Using getSRAdbFile() yields sqlite error
I'm using Bioconductor's SRAdb package. Unfortunately, when trying to download the SRAdb I run into an error in sqliteSendQuery (see below).
Someone suggested to install the latest sqlite3 version. But the problem persists.
sqlFile <-…
Axel
- 150
- 2
- 7
1
vote
1 answer
Own function in RSQL Lite Engine in R
I found this SQL code for SAS and I want to translate it into RSQL Lite.
proc sql;
create table crspcomp as
select a.*, b.ret, b.date
from ccm1 as a left join crsp.msf as b
on a.permno=b.permno
and intck('month',a.datadate,b.date)
between 3 and…
Lorenzo
- 11
- 3
1
vote
0 answers
RSQLite, how to add column names to tables?
I have dumped several .txt files to an SQLite database on my computer's hard disk using RSQLite package. Since the .txt files have no headers, I have to use the "header = FALSE" argument. Here is how my codes look:
for (i in (1:8))…
QY Luo
- 331
- 1
- 4
- 11
1
vote
1 answer
How to import a data frame in RSQLite with specifying column's constrains?
I am trying to put a large data frame into a new table of a database. It could be done simply done via:
dbWriteTable(conn=db,name="sometablename",value=my.data)
However, I want to specify the Primary keys, foreign keys and the column Types like…
monabiyan
- 9
- 2