[sqlalchemy] How do I turn this PostgreSQL upsert query into SQLAlchemy?
Hello, I have a need to perform an upsert query with PostgreSQL. the following SQL query achieves this goal: WITH upsert AS ( UPDATE metric k SET k.count = k.count + 5 WHERE event = foo AND interval = D and date = whatever RETURNING k.* ) INSERT INTO metric (event, interval, date, count) SELECT (foo, D, whatever, 5) WHERE NOT EXISTS ( SELECT 1 FROM upsert ); How do I do this sort of thing with SQLAlchemy? It only ever needs to work with PostgreSQL so any PG specific things are fine. Best, Mitchell -- 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] QueuePool limit size reached, using expression API only
Hi, I am continually getting this sort of error after some amount of time: QueuePool limit of size 30 overflow 10 reached, connection timed out, timeout 30 We're using only the SQLAlchemy Core expressions API, so we're not wrapping anything in sessions, so I'm not sure how this is happening. Any pointers? Thanks, Mitchell -- 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/-/QlU8RHFQOwQJ. 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] QueuePool limit size reached, using expression API only
On Monday, April 9, 2012 12:03:29 PM UTC-7, Michael Bayer wrote: close your connections after you are finished with them. So I suppose my confusion is where is the connection being made. I have a singleton engine instance running around, and when I query, I basically do something like this: query = select([fields]) result = engine.execute(query).fetchall() Therefore I'm using implicit connections. The reference to the ResultProxy is quickly gone, which I thought would implicitly close the connection as well. What exactly am I supposed to do here? Mitchell On Apr 9, 2012, at 2:43 PM, Mitchell Hashimoto wrote: Hi, I am continually getting this sort of error after some amount of time: QueuePool limit of size 30 overflow 10 reached, connection timed out, timeout 30 We're using only the SQLAlchemy Core expressions API, so we're not wrapping anything in sessions, so I'm not sure how this is happening. Any pointers? Thanks, Mitchell -- 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/-/QlU8RHFQOwQJ. 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 view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/IQNlf2z7RscJ. 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] QueuePool limit size reached, using expression API only
On Mon, Apr 9, 2012 at 5:32 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Apr 9, 2012, at 8:24 PM, Mitchell Hashimoto wrote: On Monday, April 9, 2012 12:03:29 PM UTC-7, Michael Bayer wrote: close your connections after you are finished with them. So I suppose my confusion is where is the connection being made. I have a singleton engine instance running around, and when I query, I basically do something like this: query = select([fields]) result = engine.execute(query).fetchall() Therefore I'm using implicit connections. The reference to the ResultProxy is quickly gone, which I thought would implicitly close the connection as well. What exactly am I supposed to do here? That pattern will return the connection to the pool immediately after use - so for that to be the only pattern at play, you'd have to have some execute() or fetchall() calls that are taking so long to complete that concurrent executions are timing out. You'd want to look at any processes/threads hanging or taking very long and causing other concurrent connections to time out. If you aren't using threads or concurrency, and expect that only one connection should be in use at a time for a given engine, then I'd give the AssertionPool a quick try which will ensure you're only checking out one connection at a time, illustrating a stack trace where a second concurrent checkout would be occurring. In addition to fetchall() there are some fetchone() calls as well. I'm assuming in these cases I need to explicitly close the ResultProxy? Mitchell -- 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. -- 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.
