I have strange behavior here. After I init inspect(cn), to_sql writes an empty table to the database.
def save_data_to_sql(df, table_name, cn):
df.to_sql(con=cn, name='test_1', if_exists='replace', index=False) << Table with data
# Check if the table exists
inspector = inspect(cn)
if inspector.has_table(table_name):
logger.info(f"Table exists")
else:
logger.info(f"Table does not exist")
df.to_sql(con=cn, name='test_2', if_exists='replace', index=False) << Empty table?!?!
Someone an idea what’s wrong?
1
The issue was how I created the connection to MariaDB. So this one works:
def crcn():
"""Create and return a database connection with SQLAlchemy."""
try:
engine = create_engine(
f"mysql+mysqlconnector://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}/{DB_CONFIG['database']}",
connect_args={
"charset": "utf8mb4",
"collation": "utf8mb4_general_ci"
}
)
print("Connected to MariaDB using SQLAlchemy")
return engine
except SQLAlchemyError as e:
print(f"Error: {e}")
return None