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 types issue
This is somewhat related to RSQLite typecasting issue.
Consider the following example:
> require(RSQLite)
> DB = dbConnect(RSQLite::SQLite(),":memory:")
> dbSendQuery(DB,"create table tbl (X1 INT, X2 INT)")
> dbSendQuery(DB,"insert into tbl values…
polTo
- 290
- 1
- 4
- 12
1
vote
0 answers
Boost simple SELECT performance of SQLite database
I'm using a SQLite database (1GB+) in which there's only one table of stacked financial tick time series. I'm using RSQLite package to connect to the database. My situation is very simple: select a subset of data from one date to the other, e.g.…
Kun Ren
- 4,715
- 3
- 35
- 50
1
vote
1 answer
Get last_insert_rowid() from bulk insert
I have an sqlite database where I need to insert spatial information along with metadata into an R*tree and an accompanying regular table. Each entry needs to be uniquely defined for the lifetime of the database. Therefore the regular table have an…
ThomasP85
- 1,624
- 2
- 15
- 26
1
vote
1 answer
RSQLite - Store date columns as character in SQLite
Is there a way how I can store dates in R data frame as character strings in SQLite using RSQLite? Currently, date columns are stored as integers. I can probably cast all dates to strings before writing to SQLite, but as I need to write to SQLite…
Tomas Greif
- 21,685
- 23
- 106
- 155
0
votes
0 answers
Parallel processing querying SQL using dplyr
I've got a very large dataset (~150gbs) stored in an sql database, and I need to query certain rows based on an identifier (id) from another dataframe (df). Using a standard approach as per below it takes about 4 minutes.
dbconn <-…
Nik-D
- 31
- 3
0
votes
1 answer
How to speed up importing to data.frame?
I need to analyse a large dataset (~40Go, 3 million rows); too big to open in a spreadsheet or R. To address that, I loaded it into an SQLite database, then used R (and RSQLite) to split into parts I can manipulate (70,000 rows). I need it in…
Dimitri
- 135
- 7
0
votes
0 answers
How do I get R to find dbConnect function?
Hi,
I seem to be having trouble with RSQLite and dbConnect. DBI and RSQlite appear to be active in the packages but continue to receive the message that the dbConnect function isn't found. Any suggestions would be appreciated.
Thanks!
ajamsterdam
0
votes
0 answers
How to these use R libraries to write and save a 'some_content' string into a field of an existing SQL table object?
How to these use R libraries to write and save a 'some_content' string into a field of an existing SQL table object?
I want to insert a 'some_content' string into the field barplot inside the blog_blogpost object in SQL. How to…
0
votes
1 answer
sql keyword IN using RSQLite and DBI package
When using the RSQLite and DBI package to form a query, I wonder if there is a way to use SQL keyword IN?
I don't think SQL keyword IN is currently implemented?
e.g.
## instead of:
dbGetQuery(con, "SELECT * FROM mtcars WHERE cyl = :targetCyl" ,…
user3224611
- 84
- 1
- 7
0
votes
1 answer
delete a record from datatable based on the value of textInput Box
How can I delete a record with an action button? The delete action button executes the command properly if I hardcode what I want deleted but it gets grayed out if I want to delete dynamically by pointing at the "delete2" textInput box. For example,…
Salvador
- 1,229
- 1
- 11
- 19
0
votes
1 answer
sql separate string column into multiple columns
I have converted a dataframe intro sqlite database using rsqlite package of R programming. First column of table sh3 is of type TEXT with 23 strings separated by separater ' '. I want to convert this column into 23 columns and drop it afterwards.…
shah nawaz
- 15
- 4
0
votes
1 answer
error loading and using RSQLite in Windows
RSQLite installed normally.
First I try to connect to a database and get an error.
library(DBI)
mydb <- dbConnect(RSQLite::SQLite(), "")
Error in h(simpleError(msg, call)) :
erro na avaliação do argumento 'drv' na seleção do método para a função…
xm1
- 1,663
- 1
- 17
- 28
0
votes
0 answers
R and SQL - misuse of aggregate function AVG() but SQL inconsistent backwards and forwards
I am using RSQLite, DBI, dbplyr, and sqldf packages.
Here are the packages:
library(dbplyr)
library(RSQLite)
library(DBI)
library(sqldf)
library(tidyverse)
First I'm using mtcars dataset with the rownames included.
mtcars <-…
hachiko
- 671
- 7
- 20
0
votes
1 answer
Syntax error in SQL code being used in R, no row_number function found
I am using RSQLite, DBI, and dbplyr packages. I'm watching this YouTube video by TechTFQ.
library(dbplyr)
library(RSQLite)
library(DBI)
mtcars <- tibble::as_tibble(mtcars, rownames = 'car')
sql_mtcars <- mtcars
con <-…
hachiko
- 671
- 7
- 20
0
votes
1 answer
R using RSQLite, DBI and dbplyr, can't have more than one table in the RSQLite in order to make a join
I'm having a hard time understanding the behavior of these three packages: RSQLite, DBI, and dbplyr.
I'm using the mtcars dataframe and I'm loading it with the tibble package to add the car name as one of the rows.
library(tibble)
mtcars <-…
hachiko
- 671
- 7
- 20