python - 如何使用 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()