Why is pandas.to_sql trying to create a table even though I specified to append if_exists?

Question

Here's my code. Simply trying to append a dataframe to a Teradata table.

import pandas as pd
from sqlalchemy import create_engine
import sqlalchemy_teradata

user = username
pasw = password
host = hostname
port = port

td_engine = create_engine('teradata://'+host+':'+port+'/?username='+user+'&password='+pasw)
td_engine = td_engine.execution_options(autocommit=True)

cnxn = td_engine.connect()
df.to_sql('mydatabase.existing_table', cnxn, if_exists='append' , index=False)
cnxn.close()

The error says it is trying to create a table and I don't have rights to do so even though I specified if_exists='append' and I do, in fact, have rights to create tables.

(teradata.api.DatabaseError) (3524, '[42000] [Teradata][ODBC Teradata Driver][Teradata Database](-3524)The user does not have CREATE TABLE access to database my_username.')

Answer 1

When you use if_exists='append', pandas tries to append the data to an existing table. Check if the table exists before using pandas.to_sql. You can use SQLAlchemy's inspect.

cnxn = td_engine.connect()

# Check if the table exists
inspector = inspect(cnxn)
table_exists = inspector.has_table('mydatabase.existing_table')

if not table_exists:
    # Create the table if it doesn't exist
    df.to_sql('mydatabase.existing_table', cnxn, index=False)

# Append the data
df.to_sql('mydatabase.existing_table', cnxn, if_exists='append', index=False)

cnxn.close()