CODEWITHSUNDEEP
rsqlite
njfrazie
njfrazie

Reputation: 91

In R: dbGetQuery() coerces a string to a numeric and causes problems

I have a table in a sqlite database with the following schema

CREATE TABLE os_logs (version STRING, user STRING, date STRING);

I set the following command to a variable called cmd. 

select count(*), version
  from os_logs
  group by version
  order by version;

After I send that command through dbGetQuery I get numeric results back for version instead of a string.

db <- dbConnect(SQLite(),"./os_backup.db")
dbGetQuery(db,cmd)

count(*)    version
1421    NA
1797    0.7
6   0.71
2152    0.71
1123    0.72
3455    1
2335    1

The versions should be

0.70.1111_Product
0.71.22_Dev
0.71.33_Product
...

Any idea on why the strings in my sqlite database are being turned into numerics in R? If I do that command on the sql cmd line it works perfectly

Edit: Here is how the tables are created. (With more info since I edited it out in the original question.

drop table vocf_logs;

CREATE TABLE vocf_logs (version STRING, driver STRING, dir STRING, uuid STRING PRIMARY KEY, t_start STRING);
CREATE TABLE log_os (uuid STRING PRIMARY KEY, os STRING);

.separator ","
.import vocf_dirs.csv vocf_logs

-- Put the OsVersion info from name_redacted into the table
UPDATE vocf_logs
SET version=(select log_os.os from log_os where uuid = vocf_logs.uuid);

Upvotes: 0

Views: 1283

Answers (2)

njfrazie
njfrazie

Reputation: 91

The creation of the original table was wrong. The method in R was correct. From the data type descriptions found here: https://www.sqlite.org/datatype3.html.

The declared type of "STRING" has an affinity of NUMERIC, not TEXT.

When table was created using type TEXT it worked as expected.

Upvotes: 0

while
while

Reputation: 3772

What you describe should work fine. You must have done something differently or inserted it incorrectly to the db.

Here is a step by step test that does the exact same and works:

# Load package and connect
R> library(RSQLite)    
R> db <- dbConnect(SQLite(),"./os_backup.db")

# Create db and insert data
R> dbSendQuery(db, "CREATE TABLE os_logs (version STRING, user STRING, date STRING);")
R> dbSendQuery(db, "INSERT INTO os_logs VALUES ('0.70.1111_Product', 'while', '2015-04-23')")
R> dbSendQuery(db, "INSERT INTO os_logs VALUES ('0.70.1111_Product', 'while', '2015-04-24')")
R> dbSendQuery(db, "INSERT INTO os_logs VALUES ('0.71.22_Dev', 'while', '2015-04-24')")

# Run query counting versions
R> dbGetQuery(db, "SELECT version, count(*) FROM os_logs GROUP BY version ORDER BY version;")

           version count(*)
1 0.70.1111_Product        2
2       0.71.22_Dev        1

Upvotes: 2

Related Questions