AF wrote:
> Hello,
>
> For instance if I have data tables for users, orders, and
> order_line_items how would I generate a report that lists:
>
> 1) users,
> 2) and for each user, his/her orders,
> 3) and for each order, all the order_line_items
>
> I guess I could do it in a loop in python, but I wonder of there is a
> better way using SQLAlchemy.
>
> Thank you,
> :)
>
I'm assuming you already have classes and mappers set up for your users,
orders, and order line items, with ORM relations set up for User->Order
and Order->OrderLineItem relations.
Looping in python is your best bet for formatting the report, but you
have some options regarding how to get the data from the database:
1. Lazy loading (default SQLAlchemy behavior): SQLAlchemy will query
the database the first time you access each user's orders and each
order's line items. This may end up sending a lot of queries to
the database, which may hurt performance, but it is easy and
minimizes the amount of redundant data sent from the database
server to your client.
ex:
q = session.query(User)
for user in q:
# not shown: output user data
# Each iteration of this for loop causes an SQL query
for order in user.orders:
# not shown: output order data
# Each iteration of this for loop causes an SQL query
for line_item in order.line_items:
# not shown: output line item data
pass
2. Eager loading: SQLAlchemy issues one SQL query with OUTER JOINs to
get all your data. This is probably your best bet, but can include
many duplicate cells for your users and orders. I would only worry
about this if you are worried about bandwidth between your client
and database server.
ex:
q = session.query(User).options(eagerload_all("orders.line_items"))
# or, if you need to filter/order by orders and/or line items:
q = (session.query(User)
.outerjoin(User.orders)
.options(contains_eager("orders"))
.outerjoin(Order.line_items)
.options(contains_eager("orders.line_items"))
.order_by(Order.date))
# Only one SQL query is sent when we start iterating over q.
for user in q:
# not shown: output user data
for order in user.orders:
# not shown: output order data
for line_item in order.line_items:
# not shown: output line item data
pass
3. Batch fetching: You send one SQL query to fetch all the users, one
SQL query to fetch all the orders, and one SQL query to fetch all
the order line items. SQLAlchemy does not have any built-in
support for this, so you have to generate the queries yourself and
relate the results yourself. However, it does give you the
no-redundant-cells advantage of lazy loading with just 3 queries.
The concept is described here:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/bc74259b45397f69
Hope it helps,
-Conor
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~----------~----~----~----~------~----~------~--~---
