- changed status to invalid
Firebird dialect strange behaivour wuth uppercase table names.
Issue #3030
invalid
When i use field/table names written in uppercase with firebird dialect, selecting stops working. I use
Python3.3, fdb==1.4, sqlalchemy==0.9.4
this is also reproducable with python 2.7.
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.schema import MetaData, Column
from sqlalchemy.types import Integer
metadata = MetaData()
DeclarativeBase = declarative_base(metadata=metadata)
class Entity_U(DeclarativeBase):
__tablename__ = 'ENTITY_U'
id = Column('ENTITY_ID', Integer, primary_key=True)
class Entity_L(DeclarativeBase):
__tablename__ = 'entity_l'
id = Column('entity_id', Integer, primary_key=True)
if __name__ == '__main__':
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
engine = create_engine('firebird://sysdba:123456@localhost/var/local/fdb/test.fdb')
session = scoped_session(sessionmaker(engine))
metadata.bind = engine
metadata.drop_all()
metadata.create_all()
session.add(Entity_U(id=1))
session.commit()
session.add(Entity_L(id=1))
session.commit()
if 1:
session.query(Entity_U).first()
else:
session.query(Entity_L).first()
this code crashes with
Traceback (most recent call last):
File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/engine/result.py", line 70, in __getitem__
processor, obj, index = self._keymap[key]
KeyError: Column('ENTITY_ID', Integer(), table=<ENTITY_U>, primary_key=True, nullable=False)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/eugeny/work/fb/schema.py", line 55, in <module>
session.query(Entity_U).first()
File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/query.py", line 2333, in first
ret = list(self[0:1])
File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/query.py", line 2200, in __getitem__
return list(res)
File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/loading.py", line 72, in instances
rows = [process[0](row, None) for row in fetch]
File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/loading.py", line 72, in <listcomp>
rows = [process[0](row, None) for row in fetch]
File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/loading.py", line 360, in _instance
tuple([row[column] for column in pk_cols])
File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/orm/loading.py", line 360, in <listcomp>
tuple([row[column] for column in pk_cols])
File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/engine/result.py", line 72, in __getitem__
processor, obj, index = self._parent._key_fallback(key)
File "/home/eugeny/work/ve33/lib/python3.3/site-packages/sqlalchemy/engine/result.py", line 332, in _key_fallback
expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'ENTITY_U.ENTITY_ID'"
when i select from table defined with uppercase name, but it works fine for lowercase table.
also there is no problem with other dialects, it is only firebird issue.
Comments (4)
-
repo owner -
repo owner if you want to force the name to be quoted or unquoted, send the flag quote=True|False to Table, or Column. This can allow you to force all names to be case sensitive or insensitive.
-
reporter thank you for explanation.
-
repo owner - changed milestone to 1.0.xx
- Log in to comment
the documentation doesn't have enough detail on this, but if you look at a docuement such as that for Column.name, SQLAlchemy refers to ALL UPPERCASE names as "case sensitive" names, which are quoted. So for case-insensitive names, use all lowecase. Firebird and Oracle are confusing here because they have the opposite convention - case insensitive names are displayed as UPPERCASE. However SQLAlchemy reverses this convention as table/column names move between Python and the database.