将含Timestamp的Pandas DataFrame插入MySQL时遇问题，求解决方案

Hey there, let's break down what's going wrong here and fix it step by step. Your core problem stems from incompatibility between timezone-aware datetime columns (datetime64[ns, UTC]) and older versions of Pandas/SQLAlchemy when interacting with MySQL, plus some small missteps in your code.

First, let's unpack your errors: #

1. ValueError: Cannot cast DatetimeIndex to dtype datetime64[us]
   Pandas 0.22.0 has limited support for timezone-aware datetimes in to_sql. When trying to convert your UTC-timestamped column to MySQL's expected datetime format (microsecond precision, no timezone), it fails because the library can't handle the timezone metadata properly.

2. ValueError: duplicate name in index/columns: cannot insert Timestamp, already exists
   You tried setting Timestamp as the index and specified it in the dtype parameter, but the column already exists in your DataFrame. This creates a conflict when to_sql tries to write both the index and the original column.

Solution 1: Remove Timezone Metadata (Most Reliable for Old Versions) #

Since older Pandas/SQLAlchemy versions struggle with timezone-aware datetimes and MySQL, the simplest fix is to strip the timezone info while preserving the UTC time value:

# Convert the timezone-aware Timestamp to a naive datetime (keeps UTC time)
test['Timestamp'] = test['Timestamp'].dt.tz_localize(None)

# Now run your original to_sql code without index issues
engine = create_engine('mysql+pymysql://xxxx:3306/xxxx')
test.to_sql(name='table1', con=engine, if_exists='append', index=False)
conn.close()

After this conversion, your Timestamp column will be of type datetime64[ns], which Pandas 0.22.0 can correctly map to MySQL's TIMESTAMP or DATETIME type.

Solution 2: Specify dtype Without Index Conflict (For Timezone Retention) #

If you need to keep timezone information (note: this works better with MySQL 8.0+), avoid setting Timestamp as the index and explicitly define the column's dtype:

from sqlalchemy import TIMESTAMP

engine = create_engine('mysql+pymysql://xxxx:3306/xxxx')
test.to_sql(
    name='table1',
    con=engine,
    if_exists='append',
    index=False,  # Don't use the Timestamp as index
    dtype={'Timestamp': TIMESTAMP(timezone=True)}  # Map column to MySQL's timezone-aware TIMESTAMP
)
conn.close()

⚠️ Heads up: This might still have issues with your old Pandas/SQLAlchemy versions. If it fails, stick with Solution 1.

Bonus Recommendation #

Your Pandas (0.22.0) and SQLAlchemy (1.2.1) versions are quite outdated. Upgrading to newer releases (Pandas 1.x+ and SQLAlchemy 1.4+) will resolve many datetime compatibility issues and add better support for timezone-aware data in to_sql.

内容的提问来源于stack exchange，提问作者Sundar N