# Upload of DataFrame data to databases

**URL:** <https://discourse.julialang.org/t/upload-of-dataframe-data-to-databases/36950>\
**Category:** Data\
**Created:** [April 3, 2020, 11:15am UTC](https://discourse.julialang.org/t/upload-of-dataframe-data-to-databases/36950 "2020-04-03T11:15:05Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![lungben](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lungben/32/12314_2.png) [@lungben](https://discourse.julialang.org/u/lungben)\
**Post date:** [April 3, 2020, 11:15am UTC](https://discourse.julialang.org/t/upload-of-dataframe-data-to-databases/36950/1 "2020-04-03T11:15:06Z")

</div>

Hi,

for evaluation of a possible projet usage I did a comparison of DataFrames.jl to Pandas, with side-by-side examples and timings:

> <https://github.com/lungben/julia_experiments/blob/master/notebooks/data_frame_comparison_julia.ipynb>

Overall, DataFrames.jl performs very well in my experiments, great work!

One functionality I could not find out-of-the-box is for writing the content of a DataFrame to a database (e.g. PostgreSQL), analogue to Pandas `df.to_sql()`.

A simple implementation of the database upload would be (taken mostly from LibPQ.jl documentation):

```julia
using DataFrames
using LibPQ
using IterTools

function insert_by_copy!(con:: LibPQ.Connection, tablename:: AbstractString, df:: DataFrame)
    row_strings = imap(eachrow(df)) do row
        join((ismissing(x) ? "" : x for x in row), ",")*"\n"
    end
    copyin = LibPQ.CopyIn("COPY $tablename FROM STDIN (FORMAT CSV);", row_strings)
    execute(con, copyin)
end

```

Note that this does not cover all cases - notably the column order must be the same for the DataFrame and Table and there must not be “`,`” in strings (and probably more edge cases I am not aware of yet).

Using the `COPY` command the performance is much better than using SQL Inserts, therefore this simple function outperforms Pandas `df.to_sql()` (but you can do the same trick for Pandas, too).

Is such a functionality already available somewhere?  
If not, where would be the best point to add it? DataFrames.jl, LibPQ.jl or in a separate package?  
Maybe the CSV.jl package could be used for improving the upload functionality and making it more general?

Best Regards  
Benjamin

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [April 3, 2020, 4:51pm UTC](https://discourse.julialang.org/t/upload-of-dataframe-data-to-databases/36950/2 "2020-04-03T16:51:44Z")

</div>

Thanks for the kind words. The comparison is very interesting. The fact that our sorting implementation is slower than Pandas is expected since we should use radix sort for integers. Regarding `filter`, a new `filter(col => fun, df)` syntax has just been added to master, it will be much faster than the current syntax.

I can’t help you regarding databases, hopefully others will comment. At least I can point you at Tables.jl, which is the general interface for tabular data in Julia, that LibPQ.jl already uses.

Just a small suggestion: using `df.col` instead of `df[!, :col]` makes the code much nicer to read (and closer to Pandas).

---

<div class="post-metadata">

**Author:** ![lungben](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lungben/32/12314_2.png) [@lungben](https://discourse.julialang.org/u/lungben)\
**Post date:** [April 3, 2020, 5:39pm UTC](https://discourse.julialang.org/t/upload-of-dataframe-data-to-databases/36950/3 "2020-04-03T17:39:57Z")

</div>

Thanks for your suggestion! I got a deprecation warning for `df[:column]` and somehow mixed it up with `df.column` - agreed that the latter syntax is nicer for simple column access.

---

<div class="post-metadata">

**Author:** ![lungben](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lungben/32/12314_2.png) [@lungben](https://discourse.julialang.org/u/lungben)\
**Post date:** [April 4, 2020, 7:50pm UTC](https://discourse.julialang.org/t/upload-of-dataframe-data-to-databases/36950/4 "2020-04-04T19:50:09Z")

</div>

I did some improvements to the PostgreSQL upload functionality and generalized it (as suggested by @nalimilan) for the Tables .jl interface:

> <https://github.com/lungben/julia_experiments/blob/master/notebooks/df_to_db.ipynb>

Imho it would best fit into LibPQ.jl, I’ll make a PR.

---

<div class="post-metadata">

**Author:** ![mmiller](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mmiller/32/20805_2.png) [@mmiller](https://discourse.julialang.org/u/mmiller)\
**Post date:** [October 16, 2020, 3:15am UTC](https://discourse.julialang.org/t/upload-of-dataframe-data-to-databases/36950/5 "2020-10-16T03:15:45Z")

</div>

> [@lungben](#):
>
> Imho it would best fit into LibPQ.jl, I’ll make a PR

@lunghben this is great, did it ever make it into LibPQ.jl?

---

<div class="post-metadata">

**Author:** ![lungben](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lungben/32/12314_2.png) [@lungben](https://discourse.julialang.org/u/lungben)\
**Post date:** [October 16, 2020, 6:43am UTC](https://discourse.julialang.org/t/upload-of-dataframe-data-to-databases/36950/6 "2020-10-16T06:43:39Z")

</div>

Already done and waiting for merge:

[https://github.com/invenia/LibPQ.jl/pull/186](https://github.com/invenia/LibPQ.jl/pull/186)

Due to the dependency on CSV.jl, this function should only be included into LibPQ.jl documentation and tests, but not into the library itself.

---

<div class="post-metadata">

**Author:** ![mmiller](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mmiller/32/20805_2.png) [@mmiller](https://discourse.julialang.org/u/mmiller)\
**Post date:** [October 19, 2020, 12:18am UTC](https://discourse.julialang.org/t/upload-of-dataframe-data-to-databases/36950/7 "2020-10-19T00:18:07Z")

</div>

Ah nice, that makes sense.