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