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

Question

I am trying to use sqlalchemy (version 1.0.11) 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()

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)

But the rows can be inserted one by one:

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

This works! Multi insert on table with no percent in columns

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

---

UPDATE: I opened this issue with the sqlalchemy issue tracker.

Answer 1

WARNING Using fancy characters (any except A-Z, 0-9 and _) in the column/table name is one of the guaranteed methods to shot yourself in the foot.

TL;DR It seems to be a bug in SQLAlchemy. It incorrectly escapes columns with % in name. You can go to the issue tracker and report a bug (if it's not reported yet).

You want to put a breakpoint in the do_execute() method of sqlalchemy.engine.default.DefaultDialect. It is the place, where your query and parameters are passed from SA to the database driver. In your case SQL looks like this:

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

Yes, SQLAlchemy added second % to the column names. Which obviously makes columns invalid. You're better ask on the issue tracker why it happens.

With inserting of a single row it works, because of a lucky coincidence. "%s" % ("value",) syntax is used to format parameters into query. And in the format string %% is the way to escape % character, so after formatting query returns to the correct form:

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

In case of inserting multiple rows lucky coincidence doesn't happen, since formatting of the params is done differently. And you end up with the following query:

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

Which obviously fails.

PS You can use following syntax to get it working:

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