On Feb 26, 2010, at 9:05 AM, Marcin Krol wrote:
> Hello everyone,
>
> I'm having a silly problem on eagerload, when I do:
>
> users = session.query(User).options(eagerload('usercity')).all()
>
> or
>
> users = session.query(User).options(eagerload(User.usercity)).all()
do not use a string for your "order by" in a relation(). The eager loader has
no way to adapt such an expression.
>
> Exception:
>
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) invalid reference to
> FROM-clause entry for table "city"
> LINE 2: ...city AS city_1 ON city_1.id = user_.city_id ORDER BY city.id
> ^
> HINT: Perhaps you meant to reference the table alias "city_1".
> 'SELECT user_.id AS user__id, user_.name AS user__name, user_.city_id AS
> user__city_id, city_1.id AS city_1_id, city_1.name AS city_1_name \nFROM
> user_ LEFT OUTER JOIN city AS city_1 ON city_1.id = user_.city_id ORDER BY
> city.id' {}
>
>
> When I do:
>
> users = session.query(User).all()
>
> ..it's all right.
>
>
>
>
>
> Complete code:
>
>
> import sqlalchemy
> import warnings
> import random
>
> from sqlalchemy import create_engine, Table, Column, Integer, Unicode,
> MetaData, ForeignKey
> from sqlalchemy.orm import sessionmaker, mapper, relation, eagerload
> from sqlalchemy.exceptions import SAWarning
>
> warnings.simplefilter('error', SAWarning)
>
>
> engine = create_engine("postgres://postgres:qwe123...@localhost/ts",
> convert_unicode=True, assert_unicode=True)
>
> metadata = MetaData(bind=engine)
>
> user_table = sqlalchemy.Table('user_', metadata,
> Column('id', Integer, primary_key=True),
> Column('name', Unicode(30)),
> Column('city_id', Integer, ForeignKey('city.id'))
> )
>
> city_table = Table('city', metadata,
> Column('id', Integer, primary_key=True),
> Column('name', Unicode(30))
> )
>
>
> class User(object):
> def __init__(self, name):
> self.name = name
>
>
> class City(object):
> def __init__(self, name):
> self.name = name
>
>
> mapper(User, user_table, properties={'usercity':relation(City,
> order_by='city.id', backref='user_backref')})
> mapper(City, city_table, properties={'users':relation(User,
> order_by='user_.id', backref='city_backref')})
>
> Sesm = sessionmaker(bind=engine)
> session = Sesm()
>
>
> def add_some():
> metadata.create_all()
> with open(r'c:\bin\diceware.wordlist.asc') as f:
> for i in range(1,10):
> for i in range(random.randint(1,500)):
> x = f.readline()
> u = User(unicode(f.readline().split()[1]))
> for i in range(random.randint(1,500)):
> x = f.readline()
> c = City(unicode(f.readline().split()[1]))
> u.city = c
> session.add(u)
> session.add(c)
> session.commit()
>
> def del_all():
> users = session.query(User).all()
> for u in users:
> session.delete(u)
> session.commit()
> cities = session.query(City).all()
> for c in cities:
> session.delete(c)
> session.commit()
>
>
> if __name__ == '__main__':
> del_all()
> add_some()
> ## users = session.query(User).all()
> users = session.query(User).options(eagerload(User.usercity)).all()
> for u in users:
> print 'name', u.name, 'city', u.usercity.name
>
>
> Complete exception:
>
> Traceback (most recent call last):
> File "<string>", line 244, in run_nodebug
> File "C:\Documents and Settings\Administrator\Desktop\ts.py", line 76, in
> <module>
> users = session.query(User).options(eagerload(User.usercity)).all()
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.8-py2.6.egg\sqlalchemy\orm\query.py",
> line 1267, in all
> return list(self)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.8-py2.6.egg\sqlalchemy\orm\query.py",
> line 1361, in __iter__
> return self._execute_and_instances(context)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.8-py2.6.egg\sqlalchemy\orm\query.py",
> line 1364, in _execute_and_instances
> result = self.session.execute(querycontext.statement, params=self._params,
> mapper=self._mapper_zero_or_none())
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.8-py2.6.egg\sqlalchemy\orm\session.py",
> line 753, in execute
> clause, params or {})
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.8-py2.6.egg\sqlalchemy\engine\base.py",
> line 824, in execute
> return Connection.executors[c](self, object, multiparams, params)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.8-py2.6.egg\sqlalchemy\engine\base.py",
> line 874, in _execute_clauseelement
> return self.__execute_context(context)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.8-py2.6.egg\sqlalchemy\engine\base.py",
> line 896, in __execute_context
> self._cursor_execute(context.cursor, context.statement,
> context.parameters[0], context=context)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.8-py2.6.egg\sqlalchemy\engine\base.py",
> line 950, in _cursor_execute
> self._handle_dbapi_exception(e, statement, parameters, cursor, context)
> File
> "C:\Python26\lib\site-packages\sqlalchemy-0.5.8-py2.6.egg\sqlalchemy\engine\base.py",
> line 931, in _handle_dbapi_exception
> raise exc.DBAPIError.instance(statement, parameters, e,
> connection_invalidated=is_disconnect)
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) invalid reference to
> FROM-clause entry for table "city"
> LINE 2: ...city AS city_1 ON city_1.id = user_.city_id ORDER BY city.id
> ^
> HINT: Perhaps you meant to reference the table alias "city_1".
> 'SELECT user_.id AS user__id, user_.name AS user__name, user_.city_id AS
> user__city_id, city_1.id AS city_1_id, city_1.name AS city_1_name \nFROM
> user_ LEFT OUTER JOIN city AS city_1 ON city_1.id = user_.city_id ORDER BY
> city.id' {}
> >>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
