executemany: Failed executing the operation; Could not process parameters (Python)

Question

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.

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

The error I get:

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

I have tested the select query (but not the INSERT one) and both connections in a more basic script and those all work fine. Can anyone see the issues?

MySQL uses backticks, not brackets, `[...]`, around identifiers like SQL Server. — Parfait, May 16 '19 at 10:12
I have updated the code above with those brackets taken out of the insert query (the other is run with SQL server). I am still getting the same error. — Josh, May 16 '19 at 10:28
Might be an issue with data. Try printing *SageResult* before insert to see content. — Parfait, May 16 '19 at 12:05
I've added a print in and changed it so it only pulls one result in the select just to make it easier to look at. Can you spot any issues? (see updated output above) — Josh, May 16 '19 at 12:14
I suspect that `SageResult` is a tuple of `pyodbc.Row` objects, and `mysql.connector` may not like those. See [this answer](https://stackoverflow.com/a/55924953/2144390) for a way to create a list of tuples instead. — Gord Thompson, May 16 '19 at 12:42

score 0 · Answer 1 · answered May 16 '19 at 09:58

0

That should have been except Exception as e instead of except Error as e:

answered May 16 '19 at 09:58

Underoos

4,708
8
42
85

Thanks. That cleared up most of the errors. Now it is just saying "Error: Failed executing the operation; Could not process parameters". That is the exception in the Insert_VPS function. – Josh May 16 '19 at 10:00
1

I guess you shouldn't be using `[` and `]` in SQL queries. – Underoos May 16 '19 at 10:07
The []'s are taken from a query generated in SQL server that works fine so I can't see that being the issue. – Josh May 16 '19 at 10:17
Are you sure that the `SageResult` is a tuple? You should check the type and its elements before performing `executemany()`. – Underoos May 16 '19 at 10:28
I'm not sure. I'm pretty new to python so I don't really know what SageResult is being outputted as (I assumed a standard array). – Josh May 16 '19 at 10:33
Check this out. It might be helpful - https://stackoverflow.com/questions/18244565/how-can-i-use-executemany-to-insert-into-mysql-a-list-of-dictionaries-in-python – Underoos May 16 '19 at 10:36
The elements that you got from the `fetchall()` queryset also should be `tuples`. and try to print the values and type before `executemany()`. – Underoos May 16 '19 at 10:45
I just checked and SageResult was a list, not a tuple so I added tuple() around the fetchall to convert it (I tested this separately and it works). However, I am still getting the same error. – Josh May 16 '19 at 10:45

score 0 · Answer 2 · answered May 16 '19 at 22:48

Consider workarounds as the special datetime and decimal types may not translate effectively from pyodbc to mysql connector DB-APIs.

CSV

Use the popular form of data transfer using MySQL's fast LOAD DATA method.

import csv
...

# SQL SERVER CSV EXPORT
mydbSage = pyodbc.connect('...')

Sagecursor = mydbSage.cursor()
Sagecursor.execute(selectQuery)
SageResult = Sagecursor.fetchall()

with open("/path/to/SageResult.csv", "w", newline='') as csv_file:
    cw = csv.writer(csv_file)
    cw.writerow([i[0] for i in Sagecursor.description])    # WRITE HEADERS
    cw.writerows(SageResult)                               # WRITE DATA ROWS  


# MYSQL CSV IMPORT
mydbVPS = mysql.connector.connect(...)

query = """LOAD DATA LOCAL INFILE '/path/to/SageResult.csv'
           INTO TABLE SOPOrderReturn
           FIELDS TERMINATED BY
           ENCLOSED BY '"'
           LINES TERMINATED BY '\r\n'
        """
VPScursor = mydbVPS.cursor()
VPScursor.execute(query)

PyODBC

Run both database connections with same API which requires downloading the MySQL ODBC driver for your OS and replace the mysql.connector). This may resolve handling of those specific types.

# SQL SERVER SELECT QUERY
mydbSage = pyodbc.connect(driver="SQL Server", host="CRMTEST", database="LIVE",
                          uid="sa", pwd="password")

Sagecursor = mydbSage.cursor()
Sagecursor.execute(selectQuery)
SageResult = tuple(Sagecursor.fetchall())

# MYSQL APPEND QUERY
mydbVPS = pyodbc.connect(driver="ODBC Driver Name", host="hostname",
                         uid="username", pwd="password", database="database")

query = """INSERT INTO SOPOrderReturn (SOPOrderReturnID, DocumentTypeID, DocumentNo,
                                       DocumentDate, CustomerID, CustomerTypeID,
                                       CurrencyID, SubtotalGoodsValue, TotalNetValue,
                                       TotalTaxValue, TotalGrossValue, SourceTypeID,
                                       SourceDocumentNo) 
           VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, , ?, ?, ?)
        """

VPScursor = mydbVPS.cursor()
VPScursor.execute(query, SageResult)

MS Access

Use the Office app as a medium between both relational databases. Since you use SQL Server you may have Microsoft Office available with a possibility of its DBMS Office app: MS Access.

Technically, MS Access is like phpMyAdmin, a GUI console to a database (where app is often conflated with its engine), except Access is not restricted to any one database but can supplement its default database, Jet/ACE SQL engine, with any known backend data and database source.

Create two linked tables (using ODBC connections) to the separate RDBMS's.

Create and then run INSERT...SELECT query on linked tables. This query will use Access' SQL dialect which supports TOP clause and bracketed names. Results should propagate immediately in MySQL table.

INSERT INTO SOPOrderReturn_mysql_linked 
SELECT TOP 50 [SOPOrderReturnID]
       ,[DocumentTypeID]
       ,[DocumentNo]
       ,[DocumentDate]
       ,[CustomerID]
       ,[CustomerTypeID]
       ,[CurrencyID]
       ,[SubtotalGoodsValue]
       ,[TotalNetValue]
       ,[TotalTaxValue]
       ,[TotalGrossValue]
       ,[SourceTypeID]
       ,[SourceDocumentNo]
FROM SOPOrderReturn_mssql_linked

In fact, have Python run above query after linking tables in a saved database. Likely the MS Access ODBC driver may already be installed with the Office app or alternatively can be installed with downloaded redistributable.

# LIST OF INSTALLED DRIVERS
print(pydbc.drivers())

# MS ACCESS APPEND QUERY
constr = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ={'C:\\Path\\To\\Database\\File.accdb'};"

accdb = pyodbc.connect(constr)
cur = accdb.cursor()
cur.execute('<APPEND QUERY USING LINKED TABLES>')

executemany: Failed executing the operation; Could not process parameters (Python)

2 Answers2