在python中的pandas tosql中将数据写入MySql

pip install pandas
pip install mysql-connector-python

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

try:
    connection = mysql.connector.connect(
        host='localhost',
        database='your_database',
        user='your_username',
        password='your_password'
    )
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        cursor = connection.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("You're connected to database: ", record)

except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

# 假设有一个名为df的DataFrame对象
df = pd.DataFrame({'column1': [1, 2, 3], 'column2': ['a', 'b', 'c']})

# 将数据写入MySQL表中
try:
    df.to_sql(name='your_table_name', con=connection, if_exists='replace', index=False)

except Error as e:
    print("Error while writing to MySQL", e)

# 假设有一个名为df的DataFrame对象
df = pd.DataFrame({'column1': [1, 2, 3], 'column2': ['a', 'b', 'c']})

# 指定列类型
dtype = {'column1': sqlalchemy.types.Integer(), 'column2': sqlalchemy.types.String()}

# 将数据写入MySQL表中
try:
    df.to_sql(name='your_table_name', con=connection, if_exists='replace', index=False, dtype=dtype)

except Error as e:
    print("Error while writing to MySQL", e)