I have some python code that gets data from one database (SQL server) and inserts it into another database (MySQL). I am trying to add a WHERE NOT EXIST to the INSERT query so only new rows are inserted, but need to use one of the values in the tuple SageResults a second time for the primary key.

Code:

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)
WHERE NOT EXISTS (SELECT * FROM SOPOrderReturn WHERE SOPOrderReturnID = %1$s)"""
    try:
        mydbVPS = mysql.connector.connect(
          host="address",
          user="user",
          passwd="password",
          database="database"
        )

        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 51 [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())

        SageResult = []
        while True:
            row = Sagecursor.fetchone()
            if row:
                SageResult.append(tuple(row))
            else:
                break
        #SageResult = Sagecursor.fetchall()

        mydbSage.commit()
    except Exception as e:
        print('MainError:', e)

    finally:
        Sagecursor.close()
        mydbSage.close()

    insert_VPS(SageResult)


if __name__ == '__main__':
    main()

Output:

D:\xampp\htdocs\stripe\group\beta>sql-sync.py
InsertError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use ne
ar 'WHERE NOT EXISTS (SELECT * FROM SOPOrderReturn WHERE SOPOrderReturnID = %1$s),(1' at line 3

The part in question is the query string variable. Everything else in here works fine. I basically need to use the SOPOrderReturnID value from the tuple a second time where I currently have %1$s

What is the issue with the query syntax? Is my use of %1$s correct?

0 Answers