from sqlalchemy import *
meta = BoundMetaData('sqlite://', echo=True)
users = Table('users', meta,
Column('user_id', Integer, primary_key = True),
Column('user_name', String(40)))
addresses = Table('addresses', meta,
Column('address_id', Integer, primary_key = True),
Column('street', String(100)))
user_address = Table('user_address', meta,
Column('user_id', Integer, ForeignKey('users.user_id'),
primary_key=True),
Column('address_id', Integer, ForeignKey('addresses.address_id'),
primary_key=True))
meta.create_all()
class User(object): pass
mapper(User, users)
s = create_session().query(User).select_by
(users.c.user_id==user_address.c.user_id, user_address.c.address_id==1)
On Jun 10, 2006, at 12:03 PM, Yuan HOng wrote:
> Having read the relavant section in the docs on this topic and made
> some experimentations, I still have no clue how I can achieve the
> following task:
>
> Suppose we have a many-to-many relationship between the table users
> and address:
>
> users = Table('users', engine,
> Column('user_id', Integer, primary_key = True),
> Column('user_name', String(40)))
>
> addresses = Table('addresses', engine,
> Column('address_id', Integer, primary_key = True),
> Column('street', String(100)))
>
> user_address = Table('user_address', engine,
> Column('user_id', Integer, ForeignKey('users.user_id'),
> primary_key=True),
> Column('address_id', Integer, ForeignKey('addresses.address_id'),
> primary_key=True))
>
> class User(object): pass
>
> Now I'd like to select all users with address_id x and map them to
> class User. I attemped to build a select like this:
>
> s = select([users], users.c.user_id==user_address.c.user_id)
>
> and then define a mapper like in the example in documentation:
>
> m = mapper(User, s)
>
> This causes an error:
>
> ArgumentError: Mapping against a Select object requires that it has a
> name. Use an alias to give it a name, i.e. s =
> select(...).alias('myselect')
>
> Apparently the syntax in the example doesn't work as is. I added an
> alias to the select:
>
> m = mapper(User, s.alias('myselect'))
>
> But I still can't use the mapper to generate the SQL code I expected.
> The SQL generated for
>
> m.select_by(user_address.c.address_id == 1)
>
> is
>
> 'SELECT myselect.user_name AS myselect_user_name, myselect.user_id AS
> myselect_user_id \nFROM user_address, (SELECT users.user_id AS
> user_id, users.user_name AS user_name \nFROM users \nWHERE
> users.user_id = user_address.user_id) AS myselect \nWHERE
> user_address.address_id = %(user_address_address_id)s'
> {'user_address_address_id': 1}
>
> whereas I would expect the SQL to be something like:
>
> SELECT users.user_name, users.user_id FROM users, user_address WEHRE
> users.user_id = user_address.user_id and user_address.address_id = 1
>
> What's the correct way to do this in SA?
>
> Thanks.
> --
> Hong Yuan
>
> 大管家网上建材超市
> 装修装潢建材一站式购物
> http://www.homemaster.cn
> _______________________________________________
> Sqlalchemy-users mailing list
> Sqlalchemy-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
