[sqlalchemy] joinedload option changes outcome of recursive query
Hello.
Imagine the following ORM definition in PostgreSQL:
class Node(Base):
__tablename__ = 'node'
id = Column(Integer(), primary_key=True)
parent_id = Column(Integer(), ForeignKey('node.id'))
# more data...
with the following query method:
def path_to_root(self, root_id=None, load_all=True):
cls = self.__class__
q_base = session.query(cls).filter(cls.id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
cls_alias = aliased(cls, name='cls_alias')
q_rec = session.query(cls_alias)
q_rec = q_rec.filter(cls_alias.id == q_cte_alias.c.parent_id)
q_rec = q_rec.filter(cls_alias.id != root_id)
q_cte_union = q_cte.union_all(q_rec)
q = session.query(cls).select_from(q_cte_union)
q = q.options(joinedload_all(cls.foo, Foo.bar)) # -- marked line
return q
It should return path from the receiver node upwards to the root.
The query works as expected without the marked line. However the order of result
items is reversed when the marked line is present. I was under the impression
that joinedload option should not have observable side-effects.
Was I wrong?
Can I preload dependent data by some other means without semantic change in the
query?
Thank you in advance,
Ladislav Lenart
--
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.
[sqlalchemy] isolation level not supported on MySQL 3.23
We were using SQLAlchemy 0.5.1 and wanted to upgrade to 0.7.8 but ran into the following problem. When trying to create an engine, the mysql dialect tries to determine the current isolation level by issuing the SELECT @@tx_isolation; SQL statement (from dialects/mysql/base.py get_isolation_level()). However, this statement is not supported on MySQL 3.23 and therefore SQLAlchemy 0.7.8 doesn't seem to work anymore. We worked around this by not using SQLAlchemy anymore for our antique MySQL 3.23 db, but this means you could also delete 3.23 from the supported database list. As a solution you could maybe add a try/except clause around it with a version check just like you do in do_commit() in dialects/mysql/base.py Best regards, Ids -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/uJQPXujgUCEJ. 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.
Re: [sqlalchemy] joinedload option changes outcome of recursive query
Hello again.
I have replaced joinedload_all option with subqueryload_all and the query works
as expected: order of the results is correct.
Ladislav Lenart
On 17.9.2012 13:19, Ladislav Lenart wrote:
Hello.
Imagine the following ORM definition in PostgreSQL:
class Node(Base):
__tablename__ = 'node'
id = Column(Integer(), primary_key=True)
parent_id = Column(Integer(), ForeignKey('node.id'))
# more data...
with the following query method:
def path_to_root(self, root_id=None, load_all=True):
cls = self.__class__
q_base = session.query(cls).filter(cls.id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
cls_alias = aliased(cls, name='cls_alias')
q_rec = session.query(cls_alias)
q_rec = q_rec.filter(cls_alias.id == q_cte_alias.c.parent_id)
q_rec = q_rec.filter(cls_alias.id != root_id)
q_cte_union = q_cte.union_all(q_rec)
q = session.query(cls).select_from(q_cte_union)
q = q.options(joinedload_all(cls.foo, Foo.bar)) # -- marked line
return q
It should return path from the receiver node upwards to the root.
The query works as expected without the marked line. However the order of
result
items is reversed when the marked line is present. I was under the impression
that joinedload option should not have observable side-effects.
Was I wrong?
Can I preload dependent data by some other means without semantic change in
the
query?
Thank you in advance,
Ladislav Lenart
--
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.
Re: [sqlalchemy] joinedload option changes outcome of recursive query
On Sep 17, 2012, at 7:19 AM, Ladislav Lenart wrote:
Hello.
Imagine the following ORM definition in PostgreSQL:
class Node(Base):
__tablename__ = 'node'
id = Column(Integer(), primary_key=True)
parent_id = Column(Integer(), ForeignKey('node.id'))
# more data...
with the following query method:
def path_to_root(self, root_id=None, load_all=True):
cls = self.__class__
q_base = session.query(cls).filter(cls.id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
cls_alias = aliased(cls, name='cls_alias')
q_rec = session.query(cls_alias)
q_rec = q_rec.filter(cls_alias.id == q_cte_alias.c.parent_id)
q_rec = q_rec.filter(cls_alias.id != root_id)
q_cte_union = q_cte.union_all(q_rec)
q = session.query(cls).select_from(q_cte_union)
q = q.options(joinedload_all(cls.foo, Foo.bar)) # -- marked line
return q
It should return path from the receiver node upwards to the root.
The query works as expected without the marked line. However the order of
result
items is reversed when the marked line is present. I was under the impression
that joinedload option should not have observable side-effects.
Was I wrong?
no, however I see something very conspicuously missing from your query above
with regard to ordering, which is an order_by.You can never rely on the
ordering of results from a relational database if ORDER BY is not explicitly
present in the query. The means by which the database creates a query plan
out of the given clauses and executes them can change radically when extra
joins are added, thereby affecting the natural ordering of results.
--
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.
Re: [sqlalchemy] isolation level not supported on MySQL 3.23
On Sep 17, 2012, at 7:21 AM, Ids wrote:
We were using SQLAlchemy 0.5.1 and wanted to upgrade to 0.7.8 but ran into
the following problem.
When trying to create an engine, the mysql dialect tries to determine the
current isolation level by issuing the SELECT @@tx_isolation; SQL statement
(from dialects/mysql/base.py get_isolation_level()). However, this statement
is not supported on MySQL 3.23 and therefore SQLAlchemy 0.7.8 doesn't seem to
work anymore.
We worked around this by not using SQLAlchemy anymore for our antique MySQL
3.23 db, but this means you could also delete 3.23 from the supported
database list. As a solution you could maybe add a try/except clause around
it with a version check just like you do in do_commit() in
dialects/mysql/base.py
can you let me know the specific point version of MySQL, I notice we have
several transaction-related directives that are disabled for versions
3.23.15..15 seems to be the first version that supports COMMIT and
ROLLBACK.
Here's a simple patch you can try:
diff -r c72dc70dba37 lib/sqlalchemy/dialects/mysql/base.py
--- a/lib/sqlalchemy/dialects/mysql/base.py Sun Sep 16 21:15:55 2012 -0400
+++ b/lib/sqlalchemy/dialects/mysql/base.py Mon Sep 17 08:57:51 2012 -0400
@@ -1872,6 +1872,8 @@
cursor.close()
def get_isolation_level(self, connection):
+if self.server_version_info (3, 23, 15):
+raise NotImplementedError()
cursor = connection.cursor()
cursor.execute('SELECT @@tx_isolation')
val = cursor.fetchone()[0]
Best regards,
Ids
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/uJQPXujgUCEJ.
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.
--
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.
Re: [sqlalchemy] joinedload option changes outcome of recursive query
On 17.9.2012 14:54, Michael Bayer wrote:
On Sep 17, 2012, at 7:19 AM, Ladislav Lenart wrote:
Hello.
Imagine the following ORM definition in PostgreSQL:
class Node(Base):
__tablename__ = 'node'
id = Column(Integer(), primary_key=True)
parent_id = Column(Integer(), ForeignKey('node.id'))
# more data...
with the following query method:
def path_to_root(self, root_id=None, load_all=True):
cls = self.__class__
q_base = session.query(cls).filter(cls.id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
cls_alias = aliased(cls, name='cls_alias')
q_rec = session.query(cls_alias)
q_rec = q_rec.filter(cls_alias.id == q_cte_alias.c.parent_id)
q_rec = q_rec.filter(cls_alias.id != root_id)
q_cte_union = q_cte.union_all(q_rec)
q = session.query(cls).select_from(q_cte_union)
q = q.options(joinedload_all(cls.foo, Foo.bar)) # -- marked line
return q
It should return path from the receiver node upwards to the root.
The query works as expected without the marked line. However the order of
result
items is reversed when the marked line is present. I was under the impression
that joinedload option should not have observable side-effects.
Was I wrong?
no, however I see something very conspicuously missing from your query above
with regard to ordering, which is an order_by.You can never rely on the
ordering of results from a relational database if ORDER BY is not explicitly
present in the query. The means by which the database creates a query plan
out of the given clauses and executes them can change radically when extra
joins are added, thereby affecting the natural ordering of results.
Hm, I see. My train of thought: This particular query returns the path from a
selected node to the root. Each iteration adds one row to the result set (one
new parent). I thought that UNION ALL keeps the order intact and thus no
order_by clause is needed. I guess I was wrong.
I can add a depth/iteration column (via literal_column) and order_by it. However
I still want the results to be a list of Node instances (i.e. without the added
depth column). How can I write such a query?
Thanks,
Ladislav Lenart
--
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.
Re: [sqlalchemy] joinedload option changes outcome of recursive query
On Sep 17, 2012, at 9:23 AM, Ladislav Lenart wrote: Hm, I see. My train of thought: This particular query returns the path from a selected node to the root. Each iteration adds one row to the result set (one new parent). I thought that UNION ALL keeps the order intact and thus no order_by clause is needed. I guess I was wrong. nothing except ORDER BY orders rows in SQL. All the rest is an artifact of how the query executes. I can add a depth/iteration column (via literal_column) and order_by it. However I still want the results to be a list of Node instances (i.e. without the added depth column). How can I write such a query? you can order_by() any expression, and that expression does not need to be in the columns clause. That is: SELECT a, b FROM x ORDER BY c is just as valid as SELECT a, b, c FROM x ORDER BY c -- 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.
[sqlalchemy] Re: isolation level not supported on MySQL 3.23
We are using MySQL 3.23.31 on that machine :-) It seems tx_isolation was added ad 4.0.3 (according to http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html#sysvar_tx_isolation). But if I add if self.server_version_info (4, 0, 3): raise NotImplementedError() I get another traceback: Traceback (most recent call last): File ./t.py, line 9, in ? con=engine.connect() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 2472, in connect return self._connection_cls(self, **kwargs) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 878, in __init__ self.__connection = connection or engine.raw_connection() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 2558, in raw_connection return self.pool.unique_connection() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 183, in unique_connection return _ConnectionFairy(self).checkout() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 387, in __init__ rec = self._connection_record = pool._do_get() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 741, in _do_get con = self._create_connection() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 188, in _create_connection return _ConnectionRecord(self) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 273, in __init__ pool.dispatch.first_connect.exec_once(self.connection, self) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/event.py, line 282, in exec_once self(*args, **kw) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/event.py, line 291, in __call__ fn(*args, **kw) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/strategies.py, line 167, in first_connect dialect.initialize(c) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/dialects/mysql/base.py, line 2005, in initialize self._detect_ansiquotes(connection) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/dialects/mysql/base.py, line 2246, in _detect_ansiquotes mode = row[1] or '' File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/dialects/mysql/base.py, line 2737, in __getitem__ item = self.rowproxy[index] TypeError: unsubscriptable object My test script is pretty simple. ... url='%(driver)s://%(user)s:%(passwd)s@%(host)s/%(db)s' % cfg engine=create_engine(url) con=engine.connect() con.close() Thanks Op maandag 17 september 2012 13:21:02 UTC+2 schreef Ids het volgende: We were using SQLAlchemy 0.5.1 and wanted to upgrade to 0.7.8 but ran into the following problem. When trying to create an engine, the mysql dialect tries to determine the current isolation level by issuing the SELECT @@tx_isolation; SQL statement (from dialects/mysql/base.py get_isolation_level()). However, this statement is not supported on MySQL 3.23 and therefore SQLAlchemy 0.7.8 doesn't seem to work anymore. We worked around this by not using SQLAlchemy anymore for our antique MySQL 3.23 db, but this means you could also delete 3.23 from the supported database list. As a solution you could maybe add a try/except clause around it with a version check just like you do in do_commit() in dialects/mysql/base.py Best regards, Ids -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/YfpYDdO8XCwJ. 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.
Re: [sqlalchemy] Re: isolation level not supported on MySQL 3.23
OK, that is good information there, I'll see if i can get the notimplemented to not break like that. On Sep 17, 2012, at 11:17 AM, Ids wrote: We are using MySQL 3.23.31 on that machine :-) It seems tx_isolation was added ad 4.0.3 (according to http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html#sysvar_tx_isolation). But if I add if self.server_version_info (4, 0, 3): raise NotImplementedError() I get another traceback: Traceback (most recent call last): File ./t.py, line 9, in ? con=engine.connect() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 2472, in connect return self._connection_cls(self, **kwargs) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 878, in __init__ self.__connection = connection or engine.raw_connection() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 2558, in raw_connection return self.pool.unique_connection() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 183, in unique_connection return _ConnectionFairy(self).checkout() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 387, in __init__ rec = self._connection_record = pool._do_get() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 741, in _do_get con = self._create_connection() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 188, in _create_connection return _ConnectionRecord(self) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 273, in __init__ pool.dispatch.first_connect.exec_once(self.connection, self) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/event.py, line 282, in exec_once self(*args, **kw) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/event.py, line 291, in __call__ fn(*args, **kw) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/strategies.py, line 167, in first_connect dialect.initialize(c) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/dialects/mysql/base.py, line 2005, in initialize self._detect_ansiquotes(connection) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/dialects/mysql/base.py, line 2246, in _detect_ansiquotes mode = row[1] or '' File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/dialects/mysql/base.py, line 2737, in __getitem__ item = self.rowproxy[index] TypeError: unsubscriptable object My test script is pretty simple. ... url='%(driver)s://%(user)s:%(passwd)s@%(host)s/%(db)s' % cfg engine=create_engine(url) con=engine.connect() con.close() Thanks Op maandag 17 september 2012 13:21:02 UTC+2 schreef Ids het volgende: We were using SQLAlchemy 0.5.1 and wanted to upgrade to 0.7.8 but ran into the following problem. When trying to create an engine, the mysql dialect tries to determine the current isolation level by issuing the SELECT @@tx_isolation; SQL statement (from dialects/mysql/base.py get_isolation_level()). However, this statement is not supported on MySQL 3.23 and therefore SQLAlchemy 0.7.8 doesn't seem to work anymore. We worked around this by not using SQLAlchemy anymore for our antique MySQL 3.23 db, but this means you could also delete 3.23 from the supported database list. As a solution you could maybe add a try/except clause around it with a version check just like you do in do_commit() in dialects/mysql/base.py Best regards, Ids -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/YfpYDdO8XCwJ. 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. -- 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.
Re: [sqlalchemy] Re: isolation level not supported on MySQL 3.23
OK, this is actually a different issue. I wonder if i can get a 3.23 version running on a VM somewhere. On Sep 17, 2012, at 11:17 AM, Ids wrote: We are using MySQL 3.23.31 on that machine :-) It seems tx_isolation was added ad 4.0.3 (according to http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html#sysvar_tx_isolation). But if I add if self.server_version_info (4, 0, 3): raise NotImplementedError() I get another traceback: Traceback (most recent call last): File ./t.py, line 9, in ? con=engine.connect() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 2472, in connect return self._connection_cls(self, **kwargs) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 878, in __init__ self.__connection = connection or engine.raw_connection() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 2558, in raw_connection return self.pool.unique_connection() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 183, in unique_connection return _ConnectionFairy(self).checkout() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 387, in __init__ rec = self._connection_record = pool._do_get() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 741, in _do_get con = self._create_connection() File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 188, in _create_connection return _ConnectionRecord(self) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/pool.py, line 273, in __init__ pool.dispatch.first_connect.exec_once(self.connection, self) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/event.py, line 282, in exec_once self(*args, **kw) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/event.py, line 291, in __call__ fn(*args, **kw) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/engine/strategies.py, line 167, in first_connect dialect.initialize(c) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/dialects/mysql/base.py, line 2005, in initialize self._detect_ansiquotes(connection) File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/dialects/mysql/base.py, line 2246, in _detect_ansiquotes mode = row[1] or '' File /opt/python-2.4/lib/python2.4/site-packages/sqlalchemy/dialects/mysql/base.py, line 2737, in __getitem__ item = self.rowproxy[index] TypeError: unsubscriptable object My test script is pretty simple. ... url='%(driver)s://%(user)s:%(passwd)s@%(host)s/%(db)s' % cfg engine=create_engine(url) con=engine.connect() con.close() Thanks Op maandag 17 september 2012 13:21:02 UTC+2 schreef Ids het volgende: We were using SQLAlchemy 0.5.1 and wanted to upgrade to 0.7.8 but ran into the following problem. When trying to create an engine, the mysql dialect tries to determine the current isolation level by issuing the SELECT @@tx_isolation; SQL statement (from dialects/mysql/base.py get_isolation_level()). However, this statement is not supported on MySQL 3.23 and therefore SQLAlchemy 0.7.8 doesn't seem to work anymore. We worked around this by not using SQLAlchemy anymore for our antique MySQL 3.23 db, but this means you could also delete 3.23 from the supported database list. As a solution you could maybe add a try/except clause around it with a version check just like you do in do_commit() in dialects/mysql/base.py Best regards, Ids -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/YfpYDdO8XCwJ. 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. -- 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.
[sqlalchemy] How to load from aliased table
I'm trying to optimise a slow performing CTE, the raw SQL is fast but I'm
having trouble getting the generated SQL from SA to be the same. My issue
is that the generated SQL contains 3 outer, self-referencing joins which
slow it down dramatically.
Here's the SQL I'm aiming for:
WITH RECURSIVE related_ledger(guid, created_at, reference_ledger_guid) AS (
SELECT ledger.guid AS guid, ledger.created_at AS created_at,
ledger.reference_ledger_guid AS reference_ledger_guid
FROM ledger
WHERE ledger.guid = %(guid_1)s
UNION ALL
SELECT ledger.guid AS ledger_guid, ledger.created_at AS
ledger_created_at, ledger.reference_ledger_guid AS
ledger_reference_ledger_guid
FROM ledger, related_ledger AS rla
WHERE ledger.reference_ledger_guid = rla.guid
)
SELECT related_ledger.guid AS related_ledger_guid,
related_ledger.created_at AS related_ledger_created_at,
related_ledger.reference_ledger_guid AS related_ledger_reference_ledger_guid
FROM related_ledger;
And here's what my CTE within python looks like:
related_ledger = Ledger.query.filter(
Ledger.guid == self.guid
).cte('related_ledger', recursive=True)
related_ledger_alias = aliased(related_ledger,
name='rl')
rec = Ledger.query.filter(
Ledger.reference_ledger_guid == related_ledger_alias.c.guid
)
union = related_ledger.union_all(rec)
Now I've got two ways to return the data as SA objects (as opposed to
straight tuples, with which this query is zippy):
Ledger.query.select_from(union)
This is slow as it generates something like this:
with related_ledger as (
...
)
select ...
FROM related_ledger
LEFT OUTER JOIN ledger AS ledger_1 ON related_ledger.guid =
ledger_1.reference_ledger_guid
LEFT OUTER JOIN ledger AS ledger_2 ON ledger_1.guid =
ledger_2.reference_ledger_guid
LEFT OUTER JOIN ledger AS ledger_3 ON ledger_2.guid =
ledger_3.reference_ledger_guid
I also tried
Ledger.query.from_statement(union.select())
I would expect this to be fast since it's mapping the original columns in
the query to what the object expects. However, I'm having trouble with this
syntax and getting this error:
** NoSuchColumnError: Could not locate column in row for column
'ledger.guid'
This appears to be because the table is aliased as reference_ledger.
So my questions are:
1. Am I writing the CTE in the wrong way to be the select_from method?
2. How can I use the from_statement method to work with an aliased table?
Thanks!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/GScftyDX5pMJ.
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.
Re: [sqlalchemy] How to load from aliased table
On Sep 17, 2012, at 1:38 PM, mjallday wrote: I also tried Ledger.query.from_statement(union.select()) I would expect this to be fast since it's mapping the original columns in the query to what the object expects. However, I'm having trouble with this syntax and getting this error: ** NoSuchColumnError: Could not locate column in row for column 'ledger.guid' OK, try out tip, since there was a bug exactly in this area, that is from_statement(union), that is fixed for 0.7.9. there's also a bunch of CTE-related fixes. This appears to be because the table is aliased as reference_ledger. So my questions are: 1. Am I writing the CTE in the wrong way to be the select_from method? Those LEFT OUTER JOINs look like you have lazy=False set up, or something, since I don't see you otherwise specifying any joins. I usually leave joined-loads off at the relationship() level. Especially if you're self-referential, and dealing with CTEs and stuff, you don't want any complexity enabled by default on your mappings.That will probably fix this issue. 2. How can I use the from_statement method to work with an aliased table? see above tip recommendation. -- 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.
Re: [sqlalchemy] How to load from aliased table
You're absolutely correct, it was the relationship declaration that was my issue. Removing the lazy='joined' from the code removed the outer joins. I really appreciate the speedy and concise answer. Thank you very much! On Monday, September 17, 2012 11:06:10 AM UTC-7, Michael Bayer wrote: On Sep 17, 2012, at 1:38 PM, mjallday wrote: I also tried Ledger.query.from_statement(union.select()) I would expect this to be fast since it's mapping the original columns in the query to what the object expects. However, I'm having trouble with this syntax and getting this error: ** NoSuchColumnError: Could not locate column in row for column 'ledger.guid' OK, try out tip, since there was a bug exactly in this area, that is from_statement(union), that is fixed for 0.7.9. there's also a bunch of CTE-related fixes. This appears to be because the table is aliased as reference_ledger. So my questions are: 1. Am I writing the CTE in the wrong way to be the select_from method? Those LEFT OUTER JOINs look like you have lazy=False set up, or something, since I don't see you otherwise specifying any joins. I usually leave joined-loads off at the relationship() level. Especially if you're self-referential, and dealing with CTEs and stuff, you don't want any complexity enabled by default on your mappings.That will probably fix this issue. 2. How can I use the from_statement method to work with an aliased table? see above tip recommendation. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/3s1Mq3_kUvIJ. 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.
