How can I insert a list returned from pyodbc mssql query into mysql through stored procedure using pymysql

Question

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 procedure in MySQL.

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_int_pmt`(
    IN pmtamt DECIMAL(16,10),
        IN pmtdt DATETIME,
        IN propmtref VARCHAR(128),
        IN rtdinv_id INT(11)
    )
BEGIN
    INSERT INTO ay_financials.payment
    (
    pmtamt,
        pmtdt,
        propmtref,
        rtdinv_id
    )
VALUES
    (
    pmtamt,
        pmtdt,
        propmtref,
        rtdinv_id
    );

END

The procedure works fine if I am inserting one record at the time. So, for now, I am iterating over the list from my MSSQL query and call the procedure for each record. I am using this code:

cursor = cnxn.cursor()

cursor.execute("""  SELECT *
            FROM [%s].[dbo].[pmt]
            WHERE pmtdt BETWEEN '2018-01-01' AND '2018-12-31'""" %(database))

a = cursor.fetchmany(25)

cnxn.close()

import pymysql

# MySQL configurations
un = 'ssssssss'
pw = '****************'
db = 'ay_fnls'
h = '100.100.100.100'

conn = pymysql.connect(host=h, user=un, password=pw, db=db, cursorclass=pymysql.cursors.DictCursor)

cur = conn.cursor()

for ay in a:
    cur.callproc('sp_int_pmt',(ay.pmtamt,ay.pmtdt,ay.propmtref,ay.rtdinv_id))

conn.commit()

The problem I will have in production is this list will contain 10,000-100,000 every day. Iterating over that data doesn't seem like an optimized way to handle this.

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

Do the MySQL server permissions require you to use that stored procedure to perform the inserts, or are you also able to INSERT rows directly into the table with the credentials you use to connect from your Python script? — Gord Thompson
– Gord Thompson, Commented Apr 24, 2019 at 23:27
there is no requirement that i have to use stored procedures, i am doing that to keep consistency with all of the other work required in this project.. — gacto
– gacto, Commented Apr 29, 2019 at 15:02

Gord Thompson · Accepted Answer · 2019-04-30 16:10:42Z

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 = []
while True:
    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

answered Apr 30, 2019 at 16:10

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gacto Over a year ago

gord - this makes sense, but let me ask one clarifying question. In your example, SELECT 1... UNION ALL, SELECT 2... UNION ALL. On days where I have 100,000 records that I need to move to MySQL, won't I wind up making 100,000 SELECT statement calls?

Gord Thompson Over a year ago

That was simply a way to create some test data without creating a table. Your code would just do a SELECT ... FROM tablename or similar.

gacto Over a year ago

ok that makes sense, just wasn't sure if there was a requirement for individual SELECT statements. I'll give this a try and let you know what happens.

Collectives™ on Stack Overflow

How can I insert a list returned from pyodbc mssql query into mysql through stored procedure using pymysql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related