Hi, I have a use case where I need to enable relationship loading on a
single object. However, I initialize the foreign key of this relationship
using an inline select statement.
from sqlalchemy import *
from sqlalchemy import select, and_, event, inspect
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import *
Base = declarative_base()
class Type(Base):
__tablename__ = 'type'
id = Column(Integer, primary_key=True)
type = Column(String(8), unique=True)
class Thing(Base):
__tablename__ = 'thing'
id = Column(Integer, primary_key=True)
type_id = Column(Integer, ForeignKey(Type.id), nullable=False)
text = Column(String(8))
type = relationship(Type)
__mapper_args__ = {
'polymorphic_on': select([Type.type]).where(Type.id == type_id).as_scalar(),
'with_polymorphic': '*'
}
def dict(self):
return {
'id': self.id,
'type': self.type.type,
'text': self.text
}
@event.listens_for(Thing, 'init', propagate=True)
def set_identity(instance, *args, **kwargs):
mapper = object_mapper(instance)
instance.type_id = select([Type.id]).where(Type.type ==
mapper.polymorphic_identity)
class Stuff(Thing):
stuff = Column(String(8))
__mapper_args__ = {
'polymorphic_identity': 'stuff'
}
class Junk(Thing):
junk = Column(String(8))
__mapper_args__ = {
'polymorphic_identity': 'junk'
}
e = create_engine('mysql+pymysql://user:password@localhost/test', echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([Type(type='stuff'), Type(type='junk')])
s.commit()
thing = Stuff(text='thing1', stuff='stuff1')
s.enable_relationship_loading(thing)
print(thing.dict())
>From the docs, I am fuzzy on whether or not this should be able to work
because the relationship loading is enabled on the foreign key
which is set here to a select statement. I get the error:
2017-02-02 08:25:58,789 INFO sqlalchemy.engine.base.Engine SELECT type.id
AS type_id, type.type AS type_type
FROM type
WHERE type.id = %(param_1)s
2017-02-02 08:25:58,789 INFO sqlalchemy.engine.base.Engine {'param_1':
<sqlalchemy.sql.selectable.Select at 0x3471b50; Select object>}
Traceback (most recent call last):
File "sqlalchemy_casc_backref_test.py", line 70, in <module>
print(thing.dict())
File "sqlalchemy_casc_backref_test.py", line 31, in dict
'type': self.type.type,
File "C:\Python35\lib\site-packages\sqlalchemy\orm\attributes.py", line
237, in __get__
return self.impl.get(instance_state(instance), dict_)
File "C:\Python35\lib\site-packages\sqlalchemy\orm\attributes.py", line
584, in get
value = self.callable_(state, passive)
File "C:\Python35\lib\site-packages\sqlalchemy\orm\strategies.py", line
560, in _load_for_state
return self._emit_lazyload(session, state, ident_key, passive)
File "<string>", line 1, in <lambda>
File "C:\Python35\lib\site-packages\sqlalchemy\orm\strategies.py", line
606, in _emit_lazyload
return loading.load_on_ident(q, ident_key)
File "C:\Python35\lib\site-packages\sqlalchemy\orm\loading.py", line 223,
in load_on_ident
return q.one()
File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 2754,
in one
ret = self.one_or_none()
File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 2724,
in one_or_none
ret = list(self)
File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 2795,
in __iter__
return self._execute_and_instances(context)
File "C:\Python35\lib\site-packages\sqlalchemy\orm\query.py", line 2818,
in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 945,
in execute
return meth(self, multiparams, params)
File "C:\Python35\lib\site-packages\sqlalchemy\sql\elements.py", line
263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line
1053, in _execute_clauseelement
compiled_sql, distilled_params
File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line
1189, in _execute_context
context)
File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line
1396, in _handle_dbapi_exception
util.reraise(*exc_info)
File "C:\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 186,
in reraise
raise value
File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line
1182, in _execute_context
context)
File "C:\Python35\lib\site-packages\sqlalchemy\engine\default.py", line
462, in do_execute
cursor.execute(statement, parameters)
File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 164, in
execute
query = self.mogrify(query, args)
File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 143, in
mogrify
query = query % self._escape_args(args, conn)
File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 123, in
_escape_args
return dict((key, conn.literal(val)) for (key, val) in args.items())
File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 123, in
<genexpr>
return dict((key, conn.literal(val)) for (key, val) in args.items())
File "C:\Python35\lib\site-packages\pymysql\connections.py", line 800, in
literal
return self.escape(obj, self.encoders)
File "C:\Python35\lib\site-packages\pymysql\connections.py", line 793, in
escape
return escape_item(obj, self.charset, mapping=mapping)
File "C:\Python35\lib\site-packages\pymysql\converters.py", line 27, in
escape_item
val = encoder(val, mapping)
File "C:\Python35\lib\site-packages\pymysql\converters.py", line 110, in
escape_unicode
return u"'%s'" % _escape_unicode(value)
File "C:\Python35\lib\site-packages\pymysql\converters.py", line 73, in
_escape_unicode
return value.translate(_escape_table)
AttributeError: 'Select' object has no attribute 'translate'
When I assume the orm attempts to load the relationship, by using the
select object itself rather than executing it as a subquery or join, as I
can see in the first line of the above.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
