将Pandas DataFrame插入本地MySQL数据库时遇错误求助

Hey there! Let's walk through your problems step by step and get that DataFrame into your local MySQL database working properly.

First Error: Why mysql.connector Directly Fails with to_sql() #

The first error happens because Pandas' to_sql() method relies on SQLAlchemy as its default database interface—it doesn't work natively with raw mysql.connector connections. When you pass the mysql.connector connection object directly, Pandas assumes you're connecting to a SQLite database (hence the sqlite_master query in the error), which causes the parameter mismatch.

The fix here is simple: you must use a SQLAlchemy engine instead of the raw mysql.connector connection.

Second Error: Access Denied with SQLAlchemy #

Your "Access denied" error has a few likely causes, all related to your connection string setup:

1. Missing port number: Your MySQL is running on port 3307, but your original connection string didn't specify this—it defaulted to 3306, which is why it couldn't reach your database.
2. Missing database driver: SQLAlchemy needs a MySQL-specific driver to communicate with the database. Common options are mysql-connector-python or pymysql.
3. Incorrect connection string format: The base mysql:// scheme defaults to the old MySQLdb driver, which you might not have installed. You need to explicitly specify the driver you're using.

Step-by-Step Solution #

1. Install Required Packages #

First, make sure you have all the necessary tools installed:

pip install pandas sqlalchemy mysql-connector-python

(Or use pymysql instead of mysql-connector-python if you prefer—just adjust the connection string below.)

2. Correct SQLAlchemy Engine Setup #

Create a properly formatted engine that includes your port, auth plugin, and driver:

• If using mysql-connector-python:

  from sqlalchemy import create_engine
  engine = create_engine("mysql+mysqlconnector://root:pw123@localhost:3307/analytics?auth_plugin=mysql_native_password")
  

• If using pymysql:

  engine = create_engine("mysql+pymysql://root:pw123@localhost:3307/analytics?auth_plugin=mysql_native_password")
  

3. Working Code to Insert Your DataFrame #

You don't need to manually open/close connections—to_sql() handles that automatically. Here's the full, working script:

import pandas as pd
import numpy as np
from sqlalchemy import create_engine

# Generate your random DataFrame
data_frame = pd.DataFrame(np.random.randint(0, 100, size=(100, 4)), columns=list('ABCD'))

# Create the SQLAlchemy engine (adjust driver if needed)
engine = create_engine("mysql+mysqlconnector://root:pw123@localhost:3307/analytics?auth_plugin=mysql_native_password")

# Insert the main DataFrame into the 'test' table
data_frame.to_sql(
    name='test',
    con=engine,
    if_exists='append',
    index=False
)

# Test inserting your smaller DataFrame too
df = pd.DataFrame(['A', 'B'], columns=['new_tablecol'])
df.to_sql(
    name='new_table',
    con=engine,
    if_exists='append',
    index=False
)

Quick Checks If You Still Run Into Issues #

• Double-check that your MySQL server is actually running on port 3307.
• Verify that the root@localhost user has write permissions for the analytics database.
• If you're using MySQL 8.0+, ensure the auth_plugin matches what your user is configured to use (you already included mysql_native_password, which matches your original connector setup).

内容的提问来源于stack exchange，提问作者Jonas Palačionis