Empty database created from csv file

(1) By luisa.d (luisadantas) on 2022-10-07 14:24:18 [link] [source]

Hey,

I'm attempting to create a database from a dataframe created by Pandas. The csv has all the informations, so as the df, but when I open the db through a viewer it is empty.

The code is long but I copied this part to a notebook to see if the error still happened and it does. The code fragment is below.

Does anyone know why this might be happening?

-----------------------------------------
import sqlite3

arquivo_db = os.path.join("path", "BaseTeste.db")

laudos_cru = pd.read_csv(
        os.path.join("path_to_csv"), #mudar nome do arquivo a ser tratado
        sep=",",
        low_memory=False,
        dtype={
            "column1": "Int64",
            "column2": "string",
            "column3": "string",
            "column4": "object",
        },
        header=0,
    )



with sqlite3.connect(arquivo_db) as con:
    laudos_cru.to_sql(
        "parametros",                               #nome da tabela
        con,                                        #conexão 
        index=False,                        #não escreve o index como coluna
        if_exists="replace",                #caso exista, tabela antiga será 
                                            #substituída pela nova
        dtype={"column": "INTEGER PRIMARY KEY"}, #tipo de dado
    )

(2) By midijohnny on 2022-10-07 21:54:09 in reply to 1 [link] [source]

Can you try breaking the CSV on purpose - to see if you get an error? (also - use a non-existent CSV filename - same reason).

(3) By anonymous on 2022-10-08 07:53:50 in reply to 1 [link] [source]

Can we also check that if you print(laudos_cru) you can view the dataframe correctly created?

If you're not generating the data frame, sqlite isn't going to help you!

It's worth pointing out that the sqlite cli app makes it fairly trivial to import a CSV into a sqlite database, if this is the important step by itself.

(4) By anonymous on 2022-10-08 11:47:10 in reply to 1 [source]

With Python's sqlite module, unless you specifically tell it to behave more sensibly, it will meddle with transactions behind your back. You'll often need to explicitly issue a COMMIT; even though you haven't started any transactions yourself.

I'm not familiar with pandas, but unless it's documented to deal with this itself, you should either set isolation_level=None when creating the connection or add con.commit() after laudos_cru.to_sql(…)

(5) By Harald Hanche-Olsen (hanche) on 2022-10-08 12:20:46 in reply to 1 [link] [source]

Perhaps you need to run con.commit() after calling laudos_cr.to_sql(…)? Maybe that method does not auto-commit.