[sqlalchemy] Closing pool connections
Hi, I have a multi-threaded web application using SQLAlchemy connections pool. As soon as a connection is opened, it's maintained opened in the pool even if it's not used anymore. On a long run, this can consume too much unnecessary connections and database server resources. So what I'm actually looking for is a way to close a given connection which is returned to the pool if it wasn't used for a given amount of time... Any idea ? Best regards, Thierry -- http://www.imagesdusport.com -- http://www.ztfy.org -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Closing pool connections
On Thu, Sep 25, 2014 at 5:33 AM, Thierry Florac tflo...@gmail.com wrote: Hi, I have a multi-threaded web application using SQLAlchemy connections pool. As soon as a connection is opened, it's maintained opened in the pool even if it's not used anymore. On a long run, this can consume too much unnecessary connections and database server resources. So what I'm actually looking for is a way to close a given connection which is returned to the pool if it wasn't used for a given amount of time... Any idea ? A while back when I wanted this, I ended up subclassing QueuePool and using that subclass with the engine. Sadly, I don't have the code at hand. But I have managed without it since then, so I'm wondering if this is really an issue. Currently, I'm using postgres with pgbouncer, and this last one can handle separating the idle from the active very well. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Closing pool connections
On Sep 25, 2014, at 4:33 AM, Thierry Florac tflo...@gmail.com wrote: Hi, I have a multi-threaded web application using SQLAlchemy connections pool. As soon as a connection is opened, it's maintained opened in the pool even if it's not used anymore. On a long run, this can consume too much unnecessary connections and database server resources. So what I'm actually looking for is a way to close a given connection which is returned to the pool if it wasn't used for a given amount of time... OK well first we assume that you do want pooling in the first place. If you just don't want any, you'd use NullPool. So assuming you do want pooling, the next thing that resembles what you describe, but I'm guessing still is not what you want, is the pool_recycle setting, which will prevent a connection that is older than N seconds from being used. This recycle occurs when the connection is to be fetched; if it is past the expiration time, it is closed and replaced with a new one. However the connection stays in the pool until the pool is accessed. So the final option is, you want the connection returned to the pool while the pool is idle. The challenge there is that nothing is happening in the app to make this happen, which implies a background thread or other asynchronous task system, so you'd have to roll that yourself. Overall if you have more detailed pooling needs the suggestion to use PGBouncer is probably a good one. If it were me, I'd just use a low pool size, just have 5 connections hanging around with a higher overflow. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] unions of selects containing a cte - is this right?
On Sep 24, 2014, at 10:13 PM, Jonathan Vanasco jvana...@gmail.com wrote:
preface: I can post an example if needed (already made it to figure out this
was the problem)
i have 2 similar queries, each containing a CTE, which are then joined by a
union
a very abbreviated version of each one is this:
cte_Stream1 = s.query(
Foo.id.label('id'),
Bar.event_timestamp.label('event_timestamp'),
)\
.cte(name=cte_steam_1)
query_Stream1 = s.query(
cte_Stream1.c.id,
cte_Stream1.c.event_timestamp,
)
cte_Stream2 = s.query(
Foo.id.label('id'),
Bar.event_timestamp.label('event_timestamp'),
)\
.cte(name=cte_steam_2)
query_Stream2 = s.query(
cte_Stream2.c.id,
cte_Stream2.c.event_timestamp,
)
_unioned = sqlalchemy.union_all(query_Stream1, query_Stream2)
the only way I am able to operate on _unioned (orders, selects, joins,
filters, etc) is by addressing the `cte_Stream1.c.id` and
`cte_Stream1.c.event_timestamp`.
this sort of makes sense, and sort of doesn't. i just wanted to make sure I
am doing this right.
probably, those CTEs are the tables you're selecting from ultimately.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Self-Referential Association Relationship SQLalchemy
Right, so it would start something like this:
class Contact(db.Model):
__tablename__ = 'contact'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Unicode(120), nullable=False, unique=False)
created_on = db.Column(db.DateTime, default=datetime.utcnow)
birthday = db.Column(db.DateTime)
background = db.Column(db.Text)
photo = db.Column(db.Unicode(120))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
to_contacts = association_proxy('to_relations', 'to_contact')
from_contacts = association_proxy('from_relations', 'from_contact')
class ContactRelation(db.Model):
__tablename__ = 'contactrelation'
id = db.Column(db.Integer, primary_key=True)
from_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
to_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
relation_type = db.Column(db.String(100), nullable=True)
from_contact = db.relationship(Contact,
primaryjoin=(from_contact_id == Contact.id),
backref='to_relations')
to_contact = db.relationship(Contact,
primaryjoin=(to_contact_id == Contact.id),
backref='from_relations')
Hope that helps,
Simon
On Wed, Sep 24, 2014 at 8:27 PM, Mohammad Reza Kamalifard
mr.kamalif...@gmail.com wrote:
Thanks,
I want to create a relationship between two contact object and add more data
like relation type to the relation table.
For example Contact with contact id 1 and contact id 2 has friend
relationship type and some more data.
Exactly i want a self association relation.
On Wed, Sep 24, 2014 at 7:47 PM, Simon King si...@simonking.org.uk wrote:
OK, firstly I'm not sure you want to be using your contactrelation
table both as a secondary as well as a mapped class. You generally
want one or the other. See the note at the bottom of the Association
Object section of
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object
For your specific error, you could try defining the primaryjoin and
secondaryjoin conditions like this, but I've honestly no idea if it is
the right approach:
primaryjoin=id=='foreign(contactrelation.c.from_contact_id)',
secondaryjoin=id=='foreign(contactrelation.c.to_contact_id)',
This definitely seems fishy to me though - I'm not sure if SA will
understand that the 2 id columns there correspond to 2 different
instances of the Contact class.
Unless you have a particular need for the secondary mechanism, I
would rewrite it so that ContactRelation has from_contact and
to_contact relationships, then use an association proxy to hide the
ContactRelation when you don't need it explicitly:
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/associationproxy.html
Hope that helps,
Simon
On Wed, Sep 24, 2014 at 2:15 PM, Mohammad Reza Kamalifard
mr.kamalif...@gmail.com wrote:
Here is the whole Contact and ContactRelation model
class Contact(db.Model):
__tablename__ = 'contact'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Unicode(120), nullable=False, unique=False)
created_on = db.Column(db.DateTime, default=datetime.utcnow)
birthday = db.Column(db.DateTime)
background = db.Column(db.Text)
photo = db.Column(db.Unicode(120))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
to_contacts = db.relationship('Contact',
secondary='contactrelation',
primaryjoin=id== 'contactrelation.c.from_contact_id',
secondaryjoin=id== 'contactrelation.c.to_contact_id',
backref = 'from_contacts')
class ContactRelation(db.Model):
__tablename__ = 'contactrelation'
id = db.Column(db.Integer, primary_key=True)
from_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
to_contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
relation_type = db.Column(db.String(100), nullable=True)
Thanks,
Mohammad Reza
On Wed, Sep 24, 2014 at 2:38 PM, Simon King si...@simonking.org.uk
wrote:
Could you show the whole model and table definition? I've lost track
of exactly what you've written.
Thanks,
Simon
On Wed, Sep 24, 2014 at 11:15 AM, Mohammad Reza Kamalifard
mr.kamalif...@gmail.com wrote:
thanks Mike
with new to_contacts relationship i have new error
ArgumentError: Could not locate any relevant foreign key columns for
primary
join condition 'contact.id = :param_1' on relationship
Contact.to_contacts.
Ensure that referencing columns are associated with a ForeignKey or
ForeignKeyConstraint, or are annotated in the join condition with the
foreign() annotation.
On Wed, Sep 24, 2014 at 5:49 AM, Michael Bayer
mike...@zzzcomputing.com
wrote:
to_contacts = db.relationship('Contact',
secondary='contactrelation',
Re: [sqlalchemy] unions of selects containing a cte - is this right?
Thanks. All the test queries have been passing, but I was just worried as
this section of code is overly complex and this type of stuff isn't really
documented (and sort of blends into expectations of the Sql Standard too).
That's what I thought. I just keep expecting to be able to query using the
values from `.label()` or `.alias()`.
ie... the correct form continues
_unioned = sqlalchemy.sql.expression.alias(unioned, name='unioned')
_selected = sqlalchemy.select(
(
cte_Stream1.c.shared_link_id.label('id'),
cte_Stream2.c.event_timestamp.label('event_timestamp')
),
)\
.distinct(cte_Stream1.c.id)\
.order_by(
cte_Stream1.c.id.desc(),
cte_Stream2.c.event_timestamp.desc(),
)
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Closing pool connections
Hi Michael, My first problem is that I have to deal with Oracle instead of PostgreSQL, so using PGBouncer is not an option :-( Moreover, what I need is not to be able to create a new connection each time it's accessed from the pool, but to be able to close a connection if it's unused for a given time. Because I have several processes, each process having several threads with a connection pool maintaining connections to several database schemas; in the end that means a lot of connections, most being unfrequently used! As you say, that probably means creating a new thread to monitor unused connection. Do you think that using pool events to monitor a pool's connections could be a good starting point? Best regards, Thierry 2014-09-25 18:04 GMT+02:00 Michael Bayer mike...@zzzcomputing.com: On Sep 25, 2014, at 4:33 AM, Thierry Florac tflo...@gmail.com wrote: Hi, I have a multi-threaded web application using SQLAlchemy connections pool. As soon as a connection is opened, it's maintained opened in the pool even if it's not used anymore. On a long run, this can consume too much unnecessary connections and database server resources. So what I'm actually looking for is a way to close a given connection which is returned to the pool if it wasn't used for a given amount of time... OK well first we assume that you do want pooling in the first place. If you just don't want any, you'd use NullPool. So assuming you do want pooling, the next thing that resembles what you describe, but I'm guessing still is not what you want, is the pool_recycle setting, which will prevent a connection that is older than N seconds from being used. This recycle occurs when the connection is to be fetched; if it is past the expiration time, it is closed and replaced with a new one. However the connection stays in the pool until the pool is accessed. So the final option is, you want the connection returned to the pool while the pool is idle. The challenge there is that nothing is happening in the app to make this happen, which implies a background thread or other asynchronous task system, so you'd have to roll that yourself. Overall if you have more detailed pooling needs the suggestion to use PGBouncer is probably a good one. If it were me, I'd just use a low pool size, just have 5 connections hanging around with a higher overflow. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- http://www.imagesdusport.com -- http://www.ztfy.org -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] selecting from a union
thanks to some testing, i realized that my previous attempt to select from a union were wrong. i ended up selecting data from only one cte. given that I have a union like this: _unioned = sqlalchemy.union(query_Stream1, query_Stream2) how can I select data from the union? i've tried different variations of unioned.subquery(), unioned.cte(), select(unioned), etc. i either generate an error or only generate sql that corresponds to the left-handed select. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] selecting from a union
whats the SQL output? where's it going wrong? unions and CTEs are both individually extremely complicated. putting them together is not surprising that it isn't working. On Sep 25, 2014, at 6:42 PM, Jonathan Vanasco jvana...@gmail.com wrote: thanks to some testing, i realized that my previous attempt to select from a union were wrong. i ended up selecting data from only one cte. given that I have a union like this: _unioned = sqlalchemy.union(query_Stream1, query_Stream2) how can I select data from the union? i've tried different variations of unioned.subquery(), unioned.cte(), select(unioned), etc. i either generate an error or only generate sql that corresponds to the left-handed select. -- 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 http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] selecting from a union
This is the test case I've been working on today. https://gist.github.com/jvanasco/9be1f528526e496fc751 The target sql looks crappy, because it's just an example. the various issues have been: * if i can build a query without an Exception: ** I duplicate the query within a subqueries ** I build it in a way that I don't have addressable columns (for sorting/joins/etc) * if i raise an exception (more often), it's because I'm not able to turn a union/cte into the correct type of object (which has addressable columns, etc) -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] selecting from a union
just curious, if you don't type cte(), you have just a selectable. From a Python perspective it should work exactly the same way (e.g. CTEs are a SQL syntactical trick that SQLAlchemy to some degree provides already in Python, at least semantically). Is there something other than performance / SQL complexity that changes? I'd have to find time to look at this as with CTE/union it's probably a crapshow, and I'm sort of overloaded with those right now On Sep 25, 2014, at 9:12 PM, Jonathan Vanasco jvana...@gmail.com wrote: This is the test case I've been working on today. https://gist.github.com/jvanasco/9be1f528526e496fc751 The target sql looks crappy, because it's just an example. the various issues have been: * if i can build a query without an Exception: ** I duplicate the query within a subqueries ** I build it in a way that I don't have addressable columns (for sorting/joins/etc) * if i raise an exception (more often), it's because I'm not able to turn a union/cte into the correct type of object (which has addressable columns, etc) -- 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 http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] selecting from a union
there's a lot there, here's a proof of concept:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String)
sess = Session()
cte_1 = sess.query(A).cte('c1')
cte_2 = sess.query(A).cte('c2')
stmt = union(cte_1.select(), cte_2.select())
print sess.query(A).select_entity_from(stmt)
if we want to filter, making an alias() out of stmt then using that works:
stmt = union(cte_1.select(), cte_2.select()).alias()
print sess.query(A).select_entity_from(stmt).filter(stmt.c.data == 'hi')
output:
WITH c1 AS
(SELECT a.id AS id, a.data AS data
FROM a),
c2 AS
(SELECT a.id AS id, a.data AS data
FROM a)
SELECT anon_1.id AS anon_1_id, anon_1.data AS anon_1_data
FROM (SELECT c1.id AS id, c1.data AS data
FROM c1 UNION SELECT c2.id AS id, c2.data AS data
FROM c2) AS anon_1
WHERE anon_1.data = :data_1
On Sep 25, 2014, at 9:12 PM, Jonathan Vanasco jvana...@gmail.com wrote:
This is the test case I've been working on today.
https://gist.github.com/jvanasco/9be1f528526e496fc751
The target sql looks crappy, because it's just an example.
the various issues have been:
* if i can build a query without an Exception:
** I duplicate the query within a subqueries
** I build it in a way that I don't have addressable columns (for
sorting/joins/etc)
* if i raise an exception (more often), it's because I'm not able to turn a
union/cte into the correct type of object (which has addressable columns, etc)
--
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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
