如何使用Python成功地将从MySQL表中选择的行列插入到目标MSSQL表中？

Question

此python脚本连接到MySQL数据库和MssQL 2008 R2数据库。MySQL数据库运行在Linux Ubuntu 11.04上。MssQL 2008在Windows上运行。该脚本在Linux (Ubuntu 11.04)上运行。

            #!/usr/bin/python

            import pymssql as ms
            import MySQLdb as mdb
            import sys

            //Connection to MSSQL
            #Connection to MSSQL
            connMSSQL=ms.connect(host='192.168.8.52', user='sa', password='hostailpw321', database='hostail', as_dict=True)

            //Connection to MySQL
            #Connection to MySQL
            connMySQL=mdb.connect('localhost', 'root', 'trail123', 'trail');

            //Cursor to MSSQL
            #Cursor to MSSQL
            curMSSQL=connMSSQL.cursor()

            //Cursor to MySQL
            #Cursor to MySQL
            curMySQL=connMySQL.cursor(mdb.cursors.DictCursor)
            curMySQL.execute('SELECT * FROM clinics_mapping')

            //Get Data from MySQL 
            #Get Data from MySQL server
            for rowMySQL in curMySQL:
            #print (rowMySQL['Clinic_name'],rowMySQL['Clinic_code'])
            #curMSSQL.executemany("INSERT INTO clinics values(%s,%s)", [(rowMySQL['Clinic_name'],rowMySQL['Clinic_code'])]) 
                    names = rowMySQL['Clinic_name']
                    codes = rowMySQL['Clinic_code']
                    qryINS="INSERT INTO clinics(name,code)values('%s','%s')" %(str(names),str(codes))
                    curMSSQL.execute(qryINS)
            #print (rowMySQL['Clinic_name'],rowMySQL['Clinic_code'])
            #When I print qryINS I get a query that executes perfect in MSSQL 2008 R2 query editor      
                    print qryINS

            //Close MSSQL connection
            #Close MSSQL connection
            connMSSQL.close()

            //Close MySQL connection
            #Close MySQL connection
            connMySQL.close()

Answer 1

在紧跟在curMSSQL.execute(qryINS)行之后调用commit()后，问题得到了解决，如下所示: connMSSQL.commit()

        #!/usr/bin/python

        import pymssql as ms
        import MySQLdb as mdb
        import sys

        #Connection to MSSQL
        connMSSQL=ms.connect(host='192.168.8.52', user='sa', password='hostailpw321', database='hostail', as_dict=True)

        #Connection to MySQL
        connMySQL=mdb.connect('localhost', 'root', 'trail123', 'trail');

        #Cursor to MSSQL
        curMSSQL=connMSSQL.cursor()

        #Cursor to MySQL
        curMySQL=connMySQL.cursor(mdb.cursors.DictCursor)
        curMySQL.execute('SELECT * FROM clinics_mapping')

        #Get Data from MySQL server
        for rowMySQL in curMySQL:
        #print (rowMySQL['Clinic_name'],rowMySQL['Clinic_code'])
        #curMSSQL.executemany("INSERT INTO clinics values(%s,%s)", [(rowMySQL['Clinic_name'],rowMySQL['Clinic_code'])]) 
                names = rowMySQL['Clinic_name']
                codes = rowMySQL['Clinic_code']
                qryINS="INSERT INTO clinics(name,code)values('%s','%s')" %(str(names),str(codes))
                curMSSQL.execute(qryINS)
                connMSSQL.commit()

                """
                I had not called commit() which persists your data if you had not put autocommit to True.I have called it as connMSSQL.commit() in my script.
                """

        #Close MSSQL connection
        connMSSQL.close()

        #Close MySQL connection
        connMySQL.close()

Answer 2

pyodbc适用于我。http://sourceforge.net/projects/pyodb/