问题描述
我正在使用pyodbc从MSSQL数据库中提取数据,该数据返回列表中的数据集.然后,需要将这些数据传输到MySQL数据库中.我已经在MySQL中编写了以下存储过程.
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
如果我一次插入一条记录,则该程序运行正常.因此,现在,我将遍历MSSQL查询中的列表,并为每个记录调用该过程.我正在使用此代码:
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()
我在生产中遇到的问题是此列表每天将包含10,000-100,000.遍历这些数据似乎不是一种优化的方式来处理此问题.
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.
如何使用MSSQL查询中的完整列表,一次调用MySQL过程并插入所有相关数据?
How can I use the full list from the MSSQL query, call the MySQL procedure one time and insert all the relevant data?
推荐答案
您无法使用书面形式的存储过程来做到这一点.它一次只能插入一行,因此要插入 n 行,您将不得不称其为 n 次.
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.
据我所知,如果不使用临时表或其他解决方法,您将无法修改存储过程以插入 n 行,因为MySQL不支持存储过程的表值参数
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.
但是,如果使用常规的INSERT语句和.executemany
,则可以一次插入多行. pymysql会将插入物捆绑成一个或多个多行插入物
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)
上面的代码在MySQL general_log中产生以下内容
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
请注意,pymysql无法以相同的方式将调用捆绑到存储过程,因此如果要使用
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)"
而不是常规的INSERT,那么general_log将包含
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
这篇关于如何使用pymysql通过存储过程将pyodbc mssql查询返回的列表插入mysql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!