[sqlalchemy] Re: Schema and database migration: how to diff?
On Friday 06 June 2008 02:06:23 Yannick Gingras wrote: [EMAIL PROTECTED] writes: see dbcook.misc.metadata.diff.py as an attempt to do this over 2 metadata's. svn co https://dbcook.svn.sourceforge.net/svnroot/dbcook/trunk/dbcook/mi sc/metadata It works pretty well. How about a small cleanup to make it truly general an a promotion to a package of its own? With the `changeset` parts of sqlalchemy_migration, we could generate most of the upgrade script from the computed diff. Unless you already do that and I missed that part somehow. no, this is all i have - didnt have time/urge to go further then. whatever u want to do/can do - do it. i'm digging into different cave / inventing another hotwater right now. if u want to discuss the changes, send them over. repository-wise, it can stay there - if u want write access - or it can go elsewhere... hmm. there seems to be a host of SA-based SA-enhancing things that are around... mine are bitemporal, aggregation, metadata-related stuff, some multilang-support... polymorphism-simulation a-la C/Corba-union-like ... most of these are not just recipes. maybe it is time to put them in some page of their own... have fun svilen --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: out of range / locate column / lost connection
first two mean the result of the query is empty. On Friday 06 June 2008 01:49:23 braydon fuller wrote: I am having some database problems (attached below), any recommendations to either recover from or fix these from happening? I am not sure why it would work OK at first, and then at some point run bad... Any recommendations on a technique to debug this would be much appreciated. Full source code of the project can be browsed here: http://git.braydon.com/gitweb.cgi?p=sparrow;a=tree;h=refs/heads/mas ter;hb=refs/heads/master For an idea of the project (screencast... although slightly old): http://interfce.com/videos/sparrow.html The site this is coming from (live): http://mochilla.com/ [05/Jun/2008:22:16:15] HTTP Traceback (most recent call last): File /var/lib/python-support/python2.5/cherrypy/_cprequest.py, line 550, in respond cherrypy.response.body = self.handler() File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py, line 24, in __call__ return self.callable(*self.args, **self.kwargs) File /var/local/mochilla/sparrow/http.py, line 38, in default return render(args, kwargs, location_st) File /var/local/mochilla/sparrow/templates.py, line 363, in render return render_skeleton(location, location_st, kwargs) File /var/local/mochilla/sparrow/templates.py, line 266, in render_skeleton body, cmds_array = compile_templates(location, kwargs) File /var/local/mochilla/sparrow/templates.py, line 192, in compile_templates doc = get_uri(location) File /var/local/mochilla/sparrow/uri.py, line 31, in get_uri selected = db.session.query(TemplateRelation).filter_by(location=uri)[0] File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 844, in __getitem__ return list(self[item:item+1])[0] IndexError: list index out of range 127.0.0.1 - - [05/Jun/2008:22:15:44] GET / HTTP/1.0 500 3293 [05/Jun/2008:22:15:59] HTTP Traceback (most recent call last): File /var/lib/python-support/python2.5/cherrypy/_cprequest.py, line 550, in respond cherrypy.response.body = self.handler() File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py, line 24, in __call__ return self.callable(*self.args, **self.kwargs) File /var/local/mochilla/sparrow/http.py, line 38, in default return render(args, kwargs, location_st) File /var/local/mochilla/sparrow/templates.py, line 363, in render return render_skeleton(location, location_st, kwargs) File /var/local/mochilla/sparrow/templates.py, line 266, in render_skeleton body, cmds_array = compile_templates(location, kwargs) File /var/local/mochilla/sparrow/templates.py, line 252, in compile_templates data = search_list(doc.oid, doc.objects, location=location, locations=locations, cmds_array=doc.cmds_array, kwargs=kwargs) File /var/local/mochilla/sparrow/templates.py, line 172, in search_list objects = object_children(object_oid) File /var/local/mochilla/sparrow/objects.py, line 39, in object_children parent = get_object(oid) File /var/local/mochilla/sparrow/objects.py, line 31, in get_object selected = db.session.query(Objects).filter_by(oid=oid).one() File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 927, in one raise exceptions.InvalidRequestError('No rows returned for one()') InvalidRequestError: No rows returned for one() 127.0.0.1 - - [05/Jun/2008:18:15:08] GET / HTTP/1.0 200 24400 [05/Jun/2008:18:15:09] HTTP Traceback (most recent call last): File /var/lib/python-support/python2.5/cherrypy/_cprequest.py, line 550, in respond cherrypy.response.body = self.handler() File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py, line 24, in __call__ return self.callable(*self.args, **self.kwargs) File /var/lib/python-support/python2.5/cherrypy/_cptools.py, line 140, in handle_func handled = self.callable(*args, **self._merged_args(kwargs)) TypeError: staticfile() got multiple values for keyword argument 'filename' class 'sqlalchemy.exceptions.NoSuchColumnError' [05/Jun/2008:21:42:22] HTTP Traceback (most recent call last): File /var/lib/python-support/python2.5/cherrypy/_cprequest.py, line 550, in respond cherrypy.response.body = self.handler() File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py, line 24, in __call__ return self.callable(*self.args, **self.kwargs) File /var/local/mochilla/sparrow/http.py, line 38, in default return render(args, kwargs, location_st) File /var/local/mochilla/sparrow/templates.py, line 346, in render get_uri(location_id) File /var/local/mochilla/sparrow/uri.py, line 31, in get_uri selected = db.session.query(TemplateRelation).filter_by(location=uri)[0] File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 844, in __getitem__ return list(self[item:item+1])[0] File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 986, in
[sqlalchemy] how to cancel order_by on a query?
i have a premade query with some order_by but i want to replace the order_by. any legal way to do it? q=q.order_by(a,b,c) seems to only add a,b,c to existing order_by; q=q.order_by() and q=q.order_by(None) do nothing --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Sharded query stops working with 0.4.6
After upgrading from 0.4.5 to 0.4.6 Session.execute(query, shard_id=shard_id), where Session is ShardedSession, throws exception: File '/var/lib/python-support/python2.5/sqlalchemy/orm/scoping.py', line 98 in do return getattr(self.registry(), name)(*args, **kwargs) TypeError: execute() got an unexpected keyword argument 'shard_id' --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to cancel order_by on a query?
On Jun 6, 2008, at 3:27 AM, [EMAIL PROTECTED] wrote: i have a premade query with some order_by but i want to replace the order_by. any legal way to do it? q=q.order_by(a,b,c) seems to only add a,b,c to existing order_by; q=q.order_by() and q=q.order_by(None) do nothing order_by(None) should do it. can you take a look at that for me ? (0.4 or 0.5 ?) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Sharded query stops working with 0.4.6
can you add a trac ticket and use Session.connection(shard_id=).execute() for now...thanks On Jun 6, 2008, at 6:53 AM, Andrew Stromnov wrote: After upgrading from 0.4.5 to 0.4.6 Session.execute(query, shard_id=shard_id), where Session is ShardedSession, throws exception: File '/var/lib/python-support/python2.5/sqlalchemy/orm/scoping.py', line 98 in do return getattr(self.registry(), name)(*args, **kwargs) TypeError: execute() got an unexpected keyword argument 'shard_id' --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to cancel order_by on a query?
On Friday 06 June 2008 17:09:02 Michael Bayer wrote: On Jun 6, 2008, at 3:27 AM, [EMAIL PROTECTED] wrote: i have a premade query with some order_by but i want to replace the order_by. any legal way to do it? q=q.order_by(a,b,c) seems to only add a,b,c to existing order_by; q=q.order_by() and q=q.order_by(None) do nothing order_by(None) should do it. can you take a look at that for me ? (0.4 or 0.5 ?) 0.4/0.5 behave same - they have ~same code in orm.query.py: def order_by(self, *criterion): criterion = [ self._adapt_clause( expression._literal_as_text(o), True, True) for o in criterion] if self._order_by is False: self._order_by = criterion else: self._order_by = self._order_by + criterion order_by = util.array_as_starargs_decorator( order_by) order_by = _generative( __no_statement_condition, __no_limit_offset )( order_by) this will only add columns. here a test case: from sqlalchemy import * from sqlalchemy.orm import * m = MetaData('sqlite:///') m.bind.echo=True t = Table( 't', m, Column('a', Integer), Column('b', Integer, primary_key=True) ) class T(object): pass mapper( T,t) m.create_all() s = create_session() q = s.query(T).order_by( T.a).order_by( None).order_by( T.b) print q.all() the result query is: SELECT t.a AS t_a, t.b AS t_b FROM t ORDER BY t.a, t.b adding something like this at beginning of Query.order_by() would fix it: if not criterion: self._order_by = False return now the result query is what expected: SELECT t.a AS t_a, t.b AS t_b FROM t ORDER BY t.b patches attached. ciao svilen --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- Index: orm/query.py === --- orm/query.py (revision 4836) +++ orm/query.py (working copy) @@ -570,6 +570,10 @@ q = self.__no_statement(order_by) +if not criterion: #cancel any order_by +q._order_by = False +return q + if self._aliases_tail: criterion = tuple(self._aliases_tail.adapt_list( [expression._literal_as_text(o) for o in criterion] Index: orm/query.py === --- orm/query.py (revision 4836) +++ orm/query.py (working copy) @@ -661,6 +661,10 @@ def order_by(self, *criterion): apply one or more ORDER BY criterion to the query and return the newly resulting ``Query`` +if not criterion: #cancel any order_by +self._order_by = False +return + criterion = [self._adapt_clause(expression._literal_as_text(o), True, True) for o in criterion] if self._order_by is False:
[sqlalchemy] Custom Comparator with order_by
Hi there, Quick question - is it possible to get a custom comparator to work with order_by? At present my code based on SA/examples/vertical/dictlike- polymorphic.py raises a NotImplementedError when attempting to use the value in an order_by statement. ta, Martin --- q = (session.query(Animal). filter(Animal.facts.any(AnimalFact.value == 5)).order_by(AnimalFact.value)) print 'any animal with a .value of 5', q.all() raise NotImplementedError(%r % self) NotImplementedError: __main__.Comparator object at 0x12710b0 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Create polymorphic mapped instance from type discriminator
Is there an API-stable way to create a polymorphic instance from only the type discriminator key? I've got a case where I need to create a mapped instance from some JSON data that contains the type discriminator, but I'd rather get the (key -- mapped class) from the sqla map and not maintain my own, which may fall out of sync as the data model changes over time. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Orphan error
With the following simplified setup I receive the following error: FlushError: instance erp.model.financial.TransactionOffset object at 0x0203CC90 is an unsaved, pending instance and is an orphan (is not attached to any parent 'Transaction' instance via that classes' 'offsets' attribute) When attempting something along the lines of transactions = Transaction.query.all() offset = TransactionOffset() offset.transaction = transactions[4] offset.offset_transaction = transactions[5] meta.Session.commit() However, if I append offset to transactions[4].offsets it will save transaction_id, but not offset_transaction_id Classes: class Transaction(Entity): pass class TransactionOffset(Entity): pass Tables: transactions = Table('transactions', meta, Column('id', Integer, primary_key=True), Column('date', Date), Column('description', Text), Column('amount', Float), Column('transaction_code', String(50)), Column('approval_code', String(50)), Column('voided_at', DateTime), ) transaction_offsets = Table('transaction_offsets', meta, Column('id', Integer, primary_key=True), Column('amount', Float), Column('transaction_id', Integer), Column('offset_transaction_id', Integer), ForeignKeyConstraint(['transaction_id'], ['transactions.id']), ForeignKeyConstraint(['offset_transaction_id'], ['transactions.id']), ) Mappers: transactions_mapper = mapper(Transaction, transactions, properties={ 'offsets': relation(TransactionOffset, primaryjoin=transaction_offsets.c.transaction_id==transactions.c.id, cascade='all, delete- orphan'), }) mapper(TransactionOffset, transaction_offsets, properties={ 'transaction': relation(Transaction, primaryjoin=transaction_offsets.c.transaction_id==transactions.c.id, post_update=True), 'offset_transaction': relation(Transaction, primaryjoin=transaction_offsets.c.offset_transaction_id==transactions.c.id, post_update=True), }) -brad --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Optimizing a slow query
Hi, I merged the two tables into a single one, changed the mapping but the query still needs more than 10 seconds :/ That's how EXPLAIN looks like now: http://paste.pocoo.org/show/63542/ I think the problem is this one: | 2 | DERIVED | forum_post | ALL| forum_post_topic_id | forum_post_topic_id | 4 | NULL| 198398 | Using filesort | do you have any idea, why mysql is using filesort for this and how I can get mysql not to search through 198398 rows? These are the indexes of forum_post: http://paste.pocoo.org/show/63543/ Benjamin Wiegand On 5 Jun., 20:33, Michael Bayer [EMAIL PROTECTED] wrote: for starters I'd combine post_table and post_text_table into onenot much is accomplished there by having two tables. Also make sure forum_post.topic_id is indexed. On Jun 5, 2008, at 1:37 PM, beewee wrote: Hi, we're writing a bulleting board using sqlalchemy at the moment, but we have the problem, that the database query for viewing a topic is quite slow for big topics. These are the relevant table definitions and mappings: http://paste.pocoo.org/show/62703/ This is the query that is slow:http://paste.pocoo.org/show/62706/ This is what EXPLAIN says:http://paste.pocoo.org/show/62708/ Executing this query needs up to 25 seconds on our test server that's just idling. On our productive servers (which aren't idling, of course) phpbb is able to execute an adequate query much faster. May you can help us speeding up this query? I don't know what to improve, since all parts of the query already use a key (except the derived one), but unfortunately I have a quite small knowledge of improving database queries. Thank you very much, Benjamin Wiegand --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Create polymorphic mapped instance from type discriminator
The mapper for the base class has a property called polymorphic_map which is a dictionary whose keys are the discriminator values and whose values are the mappers of the associated classes (you can get from there to the actual class via the mapper's class_ property). I can't speak to whether this technique is API stable. On Fri, Jun 6, 2008 at 11:43 AM, Rick Morrison [EMAIL PROTECTED] wrote: Is there an API-stable way to create a polymorphic instance from only the type discriminator key? I've got a case where I need to create a mapped instance from some JSON data that contains the type discriminator, but I'd rather get the (key -- mapped class) from the sqla map and not maintain my own, which may fall out of sync as the data model changes over time. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Orphan error
offsets and transaction need to be combined into one relation with a backref() so that an attribute set on one side generates a collection add on the other. Backrefs are described in the ORM tutorial and mapper configuration docs, to send arguments to a backref use the backref() function (examples are also within those docs). On Jun 6, 2008, at 12:08 PM, Brad Wells wrote: With the following simplified setup I receive the following error: FlushError: instance erp.model.financial.TransactionOffset object at 0x0203CC90 is an unsaved, pending instance and is an orphan (is not attached to any parent 'Transaction' instance via that classes' 'offsets' attribute) When attempting something along the lines of transactions = Transaction.query.all() offset = TransactionOffset() offset.transaction = transactions[4] offset.offset_transaction = transactions[5] meta.Session.commit() However, if I append offset to transactions[4].offsets it will save transaction_id, but not offset_transaction_id Classes: class Transaction(Entity): pass class TransactionOffset(Entity): pass Tables: transactions = Table('transactions', meta, Column('id', Integer, primary_key=True), Column('date', Date), Column('description', Text), Column('amount', Float), Column('transaction_code', String(50)), Column('approval_code', String(50)), Column('voided_at', DateTime), ) transaction_offsets = Table('transaction_offsets', meta, Column('id', Integer, primary_key=True), Column('amount', Float), Column('transaction_id', Integer), Column('offset_transaction_id', Integer), ForeignKeyConstraint(['transaction_id'], ['transactions.id']), ForeignKeyConstraint(['offset_transaction_id'], ['transactions.id']), ) Mappers: transactions_mapper = mapper(Transaction, transactions, properties={ 'offsets': relation(TransactionOffset, primaryjoin=transaction_offsets.c.transaction_id==transactions.c.id, cascade='all, delete- orphan'), }) mapper(TransactionOffset, transaction_offsets, properties={ 'transaction': relation(Transaction, primaryjoin=transaction_offsets.c.transaction_id==transactions.c.id, post_update=True), 'offset_transaction': relation(Transaction, primaryjoin =transaction_offsets.c.offset_transaction_id==transactions.c.id, post_update=True), }) -brad --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] reading from one database and writing to another
We have (what I think of as) a moderately complicated database configuration and I'm hoping there will be a way to configure sqlalchemy to deal with it. The basic scenario is like this: There are N mysql servers in different geographical regions that are all replicating against one master. In the interest of speed the rule in each location is to do reads which are very frequent against the local copy of the database and if there is a write to do that against the master. As an added wrinkle the user has an option to write to the master with a master_pos_wait so that the current process will wait until replication has caught up with the update just executed. Hopefully that makes sense and gives enough of a flavor of what I've got in mind. I'm pretty new to sqlalchemy. Is the above feasible? If so are there examples to compare with and learn from doing something similar? Where (api/code) would I start looking to accomplish the above? Any tips to get me going would be much appreciated. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: reading from one database and writing to another
On Jun 6, 2008, at 2:29 PM, qhfgva wrote: We have (what I think of as) a moderately complicated database configuration and I'm hoping there will be a way to configure sqlalchemy to deal with it. The basic scenario is like this: There are N mysql servers in different geographical regions that are all replicating against one master. In the interest of speed the rule in each location is to do reads which are very frequent against the local copy of the database and if there is a write to do that against the master. As an added wrinkle the user has an option to write to the master with a master_pos_wait so that the current process will wait until replication has caught up with the update just executed. Hopefully that makes sense and gives enough of a flavor of what I've got in mind. I'm pretty new to sqlalchemy. Is the above feasible? If so are there examples to compare with and learn from doing something similar? Where (api/code) would I start looking to accomplish the above? Any tips to get me going would be much appreciated. easiest approach is to use multiple sessions, multiple engines. Your app would need to know which engine it wants to talk to, and binds a session to that engine. Binding is described here: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_getting_binding --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Simple example of Session usage in multi threaded context
Hello, I'm trying to use SQLAlchemy for my Python project which is a multi- threaded application. While I've had good success in performing CRUD operations with a single thread, I'm having a lot of trouble when I start using multiple threads. Can anybody give a simple example of using the Session in a multi threaded context? I'm looking for a simple example like this: class User(object): - this is the User class which has the users_table class ThreadOne - open a thread local session - perform operations on user objects - save the user object class ThreadTwo - open another thread local session - connect to the same db, and get user objects - modify the user objects - save this to the db Please help. thanks, Venkatesh. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] PostgreSQL and inserting
Just an FYI, in PostgreSQL 8.2.4 they added a new feature returning which can be used to avoid the need for an additional query (to get the value of serial columns) when inserting records: test=# create table foo ( id serial primary key not null, name text ); test=# insert into foo ( name ) values ( 'test 1' ) returning id; id 1 (1 row) test=# insert into foo ( name ) values ( 'test 2' ) returning *; id | name +- 2 | test 2 (1 row) Thought it would be worth mentioning. Regards, Cliff --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: PostgreSQL and inserting
On Jun 6, 2008, at 9:11 PM, Cliff Wells wrote: Just an FYI, in PostgreSQL 8.2.4 they added a new feature returning which can be used to avoid the need for an additional query (to get the value of serial columns) when inserting records: test=# create table foo ( id serial primary key not null, name text ); test=# insert into foo ( name ) values ( 'test 1' ) returning id; id 1 (1 row) test=# insert into foo ( name ) values ( 'test 2' ) returning *; id | name +- 2 | test 2 (1 row) Thought it would be worth mentioning. the PG dialect supports RETURNING; I think its the pg_returning keyword argument to insert(). Still remaining to do is to modify the PG dialect such that this is used automatically when available for the primary key columns, or perhaps in combination with a dialect agnostic hint, so that the ORM makes usage of it implicitly. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---