[sqlalchemy] Session.add performance
In an application that is heavy on inserts and updates, cProfile output is dominated by Session.add in which about 45% of time is spent. Most of that time, in turn, is spent in cascade_iterator (43%). I can provide more detailed information if needed. The application does aggressive caching of data and has set expire_on_commit=False, in order to keep database load down. Is that the reason for Session.add slowness? Is there a way I can speed this up while keeping a similar level of cache aggressiveness? For example, in one test run Session.__contains__ was invoked 25m times over the course of only a few minutes, accounting for 27% of total time spent. Could it be a good idea to try and override this function with one that's optimized for this specific use case? Also, so far I haven't spent any effort expunging objects from the session as soon as possible. Some objects might linger for longer than necessary. Would they contribute to Session.add's overhead? -- 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] Re: Why does Session.merge only look at primary key and not all unique keys?
>> Why does Session.merge only look at primary key and not all unique >> keys? > > Well the theory of operation regarding merge() is based on that of > the identity map, which is linked to object/row identity. > Consider that it also cascades along relationship paths. It would > be a difficult operation to define if it had to choose among > multiple ways to determine the "identity" of each object along the > cascade chain. Ok. That certainly makes sense for following relationships in the merge (and in general). But for the basic existence checking that is required in the first step(s) of the merge to figure out whether the object being merged already exists or not, it does not seem unreasonable for it to check all unique keys. ie: in your docs you say the first merge step is "It examines the primary key of the instance". Can't it be "It checks any provided unique elements of the instance". From that point, normal/sensible identity map rules could resume for cascade. "Is a" checking is not the same as "is related to" checking, which is clearly nuttier. Although... you do mention later that ORM level has no knowledge of "unique" attributes, so perhaps this is also impossible? Is the "unique = True" kwarg on the Column not kept anywhere? Is it just used for table creation and then turfed? >> Leaving aside some irritating DBMS restrictions on PKs and some >> automatic indexing that tends to happen, the PK is not fundamentally >> different than other unique keys > > It is fundamentally different in that a database row within a > reasonable schema has only one "identity". The usage of surrogate > primary keys perhaps pollutes this concept to some degree. Ok again... but you also agree that the use of surrogate keys is standard (and some say "necessary") practice. In the case of Sqlite (as in my example), adding this surrogate key automatically makes the schema "unreasonable" because you now need to have the primary key as the meaningless surrogate key (single id column), and the natural key ends up being relegated to just another unique index. Unfortunately, the latter renders the object/table useless for use with the useful session.merge() function. I don't recall the details, but I think there may be a similar PostgreSQL limitation regarding autoincrements as well. > for some recent insight into my epic battle with time consumption, > see http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-profiles Great link/post! Seeing successful optimizations is always good, and the writeup is quite informative. Regarding RunSnakeRun, I've used it as well and like it. However, I'm a bit of a resolution junkie and the box frames in RunSnakeRun's visual output are limited in how they will squish all the box borders together. If you haven't given kcachegrind a shot yet for viewing cProfile results, you might want to give it a trial run as the visual output is a better representation of timing scale, and the viewing is more powerful as well (if resolution is too weak if an argument). Some tips to get it to work well for python are here: http://stackoverflow.com/questions/1896032/using-cprofile-results-with-kcachegrind > The generic "insert if not exists" pattern that is extensible to > whatever attributes you want is at: > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject Thanks for the example... I would have been worried about how fiddling with the __new__ constructor would interfere with query loads, but that example shows how to make it work. -- 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] Use of table aliases
On Dec 15, 2010, at 9:04 AM, neurino wrote:
> Hello I have 2 tables: data and acquisitions,
> - each Acquisition has many Data
> - each Data come from a different sensor
> - the single sensor is identified by the couple Acquisition.id_centr,
> Data.id_meas
>
> No I need a query with one colum for each sensor and a row for each
> Acquisition.datetime
>
> This is how I get it (in case of two sensors) with SQL:
>
q = curs.execute("""
>SELECT a.datetime, d1.value, d2.value
>FROM acquisitions AS a
>LEFT JOIN data AS d1
>ON a.id_acq=d1.id_acq
>AND a.id_centr=159
>AND d1.id_meas=1501
>LEFT JOIN data AS d2
>ON a.id_acq=d2.id_acq
>AND a.id_centr=320
>AND d2.id_meas=1551
>""")
for n, row in enumerate(q): print n, row
> :
> 0 (u'2010-09-02 12:05:00', 23.98, 25.67)
> 1 (u'2010-09-02 12:10:00', 23.77, 25.57)
> 2 (u'2010-09-02 12:15:00', 23.96, 25.57)
> 3 (u'2010-09-02 12:20:00', 24.78, 25.94)
> 4 (u'2010-09-02 12:25:00', 25.48, 26.27)
> 5 (u'2010-09-02 12:30:00', 25.91, 26.46)
> 6 (u'2010-09-02 12:35:00', 26.14, 26.62)
> 7 (u'2010-09-02 12:40:00', 26.32, 26.73)
> 8 (u'2010-09-02 12:45:00', 26.44, 26.80)
> 9 (u'2010-09-02 12:50:00', 26.55, 26.87)
> 10 (u'2010-09-02 12:55:00', 26.62, 26.92)
> 11 (u'2010-09-02 13:00:00', 26.67, 26.94)
> 12 (u'2010-09-02 13:05:00', 26.69, 26.94)
> 13 (u'2010-09-02 13:10:00', 26.71, 26.96)
> 14 (u'2010-09-02 13:15:00', 26.73, 26.98)
>
> But I can't get the same result with sqlalchemy, here's my mapping:
>
> data = Table('data', metadata,
>Column('id_data', Integer, primary_key=True),
>Column('id_meas', Integer, nullable=False),
>Column('id_acq', Integer, ForeignKey('acquisitions.id_acq'),
>nullable=False),
>Column('value', Float, nullable=False),
>)
>
> acquisitions = Table('acquisitions', metadata,
>Column('id_acq', Integer, primary_key=True),
>Column('id_centr', Integer, nullable=False),
>Column('datetime', DateTime, nullable=False),
>#acquisitions with same id_centr and datetime are duplicates
>UniqueConstraint('id_centr', 'datetime'),
>)
>
> orm.mapper(Data, data, properties={
>'acquisitions': orm.relationship(Acquisition, backref='data'),
>})
> orm.mapper(Acquisition, acquisitions)
to create aliases during an ORM query you use the aliased() construct. There's
examples at:
http://www.sqlalchemy.org/docs/orm/tutorial.html#using-aliases
you'd also be using sqlalchemy.and_() to formulate those outerjoin() conditions.
>
> Any advice?
>
> Thanks for your support
> neurino
>
> --
> 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] Something like orderinglist for secondary tables?
Hi Michael, Sorry for not following up on your reply. I only came back to this list today. :) On Thu, 2010-11-18 at 11:34 -0500, Michael Bayer wrote: > > Okay, I updated my example code and it actually works now. However, it > > feels like a lot of additional complexity just for adding order. > > Hm, I just looked at the script and compared it to your previous two pages of > Mapper/Session extensions, seems a lot simpler to me ! :-) Sure. But compared to using orderinglist (only available w/o secondary table) it looks quite a bit more complicated. > The relationship + secondary approach does support "ordering" by an extra > column in the association table, it just doesn't support direct mutation of > that value. > > The rationale for the association object pattern is that it is a consistent > way of establishing full control over the "secondary" table, using the exact > same paradigms as that which grant control over the rows of any other table. > If we did it via flags and switches to relationship(), the API and internal > complexity would increase significantly as would the potential for bugs, not > to mention ambiguity in preferred methodology. On the downside it weights in with an extra Python object with full ORM instrumentation for each entry in an ordered list. Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] Why does Session.merge only look at primary key and not all unique keys?
On Dec 15, 2010, at 5:14 PM, Russell Warren wrote: > Why does Session.merge only look at primary key and not all unique > keys? Well the theory of operation regarding merge() is based on that of the identity map, which is linked to object/row identity. Consider that it also cascades along relationship paths. It would be a difficult operation to define if it had to choose among multiple ways to determine the "identity" of each object along the cascade chain. > > Leaving aside some irritating DBMS restrictions on PKs and some > automatic indexing that tends to happen, the PK is not fundamentally > different than other unique keys It is fundamentally different in that a database row within a reasonable schema has only one "identity". The usage of surrogate primary keys perhaps pollutes this concept to some degree. > and I don't see why SQLA > distinguishes them from an integrity/relationship perspective. SQLA at the ORM level doesn't really know about any other attributes being "unique" and it would incur excessive complexity to implement that as built-in, where "complexity" here means the bookkeeping associated with storing, retrieving, and modifying items in the identity map would become a much more time consuming affair (for some recent insight into my epic battle with time consumption, see http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-profiles/ ). It also would refer to all those unintended side effects of doing such, such as two objects that are swapping attribute values, etc.The database does a perfectly good job of maintaining UNIQUE constraints so we leave that whole affair out of the Python side. > In > databases where it is already frustrating that they have funky PK > restrictions it is tough to make merge() work the way it seems it > should. For example, in the code below this post, Sqlite requires the > autoincrementing field to be the PK, and you can't composite it with > another field... with these restrictions I can't get merge() to work > the way "it should". > I was looking for a clean way in SQLAlchemy to do an "insert if not > exists" pattern, and merge() looked perfect, but I can't make it work > at the moment. The generic "insert if not exists" pattern that is extensible to whatever attributes you want is at: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject . > > I'm also aware that in the sample code the 'name' field should really > just be the "primary" key and the problem goes away, mmm the consensus I've noted for the past several years, as well as with my own experiences, is that we're better off with surrogate primary keys. SQLA does support natural primary keys fully, and note that foreign keys which reference natural primary keys are entirely valid. Mutation of these keys is supported naturally through ON UPDATE CASCADE and ON DELETE CASCADE. But I find myself usually never using them (well actually I did a yearlong project a year ago that was all on natural PKs and it was really not worth it). -- 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] Re: Why does Session.merge only look at primary key and not all unique keys?
Whoops - my clipboard had an old pre-cleaned sample code in it that I pasted. Sorry about the excess lines... maybe the logging mod will be useful for some people, though? The code is still right, just not fully cleaned up. The first Column config is the one that works around the Sqlite PK/autoincrement restriction, but doesn't work with merge() because it doesn't pick up on the fact that 'foo' already exists and the merge tries to insert it again, throwing an IntegrityError. -- 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] Why does Session.merge only look at primary key and not all unique keys?
Why does Session.merge only look at primary key and not all unique
keys?
Leaving aside some irritating DBMS restrictions on PKs and some
automatic indexing that tends to happen, the PK is not fundamentally
different than other unique keys and I don't see why SQLA
distinguishes them from an integrity/relationship perspective. In
databases where it is already frustrating that they have funky PK
restrictions it is tough to make merge() work the way it seems it
should. For example, in the code below this post, Sqlite requires the
autoincrementing field to be the PK, and you can't composite it with
another field... with these restrictions I can't get merge() to work
the way "it should".
I was looking for a clean way in SQLAlchemy to do an "insert if not
exists" pattern, and merge() looked perfect, but I can't make it work
at the moment.
I'm also aware that in the sample code the 'name' field should really
just be the "primary" key and the problem goes away, but the reality
of the grander/real scheme is that the linking id is needed in
addition to other unique keys.
In addition to the docs, these existing threads are also very
relevant:
http://groups.google.com/group/sqlalchemy/browse_frm/thread/7483736b46d56943
http://groups.google.com/group/sqlalchemy/browse_thread/thread/79736ff7ef81d1b9/0b80b54dc45ecc28
To make the "insert if not exists" pattern work I'll likely/
reluctantly be doing the __new__ hack referred to in the latter thread
to achieve what I'm after in the end, but I really don't get why the
PK is getting special treatment.
Thanks,
Russ
Sample code:
from sqlalchemy import Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
import logging
engine = create_engine('sqlite:///:memory:')
metadata = MetaData()
DeclarativeBase = declarative_base()
#Configure some clean and indented SQL logging...
class SqlFormatter(logging.Formatter):
def format(self, record):
prefix = "SQL >> "
record.msg = prefix + record.msg.replace("\n", "\n" + prefix)
return logging.Formatter.format(self, record)
sqlaLogger = logging.getLogger('sqlalchemy.engine')
sqlaLogger.setLevel(logging.INFO)
handler = logging.StreamHandler()
handler.setFormatter(SqlFormatter("%(message)s"))
sqlaLogger.addHandler(handler)
class MyStuff(DeclarativeBase):
__tablename__ = 'mystuff'
#Config below
id = Column(Integer, primary_key = True, autoincrement = True)
name = Column(String(100), nullable = False, unique = True)
#Config below no good due to composite PK...
#id = Column(Integer, primary_key = True, autoincrement = True)
#name = Column(String(100), nullable = False, primary_key = True)
#Config below doesn't give autoincrement...
#id = Column(Integer, primary_key = False, autoincrement = True)
#name = Column(String(100), nullable = False, primary_key = True)
def __init__(self, Name):
self.name = Name
DeclarativeBase.metadata.create_all(engine)
Session = sessionmaker(bind = engine)
print "Attempting 'foo' merge into empty DB..."
s1 = Session()
foo = s1.merge(MyStuff("foo"))
s1.commit()
s1.close()
print "Attempting 'foo' merge after it exists already..."
s2 = Session()
foo = s2.merge(MyStuff("foo"))
s2.commit()
s2.close()
--
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] Deletion order during flush is not correct.
It was in fact a one liner, so you can go back to your original code if you use
the latest 0.6 tip:
http://hg.sqlalchemy.org/sqlalchemy/archive/rel_0_6.tar.gz
thanks for the bug report !
On Dec 15, 2010, at 3:41 PM, Will Weaver wrote:
> Wow, this has been a problem for me for the past 3 or 4 days and took
> a while to get to that example. Defining the backrefs or the
> relationships in the opposite direction did the job. I had
> intentionally left out some of the backreffed relationships because I
> didn't need them for what I was working on, but it definitely is worth
> it to get this working.
>
> Thanks a lot.
>
> -Will
>
> On Wed, Dec 15, 2010 at 2:45 PM, Michael Bayer
> wrote:
>> This is an interesting edge case and I can probably ensure that the
>> dependency between Parent/Child is present in the unit of work even if there
>> is no known linkage at the Child.parent level for the objects actually
>> present - ticket #2002 is added for this.
>>
>> In the meantime, the uow needs to be aware of the linkage between
>> Parent->Child when flush occurs. Adding a backref "children" to the parent
>> relationship will do it, or ensuring that child.parent is accessed before
>> emitting the flush will do it. The usual way this kind of delete is
>> performed is the "delete" cascade is added to the "children" backref, then
>> the Parent is deleted alone, the deletes cascading to the Child objects
>> naturally.
>>
>> But this is a fun bug and I'll probably have a fix very soon, perhaps in 10
>> minutes or maybe not.
>>
>>
>>
>>
>> On Dec 15, 2010, at 2:17 PM, Will wrote:
>>
>>> """
>>> Hello,
>>>
>>> I've been recently having a problem with sqlalchemy not flushing
>>> deletes in the proper order. I've created a simple example for the
>>> problem that has been occuring. I tried to run this using sqlite and
>>> it doesn't have any problems, it is only with Postgresql.
>>>
>>> One thing of note is that if there is only one Child it doesn't seem
>>> to
>>> have a problem, only when there are multiple children. Not sure if
>>> that makes a difference in the SQLAlchemy code.
>>> """
>>>
>>> from sqlalchemy import create_engine
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy.orm import scoped_session, sessionmaker, relationship
>>> from sqlalchemy.schema import Column, ForeignKey
>>> from sqlalchemy.types import Integer
>>>
>>> #engine = create_engine('sqlite:///')
>>> engine = create_engine('postgresql://test_runner@/testing_db')
>>>
>>> Model = declarative_base()
>>>
>>>
>>> class Parent(Model):
>>>__tablename__ = 'parents'
>>>
>>>id = Column(Integer, primary_key=True)
>>>
>>>
>>> class Child(Model):
>>>__tablename__ = 'children'
>>>
>>>id = Column(Integer, primary_key=True)
>>>parent_id = Column(Integer, ForeignKey('parents.id'),
>>> nullable=False)
>>>parent = relationship('Parent')
>>>
>>>
>>> def begin():
>>>"""Begin transaction"""
>>>#global transaction
>>>#transaction = session.begin()
>>>
>>>session.begin()
>>>
>>>
>>> def commit():
>>>"""Commit transaction"""
>>>#global transaction
>>>#transaction.commit()
>>>
>>>session.commit()
>>>
>>>
>>> Model.metadata.create_all(engine)
>>>
>>> parent = Parent()
>>> children = [Child(parent=parent), Child(parent=parent)]
>>>
>>> Session = sessionmaker(bind=engine, autocommit=True)
>>> session = Session()
>>>
>>> try:
>>>session.bind.echo = True
>>>
>>>begin()
>>>session.add_all(children)
>>>session.add(parent)
>>>commit()
>>>
>>>
>>>begin()
>>>for child in children:
>>>session.delete(child)
>>>session.delete(parent)
>>>commit()
>>>
>>>session.bind.echo = False
>>> finally:
>>>Model.metadata.drop_all(engine)
>>>
>>>
>>> """
>>> From running the script I have two different outputs because it seems
>>> to run the deletes in a random order so subsequent runs will behave
>>> differently.
>>> """
>>>
>>> # Example Failed Run
>>> """
>>> 2010-12-15 13:45:05,050 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>>> BEGIN (implicit)
>>> 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>>> INSERT INTO parents DEFAULT VALUES RETURNING parents.id
>>> 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>>> {}
>>> 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>>> INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
>>> children.id
>>> 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>>> {'parent_id': 1}
>>> 2010-12-15 13:45:05,053 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>>> INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
>>> children.id
>>> 2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>>> {'parent_id': 1}
>>> 2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>>> COMMIT
>>> 2010-12-15 13:45:05,055 INFO sqlalchem
Re: [sqlalchemy] Deletion order during flush is not correct.
Wow, this has been a problem for me for the past 3 or 4 days and took
a while to get to that example. Defining the backrefs or the
relationships in the opposite direction did the job. I had
intentionally left out some of the backreffed relationships because I
didn't need them for what I was working on, but it definitely is worth
it to get this working.
Thanks a lot.
-Will
On Wed, Dec 15, 2010 at 2:45 PM, Michael Bayer wrote:
> This is an interesting edge case and I can probably ensure that the
> dependency between Parent/Child is present in the unit of work even if there
> is no known linkage at the Child.parent level for the objects actually
> present - ticket #2002 is added for this.
>
> In the meantime, the uow needs to be aware of the linkage between
> Parent->Child when flush occurs. Adding a backref "children" to the parent
> relationship will do it, or ensuring that child.parent is accessed before
> emitting the flush will do it. The usual way this kind of delete is
> performed is the "delete" cascade is added to the "children" backref, then
> the Parent is deleted alone, the deletes cascading to the Child objects
> naturally.
>
> But this is a fun bug and I'll probably have a fix very soon, perhaps in 10
> minutes or maybe not.
>
>
>
>
> On Dec 15, 2010, at 2:17 PM, Will wrote:
>
>> """
>> Hello,
>>
>> I've been recently having a problem with sqlalchemy not flushing
>> deletes in the proper order. I've created a simple example for the
>> problem that has been occuring. I tried to run this using sqlite and
>> it doesn't have any problems, it is only with Postgresql.
>>
>> One thing of note is that if there is only one Child it doesn't seem
>> to
>> have a problem, only when there are multiple children. Not sure if
>> that makes a difference in the SQLAlchemy code.
>> """
>>
>> from sqlalchemy import create_engine
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.orm import scoped_session, sessionmaker, relationship
>> from sqlalchemy.schema import Column, ForeignKey
>> from sqlalchemy.types import Integer
>>
>> #engine = create_engine('sqlite:///')
>> engine = create_engine('postgresql://test_runner@/testing_db')
>>
>> Model = declarative_base()
>>
>>
>> class Parent(Model):
>> __tablename__ = 'parents'
>>
>> id = Column(Integer, primary_key=True)
>>
>>
>> class Child(Model):
>> __tablename__ = 'children'
>>
>> id = Column(Integer, primary_key=True)
>> parent_id = Column(Integer, ForeignKey('parents.id'),
>> nullable=False)
>> parent = relationship('Parent')
>>
>>
>> def begin():
>> """Begin transaction"""
>> #global transaction
>> #transaction = session.begin()
>>
>> session.begin()
>>
>>
>> def commit():
>> """Commit transaction"""
>> #global transaction
>> #transaction.commit()
>>
>> session.commit()
>>
>>
>> Model.metadata.create_all(engine)
>>
>> parent = Parent()
>> children = [Child(parent=parent), Child(parent=parent)]
>>
>> Session = sessionmaker(bind=engine, autocommit=True)
>> session = Session()
>>
>> try:
>> session.bind.echo = True
>>
>> begin()
>> session.add_all(children)
>> session.add(parent)
>> commit()
>>
>>
>> begin()
>> for child in children:
>> session.delete(child)
>> session.delete(parent)
>> commit()
>>
>> session.bind.echo = False
>> finally:
>> Model.metadata.drop_all(engine)
>>
>>
>> """
>> From running the script I have two different outputs because it seems
>> to run the deletes in a random order so subsequent runs will behave
>> differently.
>> """
>>
>> # Example Failed Run
>> """
>> 2010-12-15 13:45:05,050 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> BEGIN (implicit)
>> 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> INSERT INTO parents DEFAULT VALUES RETURNING parents.id
>> 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> {}
>> 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
>> children.id
>> 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> {'parent_id': 1}
>> 2010-12-15 13:45:05,053 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
>> children.id
>> 2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> {'parent_id': 1}
>> 2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> COMMIT
>> 2010-12-15 13:45:05,055 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> BEGIN (implicit)
>> 2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> SELECT parents.id AS parents_id
>> FROM parents
>> WHERE parents.id = %(param_1)s
>> 2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> {'param_1': 1}
>> 2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0
>> DELETE FROM parents WHERE parents.id = %(id)s
>> 2010-12-15 13:45
Re: [sqlalchemy] Deletion order during flush is not correct.
This is an interesting edge case and I can probably ensure that the dependency
between Parent/Child is present in the unit of work even if there is no known
linkage at the Child.parent level for the objects actually present - ticket
#2002 is added for this.
In the meantime, the uow needs to be aware of the linkage between Parent->Child
when flush occurs. Adding a backref "children" to the parent relationship
will do it, or ensuring that child.parent is accessed before emitting the flush
will do it. The usual way this kind of delete is performed is the "delete"
cascade is added to the "children" backref, then the Parent is deleted alone,
the deletes cascading to the Child objects naturally.
But this is a fun bug and I'll probably have a fix very soon, perhaps in 10
minutes or maybe not.
On Dec 15, 2010, at 2:17 PM, Will wrote:
> """
> Hello,
>
> I've been recently having a problem with sqlalchemy not flushing
> deletes in the proper order. I've created a simple example for the
> problem that has been occuring. I tried to run this using sqlite and
> it doesn't have any problems, it is only with Postgresql.
>
> One thing of note is that if there is only one Child it doesn't seem
> to
> have a problem, only when there are multiple children. Not sure if
> that makes a difference in the SQLAlchemy code.
> """
>
> from sqlalchemy import create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import scoped_session, sessionmaker, relationship
> from sqlalchemy.schema import Column, ForeignKey
> from sqlalchemy.types import Integer
>
> #engine = create_engine('sqlite:///')
> engine = create_engine('postgresql://test_runner@/testing_db')
>
> Model = declarative_base()
>
>
> class Parent(Model):
>__tablename__ = 'parents'
>
>id = Column(Integer, primary_key=True)
>
>
> class Child(Model):
>__tablename__ = 'children'
>
>id = Column(Integer, primary_key=True)
>parent_id = Column(Integer, ForeignKey('parents.id'),
> nullable=False)
>parent = relationship('Parent')
>
>
> def begin():
>"""Begin transaction"""
>#global transaction
>#transaction = session.begin()
>
>session.begin()
>
>
> def commit():
>"""Commit transaction"""
>#global transaction
>#transaction.commit()
>
>session.commit()
>
>
> Model.metadata.create_all(engine)
>
> parent = Parent()
> children = [Child(parent=parent), Child(parent=parent)]
>
> Session = sessionmaker(bind=engine, autocommit=True)
> session = Session()
>
> try:
>session.bind.echo = True
>
>begin()
>session.add_all(children)
>session.add(parent)
>commit()
>
>
>begin()
>for child in children:
>session.delete(child)
>session.delete(parent)
>commit()
>
>session.bind.echo = False
> finally:
>Model.metadata.drop_all(engine)
>
>
> """
> From running the script I have two different outputs because it seems
> to run the deletes in a random order so subsequent runs will behave
> differently.
> """
>
> # Example Failed Run
> """
> 2010-12-15 13:45:05,050 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> BEGIN (implicit)
> 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> INSERT INTO parents DEFAULT VALUES RETURNING parents.id
> 2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {}
> 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
> children.id
> 2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'parent_id': 1}
> 2010-12-15 13:45:05,053 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
> children.id
> 2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'parent_id': 1}
> 2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> COMMIT
> 2010-12-15 13:45:05,055 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> BEGIN (implicit)
> 2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> SELECT parents.id AS parents_id
> FROM parents
> WHERE parents.id = %(param_1)s
> 2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'param_1': 1}
> 2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> DELETE FROM parents WHERE parents.id = %(id)s
> 2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'id': 1}
> 2010-12-15 13:45:05,058 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> ROLLBACK
> 2010-12-15 13:45:05,061 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> select relname from pg_class c join pg_namespace n on
> n.oid=c.relnamespace where n.nspname=current_schema() and
> lower(relname)=%(name)s
> 2010-12-15 13:45:05,061 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> {'name': u'children'}
> 2010-12-15 13:45:05,063 INFO sqlalchemy.engine.base.Engine.0x...f5d0
> select relname from pg_class c join
[sqlalchemy] Re: Deletion order during flush is not correct.
An update. This problem does occur with sqlite it's just that sqlite
doesn't enforce the foreign key so it doesn't throw an exception.
# output that deletes in the proper order
2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050
BEGIN (implicit)
2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050
INSERT INTO parents DEFAULT VALUES
2010-12-15 14:33:52,197 INFO sqlalchemy.engine.base.Engine.0x...d050
()
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,198 INFO sqlalchemy.engine.base.Engine.0x...d050
COMMIT
2010-12-15 14:33:52,199 INFO sqlalchemy.engine.base.Engine.0x...d050
BEGIN (implicit)
2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,200 INFO sqlalchemy.engine.base.Engine.0x...d050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050
(2,)
2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050
DELETE FROM children WHERE children.id = ?
2010-12-15 14:33:52,201 INFO sqlalchemy.engine.base.Engine.0x...d050
((1,), (2,))
2010-12-15 14:33:52,202 INFO sqlalchemy.engine.base.Engine.0x...d050
SELECT parents.id AS parents_id
FROM parents
WHERE parents.id = ?
2010-12-15 14:33:52,202 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050
DELETE FROM parents WHERE parents.id = ?
2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050
(1,)
2010-12-15 14:33:52,203 INFO sqlalchemy.engine.base.Engine.0x...d050
COMMIT
# output that deletes in the wrong order
2010-12-15 14:33:56,691 INFO sqlalchemy.engine.base.Engine.0x...6050
BEGIN (implicit)
2010-12-15 14:33:56,692 INFO sqlalchemy.engine.base.Engine.0x...6050
INSERT INTO parents DEFAULT VALUES
2010-12-15 14:33:56,692 INFO sqlalchemy.engine.base.Engine.0x...6050
()
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
INSERT INTO children (parent_id) VALUES (?)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,693 INFO sqlalchemy.engine.base.Engine.0x...6050
COMMIT
2010-12-15 14:33:56,694 INFO sqlalchemy.engine.base.Engine.0x...6050
BEGIN (implicit)
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
SELECT parents.id AS parents_id
FROM parents
WHERE parents.id = ?
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
DELETE FROM parents WHERE parents.id = ?
2010-12-15 14:33:56,695 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,696 INFO sqlalchemy.engine.base.Engine.0x...6050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:56,696 INFO sqlalchemy.engine.base.Engine.0x...6050
(1,)
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
SELECT children.id AS children_id, children.parent_id AS
children_parent_id
FROM children
WHERE children.id = ?
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
(2,)
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
DELETE FROM children WHERE children.id = ?
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
((1,), (2,))
2010-12-15 14:33:56,697 INFO sqlalchemy.engine.base.Engine.0x...6050
COMMIT
On Dec 15, 2:17 pm, Will wrote:
> """
> Hello,
>
> I've been recently having a problem with sqlalchemy not flushing
> deletes in the proper order. I've created a simple example for the
> problem that has been occuring. I tried to run this using sqlite and
> it doesn't have any problems, it is only with Postgresql.
>
> One thing of note is that if there is only one Child it doesn't seem
> to
> have a problem, only when there are multiple children. Not sure if
> that makes a difference in the SQLAlchemy code.
> """
>
> from sqlalchemy import create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import scoped_session, sessionmaker, relationship
> from sqlalchemy.schema import Column, ForeignKey
> from sqlalchemy.types import Integer
>
> #engine = create_engine('sqlite:///')
> engin
[sqlalchemy] Deletion order during flush is not correct.
"""
Hello,
I've been recently having a problem with sqlalchemy not flushing
deletes in the proper order. I've created a simple example for the
problem that has been occuring. I tried to run this using sqlite and
it doesn't have any problems, it is only with Postgresql.
One thing of note is that if there is only one Child it doesn't seem
to
have a problem, only when there are multiple children. Not sure if
that makes a difference in the SQLAlchemy code.
"""
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer
#engine = create_engine('sqlite:///')
engine = create_engine('postgresql://test_runner@/testing_db')
Model = declarative_base()
class Parent(Model):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
class Child(Model):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parents.id'),
nullable=False)
parent = relationship('Parent')
def begin():
"""Begin transaction"""
#global transaction
#transaction = session.begin()
session.begin()
def commit():
"""Commit transaction"""
#global transaction
#transaction.commit()
session.commit()
Model.metadata.create_all(engine)
parent = Parent()
children = [Child(parent=parent), Child(parent=parent)]
Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
try:
session.bind.echo = True
begin()
session.add_all(children)
session.add(parent)
commit()
begin()
for child in children:
session.delete(child)
session.delete(parent)
commit()
session.bind.echo = False
finally:
Model.metadata.drop_all(engine)
"""
>From running the script I have two different outputs because it seems
to run the deletes in a random order so subsequent runs will behave
differently.
"""
# Example Failed Run
"""
2010-12-15 13:45:05,050 INFO sqlalchemy.engine.base.Engine.0x...f5d0
BEGIN (implicit)
2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
INSERT INTO parents DEFAULT VALUES RETURNING parents.id
2010-12-15 13:45:05,051 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{}
2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
children.id
2010-12-15 13:45:05,052 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'parent_id': 1}
2010-12-15 13:45:05,053 INFO sqlalchemy.engine.base.Engine.0x...f5d0
INSERT INTO children (parent_id) VALUES (%(parent_id)s) RETURNING
children.id
2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'parent_id': 1}
2010-12-15 13:45:05,054 INFO sqlalchemy.engine.base.Engine.0x...f5d0
COMMIT
2010-12-15 13:45:05,055 INFO sqlalchemy.engine.base.Engine.0x...f5d0
BEGIN (implicit)
2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0
SELECT parents.id AS parents_id
FROM parents
WHERE parents.id = %(param_1)s
2010-12-15 13:45:05,056 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'param_1': 1}
2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0
DELETE FROM parents WHERE parents.id = %(id)s
2010-12-15 13:45:05,057 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'id': 1}
2010-12-15 13:45:05,058 INFO sqlalchemy.engine.base.Engine.0x...f5d0
ROLLBACK
2010-12-15 13:45:05,061 INFO sqlalchemy.engine.base.Engine.0x...f5d0
select relname from pg_class c join pg_namespace n on
n.oid=c.relnamespace where n.nspname=current_schema() and
lower(relname)=%(name)s
2010-12-15 13:45:05,061 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'name': u'children'}
2010-12-15 13:45:05,063 INFO sqlalchemy.engine.base.Engine.0x...f5d0
select relname from pg_class c join pg_namespace n on
n.oid=c.relnamespace where n.nspname=current_schema() and
lower(relname)=%(name)s
2010-12-15 13:45:05,063 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{'name': u'parents'}
2010-12-15 13:45:05,064 INFO sqlalchemy.engine.base.Engine.0x...f5d0
DROP TABLE children
2010-12-15 13:45:05,064 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{}
2010-12-15 13:45:05,066 INFO sqlalchemy.engine.base.Engine.0x...f5d0
COMMIT
2010-12-15 13:45:05,067 INFO sqlalchemy.engine.base.Engine.0x...f5d0
DROP TABLE parents
2010-12-15 13:45:05,067 INFO sqlalchemy.engine.base.Engine.0x...f5d0
{}
2010-12-15 13:45:05,068 INFO sqlalchemy.engine.base.Engine.0x...f5d0
COMMIT
Traceback (most recent call last):
File "sharded_session_issue.py", line 64, in
commit(session)
File "sharded_session_issue.py", line 47, in commit
session.commit()
File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line
623, in commit
self.transaction.commit()
File "/usr/lib/pymodules/python2.6/sqlalchemy/orm/session.py", line
385, in commit
self._prepare_impl()
File "/usr/lib/pymodules/pytho
Re: [sqlalchemy] Re: Modeling a Tree-looking structure in SqlAlchemy.
Thank you all... As soon as I have the webserver where I'm going to use that structure up and running, I'll try it and i'll let you know... 2010/12/13 Laurent Rahuel : > Hello, > > You should also take a look at http://pypi.python.org/pypi/sqlamp/0.5.2, an > implementation of Materialized Path for SQLAlchemy. > > Regards, > > Laurent > > Le 13 déc. 2010 à 23:30, Russell Warren a écrit : > >> Sorry, I just saw I messed up the nested sets SQLA example link. Here >> is the right one: >> http://www.sqlalchemy.org/trac/browser/examples/nested_sets/nested_sets.py >> >> -- >> 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. > > -- 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] Use of table aliases
Hello I have 2 tables: data and acquisitions,
- each Acquisition has many Data
- each Data come from a different sensor
- the single sensor is identified by the couple Acquisition.id_centr,
Data.id_meas
No I need a query with one colum for each sensor and a row for each
Acquisition.datetime
This is how I get it (in case of two sensors) with SQL:
>>>q = curs.execute("""
SELECT a.datetime, d1.value, d2.value
FROM acquisitions AS a
LEFT JOIN data AS d1
ON a.id_acq=d1.id_acq
AND a.id_centr=159
AND d1.id_meas=1501
LEFT JOIN data AS d2
ON a.id_acq=d2.id_acq
AND a.id_centr=320
AND d2.id_meas=1551
""")
>>>for n, row in enumerate(q): print n, row
:
0 (u'2010-09-02 12:05:00', 23.98, 25.67)
1 (u'2010-09-02 12:10:00', 23.77, 25.57)
2 (u'2010-09-02 12:15:00', 23.96, 25.57)
3 (u'2010-09-02 12:20:00', 24.78, 25.94)
4 (u'2010-09-02 12:25:00', 25.48, 26.27)
5 (u'2010-09-02 12:30:00', 25.91, 26.46)
6 (u'2010-09-02 12:35:00', 26.14, 26.62)
7 (u'2010-09-02 12:40:00', 26.32, 26.73)
8 (u'2010-09-02 12:45:00', 26.44, 26.80)
9 (u'2010-09-02 12:50:00', 26.55, 26.87)
10 (u'2010-09-02 12:55:00', 26.62, 26.92)
11 (u'2010-09-02 13:00:00', 26.67, 26.94)
12 (u'2010-09-02 13:05:00', 26.69, 26.94)
13 (u'2010-09-02 13:10:00', 26.71, 26.96)
14 (u'2010-09-02 13:15:00', 26.73, 26.98)
But I can't get the same result with sqlalchemy, here's my mapping:
data = Table('data', metadata,
Column('id_data', Integer, primary_key=True),
Column('id_meas', Integer, nullable=False),
Column('id_acq', Integer, ForeignKey('acquisitions.id_acq'),
nullable=False),
Column('value', Float, nullable=False),
)
acquisitions = Table('acquisitions', metadata,
Column('id_acq', Integer, primary_key=True),
Column('id_centr', Integer, nullable=False),
Column('datetime', DateTime, nullable=False),
#acquisitions with same id_centr and datetime are duplicates
UniqueConstraint('id_centr', 'datetime'),
)
orm.mapper(Data, data, properties={
'acquisitions': orm.relationship(Acquisition, backref='data'),
})
orm.mapper(Acquisition, acquisitions)
Any advice?
Thanks for your support
neurino
--
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] Using multiple databases for reliability (NOT sharding)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello everyone, I'm in process of writing a distributed application and I'd like to use SQLAlchemy as backend. However, it's not performance that is my concern, but reliability. Suppose I have n server nodes. Writing: I would like to be able to write on any node from 1 to n, and have write results being sent to DB on every node. Reading: I would like to randomly select one of the n clean (up-to-date) nodes and use it for reading objects (normal SA session handling). Rationale: the write stream in my case is not going to be very large (storing test results), while read stream is going to be heavy, so I can afford this sort of architecture. On the face of it, implementing such scenario manually should be simple: just wrap around SA session and have the object sent to each backend in turn (yes, I know, it's a performance hit but that's not a big problem in this project). However, suppose one of the nodes gets offline at some moment: it would have to be marked as 'dirty' and synchronized somehow with other nodes when returned to 'online' status. This gets complex and risky. Alternatively, I could go with the "low tech" version: always assign particular client to a particular server node, and back the DB up / replicate it elsewhere. But this cuts into availability and makes me maintain n backups / replicas. - -- Regards, mk - -- Premature optimization is the root of all fun. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQEcBAEBAgAGBQJNCLU/AAoJEFMgHzhQQ7hO/VYH+wXF08U/+dSJ0op9/h9KgnO3 fclL3eTuRu1ppZtISoEf3VFoJoE6bzlOU2FYd/YviGHHgU3MoK+QsgL6rPiA1lGp wITsKExnl4jZPvGBe4pT+QQivzVMdENNTuIClGjLJq+DiqXYL7gkdzU2qukdHQB7 JhyVyvKicU0h+E6jvlv8CpVg2WpLNyGXrmpSTap0Fs3FnUcs18P7hZCsZWNxt+mw nMFD9Zp/BTGiB0eOJDC6reL+ZtjDc23/oKskTp3tFI4m3KOri+k1XyO8i1DEPbiH fVvUPy2610+Im8/y3a1gnyxktECIhpDRsErE5lm4pXfe01dDchSkQc5eDIyECdY= =whqS -END PGP SIGNATURE- -- 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.
