调用mysql存储过程时sqlalchemy错误

I'm using sqlalchemy to run query on a MySql server from python.

我正在使用sqlalchemy从python在MySql服务器上运行查询。

I initialize sqlalchemy with:

我用以下内容初始化sqlalchemy：

engine = create_engine("mysql+mysqlconnector://{user}:{password}@{host}:{port}/{database}".format(**connection_params))
conn = engine.connect()

Where connection_params is a dict containing the server access details.

其中connection_params是包含服务器访问详细信息的dict。

I'm running this query:

我正在运行此查询：

SELECT 
new_db.asset_specification.identifier_code, 
new_db.asset_specification.asset_name, 
new_db.asset_specification.asset_type, 
new_db.asset_specification.currency_code, 
new_db.sector_map.sector_description, 
new_db.super_sector_map.super_sector_description, 
new_db.country_map.country_description, 
new_db.country_map.country_macro_area 

FROM new_db.asset_specification 
INNER JOIN new_db.identifier_code_legal_entity_map on new_db.asset_specification.identifier_code = new_db.identifier_code_legal_entity_map.identifier_code 
INNER JOIN new_db.legal_entity_map on projecthf_db.identifier_code_legal_entity_map.legal_entity_code = new_db.legal_entity_map.legal_entity_code 
INNER JOIN new_db.sector_map on new_db.legal_entity_map.legal_entity_sector = new_db.sector_map.sector_code 
INNER JOIN new_db.super_sector_map on projecthf_db.legal_entity_map.legal_entity_super_sector = new_db.super_sector_map.super_sector_code 
INNER JOIN new_db.country_map on new_db.legal_entity_map.legal_entity_country = new_db.country_map.country_code 
WHERE new_db.asset_specification.identifier_code = str_identifier_code;

Using conn.execute(query) (where i set query equal to the string above).

使用conn.execute（查询）（我将查询设置为等于上面的字符串）。

This runs just fine.

这运行得很好。

I tried to put my query in a stored procedure like:

我试图将我的查询放在一个存储过程中，如：

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_anag`(IN str_identifier_code varchar(100))
BEGIN
SELECT 
new_db.asset_specification.identifier_code, 
new_db.asset_specification.asset_name, 
new_db.asset_specification.asset_type, 
new_db.asset_specification.currency_code, 
new_db.sector_map.sector_description, 
new_db.super_sector_map.super_sector_description, 
new_db.country_map.country_description, 
new_db.country_map.country_macro_area 

FROM new_db.asset_specification 
INNER JOIN new_db.identifier_code_legal_entity_map on new_db.asset_specification.identifier_code = new_db.identifier_code_legal_entity_map.identifier_code 
INNER JOIN new_db.legal_entity_map on projecthf_db.identifier_code_legal_entity_map.legal_entity_code = new_db.legal_entity_map.legal_entity_code 
INNER JOIN new_db.sector_map on new_db.legal_entity_map.legal_entity_sector = new_db.sector_map.sector_code 
INNER JOIN new_db.super_sector_map on projecthf_db.legal_entity_map.legal_entity_super_sector = new_db.super_sector_map.super_sector_code 
INNER JOIN new_db.country_map on new_db.legal_entity_map.legal_entity_country = new_db.country_map.country_code 
WHERE new_db.asset_specification.identifier_code = str_identifier_code;

END

I can run the stored procedure from the query editor in mysql workbench with CALL new_db.test_anag('000000') and I get the desired result (which is a single line).

我可以使用CALL new_db.test_anag（'000000'）从mysql workbench中的查询编辑器运行存储过程，然后得到所需的结果（这是一行）。

Now I try to run:

现在我尝试运行：

res = conn.execute("CALL new_db.test_anag('000000')")

But it fails with the following exception

但它失败了以下例外

sqlalchemy.exc.InterfaceError: (mysql.connector.errors.InterfaceError) Use multi=True when executing multiple statements [SQL: "CALL projecthf_db.test_anag('0237400')"]

sqlalchemy.exc.InterfaceError：（mysql.connector.errors.InterfaceError）执行多个语句时使用multi = True [SQL：“CALL projecthf_db.test_anag（'0237400'）”]

I looked around but I can't find anything useful on this error and for the love of me I can't get my head around it. I'm not an expert on either Mysql nor sqlalchemy (or anything RDBMS) but this one looks like it should be easy to fix. Let me know if more info is required.

我环顾四周，但在这个错误中找不到任何有用的东西，对于我的爱，我无法理解它。我不是Mysql或sqlalchemy（或任何RDBMS）的专家，但这个看起来应该很容易修复。如果需要更多信息，请与我们联系。

Thank in advance for the help

提前感谢您的帮助

1 个解决方案

#1

From reading a related question it can be seen that mysql.connector automatically fetches and stores multiple result sets when executing stored procedures producing such, even if only one result set is produced. SQLAlchemy on the other hand does not support multiple result sets – directly. To execute stored procedures use callproc(). To access a DB-API cursor in SQLAlchemy you have to use a raw connection. In case of mysql.connector the produced result sets can be accessed using stored_results():

从阅读相关问题可以看出，mysql.connector在执行生成这样的存储过程时会自动获取并存储多个结果集，即使只生成了一个结果集。另一方面，SQLAlchemy不支持多个结果集 - 直接。要执行存储过程，请使用callproc（）。要在SQLAlchemy中访问DB-API游标，您必须使用原始连接。在mysql.connector的情况下，可以使用stored_results（）访问生成的结果集：

from contextlib import closing

# Create a raw MySQLConnection
conn = engine.raw_connection()

try:
    # Get a MySQLCursor
    with closing(conn.cursor()) as cursor:
        # Call the stored procedure
        result_args = cursor.callproc('new_db.test_anag', ['000000'])
        # Iterate through the result sets produced by the procedure
        for result in cursor.stored_results():
            result.fetchall()

finally:
    conn.close()