-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi.
Here is a simple example of using joins in SQLAlchemy:
from sqlalchemy import schema, types, sql, create_engine
metadata = schema.MetaData()
x = schema.Table(
'x', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('x', types.Text, nullable=False)
)
y = schema.Table(
'y', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('y', types.Text, nullable=False),
schema.ForeignKeyConstraint(['id'], [x.c.id])
)
engine = create_engine('sqlite://')
engine.create(metadata)
try:
engine.execute(x.insert(), id=1, x='X')
engine.execute(y.insert(), id=1, y='Y')
query = sql.join(x, y).select()
r = engine.execute(query).fetchone()
print r['id']
finally:
engine.drop(metadata)
This code will raise an:
sqlalchemy.exc.InvalidRequestError: Ambiguous column name 'id' in result
set! try 'use_labels' option on select statement.
Now, I would like to avoid using labels, since it will make code much
more verbose.
What is the reason why SQLAlchemy is including the `id` column two times?
After all, it should know that the `id` column is being used for the join.
In plain SQL (Postgresql database):
manlio=> select * from x NATURAL JOIN y;
id | x | y
- ----+---+---
1 | X | Y
(1 riga)
manlio=> select * from x JOIN y USING (id);
id | x | y
- ----+---+---
1 | X | Y
(1 riga)
the `id` column is being added only one time, as it should be.
Thanks Manlio
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkt62u8ACgkQscQJ24LbaUQGkwCfa/cSeg9xk1AFHTqTuDrA+LPt
aREAn0SiG75RNFav7cBv2M0Cacu2iyUx
=I+f+
-----END PGP SIGNATURE-----
--
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.
