Re: [sqlalchemy] Relate to Object by Two Refs
On May 16, 2013, at 03:09 , Ji Zhang zhangj...@gmail.com wrote:
Hi,
Say I have a Request model and User model:
class Request(Base):
id = Column(Integer, primary_key=True)
user_id = Column(Integer)
admin_id = Column(Integer)
class User(Base):
id = Column(Integer, primary_key=True)
username = Column(String)
The Request is created by a user (User) and get verified by an admin (also a
User). How to get both request.user and request.admin?
You will need to specify the foreign keys used for the relation ship:
class Request(Base):
user_id = Column(Integer, ForeignKey('user.id'))
user = relationship(User, foreign_keys=[user_id])
admin_id = Column(Integer, ForeignKey('user.id'))
admin = relationship(User, foreign_keys=[admin_id])
You can find more details in the SQLAlchemy documentation:
http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#handling-multiple-join-paths
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.
Re: [sqlalchemy] Metadata default schema
My bad, I didn't notice those notes in the documentation, thank you for pointing that out. I understand the reason for the table test1 showing twice, but why doesn't it when I reflect the public schema (explicitly, not via the None default schema) after the test2 table ? If I understand correctly, the test2 table reflection should point to the (unqualified) test1 table, thus resulting in the test1 table being created in the default schema. Shouldn't reflecting the public schema after that create another public.test1 table for the sake of consistency ? Le mercredi 15 mai 2013 17:24:59 UTC+2, Michael Bayer a écrit : On May 15, 2013, at 4:37 AM, Ronan Dunklau rdun...@gmail.comjavascript: wrote: Hello. I'm sorry if this has already been reported, but I couldn't find it. The metadata schema defaults to None, which can lead to suprising and inconsistent results when reflecting tables from various schemas. I don't know what the behaviour should be exactly, but this doesn't seem right. This has been tested on sqlalchemy 0.8.1, with postgresql. I don't know if this is specific to the postgresql backend. Please find the sql and python code corresponding to the following scenario as attachments. Let's say I have a table in a schema test_schema, which references a table test1 in the default schema, public. If I reflect the public schema, then the test_schema schema, the test1 table appear twice (once in the public schema, once in the None schema). If I reflect the test_schema schema first, then the public schema, the test1 table appear only once, in the None schema. If I reflect the default schema first (None), which should be the same as the public schema, the test1 table appear only once, as it should be. A simple workaroud for this is to initialize the metadata schema attribute with the default schema name as found in the Inspector.default_schema_name attribute. so the notes for this situation are here: http://docs.sqlalchemy.org/en/rel_0_8/dialects/postgresql.html#remote-cross-schema-table-introspection When we reflect the test_schema.test2 table and receive its collection of foreign keys, Postgresql tells us the table refers to another table called test1, but does *not* tell us which schema this table is present within. This is because the public schema is in the search path, and it is omitted.If we were able to see public, we would know that we already have a table present called public.test1 which fits the bill, but we don't, and this would also be a behavioral inconsistency. The behavior of the table inspection in any case is that the default schema of the connection corresponds to a schema of None for the reflected table. As the notes there say, the expected model is that the search_path is set to public only, and that Table objects which refer to public would use None as the schema.It's true that other models here could work too, having public be explicit in all cases, but using both models at the same time doesn't seem to be feasible, at least not without some workarounds as you've mentioned. -- 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] Metadata default schema
a little bit of a slip in metadata.reflect(), there's a patch which potentially fixes this in http://www.sqlalchemy.org/trac/ticket/2728. On May 16, 2013, at 3:35 AM, rdunklau rdunk...@gmail.com wrote: My bad, I didn't notice those notes in the documentation, thank you for pointing that out. I understand the reason for the table test1 showing twice, but why doesn't it when I reflect the public schema (explicitly, not via the None default schema) after the test2 table ? If I understand correctly, the test2 table reflection should point to the (unqualified) test1 table, thus resulting in the test1 table being created in the default schema. Shouldn't reflecting the public schema after that create another public.test1 table for the sake of consistency ? Le mercredi 15 mai 2013 17:24:59 UTC+2, Michael Bayer a écrit : On May 15, 2013, at 4:37 AM, Ronan Dunklau rdun...@gmail.com wrote: Hello. I'm sorry if this has already been reported, but I couldn't find it. The metadata schema defaults to None, which can lead to suprising and inconsistent results when reflecting tables from various schemas. I don't know what the behaviour should be exactly, but this doesn't seem right. This has been tested on sqlalchemy 0.8.1, with postgresql. I don't know if this is specific to the postgresql backend. Please find the sql and python code corresponding to the following scenario as attachments. Let's say I have a table in a schema test_schema, which references a table test1 in the default schema, public. If I reflect the public schema, then the test_schema schema, the test1 table appear twice (once in the public schema, once in the None schema). If I reflect the test_schema schema first, then the public schema, the test1 table appear only once, in the None schema. If I reflect the default schema first (None), which should be the same as the public schema, the test1 table appear only once, as it should be. A simple workaroud for this is to initialize the metadata schema attribute with the default schema name as found in the Inspector.default_schema_name attribute. so the notes for this situation are here: http://docs.sqlalchemy.org/en/rel_0_8/dialects/postgresql.html#remote-cross-schema-table-introspection When we reflect the test_schema.test2 table and receive its collection of foreign keys, Postgresql tells us the table refers to another table called test1, but does *not* tell us which schema this table is present within. This is because the public schema is in the search path, and it is omitted. If we were able to see public, we would know that we already have a table present called public.test1 which fits the bill, but we don't, and this would also be a behavioral inconsistency. The behavior of the table inspection in any case is that the default schema of the connection corresponds to a schema of None for the reflected table. As the notes there say, the expected model is that the search_path is set to public only, and that Table objects which refer to public would use None as the schema.It's true that other models here could work too, having public be explicit in all cases, but using both models at the same time doesn't seem to be feasible, at least not without some workarounds as you've mentioned. -- 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] how are foreign relations stored ?
I've been using a utility method 'columns_as_dict' to help store my data in a cache. It works well. A problem I've encountered... i need to access the related data that i've eagerloaded. My current function looks like this def columns_as_dict(self): as_dict = dict( (col.name, getattr(self, col.name)) for col in sqlalchemy_orm.class_mapper(self.__class__).mapped_table.c ) return *as_dict* * * any idea how i can access the foreign relations ? i'm specifically interested in ones that are already eagerloaded, but am also interested in accessing all of the relations in general. -- 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] how are foreign relations stored ?
if you're on 0.8 this is all available via the various getters on mapper: http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.attrs On May 16, 2013, at 1:37 PM, Jonathan Vanasco jonat...@findmeon.com wrote: I've been using a utility method 'columns_as_dict' to help store my data in a cache. It works well. A problem I've encountered... i need to access the related data that i've eagerloaded. My current function looks like this def columns_as_dict(self): as_dict = dict( (col.name, getattr(self, col.name)) for col in sqlalchemy_orm.class_mapper(self.__class__).mapped_table.c ) return as_dict any idea how i can access the foreign relations ? i'm specifically interested in ones that are already eagerloaded, but am also interested in accessing all of the relations in general. -- 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] how are foreign relations stored ?
I am and that's awesome. Thank you! On Thursday, May 16, 2013 2:47:47 PM UTC-4, Michael Bayer wrote: if you're on 0.8 this is all available via the various getters on mapper: http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.attrs On May 16, 2013, at 1:37 PM, Jonathan Vanasco jona...@findmeon.comjavascript: wrote: I've been using a utility method 'columns_as_dict' to help store my data in a cache. It works well. A problem I've encountered... i need to access the related data that i've eagerloaded. My current function looks like this def columns_as_dict(self): as_dict = dict( (col.name, getattr(self, col.name)) for col in sqlalchemy_orm.class_mapper(self.__class__).mapped_table.c ) return *as_dict* * * any idea how i can access the foreign relations ? i'm specifically interested in ones that are already eagerloaded, but am also interested in accessing all of the relations in general. -- 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. -- 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] Relationship configuration that isn't functioning as I expect
I am trying to create a relationship to work with a legacy schema and am
having trouble configuring it to behave as I want. As a disclaimer, I
understand why the schema here is not the schema one would use if starting
from scratch.
Anyway, I have simplied the situation down to the following example:
I have users and have two different types of objects that users can be
related to. For this example, those are street addresses and email
addresses. Rather than having a secondary table for each relationship,
there is a single secondary table. It has a foreign key column that will
either function as a foreign key to the street addresses table or the email
addresses table. There is a discriminator column that indicates which of
those tables is being referenced. The secondary table has its own primary
key and has an associated mapped class. The problem I am trying to solve is
setting up a relationship between the secondary table and the address
tables.
This is what I have ended up with:
class Affiliation(Base):
__tablename__ = 'affiliations'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
address_type = Column(String, nullable=False)
address_id = Column(Integer, nullable=False)
user = relationship(User, backref='affiliations')
email_address = relationship(
'EmailAddress',
primaryjoin=and_(address_type == 'email',
address_id == EmailAddress.id),
foreign_keys=[address_id],
viewonly=True)
street_address = relationship(
'StreetAddress',
primaryjoin=and_(address_type == 'street',
address_id == StreetAddress.id),
foreign_keys=[address_id],
viewonly=True)
The users, email_addresses and street_addresses tables/ classes are trivial
so I am omitting them from this post, but I have a full working example
here:
https://gist.github.com/bobbyi/5593984
For a given affiliation, I want affiliation.email_address to be
the appropriate email address if this affiliation's type is 'email'.
Otherwise, it should be None.
Given the following:
affiliation = session.query(Affiliation).get(1)
print affiliation.email_address
The SQL generated by the second line is:
SELECT email_addresses.id AS email_addresses_id, email_addresses.address AS
email_addresses_address
FROM email_addresses, affiliations
WHERE affiliations.address_type = 'email' AND 1 = email_addresses.id
Note that this is a cartesian join.
I hoped to end up with something like:
SELECT email_addresses.id AS email_addresses_id, email_addresses.address AS
email_addresses_address
FROM email_addresses, affiliations
WHERE affiliations.address_type = 'email' AND affiliations.address_id =
email_addresses.id AND affiliations.id = 1
Two questions:
1) Is the behavior I am seeing here expected given my relationship
configuration?
2) How can I configure my relationship to exhibit the desired behavior in
the situation shown above?
--
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] EAV Optimizations
I believe it was for some work you did for Akiban. If I remember correctly (ha!) I believe you detailed with some slides an updated join strategy that they are using (or that you hacked) into a custom version of SA used by them. I noted the demo as I am planning on using EAV for a personal project and was wondering if that updated EAV join strategy code would make it into the released version of SA. From your slides I think you showed that the number of joins on the EAV tables with the updated strategy was significantly reduced. Sorry I can't remember more. As a side note EAV is not a panacea but I have used it to great effect in the past judiciously. In the application in question we had millions of digital assets in effectively a proprietary photo library used by Sony. All of the data for the application except the binary asset was stored in the DB (Oracle in this case). The requirement for the project was that the users could also annotate their assets with security descriptors, groups, and any other typed metadata they wished, including links to other assets to create packages of assets. The metadata key/value count was going to be in the billions of facts. As such we made the decision to make the entire of the metadata schema EAV including internal metadata (security, stock keys such as create date, name, path, etc). By putting all of the metadata in EAV we were able to also employ such niceties as indexes for speed and table level constraints (as well as triggers for certain keys and other business logic in PL/SQL). The EAV method though did add some complexity to the application queries which we abstracted away by using some Ruby ORM libs that could work natively with EAV schemas hiding most of the complexity of the schema implementation from the application engineers. Over all the project was a big success and I believe is still in use with many billions of records, all without requiring the use of a secondary key/value document store with differing query semantics and iffy indexing and constraint enforcement. I wouldn't use EAVs for everything, not by a long shot, but properly implemented and indexed they work great in my practical experience. Mike On Monday, May 13, 2013 5:26:01 PM UTC-7, Michael Bayer wrote: what video is that ?I don't use EAV's too often. If you can point me to something I can identify what it was. On May 13, 2013, at 8:22 PM, Lycovian mfwi...@gmail.com javascript: wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. 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+...@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. -- 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] EAV Optimizations
oh, yeah I don't have that code, that was Ori talking about a particular client integration they had, where they went bananas with EAV. The point there was to show how Akiban does a much better job querying structures like that, but I had no involvement in that gig. The EAV stuff I have, you can look in examples/vertical/ for a few old ones, then there's the http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedMap recipe which is more or less like the one I use in my current work, that's about it. As for use case, I'm all in favor of judicious use of almost anything. I don't respond well to the never do XYZ! thing, I feel like there should be a, * unless you have more than 10 years of experience clause (see http://norvig.com/21-days.html for background). I use, to a greater or lesser extent, all the baddies: global variables, mixins, multiple inheritance, EAVs, explicit locking, ORM queries in my controllers, all of it, if it's been called bad I've probably got it in production in a very judicious and pragmatic way, without problems. On May 16, 2013, at 3:48 PM, Lycovian mfwil...@gmail.com wrote: I believe it was for some work you did for Akiban. If I remember correctly (ha!) I believe you detailed with some slides an updated join strategy that they are using (or that you hacked) into a custom version of SA used by them. I noted the demo as I am planning on using EAV for a personal project and was wondering if that updated EAV join strategy code would make it into the released version of SA. From your slides I think you showed that the number of joins on the EAV tables with the updated strategy was significantly reduced. Sorry I can't remember more. As a side note EAV is not a panacea but I have used it to great effect in the past judiciously. In the application in question we had millions of digital assets in effectively a proprietary photo library used by Sony. All of the data for the application except the binary asset was stored in the DB (Oracle in this case). The requirement for the project was that the users could also annotate their assets with security descriptors, groups, and any other typed metadata they wished, including links to other assets to create packages of assets. The metadata key/value count was going to be in the billions of facts. As such we made the decision to make the entire of the metadata schema EAV including internal metadata (security, stock keys such as create date, name, path, etc). By putting all of the metadata in EAV we were able to also employ such niceties as indexes for speed and table level constraints (as well as triggers for certain keys and other business logic in PL/SQL). The EAV method though did add some complexity to the application queries which we abstracted away by using some Ruby ORM libs that could work natively with EAV schemas hiding most of the complexity of the schema implementation from the application engineers. Over all the project was a big success and I believe is still in use with many billions of records, all without requiring the use of a secondary key/value document store with differing query semantics and iffy indexing and constraint enforcement. I wouldn't use EAVs for everything, not by a long shot, but properly implemented and indexed they work great in my practical experience. Mike On Monday, May 13, 2013 5:26:01 PM UTC-7, Michael Bayer wrote: what video is that ?I don't use EAV's too often. If you can point me to something I can identify what it was. On May 13, 2013, at 8:22 PM, Lycovian mfwi...@gmail.com wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. 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+...@googlegroups.com. To post to this group, send email to sqlal...@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
Re: [sqlalchemy] EAV Optimizations
On 05/14/2013 16:58, Michael Bayer wrote: When you are storing data with key/values, where the set of keys is part of the data. Storing configurational data is the main use case.HSTORE not an option because it is postgresql-specific. Yes it may be acceptable to store configurational data (although I would probably use HSTORE under PostgreSQL). But a large EAV based project sounds terribly bad to me. On May 14, 2013, at 10:49 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 16:09, Michael Bayer wrote: EAVs have a definite place and with judicious use they are extremely useful. just curious: could you give an example where EAV is useful ? On May 14, 2013, at 4:37 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 02:22, Lycovian wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. please don't ... EAV is evil and has tons of disadvantages. I don't know about your database, but in PostgreSQL you have HSTORE and JSON which could often be used to replace EAV 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. -- 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. -- 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. -- 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] EAV Optimizations
On May 16, 2013, at 4:07 PM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 16:58, Michael Bayer wrote: When you are storing data with key/values, where the set of keys is part of the data. Storing configurational data is the main use case.HSTORE not an option because it is postgresql-specific. Yes it may be acceptable to store configurational data (although I would probably use HSTORE under PostgreSQL). But a large EAV based project sounds terribly bad to me. I agree, I've seen lots of projects where they base it all on EAV, and it's a terrible idea. But I was lucky to get my everything-is-an-EAV thing done with by about 1998. On May 14, 2013, at 10:49 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 16:09, Michael Bayer wrote: EAVs have a definite place and with judicious use they are extremely useful. just curious: could you give an example where EAV is useful ? On May 14, 2013, at 4:37 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 02:22, Lycovian wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. please don't ... EAV is evil and has tons of disadvantages. I don't know about your database, but in PostgreSQL you have HSTORE and JSON which could often be used to replace EAV 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. -- 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. -- 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. -- 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] Re: SQLServer + Datetimes returning odd results?
I think this value is coming from the underlying database driver rather
than SQLAlchemy. If you execute the stored proc directly using the driver
(I guess this is pymssql), do you see the same behavior?
On Thursday, May 16, 2013 12:37:56 PM UTC-7, John Anderson wrote:
I have a SQLServer DB with a table that has a column as datetime and its
default value is `getutcdate()` (on the server).
We are using stored procedures and are running DBSession.execute('sproc')
and to do a select on the table and the rows that are returned have weird
datetime values:
datetime.datetime(2013, 16, 136, 29, 231, 4, 3888128)
It seems be be completely missing its month. In the db the value is:
2013-05-16 19:29:29.487
Any ideas what might cause this?
It is currently using SQLAlchemy 0.7.6
--
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] EAV Optimizations
On 16 May 2013, at 21:21, Michael Bayer mike...@zzzcomputing.com wrote: On May 16, 2013, at 4:07 PM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 16:58, Michael Bayer wrote: When you are storing data with key/values, where the set of keys is part of the data. Storing configurational data is the main use case.HSTORE not an option because it is postgresql-specific. Yes it may be acceptable to store configurational data (although I would probably use HSTORE under PostgreSQL). But a large EAV based project sounds terribly bad to me. I agree, I've seen lots of projects where they base it all on EAV, and it's a terrible idea. But I was lucky to get my everything-is-an-EAV thing done with by about 1998. I have a project at the moment where I'm storing manufacturing test results. Each test is basically a big set of key/value pairs. The set of keys changes over time (as new tests are written), and one of the requirements for the database is that users be able to efficiently query specific values (for example to view trends over time). My first attempt (written in 2006, using SQLAlchemy 0.3, and still running!) had a table for each test type, with columns for each measurement. The schema was tightly coupled to the test results, and every time a test was altered or added, I would have to change the code and alter the database to match. This is painful enough that I don't do it very often, so there are an increasing number of measurements which aren't stored in the database. I've recently revisited the system, and had a go at using an EAV approach. So far I think I am approaching 100,000,000 values. I thought this might be a bit much for a single table, so I've split them across a number of tables, based on a hash of the key name. I'm not sure yet if that was a good idea, but it means I've got 256 tables with between 0 and 4,000,000 rows in. The tables have 3 columns, 1 for the actual value, one is a foreign key to say which test the value belongs to, and the other is a foreign key to the table describing the key. Currently there are about 32000 distinct keys. I don't think this would be a good match for HSTORE - I assume that retrieving values from HSTORE won't be as efficient as retrieving them from their own rows. The new system is definitely much more flexible than the old, and there is a lot less code to maintain. I'm not sure about the performance yet - some of the queries feel slower than I think they should, but I haven't looked into it properly yet. Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] textcolumn.contains() escaping, the lack of icontains(), and possible bug?
Sounds like a useful feature.
Regarding case sensitivity, perhaps it would better if each of these
methods (even like() and contains()) took a keyword argument along the
lines of col.endswith('foo', case_sensitive=False) rather than adding extra
methods with weird names like iendswith.
On Monday, May 13, 2013 3:44:38 PM UTC-7, Michael Bayer wrote:
On May 13, 2013, at 6:30 PM, Daniel Grace thisgen...@gmail.comjavascript:
wrote:
So today I identified a small bug in my code and then, while trying to
resolve it, came to a few realizations:
1. column.contains(str) does not escape characters in str such as % and _.
Presumably, column.startswith(str) and column.endswith(str) have the same
behavior.
this will be called autoescape and is ticket 2694:
http://www.sqlalchemy.org/trac/ticket/2694 .if someone wants to work
on a patch for this it would be v. helpful. It's a little late to turn on
the escaping for all users now as it would break existing workarounds.
2. There is a distinct lack of column.icontains(str), though the current
implementation means it's identical to column.ilike('%' + str + '%')
since we do have ilike() as an operator icontains() would be
appropriate at this point (also startswith,endswith).
3. There is no builtin function (that I found, please correct me if I'm
wrong!) for escaping a string being passed to any functions in this family.
will be 2694
While I think that column.like and column.ilike should definitely /not/
escape their argument (you know you're trying for a pattern match here, and
that you're matching against a pattern), I think that the
.contains/.startswith/.endswith family of functions probably should perform
this escaping transparently. Between DBAPI 2.0, SQLAlchemy and
parameterized querying I don't need to worry about escaping input, so why
should I have to pay attention to that detail when using .contains? Also,
case insensitive versions of the above would probably be useful.
That said, a proper fix might be complicated since it could inadvertently
break existing code that relies on the current behavior of .contains()
-- Daniel
--
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.
--
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] EAV Optimizations
On Thu, May 16, 2013 at 7:16 PM, Simon King si...@simonking.org.uk wrote: I don't think this would be a good match for HSTORE - I assume that retrieving values from HSTORE won't be as efficient as retrieving them from their own rows. It depends on the number of attributes per test. It won't ever be as efficient the access itself, but it might compensate the overhead having one row per attribute. Furthermore, you can create functional indexes to index over a particularly interesting attribute, and with partial indexes you can even filter, which means, it could be pretty fast if there's no need to run arbitrary queries. I don't think trends over time would be a problem for an HSTORE-based schema, since you'd already pay the cost of sequential scan anyway. This is all assuming there's a limited and rather small number of attributes per test (dozens?). Otherwise EAV is probably better. -- 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] textcolumn.contains() escaping, the lack of icontains(), and possible bug?
On May 16, 2013, at 6:22 PM, Bobby Impollonia bob...@gmail.com wrote:
Sounds like a useful feature.
Regarding case sensitivity, perhaps it would better if each of these methods
(even like() and contains()) took a keyword argument along the lines of
col.endswith('foo', case_sensitive=False) rather than adding extra methods
with weird names like iendswith.
yes I've thought of that, though we already have ilike() for like(), as
this is a well known Postgresql operator, so I fear this might introduce some
inconsistency. We'd really need to add case_sensitive to like() as well and
everywhere, and just have ilike() be a synonym for like(...,
case_sensitive=False).
Additionally, case_sensitive might need to default to None - in its absence,
technically we don't know if the underlying database is doing case sensitive
comparison or not. Setting it to True means, we will use known
case-insensitive techniques for the target backend.
But then what do we do on a backend that doesn't have ilike? do we raise an
error? what if some backend already has case-insensitive collation set up ?
On Monday, May 13, 2013 3:44:38 PM UTC-7, Michael Bayer wrote:
On May 13, 2013, at 6:30 PM, Daniel Grace thisgen...@gmail.com wrote:
So today I identified a small bug in my code and then, while trying to
resolve it, came to a few realizations:
1. column.contains(str) does not escape characters in str such as % and _.
Presumably, column.startswith(str) and column.endswith(str) have the same
behavior.
this will be called autoescape and is ticket 2694:
http://www.sqlalchemy.org/trac/ticket/2694 .if someone wants to work on a
patch for this it would be v. helpful. It's a little late to turn on the
escaping for all users now as it would break existing workarounds.
2. There is a distinct lack of column.icontains(str), though the current
implementation means it's identical to column.ilike('%' + str + '%')
since we do have ilike() as an operator icontains() would be appropriate
at this point (also startswith,endswith).
3. There is no builtin function (that I found, please correct me if I'm
wrong!) for escaping a string being passed to any functions in this family.
will be 2694
While I think that column.like and column.ilike should definitely /not/
escape their argument (you know you're trying for a pattern match here, and
that you're matching against a pattern), I think that the
.contains/.startswith/.endswith family of functions probably should perform
this escaping transparently. Between DBAPI 2.0, SQLAlchemy and
parameterized querying I don't need to worry about escaping input, so why
should I have to pay attention to that detail when using .contains? Also,
case insensitive versions of the above would probably be useful.
That said, a proper fix might be complicated since it could inadvertently
break existing code that relies on the current behavior of .contains()
-- Daniel
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?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] passing additional values to association_proxy's 'creator' method
I have a User model, and a Group model, and a m2m relation between them.
I also have a proxy on the user that routes directly to the Group
instances. The creator function, however, only takes one param (group), so
when I pass in a group, I have no way to look it up to see if the (user_id,
group_id) combo is in use already, because i only have the group. It seems
like I am probably missing something very simple, but I can't figure out
what it is. The code below can be run to show the issue I am having.
The last line, user.groups = [group] , throws an error because 'self' isn't
defined. self clearly doesn't work here, what would the workaround be to
get the User instance's id value into that creator function? The group I
am passing in is the same as the group currently assigned, so rather than
purging the collection and adding a UserGroup with only a group_id (the
user_id gets populated later via the relationship, but at that point it is
too late, because it's already decided the new object is not the same as
the old, and it purges the old one), i'd like to be able to identify that
the identity_key is the same, and just let it be.
Code below##
from sqlalchemy import *
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relationship, backref
e = create_engine('sqlite:tmp/test.db', echo=False)
Base = declarative_base()
Base.metadata = MetaData(e)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
def usergroup_creator(group):
session = Session(e)
ugroup = session.query(UserGroup) \
.filter_by(user_id=self.id) \
.filter_by(group_id=group.id) \
.first()
if not ugroup:
return UserGroup(group=group)
groups = association_proxy('user_groups', 'group',
creator=usergroup_creator)
class Group(Base):
__tablename__ = 'groups'
id = Column(Integer, primary_key=True)
class UserGroup(Base):
__tablename__ = 'user_groups'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
group_id = Column(Integer, ForeignKey(Group.id), primary_key=True)
user = relationship(User, backref='user_groups')
group = relationship(Group, backref='user_groups')
if __name__ == '__main__':
Base.metadata.drop_all()
Base.metadata.create_all()
user = User(id=1)
group = Group(id=1)
ugroup = UserGroup(user=user, group=group)
session = Session(e)
session.add(user)
session.add(group)
session.add(ugroup)
session.commit()
session.expunge_all()
user = session.query(User).get(1)
group = session.query(Group).get(1)
user.groups = [group]
--
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] Relationship configuration that isn't functioning as I expect
That makes sense. I am seeing one problem with this approach and it causes
the asserts in original gist to still fail. The problem is that the
identity map remembers that I constructed my objects as instances of the
parent class even across commit and expire boundaries. So the following
assert fails:
street_affiliation = Affiliation(user=user, address_type='street',
address_id=1)
session.add(street_affiliation)
session.flush()
session.commit()
street_affiliation =
session.query(Affiliation).filter_by(address_type='street').one()
assert isinstance(street_affiliation, StreetAffiliation)
And as a result the asserts in the original gist still fail.
I understand why this happens (the identity map uses weakrefs but there is
a still a reference to the old object of type Affiliation) and how to work
around it (delete the reference to the old affiliation before querying or
construct the affiliation object as a StreetAffiliation instead of as an
Affiliation).
It is still somewhat surprising since I am used to assuming that once a
commit/ expire_all has happened, fetching from the database will give the
right results, but in this case the affiliation I fetch after the
flush incorrectly tells me that its street_address is None.
However, I don't see this being a problem in production and I think this
will work for our application. Thanks.
On Thursday, May 16, 2013 12:49:17 PM UTC-7, Michael Bayer wrote:
On May 16, 2013, at 3:02 PM, Bobby Impollonia
bob...@gmail.comjavascript:
wrote:
I hoped to end up with something like:
SELECT email_addresses.id AS email_addresses_id,
email_addresses.address AS email_addresses_address
FROM email_addresses, affiliations
WHERE affiliations.address_type = 'email' AND affiliations.address_id =
email_addresses.id AND affiliations.id = 1
Two questions:
1) Is the behavior I am seeing here expected given my relationship
configuration?
2) How can I configure my relationship to exhibit the desired behavior
in the situation shown above?
Ok well lets think of it in terms of joins. What if you wanted to load
all the affiliations and EmailAddresses together? the join would be:
select * from affiliations JOIN email_addresses ON affiliations.address_id=
email_addresses.id AND affiliations.address_type='email'
above, there's not really a space for AND affiliations.id = ...something
?, unless maybe if it were equated to itself.
So maybe, this kind of thing would be possible if you could say:
email_address = relationship(
'EmailAddress',
primaryjoin=and_(address_type == 'email',
id == lazy(id),
address_id == EmailAddress.id),
foreign_keys=[address_id],
viewonly=True)
the lazy load would need to figure out to set up a bind for one of the
slot there (that's the hypothetical lazy() annotation). its funny we're
a lot closer to that sort of thing, since we do have situations where we
have things like column == remote(column) now, but not quite in that
arrangement.
But I don't think we need to get into any of that here since your class
has a discriminator anyway, we can just use inheritance so that your
different Affiliation objects know what to do, see below.
#!/usr/bin/env python
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine,
and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref,
contains_eager, remote
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
class StreetAddress(Base):
__tablename__ = 'steet_addresses'
id = Column(Integer, primary_key=True)
address = Column(String, nullable=False)
class EmailAddress(Base):
__tablename__ = 'email_addresses'
id = Column(Integer, primary_key=True)
address = Column(String, nullable=False)
class Affiliation(Base):
__tablename__ = 'affiliations'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'), nullable = False)
address_type = Column(String, nullable=False)
address_id = Column(Integer, nullable=False)
user = relationship(User, backref='affiliations')
street_address = None
email_address = None
__mapper_args__ = dict(polymorphic_on=address_type)
class EmailAffiliation(Affiliation):
email_address = relationship(
'EmailAddress',
primaryjoin=Affiliation.address_id == EmailAddress.id,
foreign_keys=[Affiliation.address_id],
viewonly=True)
__mapper_args__ = dict(polymorphic_identity='email')
class StreetAffiliation(Affiliation):
street_address = relationship(
Re: [sqlalchemy] Relationship configuration that isn't functioning as I expect
On May 16, 2013, at 9:17 PM, Bobby Impollonia bob...@gmail.com wrote: That makes sense. I am seeing one problem with this approach and it causes the asserts in original gist to still fail. The problem is that the identity map remembers that I constructed my objects as instances of the parent class even across commit and expire boundaries. So the following assert fails: street_affiliation = Affiliation(user=user, address_type='street', address_id=1) right well you don't do that anymore ;) make it StreetAffiliation. Or use a factory method like Affiliation.create(type=street), or add a special __new__ to Affiliation to do the same thing. I understand why this happens (the identity map uses weakrefs but there is a still a reference to the old object of type Affiliation) and how to work around it (delete the reference to the old affiliation before querying or construct the affiliation object as a StreetAffiliation instead of as an Affiliation). It is still somewhat surprising since I am used to assuming that once a commit/ expire_all has happened, fetching from the database will give the right results, but in this case the affiliation I fetch after the flush incorrectly tells me that its street_address is None. the expire removes the contents of __dict__ of all the objects, but not the objects themselves, which means their type is also preserved in the session. The objects will fall out of the session if you lose all references to them on the outside, but that is also assuming they are garbage collected which won't necessarily be immediate if there are any cycles. -- 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.
