multiple insert fails with percentage symbol (%) in column name

I am trying to use sqlalchemy (version 1.0.12) to perform a multi-insert on a table that has columns with percentage symbols in the column names. Rows can be inserted one at a time without a problem, but when the performing a multi-insert with con.execute() a OperationalError exception is raised.

The code below reproduces the problem. Two tables are created, one with percentage names in columns, the other without. 1. The multi-insert on the table with % in the column names fails. 2. Inserting rows one by one in the table with the % in column names works. 3. Multi-insert on the table without % in column names works.

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]

#########################
# Try the inserts

con = engine.connect()

# 1. THIS FAILS! Mutli insert on table with percentage symbol in column names.
# 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)

# 2. But the rows can be inserted one by one:
for row in table_with_percent_rows:
    con.execute(table_with_percent.insert(), row)

# 3. This works! Multi insert on table with no percent in columns
con.execute(table_no_percent.insert(), table_no_percent_rows)

con.close()

Comments (7)