Polars DataFrame save to sql

Question

Is there a way to save Polars DataFrame into a database, MS SQL for example?

ConnectorX library doesn’t seem to have that option.

Glassful

I don't know of a way, but you can always do df.to_pandas().to_sql(...) for now. – Overfeed

For polars>=0.16.10 you can use df.write_database() method. – Noted

Answer 1

Polars doesen't support direct writing to a database. You can proceed in two ways:

1. Export the DataFrame in an intermediate format (such as .csv using .write_csv()), then import it into the database.
2. Process it in memory: you can convert the DataFrame in a simpler data structure using .to_dicts(). The result will be a list of dictionaries, each of them containing a row in key/value format. At this point is easy to insert them into a database using SqlAlchemy or any specific library for your database of choice.

Costly

What about docs.pola.rs/py-polars/html/reference/api/… ? – Espadrille

Answer 2

Polars exposes the write_database method on the DataFrame class.

Extenuate

Answer 3

Using polars 0.20.5 `write_database` for sqlite3 always returns an error. On the other hand, `read_database` works well.

Sample code:

    import sqlite3
    import polars as pl
    conn = sqlite3.connect("test.db" )

    #this code works well.
    pl.read_database(
        query='SELECT * FROM table',
        connection = conn ,
    )
    
    #this code makes error 
    df_insertdata.write_database(
        table_name = "table",
        connection = conn,
    ) 

From [the polars web site] 
polars.DataFrame.write_database
https://docs.pola.rs/py-polars/html/reference/api/polars.DataFrame.write_database.html#polars.DataFrame.write_databasel:

Conaway

I do not see how this answers the question. Are you sure that you attempt to answer according to How to Answer. It seems more like a description of something you want explained or want help with. – Bosch