Re: Using alembic in a plugin pattern
On May 11, 2014, at 9:03 AM, Steeve C steevechaill...@gmail.com wrote: hi, I'm looking for writing plugins (stevedore) for my application, all plugins are new python modules and can create some entry|columns|table in the application database, I want to manage migration by plugins|modules is it possible using alembic? is there some documentation on that purpose? Currently, a single Alembic directory maintains a linear list of migrations, that is, A, B, C, D in a sequential pattern. So if the idea here is that you have multiple, independent apps with their own migration streams, at the moment a workaround is to maintain separate Alembic directories per app, and then use different version_table entries for each. This is a little verbose but it can be done using different named sections in alembic.ini, e.g.: [alembic_app1] version_table = app1_migration_version script_location = path/to/app1_migrations/ [alembic_app2] version_table = app2_migration_version script_location = path/to/app2_migrations/ ... then in env.py: context.configure( connection=connection, target_metadata=target_metadata, version_table=config.get_main_option(version_table) ) then when you run alembic: alembic upgrade head --name alembic_app2 For now, that's it. But later, a better approach will be available, when we will add support for multiple version directories and multiple independent branches. I hope to have funded support for these features within the next six months, see https://bitbucket.org/zzzeek/alembic/issues?status=newstatus=openmilestone=tier%201. -- You received this message because you are subscribed to the Google Groups sqlalchemy-alembic group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Emptying relationship records trigger update
Im just trying to empties a one to many records by doing this: load.commodities = [] then what happens next is: IntegrityError: (IntegrityError) null value in column load_id violates not-null constraint DETAIL: Failing row contains (35d39bd9-ca61-43ef-a5c5-7590c82aca1d, 2014-05-11 06:48:27.028485-04, 2014-05-11 06:51:41.511231-04, Product 4, , 4, 4, 4, 4, , a637cc84-fd6e-417c-bb59-5a4e3a435696, a8fca522-3b5f-429f-b58b-c0de4c05d725, null, , inch, lbs). 'UPDATE load_commodities SET updated_at=%(updated_at)s, load_id=%(load_id)s WHERE load_commodities.id = %(load_commodities_id)s' {'load_commodities_id': '35d39bd9-ca61-43ef-a5c5-7590c82aca1d', 'load_id': None, 'updated_at': datetime.datetime(2014, 5, 11, 6, 51, 41, 511231)} it seems like it triggers update and set all the fields to None instead of delete. here're my classes: class Load(Base): __tablename__ = 'loads' id = Column(GUID, primary_key=True, default=uuid.uuid4) commodities = relationship(LoadCommodity, backref=backref(load, uselist=False)) class LoadCommodity(Base): __tablename__ = 'load_commodities' id = Column(GUID, primary_key=True, default=uuid.uuid4) load_id = Column(GUID, ForeignKey('loads.id'), nullable=False) -- 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.
Re: [sqlalchemy] selects, mappers and foreign keys
thanks Mike! the problem is that the other side is also a selectable, so: foo = relationship(Remote, primaryjoin=myselect.c.foo == myotherselect.c.bar) myselect.c.foo *is* a foreign key to some table primary key that is the value of myotherselect.c.bar, but i can't figure out why or how to make foreign keys to be detected, basically because i'm trying to use some weird postgres queries using recursivity and connect paths, so in the end the error I always get is: /path/to/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc in _only_column_elements(element, name) 3348 raise exc.ArgumentError( 3349 Column-based expression object expected for argument - 3350 '%s'; got: '%s', type %s % (name, element, type(element))) 3351 return element 3352 ArgumentError: Column-based expression object expected for argument 'foreign_keys'; got: 'None', type type 'NoneType' perhaps i'm asking too much of everything? :) best regards, richard. Em 2014-05-11 00:01, Michael Bayer escreveu: On May 10, 2014, at 7:13 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: hi all! situation: i'm mapping a select as a class, using mapper. so far so good. problem: some of my selected columns *are* foreign keys in their respective tables, but i would like to say to sqla that they're foreign keys to another mapped class, which have the pks from which those fks are pointing. the first question is: how? or should be able to use relationship(), set up primaryjoin with foreign() foo = relationship(Remote, primaryjoin=myselect.c.foo == foreign(table.c.foo)) it's a little weird i guess, should work out in modern versions the second question: is there a way to inherit properties (like fks) OR cheat declaring foreign keys that doesn't exists at the database level ? sure, use ForeignKey() on your Column(), doesn't matter if it's not in the DB, or use in relationship foreign_keys / foreign() annotation my best regards, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Relationship setup
On May 11, 2014, at 12:27 AM, Joseph Casale jcas...@gmail.com wrote: What I wanted to know was if it was possible to construct either a table definition for TableB so that someone could simply pass in actual values of table_a.name to meta columns in table_b. For example if #3 above is not possible and table_a has been pre populated: table_a: id name -- 1 foo 2 bar 3 biz To populate table_b: data = [ TableB(name='foo'), TableB(name='bar') TableB(name='biz') ] session.add_all(data) Of course table_b has ~13 columns for which many combinations of values from all the intermediate tables will produce unique rows... I don't know about the ~13 columns part here but what you have is a combination of: 1. unique object recipe, https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject, which will give you TableA(somename) doing a create if not exists pattern, the ORM doesn't support all the various MERGE/INSERT OR REPLACE/OR IGNORE games that mostly MySQL plays so it's just a straight up SELECT 2. association proxy, http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html, so that you can link the mutation/getting of a plain scalar (string, int, etc.) attribute to that of a mapped object. def _unique(session, cls, hashfunc, queryfunc, constructor, arg, kw): cache = getattr(session, '_unique_cache', None) if cache is None: session._unique_cache = cache = {} key = (cls, hashfunc(*arg, **kw)) if key in cache: return cache[key] else: with session.no_autoflush: q = session.query(cls) q = queryfunc(q, *arg, **kw) obj = q.first() if not obj: obj = constructor(*arg, **kw) session.add(obj) cache[key] = obj return obj class UniqueMixin(object): @classmethod def unique_hash(cls, *arg, **kw): raise NotImplementedError() @classmethod def unique_filter(cls, query, *arg, **kw): raise NotImplementedError() @classmethod def as_unique(cls, *arg, **kw): session = Session() return _unique( session, cls, cls.unique_hash, cls.unique_filter, cls, arg, kw) from sqlalchemy import Column, Integer, ForeignKey, String, create_engine from sqlalchemy.orm import relationship, scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.associationproxy import association_proxy Base = declarative_base() class TableA(UniqueMixin, Base): __tablename__ = 'tablea' id = Column(Integer, primary_key=True) name = Column(String, unique=True) def __init__(self, name): self.name = name @classmethod def unique_hash(cls, name): return hash(name) @classmethod def unique_filter(cls, query, name): return query.filter(TableA.name == name) class TableB(Base): __tablename__ = 'tableb' id = Column(Integer, primary_key=True) name_id = Column(ForeignKey('tablea.id')) _name = relationship(TableA) name = association_proxy(_name, name, creator=lambda name: TableA.as_unique(name)) engine = create_engine(sqlite://, echo=True) Base.metadata.create_all(engine) Session = scoped_session(sessionmaker(engine)) Session.add_all([ TableB(name='foo'), TableB(name='bar'), TableB(name='bat'), TableB(name='hoho'), TableB(name='bar'), TableB(name='hoho'), TableB(name='foo'), ]) Session.commit() -- 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.
Re: [sqlalchemy] Emptying relationship records trigger update
On May 11, 2014, at 3:05 AM, Alexander Luksidadi alexander.luksid...@gmail.com wrote: Im just trying to empties a one to many records by doing this: load.commodities = [] then what happens next is: IntegrityError: (IntegrityError) null value in column load_id violates not-null constraint DETAIL: Failing row contains (35d39bd9-ca61-43ef-a5c5-7590c82aca1d, 2014-05-11 06:48:27.028485-04, 2014-05-11 06:51:41.511231-04, Product 4, , 4, 4, 4, 4, , a637cc84-fd6e-417c-bb59-5a4e3a435696, a8fca522-3b5f-429f-b58b-c0de4c05d725, null, , inch, lbs). 'UPDATE load_commodities SET updated_at=%(updated_at)s, load_id=%(load_id)s WHERE load_commodities.id = %(load_commodities_id)s' {'load_commodities_id': '35d39bd9-ca61-43ef-a5c5-7590c82aca1d', 'load_id': None, 'updated_at': datetime.datetime(2014, 5, 11, 6, 51, 41, 511231)} it seems like it triggers update and set all the fields to None instead of delete. here're my classes: class Load(Base): __tablename__ = 'loads' id = Column(GUID, primary_key=True, default=uuid.uuid4) commodities = relationship(LoadCommodity, backref=backref(load, uselist=False)) class LoadCommodity(Base): __tablename__ = 'load_commodities' id = Column(GUID, primary_key=True, default=uuid.uuid4) load_id = Column(GUID, ForeignKey('loads.id'), nullable=False) you want items removed from commodities to be deleted so you set cascade=all, delete-orphan: commodities = relationship(LoadCommodity, cascade=all, delete-orphan) http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#configuring-delete-delete-orphan-cascade -- 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.
Re: [sqlalchemy] selects, mappers and foreign keys
On May 11, 2014, at 10:38 AM, Richard Gerd Kuesters rich...@humantech.com.br wrote: thanks Mike! the problem is that the other side is also a selectable, so: foo = relationship(Remote, primaryjoin=myselect.c.foo == myotherselect.c.bar) so again, i can see this might have issues, but in theory (meaning, if it doesn't work, I should be able to make it work), it would be: foo = relationship(Remote, primaryjoin=myselect.c.foo == remote(foreign(myotherselect.c.bar))) -- 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.
[sqlalchemy] Re: Emptying relationship records trigger update
Thank you Michael. you are definitely the best and most responsive author out there! On Sunday, May 11, 2014 3:05:32 AM UTC-4, Alexander Luksidadi wrote: Im just trying to empties a one to many records by doing this: load.commodities = [] then what happens next is: IntegrityError: (IntegrityError) null value in column load_id violates not-null constraint DETAIL: Failing row contains (35d39bd9-ca61-43ef-a5c5-7590c82aca1d, 2014-05-11 06:48:27.028485-04, 2014-05-11 06:51:41.511231-04, Product 4, , 4, 4, 4, 4, , a637cc84-fd6e-417c-bb59-5a4e3a435696, a8fca522-3b5f-429f-b58b-c0de4c05d725, null, , inch, lbs). 'UPDATE load_commodities SET updated_at=%(updated_at)s, load_id=%(load_id)s WHERE load_commodities.id = %(load_commodities_id)s' {'load_commodities_id': '35d39bd9-ca61-43ef-a5c5-7590c82aca1d', 'load_id': None, 'updated_at': datetime.datetime(2014, 5, 11, 6, 51, 41, 511231)} it seems like it triggers update and set all the fields to None instead of delete. here're my classes: class Load(Base): __tablename__ = 'loads' id = Column(GUID, primary_key=True, default=uuid.uuid4) commodities = relationship(LoadCommodity, backref=backref(load, uselist=False)) class LoadCommodity(Base): __tablename__ = 'load_commodities' id = Column(GUID, primary_key=True, default=uuid.uuid4) load_id = Column(GUID, ForeignKey('loads.id'), nullable=False) -- 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.
Re: [sqlalchemy] Relationship setup
Hey Michael, I really appreciate all that, it was extremely informative. For the academic sake I have this extrapolated to include all the actual intermediate tables that TableB would include. For the academic sake, without the mixin and proxy, given a traditional approach where TableA is already populated, what is the simplest customary approach usually leveraged for feeding data into the TableB objects relationship columns? Is there a shorter mechanism than an actual query statement? Thanks a lot for the assistance, jlc -- 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.
Re: [sqlalchemy] selects, mappers and foreign keys
thanks Mike, that worked fine. my code, though, didn't went further (i'll have to debug a little bit more) :) best regards, richard. Em 2014-05-11 14:43, Michael Bayer escreveu: On May 11, 2014, at 10:38 AM, Richard Gerd Kuesters rich...@humantech.com.br wrote: thanks Mike! the problem is that the other side is also a selectable, so: foo = relationship(Remote, primaryjoin=myselect.c.foo == myotherselect.c.bar) so again, i can see this might have issues, but in theory (meaning, if it doesn't work, I should be able to make it work), it would be: foo = relationship(Remote, primaryjoin=myselect.c.foo == remote(foreign(myotherselect.c.bar))) -- 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.
Re: [sqlalchemy] Relationship setup
On May 11, 2014, at 4:28 PM, Joseph Casale jcas...@gmail.com wrote: Hey Michael, I really appreciate all that, it was extremely informative. For the academic sake I have this extrapolated to include all the actual intermediate tables that TableB would include. For the academic sake, without the mixin and proxy, given a traditional approach where TableA is already populated, what is the simplest customary approach usually leveraged for feeding data into the TableB objects relationship columns? Is there a shorter mechanism than an actual query statement? Let's say, if you have TableA with 1-foo, 2-bar. That data is only in the database. Then you want TableB.name = foo to result in TableB.name_id = 1.Where else would the 1 come from, except ultimately via a query at some point? The options are: 1. hardcode 1-foo, 2-bar into the application. Sort of makes TableA pointless. For fixed sets of application values, I recommend using enums instead, see http://techspot.zzzeek.org/2011/01/14/the-enum-recipe/ for when to use. 2. SELECT TableA up front somewhere and cache it. Then put some kind of @property on TableB.name to do a cache lookup. This is just a little awkward because if TableA changes, you have to deal with cache invalidation. 3. SELECT TableA on a more specific basis, possibly use caching. That's what unique object is getting you right now, it has caching. The invalidation problem is here as well though the caching is local to a specific Session; approach #2 could possibly use this also by selecting all of TableA up front per-session. -- 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.