SQLAlchemy调用MySQL存储过程返回重复历史结果问题排查

我有若干MySQL存储过程，直接在MySQL中执行时结果正常，但通过SQLAlchemy调用时，每次调用都会返回新结果叠加之前的历史结果。例如第一次调用返回4行，第二次应返回5行，实际却返回9行（5+4），该现象持续存在。

存储过程代码 #

DELIMITER $$
CREATE DEFINER=`service`@`localhost` PROCEDURE `sp_get_filtered_items`(IN theArray varchar(255), IN searchText varchar(500))
BEGIN
    drop table if exists tmpFilteredItems; 
    if length(theArray) = 0 then
        create temporary table tmpFilteredItems
        as 
        (select * from vw_items
        where alltext like CONCAT('%', searchText, '%'));
    else
        call sp_get_code_table(theArray); -- generates tmpCodeTable from array.
        create temporary table tmpFilteredItems
        as
        (select * from vw_items 
            inner join tmpCodeTable on vw_items.instanceof = tmpCodeTable.codeCol
            where alltext like CONCAT('%', searchText, '%'));
    end if;
    select * from tmpFilteredItems;
END$$
DELIMITER ;

Python调用代码 #

def make_recordsets(self):
    raw_conn = self.eng.get_engine().raw_connection()

    try:
        # 1. create filtered results into temp table and retrieve
        cursor = raw_conn.cursor()
        cursor.callproc('sp_get_filtered_items', (self.instanceof_string, self.keyphrase,))
        for result in cursor.stored_results():
            tuples = result.fetchall()
            self.__items_to_dict(tuples)
        print(len(self.filtered_items))
        cursor.close()
        # 2. create properties list for follow-on searching; joins to temp table
        cursor = raw_conn.cursor()
        cursor.callproc('sp_get_filtered_props')
        for result in cursor.stored_results():
            tuples = result.fetchall()
            self.__props_to_dict(tuples)
        print(len(self.filtered_props))
        cursor.close()
        # 3. pick property with highest count for use in graphing link process.
        if not len(self.filtered_props) == 0:
            d = self.filtered_props[0] # get first row in rs, which has the highest count
            self.link_property = d['property']
            self.link_property_label = d['propertyLabel']
        # 4. create values list for use in graph creation; proc joins to temp table
        cursor = raw_conn.cursor()
        cursor.callproc('sp_get_filtered_values')
        for result in cursor.stored_results():
            tuples = result.fetchall()
            self.__values_to_dict(tuples)
        print(len(self.filtered_values))
        cursor.close()
        raw_conn.commit() # commit transactions to db
    except Exception as e:
        print(e.args)
        raw_conn.rollback()

    finally:
        raw_conn.close()
        self.eng.close()

问题原因与解决方案 #

原因分析 #

问题并非数据库保留历史结果，而是Python类实例的状态残留：

• self.filtered_items、self.filtered_props、self.filtered_values作为类的实例属性，每次调用make_recordsets时，__items_to_dict等方法是在原有列表基础上追加新数据，而非清空后重新赋值。
• MySQL的临时表是会话级别的，每次raw_conn.close()后会话结束，临时表会自动销毁，不存在数据库端的数据残留。直接在MySQL客户端执行时，每次调用都是独立会话，不会出现对象状态叠加的问题。

解决方案 #

在每次调用存储过程获取数据前，先清空对应的实例属性：

def make_recordsets(self):
    # 清空历史数据，避免叠加
    self.filtered_items = []
    self.filtered_props = []
    self.filtered_values = []
    
    raw_conn = self.eng.get_engine().raw_connection()
    # 后续原有代码保持不变...

也可以选择在__items_to_dict等方法内部，先清空目标列表再添加新数据，而非直接追加。

内容的提问来源于stack exchange，提问作者Andre Hulet