Re: [sqlalchemy] Entity name - multiple schema - relationship
Thank you very much for your advice. Le mardi 6 mai 2014 17:21:55 UTC+2, Michael Bayer a écrit : set_shard is a special method added by the horizontal sharding extension. you can do cross schema queries if you organize the schema names in terms of which ones apply to the “dynamic” shard and which ones to the “fixed” shard, if that’s how it works. If OTOH you literally need to join against multiple, dynamically named shards at one time, then you need to spell those out explicitly.it gets more ugly but if you want a Table that is on the fly linked to a certain schema explicitly you can use table.tometadata(), see http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html?highlight=tometadata#sqlalchemy.schema.Table.tometadata . On May 6, 2014, at 3:06 AM, Julien Meyer julien...@gmail.comjavascript: wrote: My real database schema is a little more complex. In reality, I have one database by company. In each database, I have multiple schemas who contain the same table structure. The solution schema name execution will not work in the case when I need to access to more than one schema by request. The Horizontal sharding can work : one engine by schema and set the search path when creating the engine. During the request processing, I can identify wich schema to use and with the use of set_shard on the Query object (not found in the documentation, normal ?), I can easely select the good shard to use. But I don't know how I can make a cross schema query in this case? Le lundi 5 mai 2014 19:12:06 UTC+2, Michael Bayer a écrit : part of a feature that will make this kind of thing more direct is the “schema name execution argument” feature, which is https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-as-an-execution-argument . This application is somewhat of a “multi-tenancy” application; technically its horizontally partitioned but if you know “society” up front and for the duration of an operation, you can just set that and be done with it. Assuming this is the case an easy way to do this for now is just to set the “search path” on your postgresql connection before such an operation proceeds. That way when you refer to table X or Y, it will be in terms of whatever search path you’ve set, see 5.7.3 at http://www.postgresql.org/docs/8.1/static/ddl-schemas.html. There’s no need in that case to use any kind of explicit “horizontal sharding”.Only if you need queries that are going to refer to multiple schemas at once does the HS feature come into play (and if that were the case I’d look into PG table inheritance). On May 5, 2014, at 8:41 AM, Julien Meyer julien...@gmail.com wrote: I need some help and advices to create a mapping. The context : - Multiple schemas on postgresql (dynamic number and name) who store the same tables. - SQLAlchemy used into a pyramid web application. Example : A table Customer and a table CustomerOrder (link by customer.id) and a schema by society (not know before running) I read the documentation about horizontal, vertical sharding and entity name but I'm a little bit confused about the good solution to solve my problem. If I use Entity name, I don't know how to configure the relationship between my two dynamic classes because I need to specify a class at configuration time but i really know the real subclasses only at runtime. If I use the Horizontal sharding, I need to have an engine / schema (and use search_path). The shard configurtion will be (or seems to be) tricky. If I use the Vertical sharding, I need also an engine / schema and re-configure the session several times with a new binds mapping. I made some google search with my context but it's not an usual case and i didn't find some helpful posts I also posed the question on stackoverflow last year but my solution don't really work : http://stackoverflow.com/questions/20212165/one-entity-in-multiple-schemas-how-to-switch-schema-on-runtime Thanks in advance. -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . 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] Nested select from insert into select union select
I don't quite understand why SQLA generates this query. For some reason it wraps the union part into a separate select. How can I avoid this? b_id = 2 s_id = 3 id = product.c.id sel = select( [b_id, product.c.id], ).union( select([b_id, s_id]) ) ins = insert(product).from_select([ product.c.id, product.c.other_id ], sel ) print ins # produces: # INSERT INTO product (id, other_id) SELECT 2, id # FROM (SELECT 2, product.id AS id # FROM product UNION SELECT 2, 3) # I would expect: # INSERT INTO product (id, other_id) FROM # SELECT 2, product.id AS id FROM product # UNION # SELECT 2, 3 Where is the additional `SELECT 2, id` coming from? -- 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] Threading Queries
A couple of questions: I'm writing an application using concurrent.futures (by process). The processes themselves are fairly involved - not simple functions. I'm using scoped_sessions and a context manager like so: # db.py engine = create_engine(sqlalchemy_url) Session = scoped_session(sessionmaker(bind=engine)) @contextmanager def db_session(): session = Session() try: yield session session.commit() except: session.rollback() raise finally: session.remove() Using this context manager and something like the below code: def process(): with db_session() as db: # the function is obviously more involved than this u = User(name='bob') db.add(u) return u def main(): with db_session() as db: g = Group(name='peeps') user = process() user.group = g # this line breaks db.add(g) I'm guessing this is because the call to db_session() is nested inside another, meaning that the thread-local session is being closed inside process(), and so when it gets passed back to main() the session object is gone. Is there a recommended way to handle this? Along similar lines, the application (using the session/engine creation as above) also has to use raw_connection() at a few points to access the copy_expert() cursor function from psycopg2. I'm getting very strange errors coming out of the copy functions - I suspect due to multiple copies occurring at once (there's ~4 processes running at once, but rarely copying at the same time). The copy code looks like this: from db import engine conn = engine.raw_connection() cur = conn.cursor() cur.copy_expert(COPY parts ({}) FROM STDIN WITH CSV ESCAPE E''.format(', '.join(ordering)), s) conn.commit() Does raw_connection() still pull from a connection pool, or could two calls to it at once potentially destroy things? Some of the errors are below (the data going in is clean, I've manually checked it). Thanks! --- Traceback (most recent call last): File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py, line 940, in _execute_context context) File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/default.py, line 435, in do_execute cursor.execute(statement, parameters) psycopg2.DatabaseError: insufficient data in D message lost synchronization with server: got message type 5, length 808464640 ... sqlalchemy.exc.DatabaseError: (DatabaseError) insufficient data in D message lost synchronization with server: got message type 5, length 808464640 ... psycopg2.InterfaceError: connection already closed The above exception was the direct cause of the following exception: Traceback (most recent call last): File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py, line 508, in _rollback_impl self._handle_dbapi_exception(e, None, None, None, None) File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py, line 1108, in _handle_dbapi_exception exc_info File /usr/local/lib/python3.3/dist-packages/sqlalchemy/util/compat.py, line 174, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=exc_value) File /usr/local/lib/python3.3/dist-packages/sqlalchemy/util/compat.py, line 167, in reraise raise value.with_traceback(tb) File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py, line 506, in _rollback_impl self.engine.dialect.do_rollback(self.connection) File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/default.py, line 405, in do_rollback dbapi_connection.rollback() sqlalchemy.exc.InterfaceError: (InterfaceError) connection already closed None None During handling of the above exception, another exception occurred: Traceback (most recent call last): File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py, line 233, in connection return self.__connection AttributeError: 'Connection' object has no attribute '_Connection__connection' ... Traceback (most recent call last): File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py, line 940, in _execute_context context) File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/default.py, line 435, in do_execute cursor.execute(statement, parameters) psycopg2.DatabaseError: lost synchronization with server: got message type ... Traceback (most recent call last): File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py, line 506, in _rollback_impl self.engine.dialect.do_rollback(self.connection) File /usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/default.py, line 405, in do_rollback dbapi_connection.rollback() psycopg2.InterfaceError: connection already closed ... psycopg2.DatabaseError: error with no message from the libpq -- 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] Triggers with sqlite
For a trigger template something like: trg_template = CREATE TRIGGER trg_foo_{0} AFTER {0} ON foo FOR EACH ROW BEGIN ... END; Why does the following not work to remove some redundant boiler plate code: for x in 'UPDATE', 'INSERT', 'DELETE': event.listen( Foo.__table__, after_create, lambda *args, **kwargs: DDL(trg_template.format(x)).execute(bind=engine) ) Metadata create_all sees multiple definitions of the same trigger? Thanks -- 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] Triggers with sqlite
On Thu, May 8, 2014 at 5:37 PM, Joseph Casale jcas...@gmail.com wrote: For a trigger template something like: trg_template = CREATE TRIGGER trg_foo_{0} AFTER {0} ON foo FOR EACH ROW BEGIN ... END; Why does the following not work to remove some redundant boiler plate code: for x in 'UPDATE', 'INSERT', 'DELETE': event.listen( Foo.__table__, after_create, lambda *args, **kwargs: DDL(trg_template.format(x)).execute(bind=engine) ) Metadata create_all sees multiple definitions of the same trigger? You've been bitten by a Python gotcha! http://docs.python-guide.org/en/latest/writing/gotchas/#late-binding-closures Although your loop is setting up 3 distinct listener functions, they all contain a reference to the x variable which isn't evaluated until the function is called, long after your loop has finished. Each function will see the value of x at the time the function is *called*, which is probably the last value from the loop in this case. The easiest fix is probably something like this: def make_handler(operation, engine): trigger = trg_template.format(operation) return lambda *args, **kwargs: DDL(trigger).execute(bind=engine) for x in 'UPDATE', 'INSERT', 'DELETE': event.listen(Foo.__table__, after_create, make_handler(x, engine)) Hope that helps, 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Triggers with sqlite
You've been bitten by a Python gotcha! http://docs.python-guide.org/en/latest/writing/gotchas/#late-binding-closures Ugh, thanks Simon, moment of careless haste in thinking about this. -- 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] Improving a subquery
I have scenario where I have a TableA that contains rows with with an optional reference to TableB. That foreign key column in TableA has triggers and constraints enforcing either one null or many with unique references. When I query for rows I need to select either rows with a reference that applies to specific row (there may be many rows with different references, but only one applies) or choose the null row. I use: session.query(TableA).\ filter( (TableA.col_fk_id == session.query(TableB.id).filter(TableB.col == 1)) | (TableA.col_fk_id == null()) ) This grabs the right row in TableB to filter TableA on but I'll admit not being sufficiently fluent in SQLAlchemy so I am sure their is a simpler way to do this? Any guidance would be appreciated. Thanks! -- 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] Improving a subquery
this kind of thing is achieved with an OUTER JOIN. you can put everything to do with TableB and the join into the ON clause: session.query(TableA).outerjoin(TableB, and_(TableA.col_fk_id == TableB.col, TableB.col == 1)) On May 8, 2014, at 2:32 PM, Joseph Casale jcas...@gmail.com wrote: I have scenario where I have a TableA that contains rows with with an optional reference to TableB. That foreign key column in TableA has triggers and constraints enforcing either one null or many with unique references. When I query for rows I need to select either rows with a reference that applies to specific row (there may be many rows with different references, but only one applies) or choose the null row. I use: session.query(TableA).\ filter( (TableA.col_fk_id == session.query(TableB.id).filter(TableB.col == 1)) | (TableA.col_fk_id == null()) ) This grabs the right row in TableB to filter TableA on but I'll admit not being sufficiently fluent in SQLAlchemy so I am sure their is a simpler way to do this? Any guidance would be appreciated. Thanks! -- 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] Nested select from insert into select union select
this can't be avoided right now as the insert from select feature checks the incoming object as a Select, which a UNION is not; it then calls select() on that union. a lot of databases have trouble with a raw UNION like that, we can loosen this restriction to apply to union-orinented selects as well but it's not clear if some backends might have problems with it. On May 8, 2014, at 4:28 AM, gbr doubl...@directbox.com wrote: I don't quite understand why SQLA generates this query. For some reason it wraps the union part into a separate select. How can I avoid this? b_id = 2 s_id = 3 id = product.c.id sel = select( [b_id, product.c.id], ).union( select([b_id, s_id]) ) ins = insert(product).from_select([ product.c.id, product.c.other_id ], sel ) print ins # produces: # INSERT INTO product (id, other_id) SELECT 2, id # FROM (SELECT 2, product.id AS id # FROM product UNION SELECT 2, 3) # I would expect: # INSERT INTO product (id, other_id) FROM # SELECT 2, product.id AS id FROM product # UNION # SELECT 2, 3 Where is the additional `SELECT 2, id` coming from? -- 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] Threading Queries
On May 8, 2014, at 5:06 AM, James Meneghello murod...@gmail.com wrote: A couple of questions: I'm writing an application using concurrent.futures (by process). The processes themselves are fairly involved - not simple functions. I'm using scoped_sessions and a context manager like so: # db.py engine = create_engine(sqlalchemy_url) Session = scoped_session(sessionmaker(bind=engine)) @contextmanager def db_session(): session = Session() try: yield session session.commit() except: session.rollback() raise finally: session.remove() Using this context manager and something like the below code: def process(): with db_session() as db: # the function is obviously more involved than this u = User(name='bob') db.add(u) return u def main(): with db_session() as db: g = Group(name='peeps') user = process() user.group = g # this line breaks db.add(g) I'm guessing this is because the call to db_session() is nested inside another, meaning that the thread-local session is being closed inside process(), and so when it gets passed back to main() the session object is gone. Is there a recommended way to handle this? if you want to have open-ended nesting of this style, you need to take out that session.remove step as the inner call will be blowing away your Session. Also, to nest Sessions like that, you usually want to have Session(autocommit=True), then use begin()/commit() pairs sending the subtransactions=True flag to Session.begin() which allows the nesting behavior (this is an advanced behavior so the flag is there as a check that this is definitely what the user is intending). As far as recommended, I'd construct the application to not have ad-hoc session blocks like that, there'd be a single/few well known points where session scopes begin and end. As it stands, you never know where/when your app decides to start / end transactions. Along similar lines, the application (using the session/engine creation as above) also has to use raw_connection() at a few points to access the copy_expert() cursor function from psycopg2. I'm getting very strange errors coming out of the copy functions - I suspect due to multiple copies occurring at once (there's ~4 processes running at once, but rarely copying at the same time). The copy code looks like this: from db import engine conn = engine.raw_connection() cur = conn.cursor() cur.copy_expert(COPY parts ({}) FROM STDIN WITH CSV ESCAPE E''.format(', '.join(ordering)), s) conn.commit() Does raw_connection() still pull from a connection pool, or could two calls to it at once potentially destroy things? it uses the connection pool but that says nothing about an application that uses multiple processes. Each subprocess has its own connections and its own pool. If you are spawning a subprocess, you need to ensure that any DBAPI connection is created in that subprocess and not in the parent as they won't travel over subprocess boundaries. When using an Engine the usual way to get this is to make sure create_engine() is called local to the subprocess, and that's the engine used within the process. Or use NullPool. -- 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] Nested select from insert into select union select
So what can I do? I'm using postgres 9.3 The error message I get is: ProgrammingError: (ProgrammingError) subquery in FROM must have an alias LINE 2: FROM (SELECT 2, product_id ^ HINT: For example, FROM (SELECT ...) [AS] foo. Adding `sel = sel.alias()` doesn't do anything (same error message). On Friday, May 9, 2014 5:28:32 AM UTC+10, Michael Bayer wrote: this can’t be avoided right now as the insert from select feature checks the incoming object as a “Select”, which a “UNION” is not; it then calls select() on that union. a lot of databases have trouble with a raw UNION like that, we can loosen this restriction to apply to union-orinented selects as well but it’s not clear if some backends might have problems with it. On May 8, 2014, at 4:28 AM, gbr doub...@directbox.com javascript: wrote: I don't quite understand why SQLA generates this query. For some reason it wraps the union part into a separate select. How can I avoid this? b_id = 2 s_id = 3 id = product.c.id sel = select( [b_id, product.c.id], ).union( select([b_id, s_id]) ) ins = insert(product).from_select([ product.c.id, product.c.other_id ], sel ) print ins # produces: # INSERT INTO product (id, other_id) SELECT 2, id # FROM (SELECT 2, product.id AS id # FROM product UNION SELECT 2, 3) # I would expect: # INSERT INTO product (id, other_id) FROM # SELECT 2, product.id AS id FROM product # UNION # SELECT 2, 3 Where is the additional `SELECT 2, id` coming from? -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . 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] Nested select from insert into select union select
OK well https://bitbucket.org/zzzeek/sqlalchemy/issue/3044/insert-from-select-union has a patch, that's what we'll be doing. a workaround might be to monkeypatch select() for now: sel = select( [b_id, product.c.id], ).union( select([b_id, s_id]) ) sel.select = lambda : sel On May 8, 2014, at 6:27 PM, gbr doubl...@directbox.com wrote: So what can I do? I'm using postgres 9.3 The error message I get is: ProgrammingError: (ProgrammingError) subquery in FROM must have an alias LINE 2: FROM (SELECT 2, product_id ^ HINT: For example, FROM (SELECT ...) [AS] foo. Adding `sel = sel.alias()` doesn't do anything (same error message). On Friday, May 9, 2014 5:28:32 AM UTC+10, Michael Bayer wrote: this can't be avoided right now as the insert from select feature checks the incoming object as a Select, which a UNION is not; it then calls select() on that union. a lot of databases have trouble with a raw UNION like that, we can loosen this restriction to apply to union-orinented selects as well but it's not clear if some backends might have problems with it. On May 8, 2014, at 4:28 AM, gbr doub...@directbox.com wrote: I don't quite understand why SQLA generates this query. For some reason it wraps the union part into a separate select. How can I avoid this? b_id = 2 s_id = 3 id = product.c.id sel = select( [b_id, product.c.id], ).union( select([b_id, s_id]) ) ins = insert(product).from_select([ product.c.id, product.c.other_id ], sel ) print ins # produces: # INSERT INTO product (id, other_id) SELECT 2, id # FROM (SELECT 2, product.id AS id # FROM product UNION SELECT 2, 3) # I would expect: # INSERT INTO product (id, other_id) FROM # SELECT 2, product.id AS id FROM product # UNION # SELECT 2, 3 Where is the additional `SELECT 2, id` coming from? -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@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. -- 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.