Re: [sqlalchemy] set a query timeout on a per query basis
On Mon, Nov 12, 2018 at 6:12 PM Brian Cherinka wrote:
>
> Hmm. Ok. I'm using a postgres database with the psycopg2 driver. I'm aware
> of the `statement_timeout` option in `postgres` which I can pass into the
> psycopg2 `connect` method. As far as I can tell there's not a way to pass
> that keyword in through SQLAlchemy after the db engine has been created. Is
> that correct? Does the `query.execution_options`, or something in session,
> accept that keyword?
if statement_timeout is accepted only on the "connect" method and not
once you have already connected, then it would need to be set for the
Engine globally. You would do this using the "connect" event:
https://docs.sqlalchemy.org/en/latest/core/events.html?highlight=connect%20event#sqlalchemy.events.PoolEvents.connect
Otherwise, if it can be set on a cursor, you can use
execution_options(), which you intercept with a before_cursor_execute
event:
@event.listens_for(Engine, "before_cursor_execute")
def _set_timeout(conn, cursor, stmt, params, context, executemany):
timeout = conn._execution_options.get('timeout', None)
if timeout:
cursor.statement_timeout = timeout
query.execution_options() accepts whatever keywords you pass into it
and you can get them out inside that event handler (or anywhere you
have a Connection).
>
> On Monday, November 12, 2018 at 3:15:23 PM UTC-5, Mike Bayer wrote:
>>
>> On Mon, Nov 12, 2018 at 2:08 PM Brian Cherinka wrote:
>> >
>> > What's the best way to set a timeout for specific queries? I have a
>> > custom query tool that uses SQLAlchemy to build and submit queries. This
>> > tool can be used in a local python session with a database. I'm also
>> > using it to allow queries in a Flask web-app. In general, I do not want to
>> > apply a time limit to all queries, but I only want to apply a time limit
>> > of 1 minute to queries submitted through the web app. Given a built
>> > SQLAlchemy query and a db session instance, is there any way to set a
>> > timeout through the query or session objects without having to recreate a
>> > database connection?
>>
>> There's no set way to do that at the SQLAlchemy level, this depends
>> very much on the database and the Python driver you are using and may
>> not be possible for every driver.
>>
>> >
>> > --
>> > SQLAlchemy -
>> > The Python SQL Toolkit and Object Relational Mapper
>> >
>> > http://www.sqlalchemy.org/
>> >
>> > To post example code, please provide an MCVE: Minimal, Complete, and
>> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> > description.
>> > ---
>> > You received this message because you are subscribed to the Google Groups
>> > "sqlalchemy" group.
>> > To unsubscribe from this group and stop receiving emails from it, send an
>> > email to sqlalchemy+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] set a query timeout on a per query basis
Hmm. Ok. I'm using a postgres database with the psycopg2 driver. I'm aware of the `statement_timeout` option in `postgres` which I can pass into the psycopg2 `connect` method. As far as I can tell there's not a way to pass that keyword in through SQLAlchemy after the db engine has been created. Is that correct? Does the `query.execution_options`, or something in session, accept that keyword? On Monday, November 12, 2018 at 3:15:23 PM UTC-5, Mike Bayer wrote: > > On Mon, Nov 12, 2018 at 2:08 PM Brian Cherinka > wrote: > > > > What's the best way to set a timeout for specific queries? I have a > custom query tool that uses SQLAlchemy to build and submit queries. This > tool can be used in a local python session with a database. I'm also using > it to allow queries in a Flask web-app. In general, I do not want to apply > a time limit to all queries, but I only want to apply a time limit of 1 > minute to queries submitted through the web app. Given a built SQLAlchemy > query and a db session instance, is there any way to set a timeout through > the query or session objects without having to recreate a database > connection? > > There's no set way to do that at the SQLAlchemy level, this depends > very much on the database and the Python driver you are using and may > not be possible for every driver. > > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > > --- > > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an email to sqlalchemy+...@googlegroups.com . > > To post to this group, send email to sqlal...@googlegroups.com > . > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] set a query timeout on a per query basis
On Mon, Nov 12, 2018 at 2:08 PM Brian Cherinka wrote: > > What's the best way to set a timeout for specific queries? I have a custom > query tool that uses SQLAlchemy to build and submit queries. This tool can > be used in a local python session with a database. I'm also using it to > allow queries in a Flask web-app. In general, I do not want to apply a time > limit to all queries, but I only want to apply a time limit of 1 minute to > queries submitted through the web app. Given a built SQLAlchemy query and a > db session instance, is there any way to set a timeout through the query or > session objects without having to recreate a database connection? There's no set way to do that at the SQLAlchemy level, this depends very much on the database and the Python driver you are using and may not be possible for every driver. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] set a query timeout on a per query basis
What's the best way to set a timeout for specific queries? I have a custom query tool that uses SQLAlchemy to build and submit queries. This tool can be used in a local python session with a database. I'm also using it to allow queries in a Flask web-app. In general, I do not want to apply a time limit to all queries, but I only want to apply a time limit of 1 minute to queries submitted through the web app. Given a built SQLAlchemy query and a db session instance, is there any way to set a timeout through the query or session objects without having to recreate a database connection? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: alter_column missing server_onupdate?
Thanks for the reply Mike. This is something I clearly misunderstood, and I realised that late last night after reading the docs but didn't follow-up here. The naming is confusing, since `server_default` ends up in the DDL, but server_onupdate doesn't. Even though the latter is named similar to onupdate on a Foreignkey, which is server side. I'll have to go back and add triggers for updated columns that should get automatically updated when a change occurs. Do you know if there is a recipe for this already somewhere? Thanks, Bert JW Regeer On Monday, 12 November 2018 08:02:06 UTC-7, Mike Bayer wrote: > > server_onupdate is not an actual "server side" construct, it's a > marker on the client side only to instruct SQLAlchemy that some > trigger or something set up separately will be changing the value of > the column when an UPDATE occurs. > On Mon, Nov 12, 2018 at 1:00 AM Bert JW Regeer > wrote: > > > > Hey all, > > > > After perusing the documentation, I am just making sure I am not an > idiot and overlooked something, but is altering server_onupdate possible > with `alter_coumn`? > > > > Thanks, > > Bert JW Regeer > > > > -- > > You received this message because you are subscribed to the Google > Groups "sqlalchemy-alembic" group. > > To unsubscribe from this group and stop receiving emails from it, send > an email to sqlalchemy-alembic+unsubscr...@googlegroups.com . > > > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Selects, Outer Joins, and Auto-Correlation
Thank-you! I am sure that will be right - I was trying to find a "from" in the docs, but missed the select_from(). Thanks again, Andrew On Mon, Nov 12, 2018 at 10:15:05AM -0500, Mike Bayer wrote: > On Mon, Nov 12, 2018 at 10:07 AM Mike Bayer wrote: > > > > On Mon, Nov 12, 2018 at 7:56 AM andrew cooke wrote: > > > > > > > > > I have some code that uses high-level ORM and it works fine, except that > > > one particular section is too slow. So I was trying to replace that > > > section with a lower level query. But I can't work out how to persuade > > > the Expression Language to stop auto-correlation. > > > > > > The query I am trying to generate is: > > > > > > select statistic_journal.id, > > > statistic_name.name, > > > coalesce(statistic_journal_float.value, > > > statistic_journal_integer.value) > > >from statistic_journal > > >join statistic_name > > > on statistic_journal.statistic_name_id = statistic_name.id > > >left outer join statistic_journal_float > > > on statistic_journal.id = statistic_journal_float.id > > >left outer join statistic_journal_integer > > > on statistic_journal.id = statistic_journal_integer.id > > > where statistic_name.owner = -19043; > > > > > > > this query is not using any correlation, it has no subqueries. > > > > > But the closest I can get (only aiming for the outer join part of the > > > query) is: > > > > > > Python 3.7.0 (default, Aug 20 2018, 18:32:46) > > > [GCC 7.3.1 20180323 [gcc-7-branch revision 258812]] on linux > > > Type "help", "copyright", "credits" or "license" for more information. > > > >>> from sqlalchemy import inspect > > > >>> from sqlalchemy.sql.functions import coalesce > > > >>> from sqlalchemy.sql import select > > > >>> from ch2.squeal.tables.statistic import StatisticJournal, > > > >>> StatisticName, StatisticJournalInteger, StatisticJournalFloat > > > >>> > > > >>> sj = inspect(StatisticJournal).local_table > > > >>> sji = inspect(StatisticJournalInteger).local_table > > > >>> sjf = inspect(StatisticJournalFloat).local_table > > > >>> > > > >>> print(select([sj.c.id, > > > ... coalesce(sjf.c.value, sji.c.value)]) > > > ... .correlate_except(sji, sjf) > > > ... .outerjoin(sjf).outerjoin(sji)) > > > (SELECT statistic_journal.id AS id, > > > coalesce(statistic_journal_float.value, statistic_journal_integer.value) > > > AS coalesce_1 > > > FROM statistic_journal, statistic_journal_float, > > > statistic_journal_integer) LEFT OUTER JOIN statistic_journal_float ON id > > > = statistic_journal_float.id LEFT OUTER JOIN statistic_journal_integer ON > > > id = statistic_journal_integer.id > > oh wait, the problem is that outerjoin() method you're using. that > creates a join to the SELECT as a whole and generates a subquery. > That's not appropriate here. here's how to join: > > select([x, y, z]).select_from(sj.outerjoin(sjf).outerjoin(sji)) > > > see the tutorial at > https://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-joins . > > > > > > > > > I realise this is not complete or runnable, but I hope it illustrates the > > > problem: the initial FROM includes statistic_journal_float and > > > statistic_journal_integer. I don't want those! They should come later. > > > I was hoping that the correlate_except() would remove them, but it > > > doesn't seem to have any effect. > > > > can you remove the correlate_except? this is a straightforward > > query, however I can't be sure what the structure of "sjf" is, are > > these all Table objects or are you mapping to selectables? there's > > no reason I can see which would generate that additional FROM clause. > > > > > > > > > > Also, I am not sure if this is how you mix ORM and expression language > > > (explicitly getting the tables via inspect). > > > > > > Is that clear? What am I doing wrong? This is SQLAlchemy 1.2.14 if it > > > makes any difference. > > > > > > Thanks, > > > Andrew > > > > > > -- > > > SQLAlchemy - > > > The Python SQL Toolkit and Object Relational Mapper > > > > > > http://www.sqlalchemy.org/ > > > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > > description. > > > --- > > > You received this message because you are subscribed to the Google Groups > > > "sqlalchemy" group. > > > To unsubscribe from this group and stop receiving emails from it, send an > > > email to sqlalchemy+unsubscr...@googlegroups.com. > > > To post to this group, send email to sqlalchemy@googlegroups.com. > > > Visit this group at https://groups.google.com/group/sqlalchemy. > > > For more options, visit https://groups.google.com/d/optout. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example.
Re: [sqlalchemy] Selects, Outer Joins, and Auto-Correlation
On Mon, Nov 12, 2018 at 10:07 AM Mike Bayer wrote: > > On Mon, Nov 12, 2018 at 7:56 AM andrew cooke wrote: > > > > > > I have some code that uses high-level ORM and it works fine, except that > > one particular section is too slow. So I was trying to replace that > > section with a lower level query. But I can't work out how to persuade the > > Expression Language to stop auto-correlation. > > > > The query I am trying to generate is: > > > > select statistic_journal.id, > > statistic_name.name, > > coalesce(statistic_journal_float.value, > > statistic_journal_integer.value) > >from statistic_journal > >join statistic_name > > on statistic_journal.statistic_name_id = statistic_name.id > >left outer join statistic_journal_float > > on statistic_journal.id = statistic_journal_float.id > >left outer join statistic_journal_integer > > on statistic_journal.id = statistic_journal_integer.id > > where statistic_name.owner = -19043; > > > > this query is not using any correlation, it has no subqueries. > > > But the closest I can get (only aiming for the outer join part of the > > query) is: > > > > Python 3.7.0 (default, Aug 20 2018, 18:32:46) > > [GCC 7.3.1 20180323 [gcc-7-branch revision 258812]] on linux > > Type "help", "copyright", "credits" or "license" for more information. > > >>> from sqlalchemy import inspect > > >>> from sqlalchemy.sql.functions import coalesce > > >>> from sqlalchemy.sql import select > > >>> from ch2.squeal.tables.statistic import StatisticJournal, > > >>> StatisticName, StatisticJournalInteger, StatisticJournalFloat > > >>> > > >>> sj = inspect(StatisticJournal).local_table > > >>> sji = inspect(StatisticJournalInteger).local_table > > >>> sjf = inspect(StatisticJournalFloat).local_table > > >>> > > >>> print(select([sj.c.id, > > ... coalesce(sjf.c.value, sji.c.value)]) > > ... .correlate_except(sji, sjf) > > ... .outerjoin(sjf).outerjoin(sji)) > > (SELECT statistic_journal.id AS id, coalesce(statistic_journal_float.value, > > statistic_journal_integer.value) AS coalesce_1 > > FROM statistic_journal, statistic_journal_float, statistic_journal_integer) > > LEFT OUTER JOIN statistic_journal_float ON id = statistic_journal_float.id > > LEFT OUTER JOIN statistic_journal_integer ON id = > > statistic_journal_integer.id oh wait, the problem is that outerjoin() method you're using. that creates a join to the SELECT as a whole and generates a subquery. That's not appropriate here. here's how to join: select([x, y, z]).select_from(sj.outerjoin(sjf).outerjoin(sji)) see the tutorial at https://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-joins . > > > > I realise this is not complete or runnable, but I hope it illustrates the > > problem: the initial FROM includes statistic_journal_float and > > statistic_journal_integer. I don't want those! They should come later. I > > was hoping that the correlate_except() would remove them, but it doesn't > > seem to have any effect. > > can you remove the correlate_except? this is a straightforward > query, however I can't be sure what the structure of "sjf" is, are > these all Table objects or are you mapping to selectables? there's > no reason I can see which would generate that additional FROM clause. > > > > > > Also, I am not sure if this is how you mix ORM and expression language > > (explicitly getting the tables via inspect). > > > > Is that clear? What am I doing wrong? This is SQLAlchemy 1.2.14 if it > > makes any difference. > > > > Thanks, > > Andrew > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to sqlalchemy+unsubscr...@googlegroups.com. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit
Re: [sqlalchemy] Selects, Outer Joins, and Auto-Correlation
On Mon, Nov 12, 2018 at 7:56 AM andrew cooke wrote: > > > I have some code that uses high-level ORM and it works fine, except that one > particular section is too slow. So I was trying to replace that section with > a lower level query. But I can't work out how to persuade the Expression > Language to stop auto-correlation. > > The query I am trying to generate is: > > select statistic_journal.id, > statistic_name.name, > coalesce(statistic_journal_float.value, > statistic_journal_integer.value) >from statistic_journal >join statistic_name > on statistic_journal.statistic_name_id = statistic_name.id >left outer join statistic_journal_float > on statistic_journal.id = statistic_journal_float.id >left outer join statistic_journal_integer > on statistic_journal.id = statistic_journal_integer.id > where statistic_name.owner = -19043; > this query is not using any correlation, it has no subqueries. > But the closest I can get (only aiming for the outer join part of the query) > is: > > Python 3.7.0 (default, Aug 20 2018, 18:32:46) > [GCC 7.3.1 20180323 [gcc-7-branch revision 258812]] on linux > Type "help", "copyright", "credits" or "license" for more information. > >>> from sqlalchemy import inspect > >>> from sqlalchemy.sql.functions import coalesce > >>> from sqlalchemy.sql import select > >>> from ch2.squeal.tables.statistic import StatisticJournal, StatisticName, > >>> StatisticJournalInteger, StatisticJournalFloat > >>> > >>> sj = inspect(StatisticJournal).local_table > >>> sji = inspect(StatisticJournalInteger).local_table > >>> sjf = inspect(StatisticJournalFloat).local_table > >>> > >>> print(select([sj.c.id, > ... coalesce(sjf.c.value, sji.c.value)]) > ... .correlate_except(sji, sjf) > ... .outerjoin(sjf).outerjoin(sji)) > (SELECT statistic_journal.id AS id, coalesce(statistic_journal_float.value, > statistic_journal_integer.value) AS coalesce_1 > FROM statistic_journal, statistic_journal_float, statistic_journal_integer) > LEFT OUTER JOIN statistic_journal_float ON id = statistic_journal_float.id > LEFT OUTER JOIN statistic_journal_integer ON id = statistic_journal_integer.id > > I realise this is not complete or runnable, but I hope it illustrates the > problem: the initial FROM includes statistic_journal_float and > statistic_journal_integer. I don't want those! They should come later. I > was hoping that the correlate_except() would remove them, but it doesn't seem > to have any effect. can you remove the correlate_except? this is a straightforward query, however I can't be sure what the structure of "sjf" is, are these all Table objects or are you mapping to selectables? there's no reason I can see which would generate that additional FROM clause. > > Also, I am not sure if this is how you mix ORM and expression language > (explicitly getting the tables via inspect). > > Is that clear? What am I doing wrong? This is SQLAlchemy 1.2.14 if it makes > any difference. > > Thanks, > Andrew > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Selects, Outer Joins, and Auto-Correlation
I have some code that uses high-level ORM and it works fine, except that one particular section is too slow. So I was trying to replace that section with a lower level query. But I can't work out how to persuade the Expression Language to stop auto-correlation. The query I am trying to generate is: select statistic_journal.id, statistic_name.name, coalesce(statistic_journal_float.value, statistic_journal_integer.value) from statistic_journal join statistic_name on statistic_journal.statistic_name_id = statistic_name.id left outer join statistic_journal_float on statistic_journal.id = statistic_journal_float.id left outer join statistic_journal_integer on statistic_journal.id = statistic_journal_integer.id where statistic_name.owner = -19043; But the closest I can get (only aiming for the outer join part of the query) is: Python 3.7.0 (default, Aug 20 2018, 18:32:46) [GCC 7.3.1 20180323 [gcc-7-branch revision 258812]] on linux Type "help", "copyright", "credits" or "license" for more information. >>> from sqlalchemy import inspect >>> from sqlalchemy.sql.functions import coalesce >>> from sqlalchemy.sql import select >>> from ch2.squeal.tables.statistic import StatisticJournal, StatisticName, StatisticJournalInteger, StatisticJournalFloat >>> >>> sj = inspect(StatisticJournal).local_table >>> sji = inspect(StatisticJournalInteger).local_table >>> sjf = inspect(StatisticJournalFloat).local_table >>> >>> print(select([sj.c.id, ... coalesce(sjf.c.value, sji.c.value)]) ... .correlate_except(sji, sjf) ... .outerjoin(sjf).outerjoin(sji)) (SELECT statistic_journal.id AS id, coalesce(statistic_journal_float.value, statistic_journal_integer.value) AS coalesce_1 FROM statistic_journal, statistic_journal_float, statistic_journal_integer) LEFT OUTER JOIN statistic_journal_float ON id = statistic_journal_float.id LEFT OUTER JOIN statistic_journal_integer ON id = statistic_journal_integer. id I realise this is not complete or runnable, but I hope it illustrates the problem: the initial FROM includes statistic_journal_float and statistic_journal_integer. I don't want those! They should come later. I was hoping that the correlate_except() would remove them, but it doesn't seem to have any effect. Also, I am not sure if this is how you mix ORM and expression language (explicitly getting the tables via inspect). Is that clear? What am I doing wrong? This is SQLAlchemy 1.2.14 if it makes any difference. Thanks, Andrew -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
