Re: [sqlalchemy] polymorphic inheritance and unique constraints
sorry, i mean i couldn't test it earlier, when i first asked the question :) it was not another co-worker, lol. cheers, richard. On 04/13/2015 06:30 PM, Mike Bayer wrote: On 4/13/15 4:59 PM, Richard Gerd Kuesters | Pollux Automation wrote: well, this didn't work with upstream 1.0 - sorry, I was in another project and couldn't test it myself. you're not doing the same thing this user was doing in any case... Traceback (most recent call last): File database_test.py, line 46, in module from plx.db.core import * File ../src/plx/db/core.py, line 901, in module UniqueConstraint(ContainerInstance.batch_id, ContainerAggregation.container_descriptor_id,) File /home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py, line 2464, in __init__ ColumnCollectionMixin.__init__(self, *columns, _autoattach=_autoattach) File /home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py, line 2393, in __init__ self._check_attach() File /home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py, line 2429, in _check_attach table.description) sqlalchemy.exc.ArgumentError: Column(s) 'container_aggregation.fk_container_descriptor_id' are not part of table 'container_instance'. I got sqlalchemy from git, today. sqlalchemy.__version__ '1.0.0' container_aggretation is a subclass of container_instance. I'm not using concrete inheritance here, may this be the problem? anything else, it's Python 2.7.9 + Linux + PostgreSQL 9.4.1. cheers, richard. On 03/24/2015 08:49 PM, Michael Bayer wrote: are these two separate constraints? I just looked and it seems like they are distinct. I just added a fix to 1.0 because someone was hacking around something similar to this. The easiest way to get these for the moment is just to create the UniqueConstraint outside of the class definition. class Foo(Base): # … class Bar(Foo): # … UniqueConstraint(Bar.x, Foo.y) that way all the columns are set up, should just work. Richard Gerd Kuesters | Polluxrich...@pollux.com.br wrote: well, understanding better the docs for column conflicts, can i use a declared_attr in a unique constraint? if yes, my problem is solved :) On 03/24/2015 10:33 AM, Michael Bayer wrote: Richard Gerd Kuesters | Pollux rich...@pollux.com.br wrote: hi all! i'm dealing with a little problem here. i have a parent table and its two inheritances. there is a value that both children have and must be unique along either types. is there a way to move this column to the parent and use a constraint in the child? my implementation is postgres 9.4+ with psycopg2 only. if this is single table inheritance then the constraint would most ideally be placed on the parent class. if you’re trying to make this “magic” such that you can semantically keep the unique constraints on the child classes, you’d need to build out a conditional approach within @declared_attr. IMO I think this is an idealized edge case that in the real world doesn’t matter much - just do what works (put the col / constraint on the base). the approach is described at http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/inheritance.html#resolving-column-conflicts . You’d need to make this work for both the column and the constraint. as a simple example (i'm just creating this example to simplify things), this works: class MyParent(Base): foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True) foo_name = Column(Unicode(64), nullable=False) foo_type = Column(Integer, nullable=False) __mapper_args__ = { polymorphic_on: foo_type, polymorphic_identity: 0 } class MyChild1(MyParent): foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True) bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False) child1_specific_name = Column(Unicode(5), nullable=False) child1_baz_stuff = Column(Boolean, default=False) __mapper_args__ = { polymorphic_identity: 1 } __table_args__ = ( UniqueConstraint(bar_id, child1_specific_name,), # works, bar_id is in MyChild1 ) class MyChild2(MyParent): foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True) bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False) child2_specific_code = Column(UUID, nullable=False) child2_baz_stuff = Column(Float, nullable=False) __mapper_args__ = { polymorphic_identity: 2 } __table_args__ = ( UniqueConstraint(bar_id, child2_specific_code,), # works, bar_id is in MyChild2 ) but i would like to do this, if possible: class MyParent(Base): foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True) foo_name = Column(Unicode(64
Re: [sqlalchemy] polymorphic inheritance and unique constraints
well, this didn't work with upstream 1.0 - sorry, I was in another project and couldn't test it myself. Traceback (most recent call last): File database_test.py, line 46, in module from plx.db.core import * File ../src/plx/db/core.py, line 901, in module UniqueConstraint(ContainerInstance.batch_id, ContainerAggregation.container_descriptor_id,) File /home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py, line 2464, in __init__ ColumnCollectionMixin.__init__(self, *columns, _autoattach=_autoattach) File /home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py, line 2393, in __init__ self._check_attach() File /home/richard/.pyenv/versions/vpak-pollux-2.7.9/lib/python2.7/site-packages/sqlalchemy/sql/schema.py, line 2429, in _check_attach table.description) sqlalchemy.exc.ArgumentError: Column(s) 'container_aggregation.fk_container_descriptor_id' are not part of table 'container_instance'. I got sqlalchemy from git, today. sqlalchemy.__version__ '1.0.0' container_aggretation is a subclass of container_instance. I'm not using concrete inheritance here, may this be the problem? anything else, it's Python 2.7.9 + Linux + PostgreSQL 9.4.1. cheers, richard. On 03/24/2015 08:49 PM, Michael Bayer wrote: are these two separate constraints? I just looked and it seems like they are distinct. I just added a fix to 1.0 because someone was hacking around something similar to this. The easiest way to get these for the moment is just to create the UniqueConstraint outside of the class definition. class Foo(Base): # … class Bar(Foo): # … UniqueConstraint(Bar.x, Foo.y) that way all the columns are set up, should just work. Richard Gerd Kuesters | Pollux rich...@pollux.com.br wrote: well, understanding better the docs for column conflicts, can i use a declared_attr in a unique constraint? if yes, my problem is solved :) On 03/24/2015 10:33 AM, Michael Bayer wrote: Richard Gerd Kuesters | Pollux rich...@pollux.com.br wrote: hi all! i'm dealing with a little problem here. i have a parent table and its two inheritances. there is a value that both children have and must be unique along either types. is there a way to move this column to the parent and use a constraint in the child? my implementation is postgres 9.4+ with psycopg2 only. if this is single table inheritance then the constraint would most ideally be placed on the parent class. if you’re trying to make this “magic” such that you can semantically keep the unique constraints on the child classes, you’d need to build out a conditional approach within @declared_attr. IMO I think this is an idealized edge case that in the real world doesn’t matter much - just do what works (put the col / constraint on the base). the approach is described at http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/inheritance.html#resolving-column-conflicts . You’d need to make this work for both the column and the constraint. as a simple example (i'm just creating this example to simplify things), this works: class MyParent(Base): foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True) foo_name = Column(Unicode(64), nullable=False) foo_type = Column(Integer, nullable=False) __mapper_args__ = { polymorphic_on: foo_type, polymorphic_identity: 0 } class MyChild1(MyParent): foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True) bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False) child1_specific_name = Column(Unicode(5), nullable=False) child1_baz_stuff = Column(Boolean, default=False) __mapper_args__ = { polymorphic_identity: 1 } __table_args__ = ( UniqueConstraint(bar_id, child1_specific_name,), # works, bar_id is in MyChild1 ) class MyChild2(MyParent): foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True) bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False) child2_specific_code = Column(UUID, nullable=False) child2_baz_stuff = Column(Float, nullable=False) __mapper_args__ = { polymorphic_identity: 2 } __table_args__ = ( UniqueConstraint(bar_id, child2_specific_code,), # works, bar_id is in MyChild2 ) but i would like to do this, if possible: class MyParent(Base): foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True) foo_name = Column(Unicode(64), nullable=False) foo_type = Column(Integer, nullable=False) bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False) # since both child uses bar_id, why not having it on the parent? __mapper_args__ = { polymorphic_on: foo_type, polymorphic_identity: 0 } class
Re: [sqlalchemy] Any recommended approach for creating a new dialect?
Mike, I remember an article of yours where you described much of the process of creating a new dialect for SA, for a Java database if I'm not mistaken. I wasn't able to find it, though. :) On 04/08/2015 01:19 AM, Mike Bayer wrote: On 4/7/15 1:59 PM, Ralph Heinkel wrote: Hello dialect experts, what would be the best approach for creating a SqlAlchemy dialect for a new database system? Are there any recipes available for this area, or is the way to go to read code of existing dialects and derive my own dialect from those? I had a first glance at some built-in dialects, and also some in external packages ... it is not always obvious to me why certain classes and methods have been implemented. The obvious thing is to create a subclass of sqlalchemy.enginedefault.DefaultDialect, but how would I know which methods and class attributes to override/implement, except for going through the trial and error approach? And then there are other classes which are implemented in some dialects, like compiler.DDLCompiler, compiler.GenericTypeCompiler, and so on ... where and how would I start best? Any help would be very much appreciated. Start with the README for new dialects: https://bitbucket.org/zzzeek/sqlalchemy/src/44a9820b4e02f65b3884fa2c016efce9663e4910/README.dialects.rst?at=master that will show the guidelines for writing new dialects. Then to see some examples of that layout, take a look at some of the 3rd party dialects listed at: http://docs.sqlalchemy.org/en/latest/dialects/index.html#production-ready Also the sqlalchemy-access dialect is basically something of a demo for the layout, which I basically put there after extracting it from SQLAlchemy main where it had been for many years. It might not be 100% up to date, but mostly follows that guideline and even passed tests at one point, that's at https://bitbucket.org/zzzeek/sqlalchemy-access. The key thing you'll be doing is running the suite tests, which will be part of the test suite within your own dialect. So yes, you start with a fairly plain subclass of DefaultDialect, then you probably want to get a hello world kind of program going where you just see if create_engine and then engine.execute(select * from table) work at all, and then the suite tests should test a lot more. Ciao ciao Ralph -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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. For more options, visit https://groups.google.com/d/optout.