使用SQLAlchemy导出Pandas DataFrame至MySQL时遇连接错误求助

Hey there, that error is a classic clue that your database connection string has a formatting issue—let's break this down and fix it quickly!

What's Causing the Error? #

The error message Can't connect to MySQL server on 'localhost]:[3306' tells us right away that your host/port part of the connection URL has extra, invalid square brackets. SQLAlchemy can't resolve this malformed address, hence the getaddrinfo failed part.

Step 1: Fix Your Connection String #

A valid SQLAlchemy connection URL for MySQL (using pymysql) follows this structure:

mysql+pymysql://[username]:[password]@[host]:[port]/[database_name]

Your broken version has localhost]:[3306—remove those extra brackets so it looks like this:

# Example with placeholder values
engine = create_engine("mysql+pymysql://root:your_db_password@localhost:3306/your_target_db")

Step 2: Full Working Example Code #

Here's how your complete export code should look, with the corrected connection:

import pandas as pd
from sqlalchemy import create_engine

# Sample DataFrame (replace with your actual data)
df = pd.DataFrame({"col1": [1, 2, 3], "col2": ["a", "b", "c"]})

# Corrected database connection
engine = create_engine("mysql+pymysql://root:your_password@localhost:3306/your_database")

# Export DataFrame to MySQL
try:
    df.to_sql(name="your_table_name", con=engine, if_exists="replace", index=False)
    print("Data exported successfully!")
except Exception as e:
    print(f"Error: {e}")

Extra Checks to Rule Out Other Issues #

If fixing the connection string doesn't work, double-check these:

• Make sure your MySQL server is actually running (check services on Windows, or use sudo systemctl start mysql on Linux/macOS)
• Verify that port 3306 isn't blocked by your firewall
• Confirm your username, password, and target database name are all correct
• Ensure the pymysql driver is installed (pip install pymysql if you haven't already)

内容的提问来源于stack exchange，提问作者Mazahir Bhagat