quering a table that has a field with a percentage symbol (u'\x25') in it, crashes

[Former user]

I'm trying to use sqlalchemy to query a Microsoft Dynamics NAV database. The MS Dynamics NAV database is a MSSQL Server and has a lot of fields with names like: %, Cost %, etc. (note the percentage symbol in the field names).

If I execute a query it raises an error like the following whenever a field with a percentage symbol is involved:

Traceback (most recent call last):                                         
  File "test_percentage.py", line 29, in <module>                          
    session.query(MyTable).all()
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/orm/query.py", line 998, in all
    return list(self)
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/orm/query.py", line 1088, in __iter__
    return self._execute_and_instances(context)
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/orm/query.py", line 1091, in _execute_and_instances
    result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none(), _state=self._refresh_state)
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/orm/session.py", line 750, in execute
    clause, params or {})
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/engine/base.py", line 806, in execute
    return Connection.executors[c](c)(self, object, multiparams, params)
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/engine/base.py", line 853, in execute_clauseelement
    compiled=elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) > 1),
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/sql/expression.py", line 1194, in compile
    compiler.compile()
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/sql/compiler.py", line 196, in compile
    self.string = self.process(self.statement)
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/sql/compiler.py", line 199, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "<string>", line 2, in _compiler_dispatch
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/sql/compiler.py", line 498, in visit_select
    for co in select.inner_columns
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/sql/compiler.py", line 199, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "<string>", line 2, in _compiler_dispatch
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/sql/compiler.py", line 248, in visit_label
    self._truncated_identifier("colident", label.name) or label.name
  File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/sql/compiler.py", line 413, in _truncated_identifier
    anonname = name % self.anon_map
ValueError: incomplete format

You can recreate this error with the sqlite engine, use the following code:

#!/usr/bin/env python

from sqlalchemy import create_engine, Table, Column, Integer, \
    MetaData
from sqlalchemy.orm import mapper, sessionmaker


engine = create_engine('sqlite:///:memory:', echo=True)

metadata = MetaData()

my_table = Table('my_table', metadata,
                Column('test %', Integer, primary_key=True),)

metadata.create_all(engine)

class MyTable(object):
    pass

my_table2 = Table('my_table', metadata, autoload=True)
#mapper(MyTable, my_table2, properties={'test': my_table.c[%']('test)})
mapper(MyTable, my_table2)

Session = sessionmaker(bind=engine)
session = Session()

session.query(MyTable).all()

Without this bug corrected sqlalchemy is unusable for me. I will try to dive into the code to fix the bug, but since this is the first time I get in touch with sqlalchemy, I think it will be difficult for me to fix this bug in a reasonable time.

I hope someone can fix this bug or find a workaround. Thank you.

[Mike Bayer]

This is your fix and its quite simple:

Index: lib/sqlalchemy/sql/compiler.py
===================================================================
--- lib/sqlalchemy/sql/compiler.py  (revision 5505)
+++ lib/sqlalchemy/sql/compiler.py  (working copy)
@@ -407,10 +407,12 @@

         return bind_name

+    _trunc_re = re.compile(r'%(?!\()')
     def _truncated_identifier(self, ident_class, name):
         if (ident_class, name) in self.truncated_names:
             return self.truncated_names[name)]((ident_class,)

+        name = self._trunc_re.sub('%%', name)
         anonname = name % self.anon_map

         if len(anonname) > self.label_length:

unfortunately its an extra regexp search and replace right in the middle of a performance critical area, to meet a use case that is exceedingly rare - I have never seen column names with percent signs in them and almost never with spaces either. This patch also won't work for a column that happens to be named "%(some goofy name)s" - normally I'd say, "well who would name a column like that ?"...but here we are. So the string tokenization overall might have to be replaced with its previous less performant regexp approach (0.4 is using the old method so it probably works for this case).

Need some tests added to test/sql/select.py for this which also test %(some goofy name)s as a column name.

[Mike Bayer]

6cf4db7df37e56b700e363b66ab82f5921d1434b

[Mike Bayer]

Removing milestone: 0.5.0 (automated comment)