源码
import mysql.connector
import pyodbc
def connect_mysql(host, user, password, database):
conn = mysql.connector.connect(
host=host,
user=user,
password=password,
database=database
)
return conn
def connect_sql_server(driver, server, database, username, password):
conn = pyodbc.connect(
driver=driver,
server=server,
database=database,
uid=username,
pwd=password
)
return conn
def fetch_data_mysql(conn, table, limit):
cursor = conn.cursor()
try:
query = f"SELECT * FROM {table} LIMIT {limit}"
cursor.execute(query)
result = cursor.fetchall()
return result
except mysql.connector.Error as error:
print("数据读取失败: {}".format(error))
def insert_data_sql_server(conn, data, table):
cursor = conn.cursor()
try:
# 构建插入语句
placeholders = ', '.join('?' * len(data[0]))
insert_query = f"INSERT INTO {table} VALUES ({placeholders})"
# 执行插入操作
cursor.executemany(insert_query, data)
# 提交事务
conn.commit()
print("数据插入成功!")
except pyodbc.Error as error:
print("数据插入失败: {}".format(error))
cursor.close()
conn.close()
if __name__ == '__main__':
# 连接到MySQL数据库
conn_mysql = connect_mysql('localhost', 'root', 'pwd_Admin', 'test')
# 从emp_copy表中查询10条数据
data_mysql = fetch_data_mysql(conn_mysql, 'emp_copy', 10)
# 连接到SQL Server数据库
conn_sql_server = connect_sql_server('{ODBC Driver 17 for SQL Server}', 'DESKTOP-HAI1BEH', 'T1',
'sa', 'pwd_Admin')
# 将数据插入到emp表中
insert_data_sql_server(conn_sql_server, data_mysql, 'emp')