Pickle and pypyodbc cursor

Question

我想序列化查询的结果。这是我的例子：

import pypyodbc
import pickle
connection_string ='Driver={SQL Server Native Client 11.0};Server=localhost;' 
                       'Database=someDB;Uid=someLogin;Pwd=somePassword;'
connection = pypyodbc.connect(connection_string)
sql_query = "SELECT * FROM SomeTable"
cur = connection.cursor()
cur.execute(sql_query)
query_list = list(cur)
with open(r'D:query_result', 'wb') as f:
    pickle.dump(query_list, f)
cur.close()
connection.close()

它会生成以下错误：

_pickle.PicklingError: Can't pickle <class 'pypyodbc.TupleRow.<locals>.Row'>: 
attribute lookup Row on pypyodbc failed

我猜pickle并不完全支持pypyodbc对象。什么是变通办法？

Answer 1

我能够使用pypyodbc重新创建问题，而相同的代码似乎可以很好地使用pyodbc。pypyodbc的一个可能的解决方法可能是将结果转换为字典对象列表，然后对其进行序列化：

import pickle, pypyodbc
connection_string = (
    r"Driver={SQL Server Native Client 10.0};"
    r"Server=(local)SQLEXPRESS;"
    r"Database=myDb;"
    r"Trusted_connection=yes;"
)
connection = pypyodbc.connect(connection_string)
cur = connection.cursor()
cur.execute("SELECT * FROM Donors")
column_names = [x[0] for x in cur.description]
query_list = [dict(zip(column_names, row)) for row in cur.fetchall()]
with open(r'C:UsersGordDesktopquery_result', 'wb') as f:
    pickle.dump(query_list, f)
cur.close()
connection.close()