Python Forum

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:

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.

There is no table named SOPOrderReturn that I can see in the code you posted, so you are trying to insert into something that doesn't exist. For more help post the complete traceback.