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
0
votes
1 answer
RSQlite - Find values with most occurences in group
I'm using RSQlite to import Datasets from an SQlite-Database. There are multiple millions of observations within the Database. Therefor I'd like to do as much as possible of Data selection and aggregation within the Database.
At some point I need to…
thuettel
- 165
- 1
- 11
0
votes
1 answer
Recode sqlite column values using dplyr
I have an excessively big data saved as SQLite database. I need to recode the values of some of the columns but unfortunately my code generate error that i really can't debug. Here is the code i'm using.
library(RSQLite)…
DreamNet
- 627
- 3
- 8
- 24
0
votes
1 answer
Issue Trying to Load .xlsx from Shiny to Database
I am having a hard time trying to code a Shiny app that lets the user upload a file and then with a Button it is supposed to create a new table on a Database that's connected through RSQLite, please see code below:
ui.R:
navbarPage(
"Ingreso de Data…
0
votes
2 answers
RSQLite dbGetQuery with input from Data Frame
I have a database called "db" with a table called "company" which has a column named "name".
I am trying to look up a company name in db using the following query:
dbGetQuery(db, 'SELECT name,registered_address FROM company WHERE LOWER(name) LIKE…
Valentin Franke
- 95
- 5
0
votes
0 answers
if statement in function is not executing
I have created a database with RSQLite, the connection=conn. In this Database is a Datatable dummy with keyand text.
conn <- dbConnect(RSQLite::SQLite(), "~/path.db")
dbExecute(conn,"CREATE TABLE IF NOT EXISTS 'dummy' (
key CHAR(27)…
Flow91
- 63
- 6
0
votes
0 answers
RSQLite only pretends to write to a database table with a bracketed name
I want to write data to a table called [foo/bar] inside of a SQLite database created through RSQLite. I wrote the following function to save answers to disk.
save_to_disk = function(data) {
con = dbConnect(SQLite(), "responses.db")
path =…
wdkrnls
- 4,548
- 7
- 36
- 64
0
votes
0 answers
Importing dates from SQL to R
I'm trying to exploit the wild fires data set from kaggle (https://www.kaggle.com/rtatman/188-million-us-wildfires).
Specifically I want to use the "DISCOVERY_DATE" variable, and other date variables but I cannot manage to keep them as date format…
flafont11
- 137
- 7
0
votes
1 answer
Selecting one item in a list at a time
Is there a way to select one item in a list at a time? I've attempted this with the get() function, but I did not have any luck. I have 3537 character items in a list. I would like to select one character item at a time, run a set of code on using…
John Huang
- 845
- 4
- 15
0
votes
1 answer
For loop to read in multiple tables from SQLite database
I would like to create a for loop that reads in multiple tables from a SQLite database. I would like it to either read the first 300 tables, but ideally I would like to get it to read 300 random tables from my database into R.
For each table read…
John Huang
- 845
- 4
- 15
0
votes
1 answer
Status Bar in for loop
I was trying to create a status bar in my for loop, but the status remains at 0%. I'm not sure what the issue could be.
n <- length(unique(mu_ut$AnimalID))
pb <- txtProgressBar(min = 0, max = n, style = 3)
for(i in unique(mu_ut$AnimalID)){
…
John Huang
- 845
- 4
- 15
0
votes
1 answer
Creating Multiple SQL Tables based on the factors from a column
I am trying to create a SQLite table for each factor that I have in a R dataframe. I have created a for loop in an attempt to accomplish this, but when I include the dWriteTable() function it gives me an error. I believe it might have something to…
John Huang
- 845
- 4
- 15
0
votes
1 answer
Using R to Query a SQLite file Help: DBI::dbGetQuery() never completes, very slow
I have a SQLite file that contains a table with 9 million or so rows and 30+ columns. Up until a few days ago, the following code worked fine:
path <- file.path(basepath, "Rmark_web", "SQL_Data", "2020Q3_Enterprise_Exposure_Wind.sqlite")
cn <-…
Ryan D McCrary
- 3
- 3
0
votes
0 answers
Using R Shiny User Input with RSQLite
I want to create a shiny app that takes the first date range as inputs for the SQL command to query from the RSQLite db I created. However, when I run this without having a df object prior to running the app, it does not work. If I try to update the…
casanoan
- 27
- 1
- 5
0
votes
1 answer
R SQLite issue from Python
SO i am trying to run an Rscript from python, the rscript is using Rsqlite
here is the python call
subprocess.call(['C:\\Program Files\\R\\R-4.0.1\\bin\\Rscript', 'C:\\Users\\A\\Documents\\sql\\master\\test.R'])
here are the contents of the test.R…
Alex Dinan
- 1
- 2
0
votes
2 answers
How to retrieve the minimum date having 3 columns (day), (month) and (year) with RSQLite?
I am working with SQL in R. I want to get the minimum date by choosing the minimum value of the column 'day;, the minimum value of the column 'month' and the minimum value of the column 'year'.
I have tried this by the following code:
dbGetQuery(db,…
Elaji
- 17
- 4