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()
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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
