使用SQLAlchemy和Pandas向MySQL插入数据无报错但未生效求助

Let’s break down this tricky issue—your code runs without errors but nothing hits the database. This usually boils down to small configuration or transaction handling details. Here are the most likely fixes to try:

1. Fix Transaction Handling (Most Common Culprit) #

Your current code uses engine.connect() with an explicit transaction via conn.begin(), but Pandas' to_sql works more reliably when you pass the engine directly instead of a manual connection. The library handles connection pooling and transaction commits automatically under the hood.

Rewrite your code like this:

from sqlalchemy import create_engine

# Fix the connection string syntax first (see point 2!)
engine = create_engine("mysql+mysqldb://USER:{PSWRD}@HOST:3306/schema", echo=False)
df.to_sql(name='tbl_TestPython', con=engine, if_exists='append', index=False)

If you do need to use a manual connection for custom transactions, make sure you’re explicitly committing (though the context manager should handle this—still, it’s worth testing):

with engine.connect() as conn:
    trans = conn.begin()
    try:
        df.to_sql(name='tbl_TestPython', con=conn, if_exists='append', index=False)
        trans.commit()
    except Exception as e:
        trans.rollback()
        raise e

2. Fix the Connection String Syntax #

Looking at your original code, there’s a syntax error in the password concatenation:

# Original (broken)
engine = create_engine("mysql+mysqldb://USER:"+'PSWRD'"@HOST:3306/schema", echo=False)

You’re missing a + between 'PSWRD' and "@HOST...", which should throw a syntax error. If your actual code doesn’t have this, double-check:

• HOST is correct (not a typo, and the database is reachable)
• The schema name matches exactly what exists in MySQL
• Your user account has WRITE permissions for the tbl_TestPython table

For cleaner code, use an f-string:

engine = create_engine(f"mysql+mysqldb://USER:{PSWRD}@HOST:3306/schema", echo=False)

3. Check Table Name Case Sensitivity #

MySQL is case-sensitive with table names on Linux systems. Make sure tbl_TestPython matches the exact case of the table in your database—if the table was created as tbl_testpython, your insert will silently fail without errors.

4. Enable Echo Mode to Debug SQL #

Turn on SQLAlchemy’s echo=True to see exactly what SQL is being sent to the database. This will tell you if to_sql is even generating INSERT statements:

engine = create_engine(f"mysql+mysqldb://USER:{PSWRD}@HOST:3306/schema", echo=True)
df.to_sql(...)

Look for INSERT INTO tbl_TestPython (...) VALUES (...) in the console output. If you don’t see this, the issue is with how Pandas is interpreting your DataFrame or table.

5. Validate Data Type Compatibility #

Even without errors, mismatched data types between your DataFrame and MySQL table can cause silent failures. For example:

• A MySQL INT column receiving string values that look like numbers
• A VARCHAR column with a length shorter than your DataFrame’s string values

Force data types explicitly using the dtype parameter:

from sqlalchemy.types import Integer, String, DateTime

df.to_sql(
    name='tbl_TestPython',
    con=engine,
    if_exists='append',
    index=False,
    dtype={
        'id_col': Integer(),
        'name_col': String(length=255),
        'date_col': DateTime()
    }
)

Start with the first two fixes—transaction handling and connection string syntax—since those are the most frequent causes of this exact problem.

内容的提问来源于stack exchange，提问作者Clayton Tosatti