May-16-2019, 12:42 PM
I've written a bit of python code that essentially will take data from one database (SQL Server 2008) and insert it into another (MySQL). I am fairly new to python so am struggling to find the errors in my code. The problem is with the executemany line as when I comment this out the error goes away.
My code is:
My code is:
import mysql.connector import pyodbc def insert_VPS(SageResult): query = """ INSERT INTO SOPOrderReturn(SOPOrderReturnID,DocumentTypeID,DocumentNo,DocumentDate,CustomerID,CustomerTypeID,CurrencyID,SubtotalGoodsValue,TotalNetValue,TotalTaxValue,TotalGrossValue,SourceTypeID,SourceDocumentNo) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""" try: mydbVPS = mysql.connector.connect( host="serveraddress", user="username", passwd="password;", database="databse" ) VPScursor = mydbVPS.cursor() print(SageResult) VPScursor.executemany(query, SageResult) mydbVPS.commit() except Exception as e: print('InsertError:', e) finally: VPScursor.close() mydbVPS.close() def main(): selectQuery = """ SELECT TOP 1 [SOPOrderReturnID] ,[DocumentTypeID] ,[DocumentNo] ,[DocumentDate] ,[CustomerID] ,[CustomerTypeID] ,[CurrencyID] ,[SubtotalGoodsValue] ,[TotalNetValue] ,[TotalTaxValue] ,[TotalGrossValue] ,[SourceTypeID] ,[SourceDocumentNo] FROM [Live].[dbo].[SOPOrderReturn] """ try: mydbSage = pyodbc.connect('Driver={SQL Server};' 'Server=CRMTEST;' 'Database=Live;' 'UID=sa;' 'PWD=password;') Sagecursor = mydbSage.cursor() Sagecursor.execute(selectQuery) SageResult = tuple(Sagecursor.fetchall()) mydbSage.commit() except Exception as e: print('MainError:', e) finally: Sagecursor.close() mydbSage.close() insert_VPS(SageResult) if __name__ == '__main__': main()Output:
Output:D:\xampp\htdocs\stripe\group\beta>sql-sync.py
((10447177, 0, '0000091897', datetime.datetime(2010, 8, 18, 0, 0), 186150, 1, 1, Decimal('18896.95'), Decimal('18896.95'), Decimal('3779.39'), Decimal('22676.34
'), 0, ''),)
InsertError: Failed executing the operation; Could not process parameters
All of the connections work as I've tested those separately. Can anyone see the issues? Any help would be greatly appreciated.