[sqlalchemy] How to change polymorphic_identity dynamically
Hi,
* I have a table A with __mapper_args__ = {'polymorphic_on': type}
* Table B C and D are inherited from A with polymorphic_identity
type_b, type_c and type_d respectively
I want to change the value of polymorphic_identity for an instance of
class B, how do it ??
I tried like this, but not working
instance_of_B.type = type_c
DBSession.add(instance_of_B)
transaction.commit();
I want to change the class of instance_of_B to C dynamically.
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: How to change polymorphic_identity dynamically
Hi, I also tried like this, But not working instance_of_B.__mapper_args__[polymorphic_identity] = type_c DBSession.add(instance_of_B) transaction.commit(); and instance_of_B.__mapper__.polymorphic_identity = type_c DBSession.add(instance_of_B) transaction.commit(); 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: How to change polymorphic_identity dynamically
On 05/06/2013 13:46, sajuptpm wrote: Hi, I also tried like this, But not working instance_of_B.__mapper_args__[polymorphic_identity] = type_c DBSession.add(instance_of_B) transaction.commit(); and instance_of_B.__mapper__.polymorphic_identity = type_c DBSession.add(instance_of_B) transaction.commit(); You should not modify polymorphic_identity directly: http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html?highlight=polymorphic_identity#sqlalchemy.orm.mapper.Mapper.polymorphic_identity This is a /read only/ attribute determined during mapper construction. Behavior is undefined if directly modified. 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Defining shared/inherited methods on mapped classes?
If I have several mapped classes on which I want to have defined some shared set of methods, e.g., ._todict(), how can I accomplish this (without multiple inheritance or defining them manually in each class)? I tried making the classes inherit from a shared parent class, but I receive errors because that parent class isn't mapped to anything. I appreciate any help that anyone can offer. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
On Thu, May 2, 2013 at 3:34 PM, Claudio Freire klaussfre...@gmail.com wrote: Without the C extension: ncalls tottime percall cumtime percall filename:lineno(function) 20811734 27.8290.000 27.8550.000 attributes.py:171(__get__) 7631984 13.5320.000 31.8510.000 ruby.py:86(get_param) With the C extension: ncalls tottime percall cumtime percall filename:lineno(function) 7631984 19.5140.000 21.0510.000 ruby.py:86(get_param) Notice how the C extension saves a total of 10s (cumtime, sum of internal and external time). There's no DB access when hitting those arguments, as everything has been eagerly loaded. It's all function call overhead. Assuming an application makes heavy use of attributes, as get_param does (expectable of straightforward code I'd think), that's a 30% speedup of CPU-bound code. As soon as I get GC right I'll post the patch. So... I got GC right (I think). I had to remove a few lines from profiles.txt because, obviously, there's a lot less function calls now. There's a second patch, that adds __slots__ to instance state. I found it speeds up things, marginally, but consistently (State.__init__ was another function weighing a lot because of millions of calls, this is the only way I found to speed it up). I'll get around to the Py3 things now. PS: Sorry I based it on 0.7.10... my app runs on that... I imagine I could upgrade to 0.8 with little effort, but never got around to actually doing it. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
On Mon, May 6, 2013 at 1:10 PM, Claudio Freire klaussfre...@gmail.com wrote: On Thu, May 2, 2013 at 3:34 PM, Claudio Freire klaussfre...@gmail.com wrote: Without the C extension: ncalls tottime percall cumtime percall filename:lineno(function) 20811734 27.8290.000 27.8550.000 attributes.py:171(__get__) 7631984 13.5320.000 31.8510.000 ruby.py:86(get_param) With the C extension: ncalls tottime percall cumtime percall filename:lineno(function) 7631984 19.5140.000 21.0510.000 ruby.py:86(get_param) Notice how the C extension saves a total of 10s (cumtime, sum of internal and external time). There's no DB access when hitting those arguments, as everything has been eagerly loaded. It's all function call overhead. Assuming an application makes heavy use of attributes, as get_param does (expectable of straightforward code I'd think), that's a 30% speedup of CPU-bound code. As soon as I get GC right I'll post the patch. So... I got GC right (I think). I had to remove a few lines from profiles.txt because, obviously, there's a lot less function calls now. There's a second patch, that adds __slots__ to instance state. I found it speeds up things, marginally, but consistently (State.__init__ was another function weighing a lot because of millions of calls, this is the only way I found to speed it up). I'll get around to the Py3 things now. PS: Sorry I based it on 0.7.10... my app runs on that... I imagine I could upgrade to 0.8 with little effort, but never got around to actually doing it. Stupid me... forgot to attach them. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. SQLAlchemy-0.7.10-cinstrumented.patch Description: Binary data SQLAlchemy-0.7.10-slotstate.patch Description: Binary data
Re: [sqlalchemy] How to change polymorphic_identity dynamically
On May 6, 2013, at 6:30 AM, sajuptpm sajup...@gmail.com wrote:
Hi,
* I have a table A with __mapper_args__ = {'polymorphic_on': type}
* Table B C and D are inherited from A with polymorphic_identity
type_b, type_c and type_d respectively
I want to change the value of polymorphic_identity for an instance of class
B, how do it ??
I tried like this, but not working
instance_of_B.type = type_c
DBSession.add(instance_of_B)
transaction.commit();
I want to change the class of instance_of_B to C dynamically.
set it like this:
instance_of_B.__class__ = C_Class
instance_of_B.type = type_c
session.flush()
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Defining shared/inherited methods on mapped classes?
On May 6, 2013, at 11:55 AM, Michael Nachtigal michael.nachti...@catalinamarketing.com wrote: If I have several mapped classes on which I want to have defined some shared set of methods, e.g., ._todict(), how can I accomplish this (without multiple inheritance or defining them manually in each class)? I tried making the classes inherit from a shared parent class, but I receive errors because that parent class isn't mapped to anything. I appreciate any help that anyone can offer. if you're using declarative mapping, you can define common base classes using the __abstract__ flag: http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#abstract other options include an augmented base and mixins: http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#augmenting-the-base -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
that's a lot of effort there. How confident are you that memory and references are handled correctly in the .c code? That's a lot of C code, and it took years for us to iron out all the memory leaks in the existing C extensions that we had - the original author eventually stopped maintaining them, and I had to take it all on myself and spend weeks learning the code and ironing out remaining, subtle issues (like http://hg.sqlalchemy.org/sqlalchemy/rev/8326 and http://hg.sqlalchemy.org/sqlalchemy/rev/8140). These are very insidious issues as they can't be diagnosed by usual gc reference counting. On May 6, 2013, at 12:11 PM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, May 6, 2013 at 1:10 PM, Claudio Freire klaussfre...@gmail.com wrote: On Thu, May 2, 2013 at 3:34 PM, Claudio Freire klaussfre...@gmail.com wrote: Without the C extension: ncalls tottime percall cumtime percall filename:lineno(function) 20811734 27.8290.000 27.8550.000 attributes.py:171(__get__) 7631984 13.5320.000 31.8510.000 ruby.py:86(get_param) With the C extension: ncalls tottime percall cumtime percall filename:lineno(function) 7631984 19.5140.000 21.0510.000 ruby.py:86(get_param) Notice how the C extension saves a total of 10s (cumtime, sum of internal and external time). There's no DB access when hitting those arguments, as everything has been eagerly loaded. It's all function call overhead. Assuming an application makes heavy use of attributes, as get_param does (expectable of straightforward code I'd think), that's a 30% speedup of CPU-bound code. As soon as I get GC right I'll post the patch. So... I got GC right (I think). I had to remove a few lines from profiles.txt because, obviously, there's a lot less function calls now. There's a second patch, that adds __slots__ to instance state. I found it speeds up things, marginally, but consistently (State.__init__ was another function weighing a lot because of millions of calls, this is the only way I found to speed it up). I'll get around to the Py3 things now. PS: Sorry I based it on 0.7.10... my app runs on that... I imagine I could upgrade to 0.8 with little effort, but never got around to actually doing it. Stupid me... forgot to attach them. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. SQLAlchemy-0.7.10-cinstrumented.patchSQLAlchemy-0.7.10-slotstate.patch -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
RE: [sqlalchemy] Defining shared/inherited methods on mapped classes?
Thanks, your first solution was just what I needed! From: sqlalchemy@googlegroups.com [sqlalchemy@googlegroups.com] on behalf of Michael Bayer [mike...@zzzcomputing.com] Sent: Monday, May 06, 2013 12:41 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Defining shared/inherited methods on mapped classes? On May 6, 2013, at 11:55 AM, Michael Nachtigal michael.nachti...@catalinamarketing.commailto:michael.nachti...@catalinamarketing.com wrote: If I have several mapped classes on which I want to have defined some shared set of methods, e.g., ._todict(), how can I accomplish this (without multiple inheritance or defining them manually in each class)? I tried making the classes inherit from a shared parent class, but I receive errors because that parent class isn't mapped to anything. I appreciate any help that anyone can offer. if you're using declarative mapping, you can define common base classes using the __abstract__ flag: http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#abstract other options include an augmented base and mixins: http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#augmenting-the-base -- 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.commailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.commailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Running a trigger with sqlalchemy that works with session.execute
Anyone who can help out with sqlalchemy related problem I have a app which using sqlalchemy as the orm. As the app is one in transition some of the update query's run through a session.execute . However I need to create a trigger of some kind . I am trying to use MapperExtensions but it seems that session.execute when it runs an update does not actually create a Mapper event. How can I trigger one of the mapper events? Please do check these docs: http://docs.sqlalchemy.org/en/rel_0_7/orm/deprecated.html -- A-M-I-T S|S -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
On Mon, May 6, 2013 at 1:50 PM, Michael Bayer mike...@zzzcomputing.com wrote: that's a lot of effort there. How confident are you that memory and references are handled correctly in the .c code? Quite. It's not my first C extension. But, truly, C is complex. That's a lot of C code, and it took years for us to iron out all the memory leaks in the existing C extensions that we had - the original author eventually stopped maintaining them, and I had to take it all on myself and spend weeks learning the code and ironing out remaining, subtle issues (like http://hg.sqlalchemy.org/sqlalchemy/rev/8326 and http://hg.sqlalchemy.org/sqlalchemy/rev/8140). These are very insidious issues as they can't be diagnosed by usual gc reference counting. There's an answer to those problems that I hesitated proposing, but you might want to consider: Pyrex. Or Cython. Take your pick. They *generate* C code, so it's be rather simple to replace the C extensions with them, and they look a lot more like python, and are a lot more fool-proof. Really, Pyrex is made for this kind of work. It's begging you. It's only the cost of an extra dependency (and the learning curve, which is there, but far flatter than C's). -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
did you generate your code here with pyrex?If you want to jump in and rework our C extensions to be pyrex based and everything works out just as well or better than before, it'll be a great 0.9/1.0 feature.I've got a bit of experience with cython already as I've worked on lxml a bit, cython vs. pyrex any thoughts ? based on http://docs.cython.org/src/userguide/pyrex_differences.html they seem pretty similar (though cython seems more commonplace...) On May 6, 2013, at 1:20 PM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, May 6, 2013 at 1:50 PM, Michael Bayer mike...@zzzcomputing.com wrote: that's a lot of effort there. How confident are you that memory and references are handled correctly in the .c code? Quite. It's not my first C extension. But, truly, C is complex. That's a lot of C code, and it took years for us to iron out all the memory leaks in the existing C extensions that we had - the original author eventually stopped maintaining them, and I had to take it all on myself and spend weeks learning the code and ironing out remaining, subtle issues (like http://hg.sqlalchemy.org/sqlalchemy/rev/8326 and http://hg.sqlalchemy.org/sqlalchemy/rev/8140). These are very insidious issues as they can't be diagnosed by usual gc reference counting. There's an answer to those problems that I hesitated proposing, but you might want to consider: Pyrex. Or Cython. Take your pick. They *generate* C code, so it's be rather simple to replace the C extensions with them, and they look a lot more like python, and are a lot more fool-proof. Really, Pyrex is made for this kind of work. It's begging you. It's only the cost of an extra dependency (and the learning curve, which is there, but far flatter than C's). -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
On Mon, May 6, 2013 at 2:31 PM, Michael Bayer mike...@zzzcomputing.com wrote: did you generate your code here with pyrex?If you want to jump in and rework our C extensions to be pyrex based and everything works out just as well or better than before, it'll be a great 0.9/1.0 feature.I've got a bit of experience with cython already as I've worked on lxml a bit, cython vs. pyrex any thoughts ? based on http://docs.cython.org/src/userguide/pyrex_differences.html they seem pretty similar (though cython seems more commonplace...) Cython makes a lot more progress, but it's also its drawback at times. I've sticked to Pyrex when I don't need Cython's benefits, because Pyrex is far more stable and easier to depend on. For this kind of work, I'd suggest pyrex. But really both work. I might try that, after checking Pyrex's compatibility with Py3... I've never done that. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Descriptor being overridden because of its name?
I'm using a declarative mapping in combination with reflection, and I'm providing some custom renaming of column names in the mapped class, as demonstrated below. temp_user = self.metadata.tables['user_t'] class MappedUser(Base): __table__ = temp_user user_number = temp_user.c.user_id user_id = temp_user.c.user_id user_identifier = temp_user.c.user_id I'm running into this strange issue though: I expected that I could use as a descriptor any of the attributes, user_number, user_id, or user_identifier, on an instance of this class to obtain the corresponding value in that object's field, but it doesn't work for the attribute named the same as the column name (the second attribute in the example above): If u is an instance of this class, then u.user_number and u.user_identifier give me the appropriate numeric result, but u.user_id gives me a Column object (which I don't want). I thought that maybe this attribute was being overridden by the column object because of its special name (and maybe it is), but if I delete the line user_id = temp_user.c.user_id, then the instance u will lose that attribute as well. Can someone kindly explain what's happening? How can I restore the desired behavior for the user_id attribute? Thanks very much for your time and reply, Mike -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
On Mon, May 6, 2013 at 2:31 PM, Michael Bayer mike...@zzzcomputing.com wrote: did you generate your code here with pyrex? Oh, sorry, I didn't answer this. No. I wrote it by hand. Pyrex-generated code is inscrutable, not that there's any need to inscrute. But really, when using pyrex, the C file ought to be considered merely as an intermediate file. The sources are the .pyx files. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Casting an overlap filter as an array
Hi, all. In Postgresql I have a CMS entry model with a tag column varchar(20)[]. I want to do a query so a row with any of the tags will be returned. I know overlap is the method to use but I can't get the casting done correctly. Right now I am trying (and a lot of searching revealed similar solutions): query.filter(ContentEntry.tag.overlap(cast(tags, ARRAY(VARCHAR(20) But I am getting the error: AttributeError: 'SQLCompiler' object has no attribute 'visit_array'. Any variation involving ARRAY() or array() says there is no attribute visit_ARRAY or visit_array. The following hand crafted SQL works, so for now I'm trying to at least get to this point (and as I understand, overlap will use the operator): SELECT content_entry.title, content_entry.tag FROM content_entry WHERE content_entry.tag cast(array['foo', 'bar'] as varchar(20)[]); How can I get this casted correctly? 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Descriptor being overridden because of its name?
not sure what you mean by descriptor here. Here's a test. show me how to
reproduce the broken behavior:
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
t = Table('t', Base.metadata,
Column('user_id', Integer, primary_key=True),
Column('data', String)
)
class A(Base):
__table__ = t
user_id = t.c.user_id
user_data = t.c.data
a1 = A()
a1.user_id = 5
assert a1.user_id == 5
a1.user_data = 'some data'
assert a1.user_data == 'some data'
On May 6, 2013, at 1:42 PM, Michael Nachtigal
michael.nachti...@catalinamarketing.com wrote:
I'm using a declarative mapping in combination with reflection, and I'm
providing some custom renaming of column names in the mapped class, as
demonstrated below.
temp_user = self.metadata.tables['user_t']
class MappedUser(Base):
__table__ = temp_user
user_number = temp_user.c.user_id
user_id = temp_user.c.user_id
user_identifier = temp_user.c.user_id
I'm running into this strange issue though: I expected that I could use as a
descriptor any of the attributes, user_number, user_id, or user_identifier,
on an instance of this class to obtain the corresponding value in that
object's field, but it doesn't work for the attribute named the same as the
column name (the second attribute in the example above): If u is an instance
of this class, then u.user_number and u.user_identifier give me the
appropriate numeric result, but u.user_id gives me a Column object (which I
don't want). I thought that maybe this attribute was being overridden by the
column object because of its special name (and maybe it is), but if I delete
the line user_id = temp_user.c.user_id, then the instance u will lose that
attribute as well.
Can someone kindly explain what's happening? How can I restore the desired
behavior for the user_id attribute?
Thanks very much for your time and reply,
Mike
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
RE: [sqlalchemy] Descriptor being overridden because of its name?
I was using descriptor as best I could to match its meaning as I understood
it in SQLAlchemy
(http://docs.sqlalchemy.org/ru/latest/glossary.html#term-descriptor), but it
may be wrong. Here's how I could somewhat reproduce the error using your code
(by the way, which is more appropriate for this mailing list, inline or
attached?):
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
t = Table('t', Base.metadata,
Column('user_id', Integer, primary_key=True),
Column('data', String)
)
class A(Base):
__table__ = t
user_id = t.c.user_id
user_id2 = t.c.user_id #should be another alias for user_id (right?)
a1 = A()
a1.user_id = 5
print a1.user_id
print a1.user_id2
# At this point, a1.user_id and a1.user_id2 do not evaluate to the same value.
# In my particular case, a1 was a query result/row, and I could refer to any
# of the mapped class attributes to get the value for that field, except the one
# named the same as the column name
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Descriptor being overridden because of its name?
right that won't work. if you want that pattern, use synonym():
http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html#id2
usually a hybrid is what's used here but if you really want just the alternate
name, synonym will do it.
short code examples inline are just fine.
On May 6, 2013, at 2:56 PM, Michael Nachtigal
michael.nachti...@catalinamarketing.com wrote:
I was using descriptor as best I could to match its meaning as I understood
it in SQLAlchemy
(http://docs.sqlalchemy.org/ru/latest/glossary.html#term-descriptor), but it
may be wrong. Here's how I could somewhat reproduce the error using your code
(by the way, which is more appropriate for this mailing list, inline or
attached?):
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
t = Table('t', Base.metadata,
Column('user_id', Integer, primary_key=True),
Column('data', String)
)
class A(Base):
__table__ = t
user_id = t.c.user_id
user_id2 = t.c.user_id #should be another alias for user_id (right?)
a1 = A()
a1.user_id = 5
print a1.user_id
print a1.user_id2
# At this point, a1.user_id and a1.user_id2 do not evaluate to the same value.
# In my particular case, a1 was a query result/row, and I could refer to any
# of the mapped class attributes to get the value for that field, except the
one
# named the same as the column name
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] order by on properties column
Hi,
I have 2 tables: Members GroupMembers
orm.mapper(Member, meta.Members)
orm.mapper(GroupMember, meta.GroupMembers
properties={
'member': orm.relation(Member,
primaryjoin=meta.GroupMembers.c.memberID == meta.Members.c.id,
lazy=False)
}
)
I want to do something like
query = session.query(GroupMember).order_by(GroupMember.member.name)
I get AttributeError: Neither 'InstrumentedAttribute' object nor
'Comparator' object has an attribute 'name' error.
I can do something like
query = session.query(GroupMember).join(Member).order_by(Member.name)
but this results in multiple joins on the same table.
Regards,
Viraj
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
Here's a ticket where we can keep talking about this: http://www.sqlalchemy.org/trac/ticket/2720 (here's the py3k ticket also: http://www.sqlalchemy.org/trac/ticket/2161) note that SQLAlchemy 0.9 will no longer use 2to3, and will by Python 2.6-3.3 in place.The enhancement here is targeted at 0.9 which is currently in the rel_0_9 branch. as for the __slots__ thing, that's a separate issue.if your patch doesn't break tests we can set that for 0.9 as well, I doubt anyone is subclassing InstanceState, though I'd want to see what the speedup is with that. On May 6, 2013, at 1:37 PM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, May 6, 2013 at 2:20 PM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, May 6, 2013 at 1:50 PM, Michael Bayer mike...@zzzcomputing.com wrote: that's a lot of effort there. How confident are you that memory and references are handled correctly in the .c code? Quite. It's not my first C extension. But, truly, C is complex. And as I write this... I find an... issue (not leak, but not good reference management either). Sorry. Feel free to gauge cost-benefit here. I'll think about pyrex too. Have in mind that I'll be using SQLAlchemy for years to come (or expect to). I don't think I shall withdraw support in the short term, but an oracle I am not. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. SQLAlchemy-0.7.10-cinstrumented.patch -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] order by on properties column
On May 6, 2013, at 3:21 PM, V'Raj Kanwade viraj.kanw...@gmail.com wrote:
Hi,
I have 2 tables: Members GroupMembers
orm.mapper(Member, meta.Members)
orm.mapper(GroupMember, meta.GroupMembers
properties={
'member': orm.relation(Member,
primaryjoin=meta.GroupMembers.c.memberID == meta.Members.c.id,
lazy=False)
}
)
I want to do something like
query = session.query(GroupMember).order_by(GroupMember.member.name)
I get AttributeError: Neither 'InstrumentedAttribute' object nor
'Comparator' object has an attribute 'name' error.
I can do something like
query = session.query(GroupMember).join(Member).order_by(Member.name)
but this results in multiple joins on the same table.
query(GroupMember).join(Member).order_by(Member.name) is the right answer, and
only generates one JOIN. The second join you're seeing is due to your
lazy=False, and is a separate concern which cannot be used to impact the query
results - see
http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html#the-zen-of-eager-loading
for background on this, as well as
http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html#contains-eager which is
probably what you'll end up doing.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] order by on properties column
Thanks Michael
On Monday, 6 May 2013 12:29:07 UTC-7, Michael Bayer wrote:
On May 6, 2013, at 3:21 PM, V'Raj Kanwade viraj@gmail.comjavascript:
wrote:
Hi,
I have 2 tables: Members GroupMembers
orm.mapper(Member, meta.Members)
orm.mapper(GroupMember, meta.GroupMembers
properties={
'member': orm.relation(Member,
primaryjoin=meta.GroupMembers.c.memberID == meta.Members.c.id,
lazy=False)
}
)
I want to do something like
query = session.query(GroupMember).order_by(GroupMember.member.name)
I get AttributeError: Neither 'InstrumentedAttribute' object nor
'Comparator' object has an attribute 'name' error.
I can do something like
query = session.query(GroupMember).join(Member).order_by(Member.name)
but this results in multiple joins on the same table.
query(GroupMember).join(Member).order_by(Member.name) is the right answer,
and only generates one JOIN. The second join you're seeing is due to your
lazy=False, and is a separate concern which cannot be used to impact the
query results - see
http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html#the-zen-of-eager-loadingfor
background on this, as well as
http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html#contains-eagerwhich is
probably what you'll end up doing.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
On Mon, May 6, 2013 at 4:27 PM, Michael Bayer mike...@zzzcomputing.com wrote:
as for the __slots__ thing, that's a separate issue.if your patch doesn't
break tests we can set that for 0.9 as well, I doubt anyone is subclassing
InstanceState, though I'd want to see what the speedup is with that.
About 15% on state creation (which can easily be a big chunk of any
bulk ORM operations):
class InstanceState(object):
...__slots__ = ('a','b','c','__dict__','__weakrefs__')
...def __init__(self):
...self.a = a
...self.b = b
...self.c = c
...
class InstanceStateSlow(object):
...def __init__(self):
...self.a = a
...self.b = b
...self.c = c
...
def test(which):
...for i in xrange(10):
... x = which()
...
import timeit
timeit.timeit(lambda : test(InstanceStateSlow))
8.486893892288208
timeit.timeit(lambda : test(InstanceState))
7.35853814697
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] unicode SAWarning
Hi, I'm just pulling this up because it's a little anoying. Everytime I start my application, this warning shows: *[...]/lib/python2.7/site-packages/sqlalchemy/engine/default.py:471: SAWarning: Unicode type received non-unicode bind param value.** ** processors[key](compiled_params[key])* I don't know if it's some OS+source+db combination I have in my system or something else. Anyway, some info about: * SA 0.8.1 with C extensions - from pip * psycopg2 2.5 (dt dec pq3 ext) - from pip * Python 2.7.4 (default, Apr 19 2013, 18:28:01) - [GCC 4.7.3] on linux2 * OS: [Ubuntu] Linux rkuesters 3.8-10.dmz.1-liquorix-amd64 #1 ZEN SMP PREEMPT Thu May 2 07:09:55 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux * PostgreSQL 9.1 (9.1.9-1ubuntu1) Thanks! Richard :) -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] unicode SAWarning
this happens when you do this: class SomeClass(Base): # ... some_col = Column(Unicode(50)) s = SomeClass(somestring='some value') and then when you commit s to the database, the bytestring is detected and the warning emitted. Note 'some value' is a Python bytestring, not a Unicode string, that is: u'some value'. Solutions: 1. use unicode strings, u'some value'. 2. turn on u'' everywhere, using from __future__ import unicode_literals 3. use a coercing unicode type, see the example at http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#coercing-encoded-strings-to-unicode 4. turn the warning off: http://docs.python.org/2/library/warnings.html 5. use the Unicode type with convert_unicode=False: Column(Unicode(50, convert_unicode=False)) - since you're using psycopg2, SQLAlchemy doesn't need to be involved here and setting this flag will disable the check. On May 6, 2013, at 4:33 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: Hi, I'm just pulling this up because it's a little anoying. Everytime I start my application, this warning shows: [...]/lib/python2.7/site-packages/sqlalchemy/engine/default.py:471: SAWarning: Unicode type received non-unicode bind param value. processors[key](compiled_params[key]) I don't know if it's some OS+source+db combination I have in my system or something else. Anyway, some info about: SA 0.8.1 with C extensions - from pip psycopg2 2.5 (dt dec pq3 ext) - from pip Python 2.7.4 (default, Apr 19 2013, 18:28:01) - [GCC 4.7.3] on linux2 OS: [Ubuntu] Linux rkuesters 3.8-10.dmz.1-liquorix-amd64 #1 ZEN SMP PREEMPT Thu May 2 07:09:55 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux PostgreSQL 9.1 (9.1.9-1ubuntu1) Thanks! Richard :) -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] unicode SAWarning
Thanks Mike. I know I must use unicode, and, in a matter of fact, I do respect the first item of your list. That's why I asked here :) I'll try items 2 and 5 (which seems more appropriate, since my application runs only on Postgres). Best regards, Richard. On 05/06/2013 05:50 PM, Michael Bayer wrote: this happens when you do this: class SomeClass(Base): # ... some_col = Column(Unicode(50)) s = SomeClass(somestring='some value') and then when you commit s to the database, the bytestring is detected and the warning emitted. Note 'some value' is a Python bytestring, not a Unicode string, that is: u'some value'. Solutions: 1. use unicode strings, u'some value'. 2. turn on u'' everywhere, using from __future__ import unicode_literals 3. use a coercing unicode type, see the example at http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#coercing-encoded-strings-to-unicode 4. turn the warning off: http://docs.python.org/2/library/warnings.html 5. use the Unicode type with convert_unicode=False: Column(Unicode(50, convert_unicode=False)) - since you're using psycopg2, SQLAlchemy doesn't need to be involved here and setting this flag will disable the check. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Using a UUID as primary key
I was looking at using a UUID as primary key for a table. Using the backend-agnostic GUID type from http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type I get strange behaviour though. I whipped up a simple test case: class Data(BaseObject): __tablename__ = 'data' uuid = Column(GUID(), primary_key=True) metadata.create_all() session = sessionmaker(autocommit=False)() uuid = 'ac4daeff-3af3-4b83-b8c7-3aa34e34151c' obj = Data(uuid=uuid) session.add(obj) assert session.query(Data).get(uuid) is obj assert session.query(Data).filter(Data.uuid == uuid).first() is obj Both asserts fail when I do this. The problem appears to be conversion from to a uuid.UUID instance in GUID.process_result_value: this always creates a new UUID instance, which probably causes a miss when SQLAlchemy tries to find an instance in its identify map. Is this a bug in the GUID example in the documentation, or is this something that can be improved in the identity map? Wichert. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] null values in multi column unique index
Hi All,
using 8.1
class Company(Base):
__tablename__ = 'Company'
Id = Column(Integer, primary_key=True)
class ProductClass(Base):
__tablename__ = 'ProductClass'
Id = Column(Integer, primary_key=True)
Company_Id = Column(Integer, ForeignKey(Company.Id, use_alter=True,
name=FK_ProductClassCode_Company_Id))
Company = relationship(Company)
Class = Column(String(16), nullable=False)
Code = Column(String(32), nullable=False)
How can I enforce uniqueness in a multi column unique index where
Company_Id may have null values.
I have tried:
__table_args__ = (Index(ProductClass_Index1, Company_Id, Class, Code,
unique=True),)
__table_args__ = (UniqueConstraint(Company_Id, Class, Code),)
Index(ProductClassCode_Index1, ProductClass.Company_Id,
ProductClass.Class,
ProductClass.Code, unique=True)
Thanks in advance for your help.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
from sqlalchemy import Column, ForeignKey, String, Integer, Index, create_engine
from sqlalchemy import __version__
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, Session
from sqlalchemy.schema import UniqueConstraint
Base = declarative_base()
class Company(Base):
__tablename__ = 'Company'
Id = Column(Integer, primary_key=True)
CompanyId = Column(String(16), index=True, unique=True, nullable=False)
class ProductClass(Base):
__tablename__ = 'ProductClass'
Id = Column(Integer, primary_key=True)
# Many2One
Company_Id = Column(Integer, ForeignKey(Company.Id, use_alter=True, name=FK_ProductClassCode_Company_Id, ondelete=SET NULL))
Company = relationship(Company)
Class = Column(String(16), nullable=False)
Code = Column(String(32), nullable=False)
__table_args__ = (Index(ProductClass_Index1, Company_Id, Class, Code, unique=True),)
#__table_args__ = (UniqueConstraint(Company_Id, Class, Code),)
#Index(ProductClass_Index1, ProductClass.Company_Id,
#ProductClass.Class,
#ProductClass.Code, unique=True)
engine = create_engine('sqlite://', echo=False)
Base.metadata.create_all(engine)
print __version__
if __name__ == '__main__':
session = Session(engine)
coy = Company(CompanyId=gvv)
session.add(coy)
code = ProductClass(Class=class, Code=code, Company=coy)
session.add(code)
session.commit()
# (IntegrityError) columns Company_Id, Class, Code are not unique - OK
#code = ProductClass(Class=class, Code=code, Company=coy)
#session.add(code)
#session.commit()
code = session.query(ProductClass).filter_by(Company=coy).first()
print code.Company_Id, code.Class, code.Code
print :
code = ProductClass(Class=class, Code=code)
session.add(code)
session.commit()
print code.Company_Id, code.Class, code.Code
# should be an (IntegrityError) columns Company_Id, Class, Code are not unique
code = ProductClass(Class=class, Code=code)
session.add(code)
session.commit()
print code.Company_Id, code.Class, code.Code
print :
code = session.query(ProductClass).filter_by(Company=None).all()
for cod in code:
print cod.Company_Id, cod.Class, cod.Code
[sqlalchemy] Re: Using a UUID as primary key
For reference I have attached a complete test case including a copy of the GUID
code from the documentation.
On May 6, 2013, at 23:22, Wichert Akkerman wich...@wiggy.net wrote:
I was looking at using a UUID as primary key for a table. Using the
backend-agnostic GUID type from
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type
I get strange behaviour though. I whipped up a simple test case:
class Data(BaseObject):
__tablename__ = 'data'
uuid = Column(GUID(), primary_key=True)
metadata.create_all()
session = sessionmaker(autocommit=False)()
uuid = 'ac4daeff-3af3-4b83-b8c7-3aa34e34151c'
obj = Data(uuid=uuid)
session.add(obj)
assert session.query(Data).get(uuid) is obj
assert session.query(Data).filter(Data.uuid == uuid).first() is obj
Both asserts fail when I do this. The problem appears to be conversion from
to a uuid.UUID instance in GUID.process_result_value: this always creates a
new UUID instance, which probably causes a miss when SQLAlchemy tries to find
an instance in its identify map.
Is this a bug in the GUID example in the documentation, or is this something
that can be improved in the identity map?
Wichert.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
On May 6, 2013, at 23:22, Wichert Akkerman wich...@wiggy.net wrote:I was looking at using a UUID as primary key for a table. Using the backend-agnostic GUID type fromhttp://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-typeI get strange behaviour though. I whipped up a simple test case:class Data(BaseObject): __tablename__ = 'data' uuid = Column(GUID(), primary_key=True)metadata.create_all()session = sessionmaker(autocommit=False)()uuid = 'ac4daeff-3af3-4b83-b8c7-3aa34e34151c'obj = Data(uuid=uuid)session.add(obj)assert session.query(Data).get(uuid) is objassert session.query(Data).filter(Data.uuid == uuid).first() is objBoth asserts fail when I do this. The problem appears to be conversion from to a uuid.UUID instance in GUID.process_result_value: this always creates a new UUID instance, which probably causes a miss when SQLAlchemy tries to find an instance in its identify map.Is this a bug in the GUID example in the documentation, or is this something that can be improved in the identity map?Wichert.from uuid import UUID
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects import postgresql
metadata = MetaData(bind=create_engine('sqlite://'))
BaseObject = declarative_base(metadata=metadata)
class GUID(TypeDecorator):
Platform-independent GUID type.
Uses Postgresql's UUID type, otherwise uses CHAR(36), storing as
stringified hex values.
This implementation is based on the SQLAlchemy
`backend-agnostic GUID Type
http://www.sqlalchemy.org/docs/core/types.html#backend-agnostic-guid-type`_
example.
impl = CHAR
def load_dialect_impl(self, dialect):
if dialect.name == 'postgresql':
return dialect.type_descriptor(postgresql.UUID())
else:
return dialect.type_descriptor(CHAR(36))
def process_bind_param(self, value, dialect):
if value is None:
return value
elif dialect.name == 'postgresql':
return str(value)
else:
if not isinstance(value, UUID):
return str(UUID(value))
else:
# hexstring
return str(value)
def process_result_value(self, value, dialect):
if value is None:
return value
else:
return UUID(value)
class Data(BaseObject):
__tablename__ = 'data'
uuid = Column(GUID(), primary_key=True)
metadata.create_all()
session = sessionmaker(autocommit=False)()
uuid = 'ac4daeff-3af3-4b83-b8c7-3aa34e34151c'
obj = Data(uuid=uuid)
session.add(obj)
assert session.query(Data).get(uuid) is obj
assert session.query(Data).filter(Data.uuid == uuid).first() is obj
Re: [sqlalchemy] Using a UUID as primary key
well what's happening here is fairly simple, the mapper and ORM don't know anything about the conversion from string to UUID. So when you pass it Data(uuid=some string), it persists it, passing it off to the Core which converts the UUID, but because you've supplied the primary key, it then assumes the key for this object is some string, not the UUID object, and places it into the identity map with that string identity. Upon loading the first row from the database, that row comes back with UUID(some string) instead, so now you have two different identities in the identity map. I don't actually use UUIDs for primary keys, I spent a year with one project that did and it was definitely a mistake, so these days I tend to have the UUID as a supplemental identifier for use in web services and such, but not as the internal primary key, which is why that recipe doesn't have any note about this - but also I don't use the string coercion you see here; if I'm dealing with a UUID I'd normally make sure it's a UUID the moment it enters my application (like if it were in a web form, the form library would coerce it). Anyway, the immediate solution to this test would be to make sure the value is coerced at the ORM level, most simply by using a @validates decorator. On May 6, 2013, at 5:24 PM, Wichert Akkerman wich...@wiggy.net wrote: For reference I have attached a complete test case including a copy of the GUID code from the documentation. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. On May 6, 2013, at 23:22, Wichert Akkerman wich...@wiggy.net wrote: I was looking at using a UUID as primary key for a table. Using the backend-agnostic GUID type from http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type I get strange behaviour though. I whipped up a simple test case: class Data(BaseObject): __tablename__ = 'data' uuid = Column(GUID(), primary_key=True) metadata.create_all() session = sessionmaker(autocommit=False)() uuid = 'ac4daeff-3af3-4b83-b8c7-3aa34e34151c' obj = Data(uuid=uuid) session.add(obj) assert session.query(Data).get(uuid) is obj assert session.query(Data).filter(Data.uuid == uuid).first() is obj Both asserts fail when I do this. The problem appears to be conversion from to a uuid.UUID instance in GUID.process_result_value: this always creates a new UUID instance, which probably causes a miss when SQLAlchemy tries to find an instance in its identify map. Is this a bug in the GUID example in the documentation, or is this something that can be improved in the identity map? Wichert. tst.py -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] null values in multi column unique index
this depends on the database you're using, and I'd google around for stack overflow information on how to implement unique values with NULLs allowed. For example here is postgresql: http://dba.stackexchange.com/questions/9759/postgresql-multi-column-unique-constraint-and-null-values and here's SQL Server (the answer with 223 upmods is the best): http://stackoverflow.com/questions/767657/how-do-i-create-unique-constraint-that-also-allows-nulls-in-sql-server Basically it's a bit of a tough climb - I have an app here that needs this in some places but I couldn't really get SQL Server's approach to work so it's disabled for the moment. On May 6, 2013, at 5:23 PM, gvv gvver...@gmail.com wrote: Hi All, using 8.1 class Company(Base): __tablename__ = 'Company' Id = Column(Integer, primary_key=True) class ProductClass(Base): __tablename__ = 'ProductClass' Id = Column(Integer, primary_key=True) Company_Id = Column(Integer, ForeignKey(Company.Id, use_alter=True, name=FK_ProductClassCode_Company_Id)) Company = relationship(Company) Class = Column(String(16), nullable=False) Code = Column(String(32), nullable=False) How can I enforce uniqueness in a multi column unique index where Company_Id may have null values. I have tried: __table_args__ = (Index(ProductClass_Index1, Company_Id, Class, Code, unique=True),) __table_args__ = (UniqueConstraint(Company_Id, Class, Code),) Index(ProductClassCode_Index1, ProductClass.Company_Id, ProductClass.Class, ProductClass.Code, unique=True) Thanks in advance for your help. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. test.py -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Using UniqueConstraint or unique=True :p:
On 05/03/2013 04:18 PM, Simon King wrote:
On Thu, May 2, 2013 at 11:43 PM, Paradox para...@pobox.com wrote:
CREATE TABLE user (lname string, fname string, email string,
unique(lname, fname) ON CONFLICT REPLACE);
This will allow me to add multiple rows with the same lname as long as
the fnames are different for each. Is there a way to define such unique
constraints in SqlAlchemy?
You need to put your UniqueConstraint in the __table_args__ class
attribute for SQLAlchemy to see it, something like this:
class User(Base):
__tablename__ = 'user'
__table_args__ = (
UniqueConstraint('lname', 'fname', name='full_name'),
)
See the examples at
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#table-configuration.
Note that __table_args__ here is a 1-element tuple - the comma on the
end of the line is important.
Hope that helps,
Simon
Simon,
Thanks so much, that fixed it. I appreciate the links too, I was
following the below and it hadn't occurred to me that it was a different
situation (i.e. defining the table using MetaData rather than using the
class definition).
http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html?highlight=uniqueconstraint#sqlalchemy.schema.UniqueConstraint
thomas
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] contains_eager and ordering
I am executing a query with contains_eager to load objects and their related objects from a different table. I would like to control the order of the related objects within each InstrumentedList. I had hoped this could be done through the ordering in the query. For example, with a query like: session.query(User).outerjoin(User.addresses).order_by(Users.id, Address.name, Address.more).options(contains_eager(User.addresses)) I had hoped then when I looped over each user, and then looped over user.addresses, I would find the addresses were sorted by (name, more). However, this does not seem to be the case. I have a full example here: https://gist.github.com/bobbyi/5530250 Is there a way that I can get the objects to keep the ordering from the database? I want to use the collation types, etc., as defined in the database so I'd rather avoid sorting the items again in Python. 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Using a UUID as primary key
I was curious if you might elaborate on the issues with using a GUID as the primary key? I use them extensively in my professional database projects where I have multiple remote geographic sites that might have key collisions. Actually I haven't used a bigint serial PK in years and haven't really had a problem, at least at the DB level. I'm about to implement a large project using SQLAlchemy (really awesome work BTW) and had planned on using the GUID PK's (stored as varchar) in all of the tables as a partitioning key. I tend to store the he GUID as a string though (not as PG's UUID native data type), other than the obvious storage wastage are there other issues with SA I should be aware of? Cheers. mfw On Monday, May 6, 2013 3:52:10 PM UTC-7, Michael Bayer wrote: well what's happening here is fairly simple, the mapper and ORM don't know anything about the conversion from string to UUID. So when you pass it Data(uuid=some string), it persists it, passing it off to the Core which converts the UUID, but because you've supplied the primary key, it then assumes the key for this object is some string, not the UUID object, and places it into the identity map with that string identity. Upon loading the first row from the database, that row comes back with UUID(some string) instead, so now you have two different identities in the identity map. I don't actually use UUIDs for primary keys, I spent a year with one project that did and it was definitely a mistake, so these days I tend to have the UUID as a supplemental identifier for use in web services and such, but not as the internal primary key, which is why that recipe doesn't have any note about this - but also I don't use the string coercion you see here; if I'm dealing with a UUID I'd normally make sure it's a UUID the moment it enters my application (like if it were in a web form, the form library would coerce it). Anyway, the immediate solution to this test would be to make sure the value is coerced at the ORM level, most simply by using a @validates decorator. On May 6, 2013, at 5:24 PM, Wichert Akkerman wic...@wiggy.netjavascript: wrote: For reference I have attached a complete test case including a copy of the GUID code from the documentation. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. On May 6, 2013, at 23:22, Wichert Akkerman wic...@wiggy.net javascript: wrote: I was looking at using a UUID as primary key for a table. Using the backend-agnostic GUID type from http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type I get strange behaviour though. I whipped up a simple test case: class Data(BaseObject): __tablename__ = 'data' uuid = Column(GUID(), primary_key=True) metadata.create_all() session = sessionmaker(autocommit=False)() uuid = 'ac4daeff-3af3-4b83-b8c7-3aa34e34151c' obj = Data(uuid=uuid) session.add(obj) assert session.query(Data).get(uuid) is obj assert session.query(Data).filter(Data.uuid == uuid).first() is obj Both asserts fail when I do this. The problem appears to be conversion from to a uuid.UUID instance in GUID.process_result_value: this always creates a new UUID instance, which probably causes a miss when SQLAlchemy tries to find an instance in its identify map. Is this a bug in the GUID example in the documentation, or is this something that can be improved in the identity map? Wichert. tst.py -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
