How to Create a Table in a DataBase Using DataFrames?

Specific Domains Data
, ,
1

Hi all!

I tried looking through the documentation, but I could not find it readily - is there a way to write a DataFrame to either a Postgresql (via LibPQ.jl) or MySQL (via MySQL.jl) database as a new table?
Ideally, it would be nice to have a command like:

exec(connection = conn, sql = """CREATE TABLE my_dataframe 
                               AS SELECT $(df)"""

Or something of the sort.
Any thoughts?
Thanks!

~ tcp :deciduous_tree:

2

You can checkout MySQL.load:

MySQL.jl attempts to provide a convenient MySQL.load(table, conn, table_name) function for generically loading Tables.jl-compatible sources into database tables. While the mysql API has some utilities for even making this possible, just note that it can be tricky to do generically in practice due to specific modifications needed in CREATE TABLE and column type statements.

1 Like
3

LibPQ itself doesn’t provider a wrapper for writing tables, here’s an example one that I’ve written (with limited testing, so please use at your own caution!)

function load_table!(conn, df, tablename, columns=names(df))
    table_column_names = join(string.(columns), ", ")
    placeholders = join(("\$$num" for num in 1:length(columns)), ", ")
    data = select(df, columns)
    try
        LibPQ.execute(conn, "BEGIN;")
        LibPQ.load!(
            data,
            conn,
            "INSERT INTO $tablename ($(table_column_names)) VALUES ($placeholders)"
        )
        LibPQ.execute(conn, "COMMIT;")
    catch
        LibPQ.execute(conn, "ROLLBACK;")
    end
end
1 Like
4

How would you expect String columns to be defined ?

varchar(maximum(map(length, df[!, :txt])))