[sqlalchemy] Re: INSERT…RETURNING being issued wro ngly
Figured it out. I had two self-referential foreign keys on the table, both pointing (obviously) to the primary key, but for some reason I was specifying foreign_keys to the relations, I just removed these arguments to the relations and this has resolved itself. On Aug 19, 12:28 pm, Oliver Beattie oli...@obeattie.com wrote: Still not quite sure how this was going wrong exactly (I was setting the attribute on the new object but it wasn't being passed along to the values dictionary), but I've sorted it nonetheless. Thanks for trying to help with my (extremely vague) request anyway. On Aug 19, 12:07 am, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 18, 2010, at 12:29 PM, Oliver Beattie wrote: On Aug 18, 3:39 pm, Michael Bayer mike...@zzzcomputing.com wrote: Sent from my iPhone On Aug 18, 2010, at 8:27 AM, Oliver Beattie oli...@obeattie.com wrote: I'm not entirely sure why this is happening… it seems to work for me in nearly all other circumstances so I'm a bit stumped. Basically, I have a declarative table which has a character field as its primary key (it's not an ID which can be returned by the server), yet SQLAlchemy is issuing an INSERT…RETURNING statement for it. The scenario that seems to make it happening is like this. I'm trying to create a copy of an existing object, with a new ID. All the attributes on the new object should be pulled from the old object (which I'm doing through use of the iterate_properties iterator along with setattr, getarre calls), apart from this primary key field (name) which is a character field and will be something different. A relation on the old object will be updated to point at the created object. When commit() happens, I get an IntegrityError because it's thinking the db will return it the primary key (even though I have explicitly set it on the non-persistant instance). Is this a know bug or something I'm doing wrong? If the primary key is a character field, you have to ensure the attribute is populated before the insert occurs. Otherwise sqlalchemy assumes the value is created by a generator of some kind such as a column default and issues RETURNING, which is perfectly valid for any type of column, to get the value back. Thanks for your reply, Michael — the value is being populated (as in, I am setting the value on the instance before I do session.add()) — is this what you mean? yup. if the correct attribute is populated it will be present in the VALUES clause of the INSERT. check your SQL logs to ensure this is the case. -- 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 athttp://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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://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 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] Re: INSERT…RETURNING being issued wro ngly
Still not quite sure how this was going wrong exactly (I was setting the attribute on the new object but it wasn't being passed along to the values dictionary), but I've sorted it nonetheless. Thanks for trying to help with my (extremely vague) request anyway. On Aug 19, 12:07 am, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 18, 2010, at 12:29 PM, Oliver Beattie wrote: On Aug 18, 3:39 pm, Michael Bayer mike...@zzzcomputing.com wrote: Sent from my iPhone On Aug 18, 2010, at 8:27 AM, Oliver Beattie oli...@obeattie.com wrote: I'm not entirely sure why this is happening… it seems to work for me in nearly all other circumstances so I'm a bit stumped. Basically, I have a declarative table which has a character field as its primary key (it's not an ID which can be returned by the server), yet SQLAlchemy is issuing an INSERT…RETURNING statement for it. The scenario that seems to make it happening is like this. I'm trying to create a copy of an existing object, with a new ID. All the attributes on the new object should be pulled from the old object (which I'm doing through use of the iterate_properties iterator along with setattr, getarre calls), apart from this primary key field (name) which is a character field and will be something different. A relation on the old object will be updated to point at the created object. When commit() happens, I get an IntegrityError because it's thinking the db will return it the primary key (even though I have explicitly set it on the non-persistant instance). Is this a know bug or something I'm doing wrong? If the primary key is a character field, you have to ensure the attribute is populated before the insert occurs. Otherwise sqlalchemy assumes the value is created by a generator of some kind such as a column default and issues RETURNING, which is perfectly valid for any type of column, to get the value back. Thanks for your reply, Michael — the value is being populated (as in, I am setting the value on the instance before I do session.add()) — is this what you mean? yup. if the correct attribute is populated it will be present in the VALUES clause of the INSERT. check your SQL logs to ensure this is the case. -- 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 athttp://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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://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 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] INSERT…RETURNING being issued wrongly
I'm not entirely sure why this is happening… it seems to work for me in nearly all other circumstances so I'm a bit stumped. Basically, I have a declarative table which has a character field as its primary key (it's not an ID which can be returned by the server), yet SQLAlchemy is issuing an INSERT…RETURNING statement for it. The scenario that seems to make it happening is like this. I'm trying to create a copy of an existing object, with a new ID. All the attributes on the new object should be pulled from the old object (which I'm doing through use of the iterate_properties iterator along with setattr, getarre calls), apart from this primary key field (name) which is a character field and will be something different. A relation on the old object will be updated to point at the created object. When commit() happens, I get an IntegrityError because it's thinking the db will return it the primary key (even though I have explicitly set it on the non-persistant instance). Is this a know bug or something I'm doing wrong? -- 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] Re: INSERT…RETURNING being issued wro ngly
I'm using Postgres On Aug 18, 1:27 pm, Oliver Beattie oli...@obeattie.com wrote: I'm not entirely sure why this is happening… it seems to work for me in nearly all other circumstances so I'm a bit stumped. Basically, I have a declarative table which has a character field as its primary key (it's not an ID which can be returned by the server), yet SQLAlchemy is issuing an INSERT…RETURNING statement for it. The scenario that seems to make it happening is like this. I'm trying to create a copy of an existing object, with a new ID. All the attributes on the new object should be pulled from the old object (which I'm doing through use of the iterate_properties iterator along with setattr, getarre calls), apart from this primary key field (name) which is a character field and will be something different. A relation on the old object will be updated to point at the created object. When commit() happens, I get an IntegrityError because it's thinking the db will return it the primary key (even though I have explicitly set it on the non-persistant instance). Is this a know bug or something I'm doing wrong? -- 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] Re: INSERT…RETURNING being issued wro ngly
On Aug 18, 3:39 pm, Michael Bayer mike...@zzzcomputing.com wrote: Sent from my iPhone On Aug 18, 2010, at 8:27 AM, Oliver Beattie oli...@obeattie.com wrote: I'm not entirely sure why this is happening… it seems to work for me in nearly all other circumstances so I'm a bit stumped. Basically, I have a declarative table which has a character field as its primary key (it's not an ID which can be returned by the server), yet SQLAlchemy is issuing an INSERT…RETURNING statement for it. The scenario that seems to make it happening is like this. I'm trying to create a copy of an existing object, with a new ID. All the attributes on the new object should be pulled from the old object (which I'm doing through use of the iterate_properties iterator along with setattr, getarre calls), apart from this primary key field (name) which is a character field and will be something different. A relation on the old object will be updated to point at the created object. When commit() happens, I get an IntegrityError because it's thinking the db will return it the primary key (even though I have explicitly set it on the non-persistant instance). Is this a know bug or something I'm doing wrong? If the primary key is a character field, you have to ensure the attribute is populated before the insert occurs. Otherwise sqlalchemy assumes the value is created by a generator of some kind such as a column default and issues RETURNING, which is perfectly valid for any type of column, to get the value back. Thanks for your reply, Michael — the value is being populated (as in, I am setting the value on the instance before I do session.add()) — is this what you mean? -- 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 athttp://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 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] Comparable properties
Hi, I am just wondering if it is possible to allow a declarative object to have some of its properties comparable as if they were ClauseElements. I know I'm not explaining myself terrifically well here, but consider the following property: @property def is_visible(self): return (self.enabled and not self.is_deleted) This can clearly be mapped quite easily to SQL expression `Klass.enabled == True Klass.is_deleted == False` Is this something that is possible? I know that it is possible to use a custom PropComparator to make these queryable, but for things that be so directly represented by an expression, is there a way to make that available? -- 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] Passing multiple keys to undefer()
Hi there, The documentation for the undefer() method seems to indicate that it should be able to accept multiple positional arguments for keys, however trying this results in an error. I wanted to post this here to check I'm right in thinking this before I file a ticket. The docs give the signature as `undefer(*keys)`, but the description refers to key as singular, so I'm not sure. In any case, doing something like this results in an error: query.options(undefer(*columns)) results in an error, while this obviously does not: query.options(*[undefer(c) for c in columns]) So it's not a big deal. The stack trace I get is here: http://dpaste.org/Dpgi/ -- 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] Abridged summary of sqlalchemy@googlegroups.com - 14 Messages in 7 Topics
Aa Sent from my iPhone On Jun 14, 2010, at 12:39 AM, sqlalchemy+nore...@googlegroups.com sqlalchemy+nore...@googlegroups.com wrote: Today's Topic Summary Group: http://groups.google.com/group/sqlalchemy/topics - Multiple databases or multiple schemas? #group_thread_0 [2 Updates] - Error trying to use session.execute #group_thread_1 [4 Updates] - Windows and Linux Tips #group_thread_2 [1 Update] - Questions about session #group_thread_3 [1 Update] - Where can I learn more #group_thread_4 [2 Updates] - Using SQL aggregate fcns on a PGArray column #group_thread_5 [2 Updates] - Add a unit to column #group_thread_6 [2 Updates] Topic: Multiple databases or multiple schemas?http://groups.google.com/group/sqlalchemy/t/c93e1ab26c4ae5a6 Henry Precheur he...@precheur.org Jun 13 01:27PM -0700 ^ #digest_top I'm starting a new project which is roughly hosting a bunch of sites. I want the sites to be isolated, they'll all have the same schema (data definition), but will store their data on different more...http://groups.google.com/group/sqlalchemy/msg/92246cf43927e2b8 Faheem Mitha fah...@email.unc.edu Jun 14 02:53AM +0530 ^ #digest_top [This message has also been posted.] Solution #2 is not supported natively by SQLAlchemy. Each time a request comes-in I'll have to issue an additional query SET search_path TO more... http://groups.google.com/group/sqlalchemy/msg/839cc05d071b70cd Topic: Error trying to use session.executehttp://groups.google.com/group/sqlalchemy/t/3fd8f8ef8e0a546e Michael Bayer mike...@zzzcomputing.com Jun 12 07:58PM -0400 ^#digest_top this pattern: try: foo() except: pass will get you killed every time. the difference in result is between the session's default of transactionalized execution and the Connection's more...http://groups.google.com/group/sqlalchemy/msg/df5de254e3d22d43 Faheem Mitha fah...@email.unc.edu Jun 13 01:33PM +0530 ^ #digest_top [This message has also been posted.] On Sat, 12 Jun 2010 19:58:28 -0400, Michael Bayer the difference in result is between the session's default of transactionalized execution and the more...http://groups.google.com/group/sqlalchemy/msg/c6f9b0eac14d3212 Faheem Mitha fah...@email.unc.edu Jun 13 12:51PM ^ #digest_top I remembered that text() has the autocommit=True option, and tried using it with create_drop_constraint_if_exists_function, but it didn't help. [Following up to myself] Adding more... http://groups.google.com/group/sqlalchemy/msg/f23140f7980082f8 Michael Bayer mike...@zzzcomputing.com Jun 13 11:20AM -0400 ^#digest_top On Jun 13, 2010, at 4:03 AM, Faheem Mitha wrote: aren't, please correct me. As regards 1, I assume you mean try... pass... is a bad idea. I agree, but there is no When an exception more...http://groups.google.com/group/sqlalchemy/msg/867ef9217ee2689 Topic: Windows and Linux Tipshttp://groups.google.com/group/sqlalchemy/t/5b8270f1f9ce392e pavelhaque pavelha...@gmail.com Jun 13 05:01AM -0700 ^ #digest_top Hi friends, Do u need Windows and Linux Tips? Please visit the following site: http://windowsandlinuxtips.blogspot.com/ more... http://groups.google.com/group/sqlalchemy/msg/ab5809a268b14de9 Topic: Questions about sessionhttp://groups.google.com/group/sqlalchemy/t/9ed8ef9a193fbe5e Az azfarul.is...@gmail.com Jun 12 05:45PM -0700 ^ #digest_top Hi Conor, Many apologies for being pushy but since I'm pretty much in the processing of finishing up my code (due in two days), I wonder if you could just take a look at the last three posts of more...http://groups.google.com/group/sqlalchemy/msg/8dc1429e8fba0a64 Topic: Where can I learn morehttp://groups.google.com/group/sqlalchemy/t/a3036c1cf8b66187 Paul Hemans p_hem...@hotmail.com Jun 12 05:27PM -0700 ^ #digest_top Where can I learn more about creating a dialect for SQLalchemy? I could just copy one of the existing dialects, but I don't know what I am looking for in what needs to be changed. I need a kind of more...http://groups.google.com/group/sqlalchemy/msg/731aa49491b7ad01 Michael Bayer mike...@zzzcomputing.com Jun 12 08:39PM -0400 ^#digest_top Essential SQLAlchemy unfortunately is out of date in most areas and certainly with regards to dialect creation (as a note to anyone reading this, we are interested in authors for a new SQLAlchemy more...http://groups.google.com/group/sqlalchemy/msg/b9561f6d7316898e Topic: Using SQL aggregate fcns on a PGArray columnhttp://groups.google.com/group/sqlalchemy/t/901593fa025c327c David Korz david.k...@gmail.com Jun 11 05:55PM -0700 ^ #digest_top I have a table like such: CREATE TABLE test ( mykey integer not null, mydata double precision [2][10] ) without oids; with a table defn like: test = Table(test,metadata,
[sqlalchemy] UnicodeEncodeError driving me mad
I have some code that is taking values out of one database (a MySQL latin-1 database) and inserting them into another (postgres, utf-8) database, and I can't for the life of me work out what is going wrong here. The traceback looks slightly fishy, so I can only assume something is going horribly wrong in C-land: http://dpaste.org/afO4/ So, from this, I guess the problem must be in psycopg2? The only thing is that it actually works just fine on one of my machines, but on the machine I need to run it on, it fails miserably. I'm going slightly mad here trying to find a solution to this, but so far nothing I've tried (short of an .encode('ascii', 'ignore') does the trick. Is this something anyone's seen before, or am I doing something monumentally stupid? Any help in tracking this down would be really appreciated… I'm seriously going insane trying to fix this. -- 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] Re: UnicodeEncodeError driving me mad
Hi Michael, I am indeed on SA 0.6, and I am using the Unicode column types everywhere I need them. I suspect I am on the default SQL_ASCII, and this is consequently the cause of the problems. I'll change it now and hopefully that'll work :) As always, thanks for your helpful and well-written answers, Michael; I'm sure you get sick of answering the same questions time and again, you're quite a saint! Thanks. On May 11, 6:04 pm, Michael Bayer mike...@zzzcomputing.com wrote: it depends greatly on if you are using SQLAlchemy 0.6 or not. The short answer is that you should be using the Unicode type for columns that store unicode data, or you can set this globally using create_engine(, convert_unicode=True), which encodes strings into utf-8 before being sent. However, if you're using SQLAlchemy 0.6, we don't actually do that conversion with psycopg2 anymore and instead use it's native unicode support, which only works if you've configured the client encoding of your PG database to utf-8, i.e. in postgresql.conf. If you're on the default of SQL_ASCII, and its not an option to change that (you really should change it if possible), then SQLA's usual encode to utf-8 logic can be re-established on 0.6 by also adding use_native_unicode=False. Docs athttp://www.sqlalchemy.org/docs/reference/dialects/postgresql.html#uni...(as of just now) On May 11, 2010, at 12:04 PM, Oliver Beattie wrote: I have some code that is taking values out of one database (a MySQL latin-1 database) and inserting them into another (postgres, utf-8) database, and I can't for the life of me work out what is going wrong here. The traceback looks slightly fishy, so I can only assume something is going horribly wrong in C-land:http://dpaste.org/afO4/ So, from this, I guess the problem must be in psycopg2? The only thing is that it actually works just fine on one of my machines, but on the machine I need to run it on, it fails miserably. I'm going slightly mad here trying to find a solution to this, but so far nothing I've tried (short of an .encode('ascii', 'ignore') does the trick. Is this something anyone's seen before, or am I doing something monumentally stupid? Any help in tracking this down would be really appreciated… I'm seriously going insane trying to fix this. -- 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 athttp://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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://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 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] Two subclasses (single table inheritance) both requiring the same columns
Hi there, I have some (declarative, polymorphic) classes that use single-table inheritance. My configuration is similar to the below: class BasicObject(Base): col1 = Column(…) # discriminator, polymorphic setup etc class ObjectOne(BasicObject): col2 = Column(…) col3 = Column(…) class ObjectTwo(BasicObject): col2 = Column(…) # -- This is the same as col2 on ObjectOne col4 = Column(…) Previously, this worked fine in SA 0.5, but in 0.6 it raises an ArgumentError telling me it conflicts. For the moment, I have just moved these properties to the `BasicObject`, but that's not really where they should be. I have additional classes of `BasicObject` that shouldn't have that `col2` attribute. Is there a way I can get around this (and essentially declare them twice on the same table?). I know I could probably set them as different database columns and keep them as the same attribute on the mapper, but I really need the ability to query against that column across the different mappers (without having to do an OR). Is there a way around this? Any help would be appreciated :) Thanks, Oliver -- 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] Re: Getting access to the object being updated inside a default callable?
Sorry to bug… I imagine there is no way of doing this, but would be good to know for sure. If not, this would be really useful functionality. For instance, I might want to populate one column based on the contents of one of more other columns. On 12 Jan, 11:21, Oliver Beattie oli...@obeattie.com wrote: Hi there, I feel like I'm perhaps missing something, but I wonder if there's any way to access the object being updated inside a ColumnDefault? More specifically, if I have a… let's call it DBObject (using declarative) which has a callable as its default, is there any way to access that DBObject instance inside the default function? I see it gets passed an ExecutionContext instance, but I don't see a way to get the object from there :\ If there's a way to do this, it would help me a lot :) Thanks, Oliver -- 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] Re: Getting access to the object being updated inside a default callable?
Thanks for clarifying that. The MapperExtension is what I've been using so far for this, just wondered if there was a way to do this with column-level defaults. Now that I think about it though, the current way probably does make more sense, since if the values are dependent on outside influences, they cease to be column-level defaults. On Mar 5, 2:48 pm, Michael Bayer mike...@zzzcomputing.com wrote: Oliver Beattie wrote: Sorry to bug I imagine there is no way of doing this, but would be good to know for sure. If not, this would be really useful functionality. For instance, I might want to populate one column based on the contents of one of more other columns. On 12 Jan, 11:21, Oliver Beattie oli...@obeattie.com wrote: Hi there, I feel like I'm perhaps missing something, but I wonder if there's any way to access the object being updated inside a ColumnDefault? More specifically, if I have a let's call it DBObject (using declarative) which has a callable as its default, is there any way to access that DBObject instance inside the default function? I see it gets passed an ExecutionContext instance, but I don't see a way to get the object from there :\ If there's a way to do this, it would help me a lot :) The executioncontext has all the parameters for the whole statement, you can use those. But the ORM object is not available at that level. You might want to use MapperExtension.before_insert() if you need ORM-level awareness. Thanks, Oliver -- 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. -- 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] Multiple relations to the same table in a base declarative class causes errors when querying
Hi all, I've run into something I can't for the life of me work out why is happening. I've done a quick search and can't find anything. Basically, I have a base class that is subclassed (single table inheritance) with two relations both pointing to one other table. I'm probably confusing as hell, so an code sample will hopefully clear a few things up: http://dpaste.org/95hx/ …and a few other classes that extend this in various ways. When I now try to query my User table, I get this traceback: http://dpaste.org/J8qi/ If I remove the relations, everything is fine. I'm not entirely sure what I should do next to try and fix this? As always, help greatly appreciated :) —Oliver -- 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] Re: Multiple relations to the same table in a base declarative class causes errors when querying
Ah, thanks so much. Guess sometimes you just need a second pair of eyes to spot where you've messed it up :) On Feb 23, 7:29 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 23, 2010, at 1:03 PM, Oliver Beattie wrote: Hi all, I've run into something I can't for the life of me work out why is happening. I've done a quick search and can't find anything. Basically, I have a base class that is subclassed (single table inheritance) with two relations both pointing to one other table. I'm probably confusing as hell, so an code sample will hopefully clear a few things up:http://dpaste.org/95hx/ I'm going to guess its the double-usage of customer_id as an attribute that is confusing things. the customer_id column won't get properly associated with a table like that. Its a little surprising no error is raised beforehand. …and a few other classes that extend this in various ways. When I now try to query my User table, I get this traceback: http://dpaste.org/J8qi/ If I remove the relations, everything is fine. I'm not entirely sure what I should do next to try and fix this? As always, help greatly appreciated :) —Oliver -- 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 athttp://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 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] Re: How to make relation to same table
You probably want to take a look at http://www.sqlalchemy.org/docs/05/mappers.html#adjacency-list-relationships On Feb 23, 5:42 pm, flya flya flyafl...@gmail.com wrote: here is code: Base = declarative_base() class Page(Base): __tablename__ = 'pages' id = Column(Integer, primary_key=True) parent_id = Column('parent_id', Integer, ForeignKey('pages.id')) children = relation('Page', backref='parent') I get error information: sqlalchemy.exc.ArgumentError: Page.children and back-reference Page.parent are both of the same direction symbol 'ONETOMANY. Did you mean to set remote_side on the many-to-one side ? when change the code children = relation('Page', backref='parent') to childen = relation('Page') It can work , but I can only get 'children' no 'parent' from a Page instant. -- 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] Getting access to the object being updated inside a default callable?
Hi there, I feel like I'm perhaps missing something, but I wonder if there's any way to access the object being updated inside a ColumnDefault? More specifically, if I have a… let's call it DBObject (using declarative) which has a callable as its default, is there any way to access that DBObject instance inside the default function? I see it gets passed an ExecutionContext instance, but I don't see a way to get the object from there :\ If there's a way to do this, it would help me a lot :) Thanks, Oliver -- 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] Functions on results of subquery question
Hey there, I'm probably missing something here, but no matter what I try, I can't seem to find a way to translate this query into SQLAlchemy code: SELECT AVG(sub.average) FROM ( SELECT AVG(feedback.overall_rating) AS average FROM feedback INNER JOIN listings ON feedback.listing_id = listings.id WHERE feedback.is_for_driver = false GROUP BY feedback.listing_id ) AS sub; So, is there any way someone could possibly point me in the right direction? All of the tables have SA mappers defined for them (named Feedback and Listing), if that helps. I've tried to do things like: sa.select([sa.func.avg('sub.average'), sa.select([sa.func.avg (Feedback.overall_rating).label('average')]).alias('sub')]) but no avail (and I know that doesn't include the grouping or the where :) Anyway, if someone could possibly help me out, I'd be most grateful. Thanks, Oliver Beattie -- 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] Multiple relations to the same table us ing declarative… I think I'm being a total idiot
Hi all, I know I'm probably missing something painfully obvious here, but here goes anyway. I'm trying to create a table which has two foreign keys to a different table, and failing miserably. I understand I'm supposed to use the primaryjoin argument to relation… here's what I have in my class definition: sender_id = sa.Column(sa.Integer, sa.ForeignKey(User.id), nullable=False) sender = relation(User, backref=backref('feedback_sent'), primaryjoin=(sender_id == User.id)) recipient_id = sa.Column(sa.Integer, sa.ForeignKey(User.id), nullable=False) recipient = relation(User, backref=backref('feedback_received'), primaryjoin=(recipient_id == User.id)) Which seems to work okay, until I try to actually assign a sender or recipient (with something like ClassInstance.sender = UserInstance), when I will get error messages like IntegrityError: (IntegrityError) null value in column sender_id violates not-null constraint (even though [at least as far as I am concerned] the column is not empty). I know when I find the answer to this I'm going to feel incredibly ignorant! Any help would be much appreciated. —Oliver --~--~-~--~~~---~--~~ 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] Re: Multiple relations to the same tabl e using declarative… I think I'm being a total idiot
And we have a winner. Always check you are actually passing the values to the class constructor that you think you are before posting in a public group, folks. I've think I've succeeded in humiliating myself for today… On Nov 11, 11:52 am, Oliver Beattie oli...@obeattie.com wrote: Hi all, I know I'm probably missing something painfully obvious here, but here goes anyway. I'm trying to create a table which has two foreign keys to a different table, and failing miserably. I understand I'm supposed to use the primaryjoin argument to relation… here's what I have in my class definition: sender_id = sa.Column(sa.Integer, sa.ForeignKey(User.id), nullable=False) sender = relation(User, backref=backref('feedback_sent'), primaryjoin=(sender_id == User.id)) recipient_id = sa.Column(sa.Integer, sa.ForeignKey(User.id), nullable=False) recipient = relation(User, backref=backref('feedback_received'), primaryjoin=(recipient_id == User.id)) Which seems to work okay, until I try to actually assign a sender or recipient (with something like ClassInstance.sender = UserInstance), when I will get error messages like IntegrityError: (IntegrityError) null value in column sender_id violates not-null constraint (even though [at least as far as I am concerned] the column is not empty). I know when I find the answer to this I'm going to feel incredibly ignorant! Any help would be much appreciated. —Oliver --~--~-~--~~~---~--~~ 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] Re: What happens if a session is closed inside a subtransaction?
Thanks for your quick answer, Michael, that's really helpful. Sorry for the confusion surrounding subtransactions vs. SAVEPOINTs — I am indeed using subtransactions… just clearly haven't got the lingo down yet ;) Looking at my code now, I'm not too sure why I felt the need to call close() at all as you said within arbitrary points of the callstack. The way I'm dealing with the boilerplate issue is with a context manager for transactions (with transaction(session): ...), so I'm guaranteed that either commit() or rollback() gets called on each subtransaction. I don't know as that's the best way to do it, but it does eliminate a lot of try...except...else's since I do need the explicit subtransaction stuff. In any case, thanks for clearing that up for me, and it's good to know about those accessors. On Oct 14, 3:18 pm, Michael Bayer mike...@zzzcomputing.com wrote: Oliver Beattie wrote: Hi All, I'm just wondering what happens if I were to call session.close() whilst inside a subtransaction? Would it indeed close the session and abort all of the parent transactions or would it do nothing? Looking at the code (and I haven't looked at it in any great detail, sorry) I imagine that it does indeed abort all parent transactions. If so, is there any way to tell whether the session is in a subtransaction state or not, so I could only call close() if it is the root? Sorry if this is a bit confusing or if I'm being horrendously ignorant :) close() removes all transactional markers present. The connection is returned to the pool and a rollback() occurs on it which will release any state left on the connection. If you have an application that is making explicit usage of subtransactions, that is session.begin(allow_subtransactions=True), that would imply a nesting of functionality within methods which each issue an explicit begin/commit pair (note that this is different from a nested transaction, which uses SAVEPOINT. Since you said subtransactions I'm going with that concept). In the first place, such a usage pattern is extremely rare, even though SQLA makes use of it internally - its a lot easier to construct an application where there is a single point of begin/commit for a particular session, instead of having that kind of boilerplate in multiple places. Secondly, if you are in fact using that sort of pattern, I wouldn't try to call close() within arbitrary points of the callstack. Ensuring that rollback() or commit() is called at the end of each block will ensure that transactional/connection state is released when the full nest of operations complete. Session has an is_active accessor which will indicate if a transaction is present. to tell if its a subtransation you'd need to say sess.transaction and sess.transaction.is_active and sess.transaction._parent. —Oliver --~--~-~--~~~---~--~~ 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] Re: What happens if a session is closed inside a subtransaction?
I should probably ask something else too… how much overhead is there in using subtransactions as opposed to one global transaction? On Oct 14, 3:18 pm, Michael Bayer mike...@zzzcomputing.com wrote: Oliver Beattie wrote: Hi All, I'm just wondering what happens if I were to call session.close() whilst inside a subtransaction? Would it indeed close the session and abort all of the parent transactions or would it do nothing? Looking at the code (and I haven't looked at it in any great detail, sorry) I imagine that it does indeed abort all parent transactions. If so, is there any way to tell whether the session is in a subtransaction state or not, so I could only call close() if it is the root? Sorry if this is a bit confusing or if I'm being horrendously ignorant :) close() removes all transactional markers present. The connection is returned to the pool and a rollback() occurs on it which will release any state left on the connection. If you have an application that is making explicit usage of subtransactions, that is session.begin(allow_subtransactions=True), that would imply a nesting of functionality within methods which each issue an explicit begin/commit pair (note that this is different from a nested transaction, which uses SAVEPOINT. Since you said subtransactions I'm going with that concept). In the first place, such a usage pattern is extremely rare, even though SQLA makes use of it internally - its a lot easier to construct an application where there is a single point of begin/commit for a particular session, instead of having that kind of boilerplate in multiple places. Secondly, if you are in fact using that sort of pattern, I wouldn't try to call close() within arbitrary points of the callstack. Ensuring that rollback() or commit() is called at the end of each block will ensure that transactional/connection state is released when the full nest of operations complete. Session has an is_active accessor which will indicate if a transaction is present. to tell if its a subtransation you'd need to say sess.transaction and sess.transaction.is_active and sess.transaction._parent. —Oliver --~--~-~--~~~---~--~~ 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] What happens if a session is closed inside a subtransaction?
Hi All, I'm just wondering what happens if I were to call session.close() whilst inside a subtransaction? Would it indeed close the session and abort all of the parent transactions or would it do nothing? Looking at the code (and I haven't looked at it in any great detail, sorry) I imagine that it does indeed abort all parent transactions. If so, is there any way to tell whether the session is in a subtransaction state or not, so I could only call close() if it is the root? Sorry if this is a bit confusing or if I'm being horrendously ignorant :) —Oliver --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---