Write DataFrame to SQLite table?

dataframes

#1

I’m trying to figure out how to write a data frame to a sqlite3 table using SQLite.jl, but can’t wrap my head around the docs?


#2

I figured it out.

db = SQLite.DB(db_path)
SQLite.load(db,"tablename",dataframe)


#3

SQLite.load(db, "tablename", dataframe) is now deprecated.

SQLite.load!() does not work for me. I can see the db becomes the third arg, but load! does not seem to like DataFrames. instead, it is asking for Tables.jl implementation, which DataFrames does not seem to be.

so how do I write a DataFrame to an SQL table now?


#4

my best practice:

one of the example from the test:

               _
   _       _ _(_)_     |  Documentation: https://docs.julialang.org
  (_)     | (_) (_)    |
   _ _   _| |_  __ _   |  Type "?" for help, "]?" for Pkg help.
  | | | | | | |/ _` |  |
  | | |_| | | | (_| |  |  Version 1.0.1 (2018-09-29)
 _/ |\__'_|_|_|\__'_|  |  Official https://julialang.org/ release
|__/                   |

julia> using SQLite, DataFrames

julia> db = SQLite.DB("./mytest.sqlite")
SQLite.DB("./mytest.sqlite")

julia> dt = DataFrame(zeros(5, 5))
5×5 DataFrame
│ Row │ x1      │ x2      │ x3      │ x4      │ x5      │
│     │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 2   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 3   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 4   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 5   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │

julia> tablename = dt |> SQLite.load!(db, "temp")
"temp"

julia> r = SQLite.Query(db, "select * from $tablename") |> DataFrame
5×5 DataFrame
│ Row │ x1       │ x2       │ x3       │ x4       │ x5       │
│     │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │
├─────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ 1   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 2   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 3   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 4   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 5   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │

julia> 

#5

the save works perfectly. the query works but tells me that

julia> r= SQLite.query(db, "SELECT * from $tablename") |> DataFrame
┌ Warning: `SQLite.query(db, sql)` will return an `SQLite.Query` object in the future; to materialize a resultset, do `DataFrame(SQLite.query(db, sql))` instead
│   caller = ip:0x0
└ @ Core :-1
┌ Warning: `SQLite.Source(db, sql)` is deprecated in favor of `SQLite.Query(db, sql)` which executes a query and returns a row iterator
│   caller = ip:0x0
└ @ Core :-1:
...

julia> DataFrame(SQLite.query(db, "SELECT * from $tablename"))        ## this is the 'retrieve' operation
┌ Warning: `SQLite.query(db, sql)` will return an `SQLite.Query` object in the future; to materialize a resultset, do `DataFrame(SQLite.query(db, sql))` instead
│   caller = ip:0x0
└ @ Core :-1
┌ Warning: `SQLite.Source(db, sql)` is deprecated in favor of `SQLite.Query(db, sql)` which executes a query and returns a row iterator
│   caller = ip:0x0
└ @ Core :-1
┌ Warning: In the future DataFrame constructor called with a `DataFrame` argument will return a copy. Use `convert(DataFrame, df)` to avoid copying if `df` is a `DataFrame`.
│   caller = top-level scope at none:0
└ @ Core none:0

What is the right warning-free way to do this? regards, /iaw


#6

in the runtest.jl upcase -Q : SQLite.Query() -> right warning-free way !
in your example - lowcase-q : SQLite.query() -> Warnings

1 letter difference q vs. Q

julia> r = SQLite.Query(db, "select * from $tablename") |> DataFrame
5×5 DataFrame
│ Row │ x1       │ x2       │ x3       │ x4       │ x5       │
│     │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │
├─────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ 1   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 2   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 3   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 4   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 5   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │

julia> r2 = SQLite.query(db, "select * from $tablename") |> DataFrame
┌ Warning: `SQLite.query(db, sql)` will return an `SQLite.Query` object in the future; to materialize a resultset, do `DataFrame(SQLite.query(db, sql))` instead
│   caller = ip:0x0
└ @ Core :-1
┌ Warning: `SQLite.Source(db, sql)` is deprecated in favor of `SQLite.Query(db, sql)` which executes a query and returns a row iterator
│   caller = ip:0x0
└ @ Core :-1
┌ Warning: In the future DataFrame constructor called with a `DataFrame` argument will return a copy. Use `convert(DataFrame, df)` to avoid copying if `df` is a `DataFrame`.
│   caller = |>(::DataFrame, ::Type) at operators.jl:813
└ @ Base ./operators.jl:813
5×5 DataFrame
│ Row │ x1       │ x2       │ x3       │ x4       │ x5       │
│     │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │
├─────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ 1   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 2   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 3   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 4   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │
│ 5   │ 0.0      │ 0.0      │ 0.0      │ 0.0      │ 0.0      │

julia> 


#7

oh, this is funny. I was playing with this for an hour yesterday, and couldn’t see it. thanks, imre.