percent signs in identifiers for percent-significant DBAPI drivers

[Chris Chua]

I have a column called "Bookings % change" and it looks like the table is created with the correct column name but when doing an table.insert(), SQLAlchemy is inserting on the column name but escaping the percent sign %%.

#!
(_mysql_exceptions.OperationalError) (1054, \"Unknown column 'Bookings %% change' in 'field list'\")

[Mike Bayer]

I would highly recommend not using table names with spaces in them, much less percent signs. The MySQL drivers generally consider percent signs to be significant, though depending on specifics, sometimes they do, sometimes they don't. I don't have any immediate fix for this issue.

[Chris Chua]

Thanks for the quick response! I'll take note and communicate this to my downstream users in the future.

As a developer I generally discourage unusual characters in column/database names just out of habit, but a user of my service chanced upon this on their use case.

[Mike Bayer]

hi @chrisirhc - I'm not able to produce any error. the percent signs are approrpriately escaped on all the major MySQL backends, the following test does every possible operation for all three major DBAPIs. Please adapt this into a test which illustrates the full stack trace and your error, thanks.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    percent = Column('percent % sign', String(50))

for dbapi in [
    'mysqldb', 'pymysql', 'mysqlconnector'
]:
    e = create_engine("mysql+%s://scott:tiger@localhost/test" % dbapi, echo=True)
    Base.metadata.drop_all(e)
    Base.metadata.create_all(e)

    insp = inspect(e)

    # test 1: we made the table columns correctly
    assert set([c['name'] for c in insp.get_columns('a')]) == \
        set(['id', 'percent % sign'])


    print("\n#################################\n%s:\n" % dbapi.upper())

    # test 2: ORM insert
    s = Session(e)
    s.add(A(percent='hi'))
    s.commit()

    # test 3: table.insert()
    s.execute(A.__table__.insert(), {"percent % sign": 'there'})

    # test 4: it all comes back
    assert s.execute(
        select([A.__table__.c['percent % sign']]).order_by(A.id)
    ).fetchall() == [('hi', ), ('there', )]

    s.close()

[Chris Chua]

I'm tracing the code where the insert statement is generated and it looks like this method is escaping the column identifier:

https://bitbucket.org/zzzeek/sqlalchemy/src/176ac6ab0915f99d378c7d9be67e9c0a73ab1800/lib/sqlalchemy/dialects/mysql/mysqlconnector.py?at=master#mysqlconnector.py-63:68

Is this a misconfiguration on my side?

[Chris Chua]

Looking at the check for _mysqlconnector_double_percents, it looks like this is occurring because I'm using MySQLdb-python 1.2.5. Updating to mysql-connector-python 2.

Also, note that I'm on Python 2.

[Mike Bayer]

the column identifier should be escaped for those backends. if you run the test script I posted, you'll see this is the case.

I'm looking to see exactly how you got the error message that you started out with.

[Chris Chua]

It doesn't look like that's expected on my backend. I'm running MySQL 5.6.26 and running the escaped identifier, such as: insert into some_table (`my %% perc`) values (1), fails with Unknown column 'my %% perc' in 'field list' on a table with my % perc as a column name.

Perhaps this is a change on MySQL?

[Mike Bayer]

it's not. it depends on the MySQL Python driver you are using whether or not its needed. so running the SQL on the command line is not enough of a test.

[Mike Bayer]

there's probably some mismatch of SQL being done by your client, like creating the table with two percent signs then selecting with one, and if there's a usability issue there it would be nice to know. however, no bug can be observed here right now.

[Chris Chua]

That's not right, I switched to using mysql-connector-python 2 and everything worked. I've also verified that the table was created correctly with one % sign. It was created using sqlalchemy as well. The lines create a table then insert data into it immediately, all via sqlalchemy (through pandas' to_sql)

This looks like MySQLdb-python 1.2.5 is not expecting the double percentage signs.

[Mike Bayer]

the test script I have above works against MySQLdb-python 1.2.5. it escapes the percent signs and runs everything fine.