Re: [sqlalchemy] Creating database tables from queries based on ORM API?

[Mike Bayer]

Hi there -

Answering line by line is not really a good use of either of our time
and I apologize that I cannot really work with the lack of specifics
presented here, if I can restate the situation, the "CREATE TABLE AS"
use case is not very common, and as far as an ORM use case, I have no
idea what such a feature would look like.   The SQL component of
CREATE TABLE AS is very easy to create as a recipe and since it is
very idiosyncratic to specific databases there is not a compelling
reason to prioritize designing and maintaining a built in construct,
but as always, contributors are welcome to propose and assist in
implementing specific Core and ORM-level features.   Specific
proposals and test cases are the most helpful approach.




On Mon, Apr 15, 2019 at 4:15 PM Markus Elfring  wrote:
>
> >> Can a variant of the method “CreateTableAs” (which you published on 
> >> 2015-06-01) become a standard component of the application programming 
> >> interface?
> >
> > it can,
>
> Thanks for such positive feedback.
>
>
> > but as you are seeing, it's insufficient for what people
> > actually want to do which is map ORM classes.
>
> I presented a description for another use case.
>
>
> > We like to keep these kinds of things as external recipes
>
> How do you think about to clarify these approaches a bit more?
>
>
> > until it is crystal clear what built in API should be added
> > that meets at least the following criteria,
> > noting that more criteria may be considered:
>
> I hope that these checks can be reconsidered as usual.
>
>
> > for this feature, we are lacking #1 , #2, #3, #4 and #5 right now.
>
> I imagine that similar clarification requests can trigger further
> software evolution.
>
>
> >in particular, should the construct attempt to create a new Table
> > object in memory also so that it need not be reflected?
>
> Can this aspect result in another software extension?
>
>
> > What kinds of complications might arise,
>
> Some “surprises” will occur because of the usual challenges
> of software development.
>
>
> > can we definitely determine what decisions the database makes
> > when it creates this table?
>
> I am curious on how good the determination of table properties
> can become.
>
>
> > Will people want to ORM map these classes?
>
> I would like to achieve it somehow.
>
>
> > how do we determine the primary keys then,
>
> Would any more database developers like to add their concerns
> and advices for this aspect?
>
>
> > or how do we otherwise provide an API for users to define that?
>
> I imagine that adjustments can become interesting also in this area.
>
>
> > Database support - it is unclear what databases support this
> > or how they do so; for example in SQL Server they seem to
> > have a different syntax entirely (SELECT INTO), how do the
> > capabilities of SELECT INTO differ from "CREATE TABLE AS" ?
>
> Does one of these commands belong to the official SQL standard?
>
>
> > Would we build separate constructs for different databases
>
> This might be necessary if you would like to support non-standard 
> functionality.
> https://en.wikipedia.org/wiki/SQL#Interoperability_and_standardization
>
>
> > so that they remain unaffected by each other
>
> Will a bit more than the least common denominator become applicable?
>
>
> > and if so how does the ORM use case map to that?
>
> I find that temporary tables and corresponding objects are useful
> in several cases.
>
>
> > By contrast, by providing recipes, all users can immediately do exactly
> > the thing they need without any issue as far as API compatibility,
> > cross-database compatibility, dealing with shortcomings in the API, etc.
>
> How much will these approaches influence the change acceptance
> for a better API design?
>
>
> > When you stated "I would like to count value combinations in
> > database records", that sounds like you are looking to emit a SQL query
> > of some kind
>
> Yes. - I would like to perform data analysis for my needs.
>
>
> > so there is no need to involve the ORM here,
>
> I published a few scripts which demonstrate the application
> of your class library for specific data processing tasks.
>
>
> > so it was not clear what feature of the ORM you were seeking.
>
> I hope that this programming interface can shield also me a bit more
> from challenges because of SQL variations.
>
> Selections an be constructed in convenient ways by Python query classes.
> The data export of computed results into additional tables can become nicer,
> can't it?
>
>
> > can you perhaps illustrate a code example of what you would like to do ?
>
> I could mention a bit more from a concrete example. But I imagine
> that this does not really matter here at the moment.
>
> It find it more important to take run time consequences occasionally better
> into account.
> Some computations require then that their results will be stored in new 
> tables.
>
>
> >>> or to build the Table object directly:
> >>
> >> I would prefer to reuse 

Re: [sqlalchemy] Creating database tables from queries based on ORM API?

[Markus Elfring]

>> Can a variant of the method “CreateTableAs” (which you published on 
>> 2015-06-01) become a standard component of the application programming 
>> interface?
>
> it can,

Thanks for such positive feedback.


> but as you are seeing, it's insufficient for what people
> actually want to do which is map ORM classes.

I presented a description for another use case.


> We like to keep these kinds of things as external recipes

How do you think about to clarify these approaches a bit more?


> until it is crystal clear what built in API should be added
> that meets at least the following criteria,
> noting that more criteria may be considered:

I hope that these checks can be reconsidered as usual.


> for this feature, we are lacking #1 , #2, #3, #4 and #5 right now.

I imagine that similar clarification requests can trigger further
software evolution.


>in particular, should the construct attempt to create a new Table
> object in memory also so that it need not be reflected?

Can this aspect result in another software extension?


> What kinds of complications might arise,

Some “surprises” will occur because of the usual challenges
of software development.


> can we definitely determine what decisions the database makes
> when it creates this table?

I am curious on how good the determination of table properties
can become.


> Will people want to ORM map these classes?

I would like to achieve it somehow.


> how do we determine the primary keys then,

Would any more database developers like to add their concerns
and advices for this aspect?


> or how do we otherwise provide an API for users to define that?

I imagine that adjustments can become interesting also in this area.


> Database support - it is unclear what databases support this
> or how they do so; for example in SQL Server they seem to
> have a different syntax entirely (SELECT INTO), how do the
> capabilities of SELECT INTO differ from "CREATE TABLE AS" ?

Does one of these commands belong to the official SQL standard?


> Would we build separate constructs for different databases

This might be necessary if you would like to support non-standard functionality.
https://en.wikipedia.org/wiki/SQL#Interoperability_and_standardization


> so that they remain unaffected by each other

Will a bit more than the least common denominator become applicable?


> and if so how does the ORM use case map to that?

I find that temporary tables and corresponding objects are useful
in several cases.


> By contrast, by providing recipes, all users can immediately do exactly
> the thing they need without any issue as far as API compatibility,
> cross-database compatibility, dealing with shortcomings in the API, etc.

How much will these approaches influence the change acceptance
for a better API design?


> When you stated "I would like to count value combinations in
> database records", that sounds like you are looking to emit a SQL query
> of some kind

Yes. - I would like to perform data analysis for my needs.


> so there is no need to involve the ORM here,

I published a few scripts which demonstrate the application
of your class library for specific data processing tasks.


> so it was not clear what feature of the ORM you were seeking.

I hope that this programming interface can shield also me a bit more
from challenges because of SQL variations.

Selections an be constructed in convenient ways by Python query classes.
The data export of computed results into additional tables can become nicer,
can't it?


> can you perhaps illustrate a code example of what you would like to do ?

I could mention a bit more from a concrete example. But I imagine
that this does not really matter here at the moment.

It find it more important to take run time consequences occasionally better
into account.
Some computations require then that their results will be stored in new tables.


>>> or to build the Table object directly:
>>
>> I would prefer to reuse such functionality from the selected database system 
>> (instead of duplicating it by extra Python code).
>
> The examples I've illustrated so far aren't duplications.

I got the impression (from the descriptions a moment ago) in such a direction.


> SQLAlchemy doesn't have this feature right now.

Thanks for such an information.

I am still curious then how the situation can be improved further.
How will database reflection support evolve?
https://docs.sqlalchemy.org/en/13/core/reflection.html#limitations-of-reflection

Regards,
Markus

-- 
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.

Re: [sqlalchemy] Creating database tables from queries based on ORM API?

[Mike Bayer]

On Mon, Apr 15, 2019 at 12:22 PM Markus Elfring  wrote:
>>
>> INSERT FROM SELECT refers to an existing table, yes,
>
>
> This SQL command is already supported by your class library.
>
>
>>
>> I mention it because you referred to this construct in one of the stack 
>> overflow
>> answers you mentioned.
>
>
> It was shown that is possible (in principle) to copy some table data by 
> additional programming with Python code.
>
>
>> https://stackoverflow.com/questions/30575111/how-to-create-a-new-table-from-select-statement-in-sqlalchemy#answer-30577608
>
>
> Can a variant of the method “CreateTableAs” (which you published on 
> 2015-06-01) become a standard component of the application programming 
> interface?

it can, but as you are seeing, it's insufficient for what people
actually want to do which is map ORM classes.  We like to keep these
kinds of things as external recipes until it is crystal clear what
built in API should be added that meets at least the following
criteria, noting that more criteria may be considered:  1. does
everything that people need, that is, the full universe of use cases
is discovered and addressed 2. is definitely the best and only way to
do the thing that people need 3. works very consistently on at least
most backends 4. has very comprehensive test coverage  5. is not such
a rare use case that a simple recipe (as given) is not enough

for this feature, we are lacking #1 , #2, #3, #4 and #5 right now.
   in particular, should the construct attempt to create a new Table
object in memory also so that it need not be reflected?  What kinds of
complications might arise, can we definitely determine what decisions
the database makes when it creates this table  ?   Will people want to
ORM map these classes?  how do we determine the primary keys then, or
how do we otherwise provide an API for users to define that?  How do
we document this, including what do we state as why someone might want
to do this? Database support - it is unclear what databases
support this or how they do so; for example in SQL Server they seem to
have a different syntax entirely (SELECT INTO), how do the
capabilities of SELECT INTO differ from "CREATE TABLE AS" ? Would
we build separate constructs for different databases so that they
remain unaffected by each other and if so how does the ORM use case
map to that?

There is a lot of effort to be considered when we add things to the
library including the very arduous and tedious process of dealing with
mistakes in the API design, which occur when we build features where
we don't have an absolutely solid understanding of what this use case
needs to accomplish.   By contrast, by providing recipes, all users
can immediately do exactly the thing they need without any issue as
far as API compatibility, cross-database compatibility, dealing with
shortcomings in the API, etc.





>
>
>> I'm beginning to understand what it is you might want to do.
>
>
> Was any information from my clarification request unclear (at the beginning)?

When you stated "I would like to count value combinations in database
records", that sounds like you are looking to emit a SQL query of some
kind so there is no need to involve the ORM here, so it was not clear
what feature of the ORM you were seeking.

>
>
>> The most expedient approach is to use the CreateTableAs construct,
>> then reflect the new database table using autoload,
>
>
> I was unsure on how the provided data structures would be determined from a 
> dynamically generated database table.

can you perhaps illustrate a code example of what you would like to do ?


>
>
>> or to build the Table object directly:
>
>
> I would prefer to reuse such functionality from the selected database system 
> (instead of duplicating it by extra Python code).

The examples I've illustrated so far aren't duplications.  SQLAlchemy
doesn't have this feature right now.


>
> Regards,
> Markus
>
> --
> 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 

Re: [sqlalchemy] Creating database tables from queries based on ORM API?

[Markus Elfring]

>
> INSERT FROM SELECT refers to an existing table, yes,
>

This SQL command is already supported by your class library.

 

> I mention it because you referred to this construct in one of the stack 
> overflow 
> answers you mentioned.
>

It was shown that is possible (in principle) to copy some table data by 
additional programming with Python code.
 

https://stackoverflow.com/questions/30575111/how-to-create-a-new-table-from-select-statement-in-sqlalchemy#answer-30577608


Can a variant of the method “CreateTableAs” (which you published on 
2015-06-01) become a standard component of the application programming 
interface?
 

I'm beginning to understand what it is you might want to do.
>

Was any information from my clarification request unclear (at the 
beginning)?


The most expedient approach is to use the CreateTableAs construct,
> then reflect the new database table using autoload,
>

I was unsure on how the provided data structures would be determined from a 
dynamically generated database table.


or to build the Table object directly:
>

I would prefer to reuse such functionality from the selected database 
system (instead of duplicating it by extra Python code).

Regards,
Markus

-- 
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] Creating database tables from queries based on ORM API?

[Mike Bayer]

On Mon, Apr 15, 2019 at 9:53 AM Markus Elfring  wrote:
>>
>> insert.from_select() will accept the Query object directly.
>
>
> Such data addition depends on the detail that a table object exists before, 
> doesn't it?

INSERT FROM SELECT refers to an existing table, yes, I mention it
because you referred to this construct in one of the stack overflow
answers you mentioned.

>
>
>>
>> if using the CreateTableAs recipe,
>
>
> Do you refer to any additional information source here?

this is from the StackOverflow answer you referred towards of which I
am also the author:

https://stackoverflow.com/questions/30575111/how-to-create-a-new-table-from-select-statement-in-sqlalchemy#answer-30577608




>
>
>>
>> it can be modified to coerce a Query passed to it into
>> a Core statement internally.
>
>
> Do you suggest to work with an extra conversion approach for a while?

If you're asking, if you should pass query.statement to the
hypotheical CreateTableAs construct, or if the CreateTableAs construct
should do it, it's your choice.   It is a goal of Core objects that an
ORM query passed can be interpreted as its underlying SELECT statement
which suggests the latter.


>
> I would prefer the mapping of a dynamically generated database table to an 
> additional Python object instead.

I'm beginning to understand what it is you might want to do.   The
most expedient approach is to use the CreateTableAs construct, then
reflect the new database table using autoload, or to build the Table
object directly:

   q = session.query()

   session.execute(CreateTableAs('t2', q))

   class T2(Base):
__table__ = Table('t2', Base.metadata,
autoload_with=session.connection())

Note the above requires that the Table has a primary key, however, I'm
not sure if CREATE TABLE .. AS also generates primary key constraints
on the new table.  If not, these need to be added as well:

   class T2(Base):
__table__ = Table('t2', Base.metadata,
autoload_with=session.connection())
   __mapper_args__ = {"primary_key": ["id", "a"]}  # names of
columns to be considered primary key

To create the table directly, the basic idea would be like the
following, where I'm also trying to copy out which columns would be
considered a primary key:

   def mapping_for_query(Base, q, name):
   select = q.statement

   # dynamically create a Table object, try to copy primary key
flag also from queried tables
   table = Table(name, Base.metadata, *[Column(col.name, col.type,
primary_key=col.primary_key) for col in select.inner_columns])

  # dynamically create a mapped class against this table
  return type(name, (Base, ), {"__table__": table})

With the above, you also need to run the CreateTableAs() construct to
actually generate the table in the database.

These various units could likely be composed into a polished function
that is fairly usable for simple queries. Determining the "primary
key" from a SELECT statement however is not easily generalizable, so
your functions may need to be passed additional hints from the
programmer as to which columns would be part of the primary key.   The
ORM cannot map a class without a primary key configured.






>
> Regards,
> Markus
>
> --
> 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] Creating database tables from queries based on ORM API?

[Markus Elfring]

>
> insert.from_select() will accept the Query object directly.
>

Such data addition depends on the detail that a table object exists before, 
doesn't it?

 

> if using the CreateTableAs recipe,


Do you refer to any additional information source here?

 

> it can be modified to coerce a Query passed to it into
> a Core statement internally.
>

Do you suggest to work with an extra conversion approach for a while?

I would prefer the mapping of a dynamically generated database table to an 
additional Python object instead.

Regards,
Markus

-- 
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] Commands out of sync; you can't run this command now

[Mike Bayer]

On Mon, Apr 15, 2019 at 5:41 AM tonthon  wrote:
>
> I tried to set a lower value for the pool_recycle value and it seems to work.
>
> There is a celery service running in the background, maybe it could affect 
> the session management.

this will reduce the problem but the architectural issue that is
causing it is likely still present. I'd want to look at how the
interaction with Celery is occurring within the same process.


>
> Le 12/04/2019 à 15:58, Mike Bayer a écrit :
>
> it's likely that a database connection is being returned to the pool
> in an invalid state.
>
> Switching to NullPool temporarily might reveal that this solves all
> the issues ; at the very least, I would try setting pool_recycle to a
> low number, like 5 minutes, however this won't prevent the problem,
> just make it less likely.   What you do need to find are stack traces
> that precede the error, to give a clue why a connection would be
> placed in the pool in a bad state.
>
> The other possibility is that your application is actually sharing a
> single connection across threads in some way which would be a
> different problem though with a lot of similar behaviors.   Are there
> any global in-memory caches being used of objects where an ORM object
> might be shared out among threads, or a background worker thread of
> some kind, anything like that ?Does the application use a
> "scoped_session" pattern and maybe the session being passed around in
> some cases isn't actually scoped?
>
>
> On Fri, Apr 12, 2019 at 4:03 AM tonthon  wrote:
>
> Le 10/04/2019 à 17:12, Mike Bayer a écrit :
>
> On Wed, Apr 10, 2019 at 9:23 AM tonthon  wrote:
>
> Hi,
>
> We're using sqlalchemy in a Pyramid Web Application.
>
> We use the ZopeTransactionExtension and our session factory is initialized 
> this way :
>
> DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
>
> We use the pyramid_tm that wraps each web requests in a transaction.
>
> Our services are served through apache and mod_wsgi (1 process, 10 threads).
>
>
> We recently faced the following error :
>
> 193.253.40.35 - 2019-04-08 09:29:23,054 ERROR Exception during reset or 
> similar ([sqlalchemy.pool.QueuePool._finalize_fairy:721])
> Traceback (most recent call last):
>   File 
> "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", 
> line 712, in _finalize_fairy
> fairy._reset(pool)
>   File 
> "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", 
> line 881, in _reset
> self._reset_agent.rollback()
>   File 
> "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>  line 1632, in rollback
> self._do_rollback()
>   File 
> "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>  line 1670, in _do_rollback
> self.connection._rollback_impl()
>   File 
> "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>  line 706, in _rollback_impl
> self._handle_dbapi_exception(e, None, None, None, None)
>   File 
> "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>  line 1413, in _handle_dbapi_exception
> exc_info
>   File 
> "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
>  line 265, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=cause)
>   File 
> "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
>  line 704, in _rollback_impl
> self.engine.dialect.do_rollback(self.connection)
>   File 
> "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py",
>  line 1804, in do_rollback
> dbapi_connection.rollback()
> ProgrammingError: (_mysql_exceptions.ProgrammingError) (2014, "Commands out 
> of sync; you can't run this command now") (Background on this error at: 
> http://sqlalche.me/e/f405)
>
>
> I can't reproduce this problem that seems to happen randomly.
>
> Could anyone help me giving some points to investigate ?
>
> Is there some tuning to do (I already set the pool_recycle var) ?
>
> the most important thing is what driver are you using and what version?
>
> are you doing anything with SAVEPOINTs or greenlets / eventlet ?
>
> is the web application experiencing timeouts of some kind, such that
> transactions are being dropped ?
>
> is the above error only showing up in logs or is it occurring
> synchronously with a web request and causing the request to fail ?
>
>
> We use mysqlclient 1.4.1.
>
> We don't use any greenlet or eventlet nor savepoints.
>
> I havn't seen any timeout happening so far.
>
> The given error is happening during the request's lifecycle resulting in a 
> HTTP 500 error code
>
> After the error, the user refreshes and it works.
>
> I don't know if it could be related, but we also see things like 
> "NoSuchColumnError: "Could not locate column in row for column 'count(*)'".
>
>
>
> Thanks in advance
>
> Best regards
>
> Gaston

Re: [sqlalchemy] Creating database tables from queries based on ORM API?

[Mike Bayer]

insert.from_select() will accept the Query object directly.   if using
the CreateTableAs recipe, it can be modified to coerce a Query passed
to it into a Core statement internally.  the code to do this coercion
would resemble:

def __init__(self, name, query):

if isinstance(query, orm.Query):
query = query.statement
self.name = name
self.query = query



On Mon, Apr 15, 2019 at 8:43 AM Markus Elfring  wrote:
>>
>> both of the above stackoverflow recipes refer to usage of the
>> SQLAlchemy select() object.When you have an ORM Query object, you
>> can get the underlying select() from it by calling upon the .statement
>> accessor:
>
>
> Thanks for this information.
>
> I imagine that there is no need to use the attribute “statement” if I can 
> stick to the high-level ORM programming interface.
>
>
>>
>> ins = InsertFromSelect(temp, my_query.statement)
>> session.execute(ins)
>
>
> The call of the method “Insert.from_select” can occasionally be also 
> interesting.
> But I am looking for direct support of the SQL command “CREATE TABLE AS 
> SELECT” (CTAS) without switching to the “core” API.
> Will any software extensions be helpful for this purpose?



>
> Regards,
> Markus
>
> --
> 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] Creating database tables from queries based on ORM API?

[Markus Elfring]

>
> both of the above stackoverflow recipes refer to usage of the 
> SQLAlchemy select() object.When you have an ORM Query object, you 
> can get the underlying select() from it by calling upon the .statement 
> accessor:
>

Thanks for this information.

I imagine that there is no need to use the attribute “statement” 

 
if I can stick to the high-level ORM programming interface.

 

> ins = InsertFromSelect(temp, my_query.statement) 
> session.execute(ins)
>

The call of the method “Insert.from_select” 

 
can occasionally be also interesting.
But I am looking for direct support of the SQL command “CREATE TABLE AS 
SELECT” (CTAS) 
 
without switching to the “core” API.
Will any software extensions be helpful for this purpose?

Regards,
Markus

-- 
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] Commands out of sync; you can't run this command now

[tonthon]

I tried to set a lower value for the pool_recycle value and it seems to 
work.


There is a celery service running in the background, maybe it could 
affect the session management.


Le 12/04/2019 à 15:58, Mike Bayer a écrit :

it's likely that a database connection is being returned to the pool
in an invalid state.

Switching to NullPool temporarily might reveal that this solves all
the issues ; at the very least, I would try setting pool_recycle to a
low number, like 5 minutes, however this won't prevent the problem,
just make it less likely.   What you do need to find are stack traces
that precede the error, to give a clue why a connection would be
placed in the pool in a bad state.

The other possibility is that your application is actually sharing a
single connection across threads in some way which would be a
different problem though with a lot of similar behaviors.   Are there
any global in-memory caches being used of objects where an ORM object
might be shared out among threads, or a background worker thread of
some kind, anything like that ?Does the application use a
"scoped_session" pattern and maybe the session being passed around in
some cases isn't actually scoped?


On Fri, Apr 12, 2019 at 4:03 AM tonthon  wrote:


Le 10/04/2019 à 17:12, Mike Bayer a écrit :

On Wed, Apr 10, 2019 at 9:23 AM tonthon  wrote:

Hi,

We're using sqlalchemy in a Pyramid Web Application.

We use the ZopeTransactionExtension and our session factory is initialized this 
way :

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

We use the pyramid_tm that wraps each web requests in a transaction.

Our services are served through apache and mod_wsgi (1 process, 10 threads).


We recently faced the following error :

193.253.40.35 - 2019-04-08 09:29:23,054 ERROR Exception during reset or similar 
([sqlalchemy.pool.QueuePool._finalize_fairy:721])
Traceback (most recent call last):
   File 
"/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 
712, in _finalize_fairy
 fairy._reset(pool)
   File 
"/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 
881, in _reset
 self._reset_agent.rollback()
   File 
"/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 1632, in rollback
 self._do_rollback()
   File 
"/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 1670, in _do_rollback
 self.connection._rollback_impl()
   File 
"/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 706, in _rollback_impl
 self._handle_dbapi_exception(e, None, None, None, None)
   File 
"/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 1413, in _handle_dbapi_exception
 exc_info
   File 
"/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
 line 265, in raise_from_cause
 reraise(type(exception), exception, tb=exc_tb, cause=cause)
   File 
"/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
 line 704, in _rollback_impl
 self.engine.dialect.do_rollback(self.connection)
   File 
"/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py",
 line 1804, in do_rollback
 dbapi_connection.rollback()
ProgrammingError: (_mysql_exceptions.ProgrammingError) (2014, "Commands out of sync; 
you can't run this command now") (Background on this error at: 
http://sqlalche.me/e/f405)


I can't reproduce this problem that seems to happen randomly.

Could anyone help me giving some points to investigate ?

Is there some tuning to do (I already set the pool_recycle var) ?

the most important thing is what driver are you using and what version?

are you doing anything with SAVEPOINTs or greenlets / eventlet ?

is the web application experiencing timeouts of some kind, such that
transactions are being dropped ?

is the above error only showing up in logs or is it occurring
synchronously with a web request and causing the request to fail ?


We use mysqlclient 1.4.1.

We don't use any greenlet or eventlet nor savepoints.

I havn't seen any timeout happening so far.

The given error is happening during the request's lifecycle resulting in a HTTP 
500 error code

After the error, the user refreshes and it works.

I don't know if it could be related, but we also see things like "NoSuchColumnError: 
"Could not locate column in row for column 'count(*)'".



Thanks in advance

Best regards

Gaston

--
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] Alembic. Can i pass upgrades and downgrade directly from alembic.command.revision?

[Tolstov Sergey]

If i create migrations on GUI (browser), how can i pass upgrades/downgrades?

-- 
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.