[sqlalchemy] logging which relationship triggered a query?

[Jonathan Vanasco]

Something similar was asked on the list within the past 6 months, but I 
couldn't find it.

We found a certain bit of code where one or more relationships were not 
properly eager loaded.  This results in 300 postgres queries on a page 
instead of 30.

The easiest way I could find the problem, would be to append a comment to 
the sql that notes the relationship being accessed.

Does anyone know of a simple way to do that (or if its' possible)?

ie the raw sql would become:

 - SELECT foo FROM bar;
+ SELECT foo FROM bar; -- orm.relationship(Foo.bar)

-- 
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] Where to use and vs and_()

[Jonathan Vanasco]

On Wednesday, February 17, 2016 at 4:34:35 AM UTC-5, Simon King wrote:
>
>
> (expr1 == expr2) & (expr3 == expr4)
>

you usually won't need to & or and_ though.

filter automatically "and"s a list.

.filter(expr1 == expr2, expr3 == expr4)

the only you need to use `and_` is when doing more complicated queries and 
the "and" is nested.

.filter(or_(expr1 == expr2,
   and_(expr2 == expr3,
   expr3 == expr4
   ))

-- 
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] hang on connect in forked process

[Mike Bayer]

On 02/17/2016 11:33 AM, Uri Okrent wrote:

Maybe this is a psycopg question and if so please say so.

I have a multi-threaded server which maintains a thread-pool (and a
corresponding connection pool) for servicing requests.  In order to
mitigate python's high-water-mark memory usage behavior for large
queries, I'm attempting to handle queries in particular using a forked
subprocess from the request thread.

I'm using the connection invalidation recipe described here (the second
one that adds listeners to the Pool):
  
http://docs.sqlalchemy.org/en/latest/core/pooling.html#using-connection-pools-with-multiprocessing

It seems to be working correctly -- that is, I can see that the child
process is indeed creating a new connection.  However, I'm still
experiencing intermittent hangs in the child process during connection
creation.  I've gotten a stack trace using gdb, and I think I understand
what is going on but I'm not sure how to protect the critical section.

It looks like threads creating connections in the parent process acquire
some threading synchronization primitive inside psycopg's _connect
function (that's in c so I didn't see the actual source).  This
apparently occurs occasionally at the same time as the fork, so that the
child process never sees the primitive release in the parent process and
hangs forever.  Interestingly, hangs stop after the server has been
running for a while, presumably because the parent process is warmed up
and has a full connection pool, and is no longer creating connections.

Here is my stack on a hung process:
#17 
#19 file '/usr/lib64/python2.6/site-packages/psycopg2/__init__.py', in
'connect'
#24 file
'/usr/lib64/python2.6/site-packages/sqlalchemy/engine/default.py', in
'connect'
#29 file
'/usr/lib64/python2.6/site-packages/sqlalchemy/engine/strategies.py', in
'connect'
#33 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in
'__connect'
#36 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in
'get_connection'
#39 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in
'checkout'
#43 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in
'_checkout'
#47 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in
'connect'
#50 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py',
in '_wrap_pool_connect'
#54 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py',
in 'contextual_connect'
#58 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py',
in '_connection_for_bind'
#61 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py',
in '_connection_for_bind'
#65 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py',
in 'connection'
#70 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py',
in '_connection_from_session'
#74 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py',
in '_execute_and_instances'
#77 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py',
in '__iter__'
#91 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py',
in '__getitem__'
#99 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py',
in 'first'

I'm using sqlalchemy 1.0.12 and psycopg 2.5.3

My quick and dirty fix would be to fill the connection pool in the
parent process by force before servicing requests,


well I'd not want to transfer a psycopg2 connection from a parent to a 
child fork, because now that same filehandle is in both processes and 
you'll get unsafe concurrent access on it.


I've used multiprocessing with psycopg2 for years in a wide variety of 
scenarios and I've never seen it hanging on the actual psycopg2.connect 
call.   But perhaps that's because I've never called fork() from inside 
a thread that is not the main thread - if that is what's triggering it 
here, I'd use a pattern such as a process pool or similar where the 
forking is done from the main thread ahead of time.





but that is a hack,

and in case of an invalidated connection the server would be susceptible
to the issue again while recreating the invalid connection in the parent
process.
I apparently need to synchronize my fork in one thread with connections
being created in others, but I'm not sure how to do that.  Any pointers
would be great.

TIA,
Uri

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


--
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+u

Re: [sqlalchemy] Padding columns to a query

[Horcle]

Works perfectly!

Thanks!

Greg--

On Wednesday, February 17, 2016 at 11:48:55 AM UTC-6, Simon King wrote:
>
> On Wed, Feb 17, 2016 at 5:29 PM, Horcle > 
> wrote:
>
>> I have the following query  db.session.query(label('sid', 
>> distinct(Clinical.patient_sid))) to which I would like to pad a few 
>> extra columns with constant values, like in the following SQL example 
>>
>> select distinct(Clinical.patient_sid) as sid, 'stuph' as attribute
>>
>> from Clinical
>>
>>
>> I tried the naive approach of db.session.query(label('sid', 
>> distinct(Clinical.patient_sid)),label('attribute', 'stuph')), but it 
>> choked.
>>
>> I realize this is probably very simple, but alas.
>>
>>
> I think you can use literal_column for this, something like:
>
> import sqlalchemy as sa
> print sa.select([sa.literal_column("'stuph'").label('attribute')])
>
> output:
>
> SELECT 'stuph' AS attribute
>
> Beware that literal_column doesn't use bind parameters or do any escaping, 
> so don't use it for untrusted input.
>
> Simon
>

-- 
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] Padding columns to a query

[Simon King]

On Wed, Feb 17, 2016 at 5:29 PM, Horcle  wrote:

> I have the following query  db.session.query(label('sid',
> distinct(Clinical.patient_sid))) to which I would like to pad a few extra
> columns with constant values, like in the following SQL example
>
> select distinct(Clinical.patient_sid) as sid, 'stuph' as attribute
>
> from Clinical
>
>
> I tried the naive approach of db.session.query(label('sid',
> distinct(Clinical.patient_sid)),label('attribute', 'stuph')), but it
> choked.
>
> I realize this is probably very simple, but alas.
>
>
I think you can use literal_column for this, something like:

import sqlalchemy as sa
print sa.select([sa.literal_column("'stuph'").label('attribute')])

output:

SELECT 'stuph' AS attribute

Beware that literal_column doesn't use bind parameters or do any escaping,
so don't use it for untrusted input.

Simon

-- 
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] Padding columns to a query

[Horcle]

I have the following query  db.session.query(label('sid', 
distinct(Clinical.patient_sid))) to which I would like to pad a few extra 
columns with constant values, like in the following SQL example 

select distinct(Clinical.patient_sid) as sid, 'stuph' as attribute

from Clinical


I tried the naive approach of db.session.query(label('sid', 
distinct(Clinical.patient_sid)),label('attribute', 'stuph')), but it choked.

I realize this is probably very simple, but alas.

TIA!

Greg--



-- 
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] hang on connect in forked process

[Uri Okrent]

Maybe this is a psycopg question and if so please say so.

I have a multi-threaded server which maintains a thread-pool (and a 
corresponding connection pool) for servicing requests.  In order to 
mitigate python's high-water-mark memory usage behavior for large queries, 
I'm attempting to handle queries in particular using a forked subprocess 
from the request thread.

I'm using the connection invalidation recipe described here (the second one 
that adds listeners to the Pool): 
 
http://docs.sqlalchemy.org/en/latest/core/pooling.html#using-connection-pools-with-multiprocessing

It seems to be working correctly -- that is, I can see that the child 
process is indeed creating a new connection.  However, I'm still 
experiencing intermittent hangs in the child process during connection 
creation.  I've gotten a stack trace using gdb, and I think I understand 
what is going on but I'm not sure how to protect the critical section.

It looks like threads creating connections in the parent process acquire 
some threading synchronization primitive inside psycopg's _connect function 
(that's in c so I didn't see the actual source).  This apparently occurs 
occasionally at the same time as the fork, so that the child process never 
sees the primitive release in the parent process and hangs forever. 
 Interestingly, hangs stop after the server has been running for a while, 
presumably because the parent process is warmed up and has a full 
connection pool, and is no longer creating connections.

Here is my stack on a hung process:
#17 
#19 file '/usr/lib64/python2.6/site-packages/psycopg2/__init__.py', in 
'connect'
#24 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/default.py', 
in 'connect'
#29 file 
'/usr/lib64/python2.6/site-packages/sqlalchemy/engine/strategies.py', in 
'connect'
#33 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 
'__connect'
#36 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 
'get_connection'
#39 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 
'checkout'
#43 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 
'_checkout'
#47 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 
'connect'
#50 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py', in 
'_wrap_pool_connect'
#54 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py', in 
'contextual_connect'
#58 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py', in 
'_connection_for_bind'
#61 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py', in 
'_connection_for_bind'
#65 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py', in 
'connection'
#70 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in 
'_connection_from_session'
#74 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in 
'_execute_and_instances'
#77 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in 
'__iter__'
#91 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in 
'__getitem__'
#99 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in 
'first'

I'm using sqlalchemy 1.0.12 and psycopg 2.5.3

My quick and dirty fix would be to fill the connection pool in the parent 
process by force before servicing requests, but that is a hack, and in case 
of an invalidated connection the server would be susceptible to the issue 
again while recreating the invalid connection in the parent process.
I apparently need to synchronize my fork in one thread with connections 
being created in others, but I'm not sure how to do that.  Any pointers 
would be great.

TIA,
Uri

-- 
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] OT: basic ibm_db script hangs while connecting (wasreflection taking a very long time?)

[Alex Hall]

I did as suggested and cut my script back. It still hangs while trying
to connect, or errors out if I try different ports (no surprise
there). Below I'll paste the message I just sent to the ibm_db email
list. At least, if this is identical to what ibm_db_sa is doing, it
means that fixing it here should let SA suddenly start working
properly.

I've cut back to a very, very basic script, using only ibm_db and
nothing else. I'm running into exactly what I did when trying to use
sqlalchemy: one of two errors, or an endless waiting period as I wait
in vain for an answer from the server or a timeout. My script:

import ibm_db
dbConnection = 
ibm_db.pconnect("DATABASE=myLibraryName;HOSTNAME=1.2.3.4;PORT="+port+";PROTOCOL=TCPIP;UID=username;PWD=password",
"", "")
print ibm_db.conn_errormsg()

I got the connection string from
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.swg.im.dbclient.python.doc/doc/t0054368.html

I made the port number a variable because it is the thing that keeps
giving me different results. The docs say to use 8471 or 9471 for
database access (the latter for SSL), but those are when I get no
response whatsoever. I've also tried 5, 6, and 446, all of
which return errors immediately. The high numbers give me
SQLCode-30081, and 446 gives me -30020. I even tried a DSN, but I got
an error claiming it couldn't locate the specified DSN even though
said DSN is right in the list when I open up ODBC Manager.

The thing is, we have at least five computers that talk to this 400
for hours every day, so I know it can accept incoming connections. The
computer on which I'm running this stuff can even do it, using the
same software the other stations use, so I know my machine has the
right drivers. Is there anything else I could try? I don't know much
about the 400 itself, and it definitely works with all our current
stations with no problems at all. That said, is there something on it
that I should check? Anything anyone can think of will help. Thanks.

On 2/17/16, Michal Petrucha  wrote:
> On Tue, Feb 16, 2016 at 04:02:08PM -0500, Alex Hall wrote:
>> Great; I was hoping you wouldn't say that. :) I've been through them
>> many, many times, trying to get the connection working. I've gone from
>> error to error, and thought I had it all working when I finally got
>> the create_engine line to run with no problem. Apparently I'm not as
>> far along as I thought I was. Back to the drawing board.
>
> Hi Alex,
>
> I just want to reiterate my earlier suggestion – before you try to use
> any SQLAlchemy machinery at all, first try to create a connection from
> your Python runtime directly, using whichever DBAPI driver you want to
> use (most likely you want to create a ibm_db connection object -- do
> not import anything related to SQLAlchemy at this point, neither
> sqlalchemy, nor ibm_db_sa), make sure you are able to execute SQL
> statements using that, and only once you get this to work correctly,
> try to figure out how to make it work with SQLAlchemy.
>
> And, of course, you shouldn't try to get SQLAlchemy to work all at
> once either. First, create an Engine with a connection string, but do
> not try to run any fancy introspection or anything before you make
> sure that you can execute raw SQL queries using that engine. After you
> get *that* out of the way, you can start trying out more advanced
> features of SQLAlchemy.
>
> Baby steps, you know. Divide and conquer. Do not try to solve this
> entire huge problem all at once. (And yes, as you are probably aware
> by now, successfully connecting to an enterprise database server *is*
> a huge problem.) That way you'll avoid false leads like this one.
>
> Good luck!
>
> Michal
>
> --
> 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.
>

-- 
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] Re: reflection taking a very long time?

[Michal Petrucha]

On Tue, Feb 16, 2016 at 04:02:08PM -0500, Alex Hall wrote:
> Great; I was hoping you wouldn't say that. :) I've been through them
> many, many times, trying to get the connection working. I've gone from
> error to error, and thought I had it all working when I finally got
> the create_engine line to run with no problem. Apparently I'm not as
> far along as I thought I was. Back to the drawing board.

Hi Alex,

I just want to reiterate my earlier suggestion – before you try to use
any SQLAlchemy machinery at all, first try to create a connection from
your Python runtime directly, using whichever DBAPI driver you want to
use (most likely you want to create a ibm_db connection object -- do
not import anything related to SQLAlchemy at this point, neither
sqlalchemy, nor ibm_db_sa), make sure you are able to execute SQL
statements using that, and only once you get this to work correctly,
try to figure out how to make it work with SQLAlchemy.

And, of course, you shouldn't try to get SQLAlchemy to work all at
once either. First, create an Engine with a connection string, but do
not try to run any fancy introspection or anything before you make
sure that you can execute raw SQL queries using that engine. After you
get *that* out of the way, you can start trying out more advanced
features of SQLAlchemy.

Baby steps, you know. Divide and conquer. Do not try to solve this
entire huge problem all at once. (And yes, as you are probably aware
by now, successfully connecting to an enterprise database server *is*
a huge problem.) That way you'll avoid false leads like this one.

Good luck!

Michal

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


signature.asc
Description: Digital signature

Re: [sqlalchemy] Where to use and vs and_()

[Simon King]

On Wed, Feb 17, 2016 at 7:46 AM, Krishnakant  wrote:

> Hello all,
> The subject might have made my problem already clear.
> So I am unclear about when I should use the normal Python "and " vs the
> sqlalchemy "and_" while writing where, having or similar queries including
> joins.
> I have tryed understanding this but may be I have overlooked some thing.
> Kindly give some pointers.
> Happy hacking.
> Krishnakant.
>
>
You probably don't ever want the python keyword "and" when constructing
queries. It doesn't trigger any of SQLAlchemy's smart SQL-construction
behaviour. You might be thinking of the bitwise operator "&", which
SQLAlchemy hijacks and converts to an SQL "AND".

As far as I'm aware, "expr1 & expr2" is completely equivalent to
"and_(expr1, expr2)" if expr1 and expr2 are both SQLAlchemy expressions, so
you can use whichever you prefer.

http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html#sqlalchemy.sql.expression.and_

Note the warning on that page about operator precedence though; the bitwise
operators have higher precedence than the comparison operators, so this
probably wouldn't do what you expect:

expr1 == expr2 & expr3 == expr4

You'd need to write that as:

(expr1 == expr2) & (expr3 == expr4)

Simon

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