mssql 转mysql

import pyodbc
import mysql.connector
import csv

# MSSQL连接配置
mssql_conn = pyodbc.connect('DRIVER={SQL Server};SERVER=your_server;DATABASE=your_db;UID=your_user;PWD=your_password')
mssql_cursor = mssql_conn.cursor()

# MySQL连接配置
mysql_conn = mysql.connector.connect(host='your_mysql_host', database='your_mysql_db', user='your_mysql_user', password='your_mysql_password')
mysql_cursor = mysql_conn.cursor()

# 查询MSSQL数据
mssql_cursor.execute("SELECT * FROM your_table")
rows = mssql_cursor.fetchall()

# 将数据写入CSV文件
with open('output.csv', 'w', newline='') as csvfile:
    csvwriter = csv.writer(csvfile)
    csvwriter.writerow([i[0] for i in mssql_cursor.description])  # 写入列名
    csvwriter.writerows(rows)

# 创建MySQL表结构
create_table_sql = """
CREATE TABLE your_table (
    column1 INT,
    column2 VARCHAR(255),
    ...
)
"""
mysql_cursor.execute(create_table_sql)

# 将CSV数据导入MySQL
with open('output.csv', 'r') as csvfile:
    csvreader = csv.reader(csvfile)
    next(csvreader)  # 跳过列名
    for row in csvreader:
        insert_sql = "INSERT INTO your_table (column1, column2, ...) VALUES (%s, %s, ...)"
        mysql_cursor.execute(insert_sql, row)

# 提交事务并关闭连接
mysql_conn.commit()
mssql_conn.close()
mysql_conn.close()