Calling collect outputs missing everywhere #239
Description
I'm trying to read an SQLite database created in Python. The data is written correctly, I can read it from Python and DB Browser for SQLite), but DBInterface.execute(db, "SELECT * FROM TestTable") |> collect returns an array of rows where everything is missing.
Python code:
# sqlite_bug.py
import sqlite3
import pandas as pd
df = pd.DataFrame({'x': [1.,2,3,4], 'y': [2., 4,6,8]})
conn = sqlite3.connect('db.sqlite')
df.to_sql("TestTable", conn)
conn.close()Julia code:
# sqlite_bug.jl
using SQLite
db = SQLite.DB("db.sqlite")
println(DBInterface.execute(db, "SELECT * FROM TestTable") |> collect)Running this:
forcebru ~/test> julia sqlite_bug.jl
SQLite.Row[SQLite.Row:
:index missing
:x missing
:y missing, SQLite.Row:
:index missing
:x missing
:y missing, SQLite.Row:
:index missing
:x missing
:y missing, SQLite.Row:
:index missing
:x missing
:y missing]
forcebru ~/test>
Using DBInterface.execute(db, "SELECT * FROM TestTable") |> DataFrame instead of collect works:
forcebru ~/test> julia sqlite_bug.jl
4×3 DataFrame
Row │ index x y
│ Int64 Float64 Float64
─────┼─────────────────────────
1 │ 0 1.0 2.0
2 │ 1 2.0 4.0
3 │ 2 3.0 6.0
4 │ 3 4.0 8.0
Originally I wanted to use collect to read a single column as an array without using any 3rd-party packages like DataFrames and expected collect to return that column as an array of floats.
When I query one column with DBInterface.execute(db, "SELECT x FROM TestTable") |> collect, I still get an array of SQLite.Row where the data is missing:
SQLite.Row[SQLite.Row:
:x missing, SQLite.Row:
:x missing, SQLite.Row:
:x missing, SQLite.Row:
:x missing]
Is this how the Table.jl interface mentioned in the docs supposed to work for SQLite.jl? I think it's pretty confusing that collect returns an array of rows where the data is missing.