Skip to content

Instantly share code, notes, and snippets.

@thequbit

thequbit/gist:0396aa9d28d71da38b3e

Created April 10, 2015 19:18
Show Gist options
  • Save thequbit/0396aa9d28d71da38b3e to your computer and use it in GitHub Desktop.
Save thequbit/0396aa9d28d71da38b3e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
Starting server in PID 9869.
serving on http://0.0.0.0:5000
2015-04-10 15:18:02,661 INFO [sqlalchemy.engine.base.Engine][Dummy-1] SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-04-10 15:18:02,661 INFO [sqlalchemy.engine.base.Engine][Dummy-1] ()
2015-04-10 15:18:02,662 INFO [sqlalchemy.engine.base.Engine][Dummy-1] SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-04-10 15:18:02,662 INFO [sqlalchemy.engine.base.Engine][Dummy-1] ()
2015-04-10 15:18:02,663 INFO [sqlalchemy.engine.base.Engine][Dummy-1] BEGIN (implicit)
2015-04-10 15:18:02,664 INFO [sqlalchemy.engine.base.Engine][Dummy-1] SELECT customers.id AS customers_id, customers.name AS customers_name, customers.description AS customers_description
FROM customers
2015-04-10 15:18:02,664 INFO [sqlalchemy.engine.base.Engine][Dummy-1] ()
2015-04-10 15:18:02,668 INFO [sqlalchemy.engine.base.Engine][Dummy-1] SELECT accounts.id AS accounts_id, accounts.customer_id AS accounts_customer_id, accounts.name AS accounts_name, accounts.description AS accounts_description
FROM accounts
WHERE ? = accounts.customer_id
2015-04-10 15:18:02,668 INFO [sqlalchemy.engine.base.Engine][Dummy-1] (1,)
[{'accounts': [{'name': u'GE Location X', 'description': u'GE Location X'}], 'name': u'GE', 'description': u'General Electric Corp.'}]
2015-04-10 15:18:02,671 INFO [sqlalchemy.engine.base.Engine][Dummy-1] ROLLBACK
@thequbit
Copy link
Author

The above is the output from sqlalchemy when i go to /customers from the browser. This is the models for Customers and Accounts:

Customers:
https://github.com/thequbit/sqlalchemy-relationship-test/blob/master/test/test/models.py#L61

Accounts:
https://github.com/thequbit/sqlalchemy-relationship-test/blob/master/test/test/models.py#L71

There is a relationship on customers to accounts.

Added Customer, and account linked to it in init script:
https://github.com/thequbit/sqlalchemy-relationship-test/blob/master/test/test/scripts/initializedb.py#L44

The view that calls out to customers to get all of the customers, and their accounts (via the relationship):
https://github.com/thequbit/sqlalchemy-relationship-test/blob/master/test/test/views.py#L13

This all looks great, except why isn't sqlalchemy just doing a JOIN? To get this, it makes two calls to the database. If I had 50 accounts per customer, and 50 customers, it would be making 2500 queries!

@thequbit
Copy link
Author

From running on MySQL, I get the same operation:

Starting server in PID 13336.
serving on http://0.0.0.0:5000
2015-04-10 16:02:19,138 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] SHOW VARIABLES LIKE 'sql_mode'
2015-04-10 16:02:19,139 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] ()
2015-04-10 16:02:19,142 DEBUG [sqlalchemy.engine.base.Engine][Dummy-1] Col ('Variable_name', 'Value')
2015-04-10 16:02:19,142 DEBUG [sqlalchemy.engine.base.Engine][Dummy-1] Row ('sql_mode', 'NO_ENGINE_SUBSTITUTION')
2015-04-10 16:02:19,142 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] SELECT DATABASE()
2015-04-10 16:02:19,143 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] ()
2015-04-10 16:02:19,145 DEBUG [sqlalchemy.engine.base.Engine][Dummy-1] Col ('DATABASE()',)
2015-04-10 16:02:19,145 DEBUG [sqlalchemy.engine.base.Engine][Dummy-1] Row ('sqla_test',)
2015-04-10 16:02:19,147 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2015-04-10 16:02:19,147 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] ()
2015-04-10 16:02:19,150 DEBUG [sqlalchemy.engine.base.Engine][Dummy-1] Col ('Collation', 'Charset', 'Id', 'Default', 'Compiled', 'Sortlen')
2015-04-10 16:02:19,150 DEBUG [sqlalchemy.engine.base.Engine][Dummy-1] Row ('utf8_bin', 'utf8', 83L, '', 'Yes', 1L)
2015-04-10 16:02:19,151 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2015-04-10 16:02:19,151 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] ()
2015-04-10 16:02:19,154 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2015-04-10 16:02:19,154 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] ()
2015-04-10 16:02:19,155 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2015-04-10 16:02:19,155 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] ()
2015-04-10 16:02:19,170 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] BEGIN (implicit)
2015-04-10 16:02:19,170 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] SELECT customers.id AS customers_id, customers.name AS customers_name, customers.description AS customers_description
FROM customers
2015-04-10 16:02:19,171 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] ()
2015-04-10 16:02:19,172 DEBUG [sqlalchemy.engine.base.Engine][Dummy-1] Col ('customers_id', 'customers_name', 'customers_description')
2015-04-10 16:02:19,172 DEBUG [sqlalchemy.engine.base.Engine][Dummy-1] Row (1L, 'GE', 'General Electric Corp.')
2015-04-10 16:02:19,173 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] SELECT accounts.id AS accounts_id, accounts.customer_id AS accounts_customer_id, accounts.name AS accounts_name, accounts.description AS accounts_description
FROM accounts
WHERE %s = accounts.customer_id
2015-04-10 16:02:19,173 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] (1L,)
2015-04-10 16:02:19,174 DEBUG [sqlalchemy.engine.base.Engine][Dummy-1] Col ('accounts_id', 'accounts_customer_id', 'accounts_name', 'accounts_description')
2015-04-10 16:02:19,174 DEBUG [sqlalchemy.engine.base.Engine][Dummy-1] Row (1L, 1L, 'GE Location X', 'The GE Plant at Location X')
[{'accounts': [{'name': u'GE Location X', 'description': u'GE Location X'}], 'name': u'GE', 'description': u'General Electric Corp.'}]
2015-04-10 16:02:19,175 INFO  [sqlalchemy.engine.base.Engine][Dummy-1] ROLLBACK

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment