# Write DataFrame to SQLite table?
**URL:** https://discourse.julialang.org/t/write-dataframe-to-sqlite-table/8319
**Category:** General Usage
**Tags:** dataframes
**Created:** [January 12, 2018, 2:31pm UTC](https://discourse.julialang.org/t/write-dataframe-to-sqlite-table/8319 "2018-01-12T14:31:36Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@onetonfoot](https://discourse.julialang.org/u/onetonfoot)
#### Post date: [January 12, 2018, 2:31pm UTC](https://discourse.julialang.org/t/write-dataframe-to-sqlite-table/8319/1 "2018-01-12T14:31:36Z")
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?
---
### Author:  [@onetonfoot](https://discourse.julialang.org/u/onetonfoot)
#### Post date: [January 12, 2018, 3:22pm UTC](https://discourse.julialang.org/t/write-dataframe-to-sqlite-table/8319/2 "2018-01-12T15:22:43Z")
I figured it out.
```julia
db = SQLite.DB(db_path)
SQLite.load(db,"tablename",dataframe)
```
---
### Author:  [@iwelch](https://discourse.julialang.org/u/iwelch)
#### Post date: [October 4, 2018, 2:27am UTC](https://discourse.julialang.org/t/write-dataframe-to-sqlite-table/8319/3 "2018-10-04T02:27:59Z")
`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?
---
### Author:  [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)
#### Post date: [October 4, 2018, 2:55am UTC](https://discourse.julialang.org/t/write-dataframe-to-sqlite-table/8319/4 "2018-10-04T02:55:40Z")
my best practice:
- checking the `runtest.jl` : [SQLite.jl/runtests.jl at master · JuliaDatabases/SQLite.jl · GitHub](https://github.com/JuliaDatabases/SQLite.jl/blob/master/test/runtests.jl) sometimes the documentation is not perfect yet.
one of the example from the test:
```julia
_
_ _ _(_)_ | 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>
```
---
### Author:  [@iwelch](https://discourse.julialang.org/u/iwelch)
#### Post date: [October 4, 2018, 3:48am UTC](https://discourse.julialang.org/t/write-dataframe-to-sqlite-table/8319/5 "2018-10-04T03:48:01Z")
the save works perfectly. the query works but tells me that
```julia
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
---
### Author:  [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)
#### Post date: [October 4, 2018, 4:57am UTC](https://discourse.julialang.org/t/write-dataframe-to-sqlite-table/8319/6 "2018-10-04T04:57:46Z")
> [@iwelch](#):
>
> the save works perfectly. the query works but tells me that
> …
> What is the right warning-free way to do this?
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
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>
```
---
### Author:  [@iwelch](https://discourse.julialang.org/u/iwelch)
#### Post date: [October 4, 2018, 5:30pm UTC](https://discourse.julialang.org/t/write-dataframe-to-sqlite-table/8319/7 "2018-10-04T17:30:30Z")
oh, this is funny. I was playing with this for an hour yesterday, and couldn’t see it. thanks, imre.