Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Tue, Nov 8, 2011 at 6:59 AM, Michael Bayer mike...@zzzcomputing.com wrote: OK, here's my attempt. Takes place pretty much at the pool level and is not too intrusive, also leaves in place all the existing reconnect stuff which should just work as is, not to mention leaves creator() in place which also has some exception handling. I'm hoping you can test it out and grok the general idea in case it needs adjustment, and if we can write full tests it can be part of the distribution too, maybe as an ext. Oh wow, thanks a lot Michael. That looks much cleaner. I'll integrate it in our codebase and see if I can complete the tests. We will bench it in our infra to see how it goes with the real Sync app/data. For the reconnect stuff, I am not sure to understand how things currently work: in case of a connection error in MySQL (2013 and the likes) the engine.execute() method will throw the error and unless I have done things wrong, the error bubbles up and the pool does not attempt to recreate a new connection and run the query again. The small block I have here: https://hg.mozilla.org/services/server-core/file/2.6.1/services/util.py#l621 Does exactly this. Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
Here's my v3, with a mechanism to refresh the shared pool when an engine rebuilds it via dispose http://tarek.pastebin.mozilla.org/1376367 It also have a few thread locks to try to make it thread-safe. I've written some tests with hundreds of threads and it seems to work fine, but I don't really know if there are other places where I should do something. On Fri, Nov 4, 2011 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 4, 2011, at 3:41 AM, Tarek Ziadé wrote: On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in. But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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. -- Tarek Ziadé | http://ziade.org -- 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.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Thu, Nov 3, 2011 at 11:31 PM, Michael Bayer mike...@zzzcomputing.com wrote: ... I should look at this more closely, took a brief glance. One thought I had was why not do the switch the schema thing within Engine.connect(), at least there you know which engine you're dealing with. Ok I'll try this. I was not sure it was the proper place to do the initialization of the schema Though I don't really understand how this is organized anyway, the query() function for example seems a little weird, wouldn't you want this to be transparent at the Engine level ? Yeah sure -- that's the optimal goal. I will try to refactor everything as a custom Engine I guess, that handles/initialize its own set of pools, There should be a simple way to make two engines talk to one pool and switch the schema based on each engine. Maybe some context to be provided to the checkout event- possibly a small API change. Will try and come back ;) Thanks -- 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. -- Tarek Ziadé | http://ziade.org -- 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.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Fri, Nov 4, 2011 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com wrote: ... OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in. I did not know. Do you have any pointer ? But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Ah I see.. maybe some kind of event the engine can register to, to refresh its pool variable ? Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
So, I have worked on a first prototype, and was unable to use the event system as-is, because of a lack of context to know which database the user is trying to access. So, after a bit of hacking, here's what I have done: http://tarek.pastebin.mozilla.org/1372473 - the url is passed along the ConnectionFairy checkout method, so I can decide if I have to switch the database - I keep a few globals to decide lazily if a database should be initialized - I keep one engine and one pool *per server* it seems to work, here's an example of usage with the query() function: http://tarek.pastebin.mozilla.org/1372476 So, now I am pretty sure most of this code is crappy, and there's a better way to do this. I still need to make sure all of this is thread-safe. Thoughts ? Feedback ? Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Thu, Nov 3, 2011 at 2:28 PM, Tarek Ziadé ziade.ta...@gmail.com wrote: So, I have worked on a first prototype, and was unable to use the event system as-is, because of a lack of context to know which database the user is trying to access. So, after a bit of hacking, here's what I have done: http://tarek.pastebin.mozilla.org/1372473 ooops, I missed a piece : def _raw(self): return self.pool.unique_connection(self.url) -- Tarek Ziadé | http://ziade.org -- 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.
Re: [sqlalchemy] with_comment() ?
On Tue, Jul 12, 2011 at 4:46 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 11, 2011, at 5:46 PM, Tarek Ziadé wrote: Hello We're using with_hint() to add comments to sql queries, but that'll be work only with selects. I guess I can do a compiler extension to be able to add a /* comment */ in my queries, but I was wondering: unless I missed something, wouldn't it be useful to have a with_comment() method to be able to add a comment to a select, insert or update statement ? It would be useful yes, as well as available on Query as we've had some requests for this. Assuming the format of SQL comments doesn't change much across backends, it would be an easy patch (with unit tests of course). I'll give it a look then.. Still unanswered is what commenting would be desirable for INSERT/UPDATE/DELETE emitted during a flush, if any. I would say no in my use case. For us, the main reason to comment queries is to track them easily in the sql logs, even if the query text changes over time -- we don't rely on automatic queries, sessions etc, Cheers -- Tarek Ziadé | http://ziade.org -- 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] with_comment() ?
Hello We're using with_hint() to add comments to sql queries, but that'll be work only with selects. I guess I can do a compiler extension to be able to add a /* comment */ in my queries, but I was wondering: unless I missed something, wouldn't it be useful to have a with_comment() method to be able to add a comment to a select, insert or update statement ? Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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] per-host pooling vs per-engine pooling
Hello
I was wondering if there's a simple way to do per-host pooling.
Here's my use case: I have several hosts and on each one of them has
several databases hosted in mysql.
the engine is tied to a specific database when you create it, so the
default pool will pool connection on a specific database.
what I'd like to do is share the same pool of connectors across all
engines that are working with a given host.f
Example in pseudo-code:
pool_server1 = Pool('mysql://server1', size=10)
engine1 = create_engine('mysql://server1/database1', pool=pool_server1)
engine2 = create_engine('mysql://server1/database2', pool=pool_server1)
The goal is to reduce the number of sockets open for every host
Is there a way to do it with the existing pools ? If not, I guess
I'll work on a custom Pool,
Thanks
Tarek
--
Tarek Ziadé | http://ziade.org
--
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.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Fri, Jun 24, 2011 at 7:04 PM, Michael Bayer mike...@zzzcomputing.com wrote: ... Is there a way to do it with the existing pools ? If not, I guess I'll work on a custom Pool, well a pool is geared towards one specific connection which already has a database set up on it. I guess maybe you'd use pool events to switch the database on checkout. do you known if the switching can be done without closing the socket ? You can share a pool between engines using the pool argument to create_engine(), but one problem with that right now is that the dialect which is per-engine uses the on first checkout event to handle some important dialect initialization. That step would have to be accommodated in some way. Also this solution is only local to one Python process, not even a fork. You might have a simpler time limiting TCP connections using a product like PGBouncer, and turn pooling way down or off for each SQLAlchemy pool, that way you can truly limit connections per host. Ok thanks for the tip I'll investigate on this Cheers Tarek -- 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.
Re: [sqlalchemy] Pypi release policy
On Tue, Feb 15, 2011 at 3:27 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote .. But aren't apps supposed to use =0.6.99 to avoid backward compats issues? Apps that don't will also break when 0.7 final is on pypi. There are different things here: 1/ PyPI allows projects to publish any release, and easy_install will pick the latest one, whether it's a final (==stable) or not. You can publish your trunk if you want. 2/ An application that defines a dependency can define it in different flavors: a - Give me the latest release that was made available at PyPI b - Give me the latest release from the 0.6.x series, it can use a 0.7 or 0.6.99 c - Give me version XX --- best practice once in production For applications that are using 2.a, the interpretation of most people is that the latest release at PyPI they are depending on is not a development release. If they want a development release, they do it explicitly in their environment to leave on the edge. 3/ a user types easy_install SQLAlchemy and wants the latest stable So, yeah, when 0.7.1 final will be out, some apps will break -- but they've been warned and they can choose to change their code or pin their dependency to the 0.6.x series. But right now, it's a development release that has been published for feedback as opposed to a final release. The less disruptive process (until distutils2 is available) in that case is to let people opt in to be beta testers, and let SQLAlchemy means latest stable, whether it's called by easy_install SQLAlchemy or in the install_requires option in setuptools. Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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.
Re: [sqlalchemy] Pypi release policy
On Mon, Feb 14, 2011 at 11:18 AM, Wichert Akkerman wich...@wiggy.net wrote: On 2/14/11 10:57 , M3nt0r3 wrote: Yesterday my life become an hell. :) On saturday ( :O ) SA0.7.b1 is released and easy_install start to use it. The problem is that it broke some installer and doesn't work with my app. That sounds like a bug in your installer, not in SQLAlchemy. I had a similar issue this week-end, all my buildbots turned red this week-end, so I had to pin to 0.6.6 my trunk It seems that 0.6.7b1 introduced a backward incompatible change without a deprecation step, from 0.6.6 to 0.7.1b (Unless I missed a previous deprecation warning, but I don't recall seeing any) ERROR: Failure: TypeError (__init__() takes exactly 1 argument (2 given)) -- Traceback (most recent call last): File /srv/hudson/jobs/Sync-Server-Unit-tests/workspace/lib/python2.6/site-packages/nose-1.0.0-py2.6.egg/nose/loader.py, line 390, in loadTestsFromName addr.filename, addr.module) File /srv/hudson/jobs/Sync-Server-Unit-tests/workspace/lib/python2.6/site-packages/nose-1.0.0-py2.6.egg/nose/importer.py, line 39, in importFromPath return self.importFromDir(dir_path, fqname) File /srv/hudson/jobs/Sync-Server-Unit-tests/workspace/lib/python2.6/site-packages/nose-1.0.0-py2.6.egg/nose/importer.py, line 86, in importFromDir mod = load_module(part_fqname, fh, filename, desc) File /srv/hudson/jobs/Sync-Server-Unit-tests/workspace/deps/server-storage/syncstorage/tests/test_sql.py, line 41, in module from syncstorage.storage.sqlmappers import get_wbo_table_name File /srv/hudson/jobs/Sync-Server-Unit-tests/workspace/deps/server-storage/syncstorage/storage/sqlmappers.py, line 52, in module class Collections(_Base): File /srv/hudson/jobs/Sync-Server-Unit-tests/workspace/deps/server-storage/syncstorage/storage/sqlmappers.py, line 55, in Collections userid = Column(Integer(11), primary_key=True, nullable=False) TypeError: __init__() takes exactly 1 argument (2 given) The code: http://hg.mozilla.org/services/server-storage/file/78762deede5d/syncstorage/storage/sqlmappers.py#l55 Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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.
Re: [sqlalchemy] Pypi release policy
On Mon, Feb 14, 2011 at 11:31 AM, Wichert Akkerman wich...@wiggy.net wrote: On 2/14/11 11:25 , M3nt0r3 wrote: My installer for windows download during the process some libs. I use easy_install sqlalchemy and it works. Now too works but 0.7.b1 is not working with the app itself now so it create a lot of problem. I thought that if 0.7.b1 is a beta and brokes API maybe it should be better to use sqlalchemy==0.7 or sqlalchemy == dev. It sounds like the dependencies in your package are too liberal. I would change your dependency to SQLAlchemy =0.6, 0.7dev. That way you can never accidentally install an incompatible SQLAlchemy version. If the changes that break the code did not have a deprecation step in 0.6, that's still an issue to fix imo you don't release at pypi a version that breaks the latest stable. or if you do, you check the hidden attribute on that release, to avoid this problem with installers Wichert. -- 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. -- Tarek Ziadé | http://ziade.org -- 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.
Re: [sqlalchemy] Pypi release policy
On Mon, Feb 14, 2011 at 11:44 AM, Wichert Akkerman wich...@wiggy.net wrote: .. you don't release at pypi a version that breaks the latest stable. or if you do, you check the hidden attribute on that release, to avoid this problem with installers SQLAlchemy 0.7b1 is hidden. The hidden flag only hides it from humans though, not from setuptools. oh true...I forgot it's not hidden in the simple index :/ -- 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.
Re: [sqlalchemy] Pypi release policy
On Mon, Feb 14, 2011 at 6:00 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 14, 2011, at 5:53 AM, Tarek Ziadé wrote: On Mon, Feb 14, 2011 at 11:44 AM, Wichert Akkerman wich...@wiggy.net wrote: .. you don't release at pypi a version that breaks the latest stable. or if you do, you check the hidden attribute on that release, to avoid this problem with installers SQLAlchemy 0.7b1 is hidden. The hidden flag only hides it from humans though, not from setuptools. oh true...I forgot it's not hidden in the simple index :/ OK so you're the expert - how does one release a beta on pypi without crashing everyone's stable install ? should i just stick to sourceforge until final release ? Unfortunately, Setuptools will pick the latest version and won't care about beta tags (zc.buildout has such feature -- prefer-final, and Distutils2 too) So I guess the best way with the current eco-system is to avoid pushing any unstable release to PyPI or... if you have the time to do so, push a new 0.7 beta that makes sure people that run on the latest 0.6 can run it -- with deprecation warnings all over the place :) Cheers Tarek -- 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. -- Tarek Ziadé | http://ziade.org -- 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] Adding order_by, offset and limit support to Delete
Hello,
I need to add order_by, limit and offset support to Delete objects for
MySQL. Here's what I've done so far, which works. See the long paste
below, I use delete() on my tables.
I would like to know if this is the right way to do things, or if I am
missing something. I am currently using 0.6.x
Thanks !
Tarek
from sqlalchemy.sql.expression import _generative, Delete, _clone, ClauseList
from sqlalchemy import util
from sqlalchemy.sql.compiler import SQLCompiler
class CustomCompiler(SQLCompiler):
def visit_delete(self, delete_stmt):
self.stack.append({'from': set([delete_stmt.table])})
self.isdelete = True
text = DELETE FROM + self.preparer.format_table(delete_stmt.table)
if delete_stmt._returning:
self.returning = delete_stmt._returning
if self.returning_precedes_values:
text += + self.returning_clause(delete_stmt,
delete_stmt._returning)
if delete_stmt._whereclause is not None:
text += WHERE + self.process(delete_stmt._whereclause)
if len(delete_stmt._order_by_clause) 0:
text += ORDER BY + self.process(delete_stmt._order_by_clause)
if delete_stmt._limit is not None or delete_stmt._offset is not None:
text += self.limit_clause(delete_stmt)
if self.returning and not self.returning_precedes_values:
text += + self.returning_clause(delete_stmt,
delete_stmt._returning)
self.stack.pop(-1)
return text
class DeleteOrderBy(Delete):
def __init__(self, table, whereclause, bind=None, returning=None,
order_by=None, limit=None, offset=None, **kwargs):
Delete.__init__(self, table, whereclause, bind, returning, **kwargs)
self._order_by_clause = ClauseList(*util.to_list(order_by) or [])
self._limit = limit
self._offset = offset
@_generative
def order_by(self, *clauses):
self.append_order_by(*clauses)
def append_order_by(self, *clauses):
if len(clauses) == 1 and clauses[0] is None:
self._order_by_clause = ClauseList()
else:
if getattr(self, '_order_by_clause', None) is not None:
clauses = list(self._order_by_clause) + list(clauses)
self._order_by_clause = ClauseList(*clauses)
@_generative
def limit(self, limit):
self._limit = limit
@_generative
def offset(self, offset):
self._offset = offset
def _copy_internals(self, clone=_clone):
self._whereclause = clone(self._whereclause)
for attr in ('_order_by_clause',):
if getattr(self, attr) is not None:
setattr(self, attr, clone(getattr(self, attr)))
def get_children(self, column_collections=True, **kwargs):
children = Delete.get_children(column_collections, **kwargs)
return children + [self._order_by_clause]
def _compiler(self, dialect, **kw):
return CustomCompiler(dialect, self, **kw)
def delete(table, whereclause = None, **kwargs):
return DeleteOrderBy(table, whereclause, **kwargs)
--
Tarek Ziadé | http://ziade.org
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] table inheritance
Hey,
I have a declarative table called 'Foo':
_Base = declarative_base()
class Foo(_Base):
__tablename__ = 'foo'
__table_args__ = {'mysql_engine': 'InnoDB',
'mysql_charset': 'latin1'}
id = Column(String(64), primary_key=True, autoincrement=False)
stuff = Column(Integer(11), primary_key=True, nullable=False)
Now I create Foo1 that has exactly the same definition but differs
just by the name:
class Foo1(_Base):
__tablename__ = 'foo1'
__table_args__ = {'mysql_engine': 'InnoDB',
'mysql_charset': 'latin1'}
id = Column(String(64), primary_key=True, autoincrement=False)
stuff = Column(Integer(11), primary_key=True, nullable=False)
And I need more of those (FooN with N = 10). The use case is to shard
data across several tables.
To avoid cut/n/pastes, I tried inheritance, meta-class, a type
factory and the like, but everything fails. Whether because I was
reusing the same column class objects or whether because I was
conflicting with SQLALchemy meta-class magic.
Is there any sane way to do this and avoid a crazy copy/paste ?
Cheers
Tarek
--
Tarek Ziadé | http://ziade.org
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] table inheritance
On Tue, Dec 7, 2010 at 5:08 PM, Michael Bayer mike...@zzzcomputing.com wrote: This is the entity name recipe, and we have a classical and declarative version over at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName . It's using just the straight type() factory. You definitely want to use distinct Column objects for each class, this because a Column object is immediately made to reference its parent Table, for obvious reasons. Recipes that generate multiple tables from a single set of Column objects use the copy() method on Column for this purpose. However, you don't even need to deal with that, since declarative mixins take care of the copy() stuff for you. I've updated the EntityName example to illustrate taking advantage of the mixin so check it out. Thanks ! I am always amazed by how fast and accurately you are always answering here, kudos for this. Cheers Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] rollbacks on select
Hello, I use the default options to run queries via sql expressions and I've noticed that SQLAlchemy does a rollback after every select using the mysql default engine. These rollback are not really useful and eat 15% of the CPU time. Is this a normal behavior, part of the auto commit custom strategy SQLAlchemy implements ? If yes, is there a way to avoid those extra rollbacks ? Regards Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] rollbacks on select
On Mon, Aug 23, 2010 at 5:48 PM, Michael Bayer mike...@zzzcomputing.com wrote: Sent from my iPhone On Aug 23, 2010, at 8:29 PM, Tarek Ziadé ziade.ta...@gmail.com wrote: Hello, I use the default options to run queries via sql expressions and I've noticed that SQLAlchemy does a rollback after every select using the mysql default engine. These rollback are not really useful and eat 15% of the CPU time. Is this a normal behavior, part of the auto commit custom strategy SQLAlchemy implements ? If yes, is there a way to avoid those extra rollbacks ? Because we're using a connection pool, putting the connection back into the pool without a rollback means you throw existing transactional locks and state into the pool as well, holding them open indefinitely and generally causing problems for subsequent usages of those pooled connections. However, we get a complaint about every 6 months from a mysql myisam user, who uses many ad-hoc connection checkouts (which in itself is a little unusual) and who would rather not have it (as myisam has no transactional integrity anyway). For those cases, we tell them to add rollback_on_return=False to their create_engine to turn the behavior off. But also maybe consider why you have a high volume of checkins, rather then working in some kind of transaction-per-logical-operation scheme (like a web request). Thanks Michael ! -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] rollbacks on select
On Mon, Aug 23, 2010 at 5:53 PM, Tarek Ziadé ziade.ta...@gmail.com wrote: On Mon, Aug 23, 2010 at 5:48 PM, Michael Bayer mike...@zzzcomputing.com wrote: Sent from my iPhone On Aug 23, 2010, at 8:29 PM, Tarek Ziadé ziade.ta...@gmail.com wrote: Hello, I use the default options to run queries via sql expressions and I've noticed that SQLAlchemy does a rollback after every select using the mysql default engine. These rollback are not really useful and eat 15% of the CPU time. Is this a normal behavior, part of the auto commit custom strategy SQLAlchemy implements ? If yes, is there a way to avoid those extra rollbacks ? Because we're using a connection pool, putting the connection back into the pool without a rollback means you throw existing transactional locks and state into the pool as well, holding them open indefinitely and generally causing problems for subsequent usages of those pooled connections. However, we get a complaint about every 6 months from a mysql myisam user, who uses many ad-hoc connection checkouts (which in itself is a little unusual) and who would rather not have it (as myisam has no transactional integrity anyway). For those cases, we tell them to add rollback_on_return=False to their create_engine to turn the behavior off. But also maybe consider why you have a high volume of checkins, rather then working in some kind of transaction-per-logical-operation scheme (like a web request). Thanks Michael ! Just for the record for others, the option is reset_on_return Cheers Tarek -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] binding values for the in() statement
Hello,
I am trying to bind a list for an in() operator in a pure SQL query :
import sqlalchemy
engine = create_engine('mysql://localhost/database')
connection = engine.connect()
connection.execute(engine.text(select * from user where email in
(:emails)), emails=['ta...@ziade.org'])
This will fail because the dialect will not bind a list for the
:emails params.
Is there a way to do this ? I looked at the expression compiler but
didn't find anything relevant.
Or do I have to build the query manually with a ','.join() in this case ?
Regards,
Tarek
--
Tarek Ziadé | http://ziade.org
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] simplest pattern to query a database
Hi, I am using this pattern to execute a simple query on a database: def execute(sqluri, query): engine = create_engine(sqluri) connection = engine.connect() try: connection.execute(query) finally: connection.close() I was wondering if this was the best pattern, or if something simpler exists.. Regards Tarek -- Tarek Ziadé | http://ziade.org -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Configuring an existing session
Hi,
I use a global session instance to work with my DB, so all module
import it and use it. But at some point I sometimes need to
reconfigure the engine to use another DB. I want to reconfigure the
existing instance so other modules can still use the same session
object to work with the DB.
can I safely change the bind attribute of an existing scoped session
or is there any things to be taken care of before I do it ? (like
closing active connections in the pool maybe ?)
I've tried to use configure to reset the engine, but it doesn't work
(the bind attribute remain unchanged) :
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
Session = scoped_session(sessionmaker())
Session.configure(bind=create_engine('sqlite:///tmp/db1'))
Session.bind
Engine(sqlite:///tmp/db1)
Session.configure(bind=create_engine('sqlite:///tmp/db2'))
Session.bind
Engine(sqlite:///tmp/db1) --- same !
So, it this safe :
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine
Session = scoped_session(sessionmaker())
Session.configure(bind=create_engine('sqlite:///tmp/db1'))
Session.bind
Engine(sqlite:///tmp/db1)
Session.bind = create_engine('sqlite:///tmp/db2')
Session.bind
Engine(sqlite:///tmp/db2)
Thanks
Tarek
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
