Menu
I would like to know wether there is a way to combine joining two tables, and retrieving the two entities only with their relevant columns.
I want to avoid doing a thing such select * after joining two tables and getting only column id from Table A and column address from table B.
Working with python2.7
I heard of sqlalchemy feature called with_entities, but not sure how can it be mixed with this kind of join, lets work on this example from sqlalchemy tutorial:
u, a = session.query(User, Address).\
filter(User.id==Address.user_id).\
filter(Address.email_address=='[email protected]').\
first():
323
Python Flask and SQLAlchemy ORM All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.
I've found that the following works to join two tables: result = session. query(User, Document). select_from(join(User, Document)). filter(User.
The select() method of table object enables us to construct SELECT expression. The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method. Here, we have to note that select object can also be obtained by select() function in sqlalchemy.
SQLAlchemy is great because it provides a good connection / pooling infrastructure; a good Pythonic query building infrastructure; and then a good ORM infrastructure that is capable of complex queries and mappings (as well as some pretty stone-simple ones).
Check out Query.join()
id, address = session.query(A.id, B.address).\
join(B, B.user_id == A.id).\
filter(A.email_address=='[email protected]').\
first()
This can be done using a join or outerjoin depending on the use case, joins can be implicit or explicit. The .join second argument is the explicit join statement.
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
