How Can I Insert A List Returned From Pyodbc Mssql Query Into Mysql Through Stored Procedure Using Pymysql

I am pulling data from a MSSQL db using pyodbc which returns my data set in a list. This data then needs to be transferred into a MySQL db. I have written the following stored proc

Solution 1:

How can I use the full list from the MSSQL query, call the MySQL procedure one time and insert all the relevant data?

You can't do that with your stored procedure as written. It will only insert one row at a time, so to insert n rows you would have to call it n times.

Also, as far as I know you can't modify the stored procedure to insert n rows without using a temporary table or some other workaround because MySQL does not support table-valued parameters to stored procedures.

You can, however, insert multiple rows at once if you use a regular INSERT statement and .executemany. pymysql will bundle the inserts into one or more multi-row inserts

mssql_crsr = mssql_cnxn.cursor()
mssql_stmt = """\
SELECT 1 AS id, N'Alfa' AS txt
UNION ALL
SELECT 2 AS id, N'Bravo' AS txt
UNION ALL
SELECT 3 AS id, N'Charlie' AS txt
"""
mssql_crsr.execute(mssql_stmt)
mssql_rows = []
whileTrue:
    row = mssql_crsr.fetchone()
    if row:
        mssql_rows.append(tuple(row))
    else:
        break

mysql_cnxn = pymysql.connect(host='localhost', port=3307,
                             user='root', password='_whatever_',
                             db='mydb', autocommit=True)
mysql_crsr = mysql_cnxn.cursor()
mysql_stmt = "INSERT INTO stuff (id, txt) VALUES (%s, %s)"
mysql_crsr.executemany(mysql_stmt, mssql_rows)

The above code produces the following in the MySQL general_log

190430 10:00:53     4 Connect   root@localhost on mydb
            4 Query INSERT INTO stuff (id, txt) VALUES (1, 'Alfa'),(2, 'Bravo'),(3, 'Charlie')
            4 Quit  

Note that pymysql cannot bundle calls to a stored procedure in the same way, so if you were to use

mysql_stmt = "CALL stuff_one(%s, %s)"

instead of a regular INSERT then the general_log would contain

190430  9:47:10     3 Connect   root@localhost on mydb
            3 Query CALL stuff_one(1, 'Alfa')
            3 Query CALL stuff_one(2, 'Bravo')
            3 Query CALL stuff_one(3, 'Charlie')
            3 Quit