[sqlalchemy] Composite foreign key constraints
First of all I realise this question might be more of a design choice
question than a technical issue. The issue I am facing is creating a
model reflecting a situation where multiple tables all contain data for
a particular period. This period consists of both a date and an hour,
these two can not be combined to one datetime column due to the nature
of the data (hourly indices on energy markets in which each hours
represents a different product/instrument, in other words there is no
direct link between different hours on the same day).
For proper normalisation I thought I would be better to not use both
date and hour column in the three separate tables with data, but define
a period table (with id, date and hour columns) and have the tables
refer to an period id (foreign key). This is what I currently have using
declarative base:
class Period(Base):
__tablename__ = period
id = Column(Integer, primary_key=True)
date = Column(Date, nullable=False)
hour = Column(SmallInteger, nullable=False)
__table_args__ = (
UniqueConstraint(date, hour),
{}
)
class Result(Base):
__tablename__ = result
id = Column(Integer, primary_key=True)
period_id = Column(Integer, ForeignKey(period.id))
period = relation(Period, uselist=False, backref=backref(result))
[...Actual data...]
The example in the 'Essential Alchemy' has something similar with
products and SKUs, but solved using composite foreign keys. The previous
code become something like this:
class Period(Base):
__tablename__ = period
date = Column(Date, primary_key=True)
hour = Column(SmallInteger, primary_key=True)
class Result(Base):
__tablename__ = result
id = Column(Integer, primary_key=True)
date = Column(Date, primary_key=True)
hour = Column(SmallInteger, primary_key=True)
[...Actual data...]
__table_args__ = (
ForeignKeyConstraint([date, hour], [period.date,
period.hour]),
{}
)
Is the second the preferred option? If so, what is the added value of
the separate period table? Or maybe in more general; if my efforts in
trying to explain the issue have been successful could someone indicate
what would be the better solution for this?
Thijs
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Append column
On Mar 12, 2010, at 10:14 AM, YAD wrote:
Hello
from sqlalchemy import *
engine = create_engine('mysql://root:r...@localhost/sqlalchemy')
meta = MetaData(engine)
tb = Table('account', meta, autoload = True)
tb.append_column(Column('c',String(50)))
this code does not work properly, it adds a column in the table but in
fact the table remains intact. Where mistake?
SQLA doesn't have automatic support for ALTER TABLE. append_column() would
normally be called before a create() is issued.
To work with ALTER, use a recipe like that described in
http://www.sqlalchemy.org/docs/reference/ext/compiler.html#dialect-specific-compilation-rules
or look into using sqlalchemy-migrate.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Composite foreign key constraints
On Mar 13, 2010, at 7:26 AM, sqlalch...@lists.fastmail.net wrote:
class Period(Base):
__tablename__ = period
id = Column(Integer, primary_key=True)
date = Column(Date, nullable=False)
hour = Column(SmallInteger, nullable=False)
__table_args__ = (
UniqueConstraint(date, hour),
{}
)
class Result(Base):
__tablename__ = result
id = Column(Integer, primary_key=True)
period_id = Column(Integer, ForeignKey(period.id))
period = relation(Period, uselist=False, backref=backref(result))
[...Actual data...]
The example in the 'Essential Alchemy' has something similar with
products and SKUs, but solved using composite foreign keys. The previous
code become something like this:
class Period(Base):
__tablename__ = period
date = Column(Date, primary_key=True)
hour = Column(SmallInteger, primary_key=True)
class Result(Base):
__tablename__ = result
id = Column(Integer, primary_key=True)
date = Column(Date, primary_key=True)
hour = Column(SmallInteger, primary_key=True)
[...Actual data...]
__table_args__ = (
ForeignKeyConstraint([date, hour], [period.date,
period.hour]),
{}
)
Is the second the preferred option? If so, what is the added value of
the separate period table? Or maybe in more general; if my efforts in
trying to explain the issue have been successful could someone indicate
what would be the better solution for this?
It reminds me a little of a star schema. The periods being in a separate
table would allow you to join your fact tables along common periods, using
only the surrogate identifier period.id. My impression is that the
surrogate primary key approach is more useful here. Having the period table
with a composite primary key which doesn't reference anything (i.e. its not an
association table) isn't of much use if Period doesn't store anything else
meaningful.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] not-null constraint violation in 0.6beta1
Hi, I have an application that used to work fine with SQLAlchemy 0.5.6. With 0.6beta1 I observe commit failures when I try to update references in a 1-to-many relationship and delete the then unreferences object. I did not see anything pertinent to this issue in the 0.6 migration guide. The attached sample program demonstrates the issue. In the beginning, both B instances named b2 and b3 reference the A instance with size 23. The A instance with size 29 is not referenced yet. In a new session I then make both b2 and b3 reference the A instance with size 29 and I also delete the A instance with size 23 they referenced before. The program terminates without error when using SQLAlchemy 0.5.6 and gives the expeced output: A(17, a4729ae4-55e5-4793-b26b-54dae985be2e): bs = ['b1'] A(23, cb51a069-ca24-4746-9338-e55f4b7b0812): bs = ['b2', 'b3'] A(29, 915246ac-cf17-4c4f-b17b-9f6170c5a740): bs = [] B(A(17, a4729ae4-55e5-4793-b26b-54dae985be2e), 'b1') B(A(23, cb51a069-ca24-4746-9338-e55f4b7b0812), 'b2') B(A(23, cb51a069-ca24-4746-9338-e55f4b7b0812), 'b3') A(17, a4729ae4-55e5-4793-b26b-54dae985be2e): bs = ['b1'] A(29, 915246ac-cf17-4c4f-b17b-9f6170c5a740): bs = ['b2', 'b3'] B(A(17, a4729ae4-55e5-4793-b26b-54dae985be2e), 'b1') B(A(29, 915246ac-cf17-4c4f-b17b-9f6170c5a740), 'b2') B(A(29, 915246ac-cf17-4c4f-b17b-9f6170c5a740), 'b3') When I execute the same program with SQLAlchemy 0.6beta1, then session.commit() raises an IntegrityError: A(17, 3fe37355-4021-4dfc-b9c3-44a20c738835): bs = ['b1'] A(23, 59c195cd-39df-49af-99d4-c9bb7f3bfa1d): bs = ['b2', 'b3'] A(29, fa04e682-5bba-452b-a8ed-82ba979633fd): bs = [] B(A(17, 3fe37355-4021-4dfc-b9c3-44a20c738835), 'b1') B(A(23, 59c195cd-39df-49af-99d4-c9bb7f3bfa1d), 'b2') B(A(23, 59c195cd-39df-49af-99d4-c9bb7f3bfa1d), 'b3') Traceback (most recent call last): File Eclipse-Galileo/workspace/SQLAlchemyTest/src/deletetest.py, line 114, in module session.commit() File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/session.py, line 655, in commit self.transaction.commit() File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/session.py, line 368, in commit self._prepare_impl() File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/session.py, line 352, in _prepare_impl self.session.flush() File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/session.py, line 1324, in flush self._flush(objects) File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/session.py, line 1402, in _flush flush_context.execute() File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/unitofwork.py, line 261, in execute UOWExecutor().execute(self, tasks) File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/unitofwork.py, line 753, in execute self.execute_save_steps(trans, task) File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/unitofwork.py, line 768, in execute_save_steps self.save_objects(trans, task) File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/unitofwork.py, line 759, in save_objects task.mapper._save_obj(task.polymorphic_tosave_objects, trans) File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/orm/mapper.py, line 1456, in _save_obj c = connection.execute(statement.values(value_params), params) File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py, line 1035, in execute return Connection.executors[c](self, object, multiparams, params) File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py, line 1097, in _execute_clauseelement return self.__execute_context(context) File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py, line 1120, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py, line 1182, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File /Users/cludwig/musicGPSAlchemyEnv/lib/python2.6/site-packages/SQLAlchemy-0.6beta1-py2.6.egg/sqlalchemy/engine/base.py, line 1180, in
Re: [sqlalchemy] not-null constraint violation in 0.6beta1
On Mar 13, 2010, at 6:45 PM, Christoph Ludwig wrote: Hi, I have an application that used to work fine with SQLAlchemy 0.5.6. With 0.6beta1 I observe commit failures when I try to update references in a 1-to-many relationship and delete the then unreferences object. I did not see anything pertinent to this issue in the 0.6 migration guide. this is a bug, and the pertinent area of change is that described in http://www.sqlalchemy.org/trac/wiki/06Migration#Many-to-oneEnhancements . The first line there which mentions no longer fetching the old value is a feature which needed to be partially rolled back to fix this, as what is special about your test is that your foreign key is against a non-primary key column (its only UNIQUE). When the old value is present in the current session, it is in fact needed for the backref accounting to work correctly, but for all our current tests this apparently has been relying upon the old value identified by primary key and therefore available via the local identity map. So if the relation can't use a simple get() it has to go back to the database. The test is pending and the ticket is #1737, however I have already committed the actual fix in r502f1a4f92d5 - the latest is available from mercurial or the development links at http://www.sqlalchemy.org/download.html . thanks for the full test case. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] does indexing on database tables have any affect on sqlalchemy mapped classes
Hello, This might be a very simple question to answer, but I am not finding any suitable benchmark tests so asking on the mailing list. I want to know if indexing on certain columns impacts the performance of sqlalchemy? for example I am using postgresql with python-psycopg2 and mapping all the tables to classes through alchemy. Now if I follow the postgresql tuning tips and index the needed columns from the relevent tables, will sqlalchemy perform better? My confusion is because I see that tables are mapped to classes and once that is done I don't directly interract with tables except through the engine.execute() where direct queries are passed. So will tuning of database tables have direct impact on performance with sqlalchemy? Happy hacking. Krishnakant. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
