如何通过Pandas将API生成数据直接写入本地MySQL数据库？

Hey there! Glad to hear you’ve already got your database connection tested and tables created—you’re already ahead of the game. Skipping the CSV step and writing straight to MySQL with Pandas is totally doable, and it’s more efficient too. Here’s how to make it work:

Step 1: Install Required Packages #

First, you’ll need SQLAlchemy (Pandas uses this for reliable database connections) and your existing mysql-connector-python package. If you haven’t installed SQLAlchemy yet, run this in your terminal:

pip install sqlalchemy mysql-connector-python

Step 2: Use Pandas to_sql() with SQLAlchemy Engine #

This is the recommended approach because it handles connections and transactions smoothly. Replace your CSV export line with this code:

import pandas as pd
from sqlalchemy import create_engine

# Create a SQLAlchemy engine using your database credentials
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3307/analytics?auth_plugin=mysql_native_password')

# Write your DataFrame directly to the MySQL table
export.to_sql(
    name='test_table',  # Name of your existing MySQL table
    con=engine,
    if_exists='append',  # Choose how to handle existing data:
                         # - 'append': add new rows to the table
                         # - 'replace': drop and recreate the table (use carefully!)
                         # - 'fail': throw an error if the table exists
    index=False,  # Same as your CSV export: don't write the DataFrame index to the table
    chunksize=1000  # Optional: if your dataset is large, split into chunks to avoid memory issues
)

# Close the engine connection when done
engine.dispose()

Alternative: Use Your Existing mysql.connector Connection #

If you prefer to stick with the mysql.connector setup you already tested, you can use this approach. Note that you’ll need to handle transactions manually:

import mysql.connector
from mysql.connector import Error
import pandas as pd

# Reuse your existing connection code
db = mysql.connector.connect(
    host="localhost",
    user='root',
    password='password',
    database='analytics',
    port='3307',
    auth_plugin='mysql_native_password'
)

try:
    # Write the DataFrame to the table
    export.to_sql(
        name='test_table',
        con=db,
        if_exists='append',
        index=False,
        method='multi'  # Enables bulk inserts for better performance
    )
    db.commit()  # Commit the transaction to save changes
    print("Data successfully written to MySQL!")
except Error as e:
    print(f"Error occurred: {e}")
    db.rollback()  # Undo changes if something goes wrong
finally:
    # Always close the connection when finished
    if db.is_connected():
        db.close()

Key Notes to Keep in Mind #

• Column Matching: Make sure your DataFrame’s column names exactly match the column names in your MySQL test_table (MySQL is case-insensitive by default, but it’s best to keep them consistent to avoid issues).
• Data Types: Double-check that your DataFrame’s data types align with the MySQL table’s column types (e.g., a date column in Pandas should be a datetime type, matching MySQL’s DATE or DATETIME type).
• Large Datasets: Use the chunksize parameter if you’re working with very large files—it splits the data into smaller batches to prevent memory overload.
• Permissions: Ensure your MySQL user (root in your case) has write permissions for the analytics database and test_table.

That’s it! Replace your export.to_csv() line with one of the to_sql() examples above, and you’ll be writing data directly to MySQL without generating intermediate CSV files.

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