- changed status to wontfix
TypeDecorator types columns crashing on select([Model.__table__.c.custom_type_column])
select() doesn't work with custom columns when they are used as Foo.table.c.custom_column not Foo.custom_column, here is the code to demonstrate this
import json
from sqlalchemy.types import TypeDecorator, UnicodeText, VARCHAR
from sqlalchemy import ForeignKey, select
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer
Base = declarative_base()
engine = create_engine('sqlite://', echo=True)
Session = sessionmaker(autoflush=False, bind=engine)
class JSONSerialized(TypeDecorator):
"""
Represents an immutable structure as a json-serialized string.
"""
impl = UnicodeText
# impl = VARCHAR
def process_bind_param(self, value, dialect):
if value is not None:
value = unicode(json.dumps(value))
return value
def process_result_value(self, value, dialect):
if value is not None:
value = json.loads(value)
return value
def copy(self):
return JSONSerialized()
class Foo(Base):
__tablename__ = 'users'
id = Column(Integer(), primary_key=True)
values = Column(JSONSerialized(), nullable=False)
session = Session()
Base.metadata.create_all(engine)
session.add_all([i}) for i in xrange(3)](Foo(values={'i':))
session.commit()
# this works:
session.execute(select([Foo.values](Foo.id,)))
# this doesnt:
session.execute(select([Foo.__table__.c.values](Foo.__table__.c.id,)))
gives:
2012-12-07 14:48:31,570 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2012-12-07 14:48:31,570 INFO sqlalchemy.engine.base.Engine ()
2012-12-07 14:48:31,571 INFO sqlalchemy.engine.base.Engine
CREATE TABLE users (
id INTEGER NOT NULL,
"values" TEXT NOT NULL,
PRIMARY KEY (id)
)
2012-12-07 14:48:31,571 INFO sqlalchemy.engine.base.Engine ()
2012-12-07 14:48:31,572 INFO sqlalchemy.engine.base.Engine COMMIT
2012-12-07 14:48:31,573 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-12-07 14:48:31,574 INFO sqlalchemy.engine.base.Engine INSERT INTO users ("values") VALUES (?)
2012-12-07 14:48:31,574 INFO sqlalchemy.engine.base.Engine (u'{"i": 0}',)
2012-12-07 14:48:31,574 INFO sqlalchemy.engine.base.Engine INSERT INTO users ("values") VALUES (?)
2012-12-07 14:48:31,574 INFO sqlalchemy.engine.base.Engine (u'{"i": 1}',)
2012-12-07 14:48:31,575 INFO sqlalchemy.engine.base.Engine INSERT INTO users ("values") VALUES (?)
2012-12-07 14:48:31,575 INFO sqlalchemy.engine.base.Engine (u'{"i": 2}',)
2012-12-07 14:48:31,575 INFO sqlalchemy.engine.base.Engine COMMIT
2012-12-07 14:48:31,576 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-12-07 14:48:31,576 INFO sqlalchemy.engine.base.Engine SELECT users.id, users."values"
FROM users
2012-12-07 14:48:31,576 INFO sqlalchemy.engine.base.Engine ()
2012-12-07 14:48:31,577 INFO sqlalchemy.engine.base.Engine SELECT users.id, <bound method ImmutableColumnCollection.values of <sqlalchemy.sql.expression.ImmutableColumnCollection object at 0x2efa290>>
FROM users
2012-12-07 14:48:31,577 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
File "custom_type.py", line 51, in <module>
session.execute(select([Foo.__table__.c.values](Foo.__table__.c.id,)))
File "/home/virhilo/Projekty/sqlalchemy_experiments/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 884, in execute
clause, params or {})
File "/home/virhilo/Projekty/sqlalchemy_experiments/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 649, in execute
params)
File "/home/virhilo/Projekty/sqlalchemy_experiments/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 751, in _execute_clauseelement
compiled_sql, distilled_params
File "/home/virhilo/Projekty/sqlalchemy_experiments/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 865, in _execute_context
context)
File "/home/virhilo/Projekty/sqlalchemy_experiments/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 858, in _execute_context
context)
File "/home/virhilo/Projekty/sqlalchemy_experiments/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 330, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) near "<": syntax error u'SELECT users.id, <bound method ImmutableColumnCollection.values of <sqlalchemy.sql.expression.ImmutableColumnCollection object at 0x2efa290>> \nFROM users' ()
Comments (4)
-
repo owner -
reporter I just realized that in 0.7.x line it didn't crashed but returned {{{<built-in method values of RowProxy object at 0x2513910>}}} instead of values, {{{Foo.table.c'values'}}} return {{{<built-in method values of RowProxy object at 0x2513910>}}} in 0.8.0b1 as well but {{{Foo.table.c'values'()}}} raises {{{TypeError: 'Column' object is not callable}}} how to get the value then?:)
-
repo owner Foo.__table__.c['values']('values')
, no method call required.sometable.c.<x>
andsometable.c['<x>']('<x>')
are equivalent. -
reporter Yeah, you are right, I printed column {{{Foo.table.c'values'}}} instead of one from my query, thanks for explanation, and sorry for messing:)
- Log in to comment
heh. no, its not quite like that. "values" is a method on the Table.c column collection. So you'd have to call
__table__.c['values']('values')
for that particular name, sorry :)as far as "values" being there on
Table.c
, it's been that way for years as are some other methods, I'd probably not have done it like that today but we're sort of stuck with it for now.