【1】准备工作

安装插件

pip install pymysql
pip install pymssql

如果是pycharm的话

回到顶部

【2】python从mssql 查询结果，插入到mysql表

import pymssql
import pymysql

mssql_db = {
    'mssql1': {
        'host': '192.168.191.81'
        , 'user': 'sa'
        , 'password': 'a123456!'
        , 'database': 'master'
    }
    , 'mssql2': {
        'host': '192.168.191.80'
        , 'user': 'sa'
        , 'password': 'a123456!'
        , 'database': 'master'
    }
}
mysql_db = {
    'mysql1': {
        'host': '192.168.175.129'
        , 'user': 'root'
        , 'password': '123456'
        , 'database': 'test'
    }
}
mysql_host = mysql_db['mysql1']['host']
mysql_user = mysql_db['mysql1']['user']
mysql_password = mysql_db['mysql1']['password']
mysql_database = mysql_db['mysql1']['database']
mysql_conn = pymysql.connect(host=mysql_host, user=mysql_user, password=mysql_password, database=mysql_database,charset='utf8')
mysql_cursor = mysql_conn.cursor()
for i in mssql_db:
    mssql_host = mssql_db[i]['host']
    mssql_user = mssql_db[i]['user']
    mssql_password = mssql_db[i]['password']
    mssql_database = mssql_db[i]['database']
    mssql_conn = pymssql.connect(host=mssql_host, user=mssql_user, password=mssql_password, database=mssql_database,charset='utf8')
    mssql_cursor = mssql_conn.cursor()
    mssql_sql = """select 'a' as info,'a1' as info1,1 as r
    union all
    select 'b','b1',2
    """
    mssql_cursor.execute(mssql_sql)
    rs = mssql_cursor.fetchall()
    for q in range(len(rs)):
        values = rs[q]
        db_insert = 'insert into test_mysql values('
        row_len = len(rs[q])
        for q1 in range(row_len):
            db_insert = db_insert+'%s,'
        db_insert = db_insert[:-1]+');'
        print(db_insert)
        mysql_cursor.execute(db_insert,values)
        mysql_conn.commit()