[sqlalchemy] Re: Synchronization problem at backref of a one-to-many relation to the same class.
Michael Bayer schrieb: I had the idea that since a1 appears to be in the collections of both u1 and u2, it would be arbitrary where a1 ended up after the flush completed. But that is likely wrong, in that the flush() is going to look at change events to determine what state changes to persist to the database. I'm not sure if there's a more complex series of moves between u1 and u2 which would make predicting the final destination of a1 difficult, though. I still don't really understand why/how a flush operation would change the .addresses attribute of any object as a side effect. Maybe I just understand SA's unit-of-work implementation not good enough. -- Christoph --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] problem with like
Hi everybody, I m stuck with a query about telephone number : I want to find in my database all the contact who have a telephone number. The difficulty is that some number in the database can have space or . between numbers example : 06.06.50.44.11 or 45 87 12 45 65 This my query with like but it s not what i want because i ignore telephone number who have special chars NumeroApparu = 064544 s = session.query(Contact.IdContact, Contact.Civilite, Contact.Nom, Contact.Prenom, ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier).filter(ContactTel.Tel.like(NumeroApparu)) i saw *class *sqlalchemy.sql.expression.ColumnOperators¶http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/expressions.html?highlight=like#sqlalchemy.sql.expression.ColumnOperatorsin the doc but lack of explication... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Duck-typing style of relations
Greetings Alchemists,
this is more of a general data modeling question but maybe Alchemy has
a neat trick to resolve this issue.
It happens quite often that I want to use instances of a class as
attributes of unrelated objects. One example is Addresses. Both
Companies and Persons have addresses and it would be somewhat awkward
to derive both from a common ancestor, even though mixins would do the
trick. However, the concept of mixins is not straightforward to
transpose to data mapping. The Address example could be implemented
as follow:
class Address(DeclarativeBase):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
city = Column(Unicode(255))
postal_code = Column(Unicode(15))
# ...
class Company(DeclarativeBase):
__tablename__ = 'company'
address_id = Column(Integer, ForeignKey('address.id'))
address = relation(Address)
class Person(DeclarativeBase):
__tablename__ = 'person'
address_id = Column(Integer, ForeignKey('address.id'))
address = relation(Address)
One problem with that is that it's hard to prevent orphan addresses.
Another thing that may or may not be a problem is that you could have
the same address being used for more than one company or person, which
could lead to a funny situation if one of them moves.
To allow for more than one address per entity, one could do:
class Company(DeclarativeBase):
__tablename__ = 'company'
shipping_address_id = Column(Integer, ForeignKey('address.id'))
shipping_address = relation(Address, primary_join=...)
billing_address_id = Column(Integer, ForeignKey('address.id'))
billing_address = relation(Address, primary_join=...)
Similarly, an open ended number of addresses can be implemented with a
join table:
class CompanyAddress(DeclarativeBase):
__tablename__ = 'company_address'
address_id = Column(Integer, ForeignKey('address.id'))
address = relation(Address, primary_join=...)
company_id = Column(Integer, ForeignKey('company.id'))
company_address = relation(Company, primary_join=...)
class PersonAddress(DeclarativeBase):
__tablename__ = 'preson_address'
address_id = Column(Integer, ForeignKey('address.id'))
address = relation(Address, primary_join=...)
person_id = Column(Integer, ForeignKey('person.id'))
person_address = relation(Person, primary_join=...)
But we still have the problem of orphan addresses and it get somewhat
tricky to prevent addresses re-use.
It's easier to check for address re-use with a multi-slot join table:
class AddressMap(DeclarativeBase):
__tablename__ = 'address_map'
address_id = Column(Integer, ForeignKey('address.id'))
address = relation(Address, primary_join=...)
company_id = Column(Integer, ForeignKey('company.id'))
company_address = relation(Company, primary_join=...)
person_id = Column(Integer, ForeignKey('person.id'))
person_address = relation(Person, primary_join=...)
However, it's hard to extend when you need a new kind of objects that
has addresses.
Is there a better solution?
--
Yannick Gingras
http://ygingras.net
signature.asc
Description: This is a digitally signed message part.
[sqlalchemy] Re: Synchronization problem at backref of a one-to-many relation to the same class.
Christoph Zwerschke wrote: Michael Bayer schrieb: I had the idea that since a1 appears to be in the collections of both u1 and u2, it would be arbitrary where a1 ended up after the flush completed. But that is likely wrong, in that the flush() is going to look at change events to determine what state changes to persist to the database. I'm not sure if there's a more complex series of moves between u1 and u2 which would make predicting the final destination of a1 difficult, though. I still don't really understand why/how a flush operation would change the .addresses attribute of any object as a side effect. Maybe I just understand SA's unit-of-work implementation not good enough. meaning, the foreign key user_id on the address row would be populated with either u1's id, or u2's id, and sent to the database as an UPDATE. when the session's contents are expired and/or reloaded, the new relationship of a1 to either u1 or u2 would be expressed. So u1.addresses would not change until reloaded. -- Christoph --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Duck-typing style of relations
Yannick Gingras wrote:
Greetings Alchemists,
this is more of a general data modeling question but maybe Alchemy has
a neat trick to resolve this issue.
It happens quite often that I want to use instances of a class as
attributes of unrelated objects. One example is Addresses. Both
Companies and Persons have addresses and it would be somewhat awkward
to derive both from a common ancestor, even though mixins would do the
trick. However, the concept of mixins is not straightforward to
transpose to data mapping. The Address example could be implemented
as follow:
class Address(DeclarativeBase):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
city = Column(Unicode(255))
postal_code = Column(Unicode(15))
# ...
class Company(DeclarativeBase):
__tablename__ = 'company'
address_id = Column(Integer, ForeignKey('address.id'))
address = relation(Address)
class Person(DeclarativeBase):
__tablename__ = 'person'
address_id = Column(Integer, ForeignKey('address.id'))
address = relation(Address)
One problem with that is that it's hard to prevent orphan addresses.
Another thing that may or may not be a problem is that you could have
the same address being used for more than one company or person, which
could lead to a funny situation if one of them moves.
To allow for more than one address per entity, one could do:
class Company(DeclarativeBase):
__tablename__ = 'company'
shipping_address_id = Column(Integer, ForeignKey('address.id'))
shipping_address = relation(Address, primary_join=...)
billing_address_id = Column(Integer, ForeignKey('address.id'))
billing_address = relation(Address, primary_join=...)
Similarly, an open ended number of addresses can be implemented with a
join table:
class CompanyAddress(DeclarativeBase):
__tablename__ = 'company_address'
address_id = Column(Integer, ForeignKey('address.id'))
address = relation(Address, primary_join=...)
company_id = Column(Integer, ForeignKey('company.id'))
company_address = relation(Company, primary_join=...)
class PersonAddress(DeclarativeBase):
__tablename__ = 'preson_address'
address_id = Column(Integer, ForeignKey('address.id'))
address = relation(Address, primary_join=...)
person_id = Column(Integer, ForeignKey('person.id'))
person_address = relation(Person, primary_join=...)
But we still have the problem of orphan addresses and it get somewhat
tricky to prevent addresses re-use.
It's easier to check for address re-use with a multi-slot join table:
class AddressMap(DeclarativeBase):
__tablename__ = 'address_map'
address_id = Column(Integer, ForeignKey('address.id'))
address = relation(Address, primary_join=...)
company_id = Column(Integer, ForeignKey('company.id'))
company_address = relation(Company, primary_join=...)
person_id = Column(Integer, ForeignKey('person.id'))
person_address = relation(Person, primary_join=...)
However, it's hard to extend when you need a new kind of objects that
has addresses.
Is there a better solution?
AFAIK the only part of SQLAlchemy that looks similar to your use case is
concrete inheritance, but I believe even that will not address your
concerns about orphan addresses and shared addresses.
I think your best solution is similar to your AddressMap idea above, but
just make it part of Address instead with check and unique constraints
on your FK columns:
class Address(DeclarativeBase):
__tablename__ = 'address'
__table_args__ = (
CheckConstraint(CASE WHEN company_id IS NULL THEN 0 ELSE 1 END
+ CASE WHEN person_id IS NULL THEN 0 ELSE 1 END = 1),
UniqueConstraint(company_id),
UniqueConstraint(person_id))
# original columns go here...
company_id = Column(Integer, ForeignKey('company.id', ondelete='CASCADE'))
person_id = Column(Integer, ForeignKey('person.id', ondelete='CASCADE'))
The check constraint above is a bit overkill for just 2 FK columns (you
could just use (company_id IS NULL) (person_id IS NULL), but it is
easy to extend to 2 FK columns.
Hope it helps,
-Conor
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Duck-typing style of relations
On October 9, 2009, Conor wrote: I think your best solution is similar to your AddressMap idea above, but just make it part of Address instead with check and unique constraints on your FK columns: [...] The check constraint above is a bit overkill for just 2 FK columns (you could just use (company_id IS NULL) (person_id IS NULL), but it is easy to extend to 2 FK columns. Thats a very nice solution indeed. Thanks! -- Yannick Gingras http://ygingras.net signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Duck-typing style of relations
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Yannick Gingras Sent: 09 October 2009 14:43 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Duck-typing style of relations Greetings Alchemists, this is more of a general data modeling question but maybe Alchemy has a neat trick to resolve this issue. It happens quite often that I want to use instances of a class as attributes of unrelated objects. One example is Addresses. Both Companies and Persons have addresses and it would be somewhat awkward to derive both from a common ancestor, even though mixins would do the trick. However, the concept of mixins is not straightforward to transpose to data mapping. The Address example could be implemented as follow: [snip] Mike wrote a blog post about this a while ago: http://techspot.zzzeek.org/?p=13 It even uses Addresses as the example. It's quite old, so some of the syntax will probably need updating, but the basic idea should still hold. Hope that helps, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: postgresql CREATE SCHEMA statement does not create schema
Confirmed by Alex Grönholm on #postgresql on freenode with pg 8.3 and sqla 0.5.6. If this is not a bug, i'd like to know what is going on. Typing the text in gq directly into psql (all on one line) produces the schema foo as expected. Regards, Faheem. btsnp=# \dn List of schemas Name| Owner +-- information_schema | postgres pg_catalog | postgres pg_toast | postgres pg_toast_temp_1| postgres public | postgres (5 rows) btsnp=# SET search_path TO public; DROP SCHEMA IF EXISTS foo CASCADE; CREATE SCHEMA foo; SET NOTICE: schema foo does not exist, skipping DROP SCHEMA CREATE SCHEMA btsnp=# \dn List of schemas Name| Owner +-- foo| faheem information_schema | postgres pg_catalog | postgres pg_toast | postgres pg_toast_temp_1| postgres public | postgres (6 rows) On Wed, 7 Oct 2009, Faheem Mitha wrote: Hi, When running this function with postgresql 8.4 and sqla 0.5.5, def test(dbstring): from sqlalchemy import create_engine db = create_engine(dbstring) conn = db.connect() from sqlalchemy.sql import text gq = text( SET search_path TO public; DROP SCHEMA IF EXISTS foo CASCADE; CREATE SCHEMA foo; ) conn.execute(gq) conn.close() the schema foo is not created. However, removing the SET search_path TO public; line makes it work. This is not the case when issuing these commands directly via psql. Any idea what might be going on here? Regards, Faheem. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: postgresql CREATE SCHEMA statement does not create schema
did you commit your transaction or set autocommit=True in your text() statement ? that string you have will not trip off SQLA's autocommit feature. Faheem Mitha wrote: Confirmed by Alex Grönholm on #postgresql on freenode with pg 8.3 and sqla 0.5.6. If this is not a bug, i'd like to know what is going on. Typing the text in gq directly into psql (all on one line) produces the schema foo as expected. Regards, Faheem. btsnp=# \dn List of schemas Name| Owner +-- information_schema | postgres pg_catalog | postgres pg_toast | postgres pg_toast_temp_1| postgres public | postgres (5 rows) btsnp=# SET search_path TO public; DROP SCHEMA IF EXISTS foo CASCADE; CREATE SCHEMA foo; SET NOTICE: schema foo does not exist, skipping DROP SCHEMA CREATE SCHEMA btsnp=# \dn List of schemas Name| Owner +-- foo| faheem information_schema | postgres pg_catalog | postgres pg_toast | postgres pg_toast_temp_1| postgres public | postgres (6 rows) On Wed, 7 Oct 2009, Faheem Mitha wrote: Hi, When running this function with postgresql 8.4 and sqla 0.5.5, def test(dbstring): from sqlalchemy import create_engine db = create_engine(dbstring) conn = db.connect() from sqlalchemy.sql import text gq = text( SET search_path TO public; DROP SCHEMA IF EXISTS foo CASCADE; CREATE SCHEMA foo; ) conn.execute(gq) conn.close() the schema foo is not created. However, removing the SET search_path TO public; line makes it work. This is not the case when issuing these commands directly via psql. Any idea what might be going on here? Regards, Faheem. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Instance XXX is not persistent within this Session?
I have been using the following recipe to keep a unique string table in our database: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject After upgrading to 5.6, I started getting the following error Instance UniqueName XXX is not persistent within this Session 1. Does somebody have an updated recipe? 2. Is there a much better way to keep a table of unique ID or strings in the Database? Thanks Kris --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: problem with like
This is not really a sqlalchemy question, but the quick answer is that you need to convert both your indexed data and your queries to the same normal form. In your example, you appear to be correctly stripping spaces and periods in your query. If you haven't done that in the database, then you should do it there, too. If you need to preserve the original formatting of the telephone number column, then create another column that contains the stripped phone numbers. You probably also want to put an index on that column. Lastly, a minor note on the sample code: you appear to be missing a % operator in your LIKE query. Cheers, Andre On Fri, Oct 9, 2009 at 5:46 AM, Christian Démolis christiandemo...@gmail.com wrote: Hi everybody, I m stuck with a query about telephone number : I want to find in my database all the contact who have a telephone number. The difficulty is that some number in the database can have space or . between numbers example : 06.06.50.44.11 or 45 87 12 45 65 This my query with like but it s not what i want because i ignore telephone number who have special chars NumeroApparu = 064544 s = session.query(Contact.IdContact, Contact.Civilite, Contact.Nom, Contact.Prenom, ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier).filter(ContactTel.Tel.like(NumeroApparu)) i saw class sqlalchemy.sql.expression.ColumnOperators¶ in the doc but lack of explication... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: postgresql CREATE SCHEMA statement does not create schema
[This message has also been posted.] On Fri, 9 Oct 2009 13:28:58 -0400, Michael Bayer mike...@zzzcomputing.com wrote: did you commit your transaction or set autocommit=True in your text() statement ? that string you have will not trip off SQLA's autocommit feature. Hi, Thanks for the explanation. No, the code I used is exactly as written, modulo the db string. which was of the form postgres://dbuser:pas...@localhost:5432/dbname I see that the documentation for sqlalchemy.sql.expression.text has an option autocommit=True indicates this SELECT statement modifies the database, and should be subject to autocommit behavior if no transaction has been started. I missed this, since I assumed that the cursor object would have such a option, which does not appear to be the case. I've confirmed adding this option to the text string fixes the problem. Can you explain why removing the SET search_path TO public; string makes a commit happen? You also say that string you have will not trip off SQLA's autocommit feature. How does this autocommit feature work, and are there certain strings that will trigger an autocommit? Regards, Faheem. Faheem Mitha wrote: Confirmed by Alex Grönholm on #postgresql on freenode with pg 8.3 and sqla 0.5.6. If this is not a bug, i'd like to know what is going on. Typing the text in gq directly into psql (all on one line) produces the schema foo as expected. Regards, Faheem. btsnp=# \dn List of schemas Name| Owner +-- information_schema | postgres pg_catalog | postgres pg_toast | postgres pg_toast_temp_1| postgres public | postgres (5 rows) btsnp=# SET search_path TO public; DROP SCHEMA IF EXISTS foo CASCADE; CREATE SCHEMA foo; SET NOTICE: schema foo does not exist, skipping DROP SCHEMA CREATE SCHEMA btsnp=# \dn List of schemas Name| Owner +-- foo| faheem information_schema | postgres pg_catalog | postgres pg_toast | postgres pg_toast_temp_1| postgres public | postgres (6 rows) On Wed, 7 Oct 2009, Faheem Mitha wrote: Hi, When running this function with postgresql 8.4 and sqla 0.5.5, def test(dbstring): from sqlalchemy import create_engine db = create_engine(dbstring) conn = db.connect() from sqlalchemy.sql import text gq = text( SET search_path TO public; DROP SCHEMA IF EXISTS foo CASCADE; CREATE SCHEMA foo; ) conn.execute(gq) conn.close() the schema foo is not created. However, removing the SET search_path TO public; line makes it work. This is not the case when issuing these commands directly via psql. Any idea what might be going on here? Regards, Faheem. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: postgresql CREATE SCHEMA statement does not create schema
On Oct 9, 2009, at 7:35 PM, Faheem Mitha wrote: Can you explain why removing the SET search_path TO public; string makes a commit happen? You also say that string you have will not trip off SQLA's autocommit feature. How does this autocommit feature work, and are there certain strings that will trigger an autocommit? for textual statements, autocommit is applied when this regexp matches: AUTOCOMMIT_REGEXP = re.compile(r'\s*(?:UPDATE|INSERT|CREATE|DELETE| DROP|ALTER)', re.I | re.UNICODE) Regards, Faheem. Faheem Mitha wrote: Confirmed by Alex Grönholm on #postgresql on freenode with pg 8.3 and sqla 0.5.6. If this is not a bug, i'd like to know what is going on. Typing the text in gq directly into psql (all on one line) produces the schema foo as expected. Regards, Faheem. btsnp=# \dn List of schemas Name| Owner +-- information_schema | postgres pg_catalog | postgres pg_toast | postgres pg_toast_temp_1| postgres public | postgres (5 rows) btsnp=# SET search_path TO public; DROP SCHEMA IF EXISTS foo CASCADE; CREATE SCHEMA foo; SET NOTICE: schema foo does not exist, skipping DROP SCHEMA CREATE SCHEMA btsnp=# \dn List of schemas Name| Owner +-- foo| faheem information_schema | postgres pg_catalog | postgres pg_toast | postgres pg_toast_temp_1| postgres public | postgres (6 rows) On Wed, 7 Oct 2009, Faheem Mitha wrote: Hi, When running this function with postgresql 8.4 and sqla 0.5.5, def test(dbstring): from sqlalchemy import create_engine db = create_engine(dbstring) conn = db.connect() from sqlalchemy.sql import text gq = text( SET search_path TO public; DROP SCHEMA IF EXISTS foo CASCADE; CREATE SCHEMA foo; ) conn.execute(gq) conn.close() the schema foo is not created. However, removing the SET search_path TO public; line makes it work. This is not the case when issuing these commands directly via psql. Any idea what might be going on here? Regards, Faheem. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
