Search code examples
pythonpandassqlitepandas-to-sql

Error "table ... already exits" when using to_sql(...,if_exists='append') with Pandas and SQLite

Using Panadas 2.2.3, sqlite3.version 2.6.0 and python 3.12.5, I get an error "table ... already exits" when using to_sql with if_exists='append'. I just try to append some data from a Pandas df to a SQLite DB table. Using if_exists='replace' produces the same result.

In order to make sure that the db connection is active and the columns match, I used some simple print statements in a first try block and the failing to.sql in a second try block. Also a "select statement" from the same table is used in the first block. The first block is executed without an exception and the second block throws the message 'table "groupedData" already exists': (See print('ERROR Try 2'))

Source code:

try:
    print(db_conn)
    print(table_grouped)
    data = [x.keys() for x in db_conn.cursor().execute(f'select * from {table_grouped};').fetchall()]
    print(data)
except Error as e:
    print('ERROR Try 1')
    print(e)

try: 
    print(df_grouped.head(5))
        
    df_grouped.to_sql(table_grouped, db_conn, if_exists='append', index=False) 
    #if_exists : {‘fail’, ‘replace’, ‘append’}
    db_conn.commit()

except Error as e:
    print('ERROR Try 2')
    print(e)

Output:

<sqlite3.Connection object at 0x000001C0E7C0EB60>
groupedData
[['CustomerID', 'TotalSalesValue', 'SalesDate']]
   CustomerID  TotalSalesValue  SalesDate
0       12345            400.0 2020-02-01
1       12345           1050.0 2020-02-04
2       12345             10.0 2020-02-10
3       12345            200.0 2021-02-01
4       12345             50.0 2021-02-04
ERROR Try 2
table "groupedData" already exists
Solution

  • It turned out (thanks to a friend who does not have a stackoverflow account -yet) that the problem was case sensitivity. I named the SQLite db table GroupedData and used groupedData in Python with Pandas.

    While SQL table names are usually not case-sensitive and also sqlite3 is not case-sensitive with table names...

    db_conn = sqlite3.connect(db_file_path)
db_conn.cursor().execute(f'select * from {table_grouped};').fetchall()

    to_sql() from Pandas shows a case sensitive behaviour with a error message not indicating the problem properly. Using table_grouped='GroupedData', the second try block worked for both append and replace.

    I will report the topic to Pandas using (https://github.com/pandas-dev/pandas/issues/new?) and linking the stackoverflow question. Either giving a more specific error message or changing the behaviour of to_sql() to be also non case-sensitive would be my suggestion.