python - sqlalchemy 查询根据值卡住

Question

我也是 python 和 MySql 的新手。我正在尝试使用 python 将 excel 文件中收到的数据导入 MySql。脚本运行正常，但是我有一个简单的选择部分，它不能正常工作。它尝试从存储已导入文件名的单个表中检索数据，按文件类型分类。所以表格有这两列和几百行。

CREATE TABLE `imp_doc` (
  `docname` varchar(50) NOT NULL,
  `doc_type` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`docname`),
  KEY `doctype` (`doc_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

脚本运行所有“文件类型”，除了一种。当那个作为值发送给选择时，脚本停止而没有错误消息。

“doc_type”参数设置为“ibot”的脚本使其停止运行!

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool

engine = create_engine('mysql+mysqlconnector://userid:password@0.0.0.0:3306/mydb', poolclass = NullPool)
engine.echo = True
conn = engine.connect()

saved_list =[]
doc_type="ibot"
query = "SELECT docname FROM imp_doc WHERE doc_type = %s"
cur=conn.execute(query, (doc_type,))
for (docname) in cur:
    dn=docname[0]
    dn=unicode.encode(dn,'cp1252')
    saved_list.append(str(dn))
print saved_list
cur.close()

结果，似乎 python 正在等待来自 MySql 的结果，但在数据库中，连接处于“ sleep ”状态。

2016-12-28 14:18:15,407 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2016-12-28 14:18:15,407 INFO sqlalchemy.engine.base.Engine {}
2016-12-28 14:18:15,598 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2016-12-28 14:18:15,598 INFO sqlalchemy.engine.base.Engine {}
2016-12-28 14:18:15,987 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2016-12-28 14:18:15,997 INFO sqlalchemy.engine.base.Engine {}
2016-12-28 14:18:16,187 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2016-12-28 14:18:16,187 INFO sqlalchemy.engine.base.Engine {}
2016-12-28 14:18:16,747 INFO sqlalchemy.engine.base.Engine SELECT docname FROM imp_doc WHERE doc_type = %s
2016-12-28 14:18:16,757 INFO sqlalchemy.engine.base.Engine ('ibot',)

当我用其他 doc_type 值运行相同的代码时，它完美地工作:

2016-12-28 14:31:45,105 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2016-12-28 14:31:45,105 INFO sqlalchemy.engine.base.Engine {}
2016-12-28 14:31:45,306 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2016-12-28 14:31:45,306 INFO sqlalchemy.engine.base.Engine {}
2016-12-28 14:31:45,727 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2016-12-28 14:31:45,727 INFO sqlalchemy.engine.base.Engine {}
2016-12-28 14:31:45,947 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2016-12-28 14:31:45,957 INFO sqlalchemy.engine.base.Engine {}
2016-12-28 14:31:46,507 INFO sqlalchemy.engine.base.Engine SELECT docname FROM imp_doc WHERE doc_type = %s
2016-12-28 14:31:46,507 INFO sqlalchemy.engine.base.Engine ('wiz',)
['WIZARD_2015-W1001-20160102.xlsx', 'WIZARD_2016-W0104-20160612.xlsx', 'WIZARD_2016-W16.xlsx', 'WIZARD_2016-W17.xlsx', 'WIZARD_2016-W18.xlsx', 'WIZARD_2016-W19.xlsx', 'WIZARD_2016-W20.xlsx', 'WIZARD_2016-W21.xlsx', 'WIZARD_2016-W22.xlsx', 'WIZARD_2016-W23.xlsx', 'WIZARD_2016-W24.xlsx', 'WIZARD_2016-W25.xlsx', 'WIZARD_2016-W26.xlsx', 'WIZARD_2016-W27.xlsx', 'WIZARD_2016-W28.xlsx', 'WIZARD_2016-W29.xlsx', 'WIZARD_2016-W30.xlsx', 'WIZARD_2016-W31.xlsx', 'WIZARD_2016-W32.xlsx', 'WIZARD_2016-W33.xlsx', 'WIZARD_2016-W34.xlsx', 'WIZARD_2016-W35.xlsx', 'WIZARD_2016-W36.xlsx', 'WIZARD_2016-W37.xlsx', 'WIZARD_2016-W38.xlsx', 'WIZARD_2016-W39.xlsx', 'WIZARD_2016-W40.xlsx', 'WIZARD_2016-W41.xlsx', 'WIZARD_2016-W42.xlsx', 'WIZARD_2016-W43.xlsx', 'WIZARD_2016-W44.xlsx', 'WIZARD_2016-W45.xlsx', 'WIZARD_2016-W46.xlsx', 'WIZARD_2016-W47.xlsx', 'WIZARD_2016-W48.xlsx', 'WIZARD_2016-W49.xlsx', 'WIZARD_2016-W50.xlsx']

当然也可以在 MySql 中运行选择:

docname                                 doc_type    
Hotspot Detail 2.0 iBot_20161226.xlsx   ibot
Hotspot Detail 2.0 iBot_20161225.xlsx   ibot
Hotspot Detail 2.0 iBot_20161224.xlsx   ibot
20161226 def.xlsx                       def
20161225 def.xlsx                       def
20161224 def.xlsx                       def
20161223 def.xlsx                       def

我确定问题不在于参数的值，但请帮助我理解为什么相同的选择有时有效而在其他情况下无效。谢谢

Answer 1

您是否尝试过使用 SQLAlchemy text()使用您的代码运行？

例如像这样:

from sqlalchemy.sql import text

query = text("SELECT docname FROM imp_doc WHERE doc_type = :type")
cur=conn.execute(query, type=doc_type)

从技术上讲，您所得到的看起来是有效的，但是使用 text() 是编写文本 SQL 的 SQLAlchemy 最佳实践，如果不使用它有时会出现意外行为(根据我的经验).