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
1 answer
R Export SQLiteResult Recordset to Excel or csv
I am using the RSQLite package to write data to a local SQLite database successfully.
I want to be able export the results of a select query to Excel. The issue is that the class of the resultant recordset is:
>class(rs)
[1]…
Baldy
- 47
- 4
1
vote
1 answer
How to insert values to the SQLite database from R.Data?
I am trying to insert data to sqlite database from R data.frame but I failed.
Here is the code in R studio I used.
So there two loops using which I'm trying to load all dbf files listed in the specified folders (working directory). And then I'm…
David Bijoyan
- 117
- 9
1
vote
1 answer
Unique identifier not recognized by SQL query
I am making a table for users to fill out in Shiny using SQLite. At the end of each session I want to delete all entries containing the unique sessionID:
library(RSQLite)
library(pool)
library(DBI)
#Generates unique token. For example…
Nivel
- 629
- 4
- 12
1
vote
1 answer
Is it possible to run sql geometry functions on a geopackage within R
I have a geopackage containing millions of traffic points as well as a highway zone layer. I would like to extract points inside the highway zones within a R-script.
It is possible to query the attribute data using the library(RSQLite) and…
Kempie
- 263
- 2
- 10
1
vote
0 answers
How to import file sas7bdat into R with where clause
I would like to import a file in .sas7bdat into R. The file is very big, so I need to import only the rows that match given criteria.
With a file .csv, the code would be
library(sqldf)
read.csv.sql("mtcars.csv", sql = "select * from file where carb…
John
- 11
- 3
1
vote
2 answers
RSQLite - dbWriteTable - field.type - How to get proper Date format?
RSQLite::dbWriteTable function, properly pass Dates from df to db.
I have some date fields on my df, and when I try to write the table on an SQLite db using the aforementioned function and setting field.types = c(col1 = "Date", col2 = "Date") it…
Mirko Gagliardi
- 69
- 1
- 10
1
vote
0 answers
How can I connect to SQLite on an FTP server in R?
I have a SQLite database located on a Windows VM that can be accessed via sftp. I am unsure how to access it from within R.
Currently, I can use RCurl to get a listing of files, but really can't figure out the rest. Is it possible to do such a…
Scott Stoltzman
- 363
- 1
- 15
1
vote
1 answer
How to extract using dplyr filter on first column of table in sqlite DB
I am trying to extract information from an sqlite table using dplyr.
Norway <- tbl(conn, "own_fleet") %>% (mmsi==235060247) %>% filter(timestamp>='2018-08-16T00:00:01') %>% collect()
This results in an error:
Error in eval(rhs, env, env) : object…
Dhiraj
- 1,650
- 1
- 18
- 44
1
vote
1 answer
sqldf in R generating an Error
I am trying to merge two tables in R by using the following command:
library(gsubfn)
library(proto)
library(RSQLite)
library(sqldf)
sqldf("SELECT Gender, Pmu.try.Sat.Ltd, Online.Campus.Student, Residency,
tutorial_avg_score
…
Sandy
- 1,100
- 10
- 18
1
vote
1 answer
RSQLite does not create local database
Reprex:
con <- DBI::dbConnect(RSQLite::SQLite(), path = "test.sqlite")
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
[1] "mtcars"
dbDisconnect(con)
When I come back:
con <- DBI::dbConnect(RSQLite::SQLite(), path =…
skurp
- 389
- 3
- 13
1
vote
1 answer
Collect() from table in SQLite in R
I am trying to use the collect() function from dplyr to query a table (comp_fleet) in SQLite. The following code works:
Cposns <- tbl(db_con,"comp_fleet") %>%
arrange(mmsi, desc(timestamp))%>%
dplyr::filter(!is.na(lat)) %>% collect()
However…
Dhiraj
- 1,650
- 1
- 18
- 44
1
vote
2 answers
Unknown error in sqldf package
What does this error mean?
Closing open result set, pending rowsError in result_create(conn@ptr, statement) : near "(": syntax error
This appears when i want to run the code
```{r}
library(sqldf)
first <- dbConnect(SQLite(), dbname=…
rahul yadav
- 432
- 3
- 20
1
vote
0 answers
dbSendquery giving format error
I need to create a table and insert some values into it uisng r
library(sqldf)
Company_Master <- dbConnect(SQLite(), dbname="Company Master.sqlite" )
dbSendQuery(conn = Company_Master,
"CREATE TABLE Company Master(
…
rahul yadav
- 432
- 3
- 20
1
vote
2 answers
Package sqldf download error
I was trying to install the sqldf package in rstudio server by the code
install.packages("sqldf")
but when i tried downloading this the following error came
Installing package into ‘/home/mrshekar/R/x86_64-pc-linux-gnu-library/3.4’
(as ‘lib’ is…
mrigank shekhar
- 544
- 3
- 15
1
vote
1 answer
correct usage of the FOREIGN KEY function
In my database I have two tables named artists and tracks. In the creation of the tracks table I set the FOREIGN KEY to artist. But when I run this shown code it is no problem to insert a track row with an non-existing artist.
That's definitely not…
tueftla
- 369
- 1
- 3
- 16