Sqlalchemy多次插入失败，列名中有百分比符号(%)

Question

我正在尝试使用sqlalchemy（版本1.0.11）对一个表执行多重插入，该表的列名中有带百分比符号的列。可以一次插入一行，但当使用con.execute()执行多重插入时，会引发OperationalError异常。

下面的代码再现了问题。创建了两个表，一个表的列中有百分比名称，另一个表没有。

1. 对列名中包含%的表执行多重插入操作失败
2. 在列名为%的表中逐个插入行是有效的
3. 在没有列名%的表上进行多次插入即可

    from sqlalchemy import *
    
    USER = 'root'
    PASSWORD = ''
    HOST = '127.0.0.1'
    DBNAME = 'test'
    
    connect_str = "mysql://{USER}:{PASSWORD}@{HOST}/{DBNAME}".format(
        USER = USER,
        PASSWORD = PASSWORD,
        HOST = HOST,
        DBNAME = DBNAME
    )
    
    engine = create_engine(connect_str, echo = False)
    
    metadata = MetaData()
    
    table_with_percent = Table('table_with_percent', metadata,
         Column('A%', Integer),
         Column('B%', Integer)
    )
    
    table_no_percent = Table('table_no_percent', metadata,
         Column('A', Integer),
         Column('B', Integer)
    )
    
    metadata.create_all(engine, checkfirst = True)
    
    #####################################
    # Create rows to be inserted
    
    rows = [(1,2), (3,4), (5,6)]
    table_with_percent_rows = [dict(zip(table_with_percent.c.keys(), row)) for row in rows]
    table_no_percent_rows = [dict(zip(table_no_percent.c.keys(), row)) for row in rows]
    

尝试插入

con = engine.connect()

失败了在表中插入列名称中的百分比符号。

OperationalError: (_mysql_exceptions.OperationalError) (1054, "Unknown column 'A%%' in 'field list'") [SQL: u'INSERT INTO table_with_percent (`A%%`, `B%%`) VALUES (%s, %s)'] [parameters: ((1, 2), (3, 4), (5, 6))]
    con.execute(table_with_percent.insert(), table_with_percent_rows)

但是这些行可以一个接一个地插入：

for row in table_with_percent_rows:
    con.execute(table_with_percent.insert(), row)

这很管用在列中没有百分比的表上多次插入

con.execute(table_no_percent.insert(), table_no_percent_rows)
con.close()

---

更新：我用sqlalchemy问题跟踪器打开了这个问题。

Answer 1

警告在列/表名称中使用花哨的字符（除A-Z、0-9和_外的任何字符）是开枪自杀的保证方法之一。

TL；DR这似乎是SQLAlchemy中的一个错误。它错误地转义了名称中带有%的列。您可以转到问题跟踪器并报告错误（如果尚未报告）。

您希望在sqlalchemy.engine.default.DefaultDialect的do_execute()方法中放置一个断点。它是将查询和参数从SA传递给数据库驱动程序的地方。在您的案例中，SQL看起来像这样：

INSERT INTO table_with_percent (`A%%`, `B%%`) VALUES (%s, %s)

是的，SQLAlchemy在列名中添加了第二个%。这显然会使列无效。你最好在问题跟踪器上询问为什么会发生这种情况。

由于一个幸运的巧合，插入一行就可以了。"%s" % ("value",)语法用于将参数格式化为查询。并且在格式字符串%%中是转义%字符的方式，所以格式化后查询返回正确的形式：

INSERT INTO table_with_percent (`A%`, `B%`) VALUES (1, 2)

在插入多行的情况下，幸运的巧合不会发生，因为params的格式化是不同的。您最终会得到以下查询：

INSERT INTO table_with_percent (`A%%`, `B%%`) VALUES (1, 2), (3, 4), (5, 6)

这显然是失败的。

PS您可以使用以下语法使其工作：

con.execute(table_with_percent.insert().values(table_with_percent_rows))