Re: [sqlalchemy] Multi-column primary key with autoincrement?
Heyho! [multi-column primary key where one column is autoincrement int] On Wednesday 16 December 2009 05.29:54 Daniel Falk wrote: The true problem here is with sqlite, which tries to make a smart choice about whether to autoincrement or not. And it gets it wrong. SQLAlchemy is correct to not specify the id field in the INSERT statement. That's the cue to the db that it needs to supply that value on its own. Hmm. Closer inspection shows that * sqlite doesn't even support the scenario I want (autoincrement on one column with multi column primary key) and * sqlalchemy doesn't notice this and just creates a two column primary key without autoincrement at all. Is there any hope that sqlalchemy will start to simulate the feature on sqlite? (I'm using the python-pysqlite2 2.5.5-3 / libsqlite3-0 3.6.20-1 / python-sqlalchemy 0.5.6-1 packages from Debian squeeze, btw) I've opened #1642 now. PostgreSQL handles this in just the way I was expecting (no surprise since my expectation on autoincrement columns is derived from the way pg builds its serial data type :-), so I'll have to test if sqlalchemy will do the right thing here. Then I can at least use pg (I was planning to do so in production anyway; sqlite is convenient for development though.) cheers -- vbi -- featured product: GNU Privacy Guard - http://gnupg.org signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Warning when relation on mapper supercedes the same relation on inherited mapper
Hi,
I created a mapper inheriting from another mapper and overriding a
relation definition, and got this warning:
Warning: relation 'dimensions' on mapper 'Mapper|DataSetSlice|
dataset_slice' supercedes the same relation on inherited mapper
'Mapper|DataSet|dataset'; this can cause dependency issues during
flush
I'd like to understand those dependency issues better (read: at all),
to know whether they apply in my case.
The new class I am mapping is class DataSetSlice(DataSet), which
defines a slice out of another DataSet (identified by parent_id, a
self-join on the dataset table), but is also a DataSet in its own
right (fitting into our inventory/categorization system). So there is
an inheritance relationship and also (separately) a parent/child
relationship.
A DataSet has dimensions (many-to-many), and a DataSetSlice logically
has the same dimensions as its parent. So the DataSet mapper has this
relation:
dataset_mapper = mapper(DataSet, dataset_table
...,
properties={
'dimensions': relation(Dimension,
secondary=dataset_dimension_association,
order_by=dataset_dimension_association.c.dimension_id,
backref='referencing_datasets'
),
})
And the DataSetSlice (subclass) mapper has this instead:
datasetslice_mapper = mapper(DataSetSlice, datasetslice_table,
...,
properties={
'dimensions': relation(Dimension,
secondary=dataset_dimension_association,
primaryjoin=dataset_dimension_association.c.dataset_id
== datasetslice_table.c.parent_id,
foreign_keys=[dataset_dimension_association.c.dataset_id,
dataset_dimension_association.c.dimension_id
],
order_by=dataset_dimension_association.c.dimension_id,
backref='referencing_dataset_slices'
),
})
The salient difference is that the primary join references parent_id
in the subclass table instead of id in the superclass table --- but
these are the same by a foreign key relationship. Thus I'm making a
slice have the same dimensions as its parent.
Could someone please explain the dependency issues that can arise from
this? (Or explain generally the kind of dependency issue this warning
refers to?)
Regards,
- Gulli
--
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] Multi-column primary key with autoincrement?
Adrian von Bidder wrote: Heyho! [multi-column primary key where one column is autoincrement int] On Wednesday 16 December 2009 05.29:54 Daniel Falk wrote: The true problem here is with sqlite, which tries to make a smart choice about whether to autoincrement or not. And it gets it wrong. SQLAlchemy is correct to not specify the id field in the INSERT statement. That's the cue to the db that it needs to supply that value on its own. Hmm. Closer inspection shows that * sqlite doesn't even support the scenario I want (autoincrement on one column with multi column primary key) and * sqlalchemy doesn't notice this and just creates a two column primary key without autoincrement at all. Is there any hope that sqlalchemy will start to simulate the feature on sqlite? (I'm using the python-pysqlite2 2.5.5-3 / libsqlite3-0 3.6.20-1 / python-sqlalchemy 0.5.6-1 packages from Debian squeeze, btw) I've opened #1642 now. Sorry, I've closed it. SQLite doesn't support autoincrement on composite PKs and theres no one-size-fits-all way to simulate this, so its up to the user. You need to either use the default keyword and specify a function or SQL expression that will generate new identifiers, or just set up the PK attributes on your new objects before adding them to the session. PostgreSQL handles this in just the way I was expecting (no surprise since my expectation on autoincrement columns is derived from the way pg builds its serial data type :-), so I'll have to test if sqlalchemy will do the right thing here. Then I can at least use pg (I was planning to do so in production anyway; sqlite is convenient for development though.) absolutely, PG uses sequences and has no issue here. the no-autoincrement-sqlite thing is an only-sqlite issue and we also have some unit tests that specifically skip sqlite for this reason. cheers -- vbi -- featured product: GNU Privacy Guard - http://gnupg.org -- 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] Warning when relation on mapper supercedes the same relation on inherited mapper
Gunnlaugur Briem wrote:
Hi,
I created a mapper inheriting from another mapper and overriding a
relation definition, and got this warning:
Warning: relation 'dimensions' on mapper 'Mapper|DataSetSlice|
dataset_slice' supercedes the same relation on inherited mapper
'Mapper|DataSet|dataset'; this can cause dependency issues during
flush
I'd like to understand those dependency issues better (read: at all),
to know whether they apply in my case.
The new class I am mapping is class DataSetSlice(DataSet), which
defines a slice out of another DataSet (identified by parent_id, a
self-join on the dataset table), but is also a DataSet in its own
right (fitting into our inventory/categorization system). So there is
an inheritance relationship and also (separately) a parent/child
relationship.
A DataSet has dimensions (many-to-many), and a DataSetSlice logically
has the same dimensions as its parent. So the DataSet mapper has this
relation:
dataset_mapper = mapper(DataSet, dataset_table
...,
properties={
'dimensions': relation(Dimension,
secondary=dataset_dimension_association,
order_by=dataset_dimension_association.c.dimension_id,
backref='referencing_datasets'
),
})
And the DataSetSlice (subclass) mapper has this instead:
datasetslice_mapper = mapper(DataSetSlice, datasetslice_table,
...,
properties={
'dimensions': relation(Dimension,
secondary=dataset_dimension_association,
primaryjoin=dataset_dimension_association.c.dataset_id
== datasetslice_table.c.parent_id,
foreign_keys=[dataset_dimension_association.c.dataset_id,
dataset_dimension_association.c.dimension_id
],
order_by=dataset_dimension_association.c.dimension_id,
backref='referencing_dataset_slices'
),
})
The salient difference is that the primary join references parent_id
in the subclass table instead of id in the superclass table --- but
these are the same by a foreign key relationship. Thus I'm making a
slice have the same dimensions as its parent.
Just to establish what I've understood here:
- there's two tables, dataset_table and datasetslice_table.
- these two tables have *two* foreign key references to each other - an
inheritance relationship (on unknown columns since they aren't displayed
here) and another on parent_id to id, which is a one-to-many
association.
- for each row in dataset_table, a row in dataset_dimension_association
may exist.
- *however*, if a row in dataset_table also references datasetslice_table
via the inheritance relation, then there may *not* be a row in
dataset_dimension_association with that dataset_table's id - because the
mapping says that dimensions now needs to reference
dataset_dimension_association via *another* row in dataset_table which it
references via parent_id. This is a constraint that isn't expressed in
the schema but is implied by the mapping I see.
It's that last constraint that is very awkward here and is not in the
spirit of relational databases. It means that its impossible to create a
reasonable join from dataset_table to dataset_dimension_association, since
for some rows in dataset_table, the join is invalid.
The hypothetical issue as far as flush() is that both dimension relations
would have some interaction when working out dependencies and association
row values, even for a DataSetSlice object, and would incur conflicting
data. I'm not sure if that is the current behavior and would have to
verify, though the warning stays in place since with inheritance, you need
to look at relations from table hierarchy A to table B in terms of the
bottommost table in the A hierarchy - relational inheritance is not really
polymorphic in that way, for the join reasons I mention above.
--
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] Multi-column primary key with autoincrement?
On Dec 16, 2009, at 09:32 , Adrian von Bidder wrote: sqlite is convenient for development though Except when it *adds* complexity? ;-) -- Alex Brasetvik -- 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] sqlite and thread
Hi, I'm using a sqlite file based database, I'm having problem making database calls from two different thread, the error is the following: SQLite objects created in a thread can only be used in that same thread here is my sa configuration: from sqlalchemy.orm import sessionmaker from sqlalchemy.orm import scoped_session from sqlalchemy import create_engine from sqlalchemy.pool import NullPool from myapp import settings import sqlite3 connectionstring = 'sqlite:///' + settings.DATABASE_PATH+'? check_same_thread=False' #engine = create_engine(connectionstring, echo=settings.DEBUG, echo_pool=settings.DEBUG) engine = create_engine(connectionstring, echo=settings.DEBUG, pool=NullPool(lambda: sqlite3.connect(settings.DATABASE_PATH))) #engine = create_engine(connectionstring, echo=True, echo_pool=True) sess = scoped_session(sessionmaker(bind=engine)) #sess = sessionmaker(bind=engine) as you can see I'm using check_same_thread=False, Null connection pool and scoped session, what's wrong? thanks Nicola -- 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] sqlite and thread
drakkan wrote: Hi, connectionstring = 'sqlite:///' + settings.DATABASE_PATH+'? check_same_thread=False' #engine = create_engine(connectionstring, echo=settings.DEBUG, echo_pool=settings.DEBUG) engine = create_engine(connectionstring, echo=settings.DEBUG, this line: pool=NullPool(lambda: sqlite3.connect(settings.DATABASE_PATH))) negates the effect of the check_same_thread flag in this line since the fully constructed pool + creation function you're sending means the URL isn't used: engine = create_engine(connectionstring, echo=settings.DEBUG, you only need pool_class=NullPool in your create_engine here. Example here: http://www.sqlalchemy.org/trac/wiki/DatabaseNotes#SQLite -- 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: Modify the start number of a sequence after it's been created
Here's what I do.
First, I get the sequence f rom the table's columns
seq = table.c.id.default
# or if you have a mapper-class
# seq = MyClass.id_property.property.columns[0].default
Then, I execute that sequence to advance the sequence until it's the
value needed.
engine.execute(seq)
The reason I only execute the sequence once is because in my use case,
I know I've just inserted one record manually, so I only want to
advance the sequence once.
I think in your case, after getting the sequence, I would do something
like the following:
column = table.c.id
# or if you have a mapper
# column = MyClass.id_property.property.columns[0]
seq = column.sequence
# get the max ID
maxid = session.query(func.max(column)).one()[0]
# repeatedly advance the sequence until it's greater or equal than the
maxid
while seq.execute() maxid: pass
I don't think this is the best solution, but it's the best thing I've
come up with so far with my limited knowledge.
Good luck.
On Nov 16, 12:56 pm, Jaume Sabater jsaba...@gmail.com wrote:
Hello everyone!
I've been searching information regarding this topic but without help
so far. I have a dbloader.py script that takes information from a
number of CSV files and dumps it into the database. After that we
execute a number of tests to check all services are working fine
(returning the right information and calculated data such as
availability and so on).
In these CSV files I have manually set up the id fields of every
table, but when I add the rows to the database, I am passing the id
value as part of the dictionary, therefore the sequence of the primary
key is not updated. So, after all data has been loaded, I'd like to
update the start value of the sequence.
It's PostgreSQL, therefore I could do something like:
maxid = engine.execute('select max(id) from table').fetchone()[0]
engine.execute(select setval('table_id_seq', %s) % (maxid + 1))
But I'd like to do this properly, at SA level. Just in case database
changes in the future or just to satisfy myself. But I have not been
able to find the way to modify the attributes of the sequence of each
table I find in the metadata. I have found ways of retrieving the
nextid in the documentation, but not modifying it.
Is there a way to grab a sequence and set it's next value using the
abstractions SA facilitates?
Thanks in advance.
--
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: Type of Column added with column_property
Just to be clearer: if I used func.count, the property is correctly
set to Intege type. How can I create a column in the mapper and have
the column reflect the real type in the db.
I do introspections in columns to prepare the gui to display it and to
add filters on that field (http://sqlkit.argolinux.org/sqlkit/
filters.html) and that would help a lot.
thanks in advance
sandro
*:-)
On 15 Dic, 19:58, Alessandro Dentella san...@e-den.it wrote:
Hi,
is there a way to set the type of a column added to a mapper with
column_property?
m = mapper(New, t, properties={
'my_bool': column_property(
func.my_bool(t.c.id, type=Boolean)
)
})
func 'my_bool' is a stored procedure on Postgresql and returns a boolean, but
the type of the column is NullType:
m.get_property('my_bool').columns[0].type
NullType()
--
Sandro Dentella *:-)http://sqlkit.argolinux.org SQLkit home page -
PyGTK/python/sqlalchemy
--
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] Re: Type of Column added with column_property
sandro dentella wrote:
Just to be clearer: if I used func.count, the property is correctly
set to Intege type. How can I create a column in the mapper and have
the column reflect the real type in the db.
I do introspections in columns to prepare the gui to display it and to
add filters on that field (http://sqlkit.argolinux.org/sqlkit/
filters.html) and that would help a lot.
thanks in advance
sandro
*:-)
On 15 Dic, 19:58, Alessandro Dentella san...@e-den.it wrote:
Hi,
is there a way to set the type of a column added to a mapper with
column_property?
m = mapper(New, t, properties={
'my_bool': column_property(
func.my_bool(t.c.id, type=Boolean)
)
})
func 'my_bool' is a stored procedure on Postgresql and returns a
boolean, but
the type of the column is NullType:
you mean to say type_=Boolean.
m.get_property('my_bool').columns[0].type
NullType()
--
Sandro Dentella *:-)http://sqlkit.argolinux.org SQLkit home page
- PyGTK/python/sqlalchemy
--
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] Multi-column primary key with autoincrement?
Heyho! On Wednesday 16 December 2009 16:36:10 Michael Bayer wrote: You need to either use the default keyword and specify a function or SQL expression that will generate new identifiers, or just set up the PK attributes on your new objects before adding them to the session. ... or just switch to pg for testing. I don't care to support other db anyway. Ok, thanks for clearing this up. cheers -- vbi -- If we can capitalize on something that did or did not happen in 1947 then it can help the entire state. -- Rep. Dan Foley on inventing the Extraterrestrial Culture Day signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: performance issues
Hello, just in case you're not motivated to share mappings here, I would note that an incorrect placement of a flag like remote_side on a relation() may be causing this. I would have to produce anonymized mappings, but I will do so if it's useful. What do you mean by incorrect placement of a flag like `remote_side`? I do have one (exactly one) relation with a `remote_side` flag, but the class it is defined on isn't involved in the script I have timed here. (it is on a recursive relation) yet another thought, which again reveals how much easier this would be with some sample code - if you're in a loop that is calling query(), then changing things, then around again, you may have excessive triggering of autoflush going on, which also can result in lots of _save_obj() calls. Set the autoflush flag on your Session to be false, which you can do temporarily within a particular section or across the whole session. Calling commit() will issue a flush(), or alternatively you can call flush() every few thousand new records. Ah, a very useful tip. Setting autoflush to False does speed up things quite a bit (a sample run of the script is 2x faster). I haven't found how to enable it temporarily, though. I'm using a ScopedSession instance and if I call `db_session.configure (autoflush=False)`, or set the autoflush attribute, autoflushing still happens. An example: db_session sqlalchemy.orm.scoping.ScopedSession object at 0x2e467d0 db_session.autoflush = False lg = db_session.query(Logement)[0] [ SNIP long SELECT ] lg.tel1 = abc db_session.query(Zone)[0] 2009-12-17 00:11:35,161 INFO sqlalchemy.engine.base.Engine.0x...c550 UPDATE logement SET date_modif=%s, tel1=%s WHERE logement.id_logement = %s 2009-12-17 00:11:35,162 INFO sqlalchemy.engine.base.Engine.0x...c550 [datetime.datetime(2009, 12, 17, 0, 11, 35, 161436), 'abc', 4L] 2009-12-17 00:11:35,167 INFO sqlalchemy.engine.base.Engine.0x...c550 SELECT zone.id_zone AS zone_id_zone, zone.cp AS zone_cp FROM zone LIMIT 0, 1 2009-12-17 00:11:35,168 INFO sqlalchemy.engine.base.Engine.0x...c550 [] xxx.Zone object at 0x3309150 (as you see, an UPDATE is issued before the SELECT for the query) Regards Antoine. -- 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] Re: performance issues
Antoine Pitrou wrote: Hello, just in case you're not motivated to share mappings here, I would note that an incorrect placement of a flag like remote_side on a relation() may be causing this. I would have to produce anonymized mappings, but I will do so if it's useful. What do you mean by incorrect placement of a flag like `remote_side`? I do have one (exactly one) relation with a `remote_side` flag, but the class it is defined on isn't involved in the script I have timed here. (it is on a recursive relation) if its backwards versus a backref that is also present, it can create an overly complex dependency chain. yet another thought, which again reveals how much easier this would be with some sample code - if you're in a loop that is calling query(), then changing things, then around again, you may have excessive triggering of autoflush going on, which also can result in lots of _save_obj() calls. Set the autoflush flag on your Session to be false, which you can do temporarily within a particular section or across the whole session. Calling commit() will issue a flush(), or alternatively you can call flush() every few thousand new records. Ah, a very useful tip. Setting autoflush to False does speed up things quite a bit (a sample run of the script is 2x faster). I haven't found how to enable it temporarily, though. I'm using a ScopedSession instance and if I call `db_session.configure (autoflush=False)`, or set the autoflush attribute, autoflushing still happens. An example: the configure only takes effect for the next session created. so if you said Session.remove(); Session.configure(); Session(), that would do it. Easier for this is to hit the flag directly: Session().autoflush = False db_session sqlalchemy.orm.scoping.ScopedSession object at 0x2e467d0 db_session.autoflush = False lg = db_session.query(Logement)[0] [ SNIP long SELECT ] lg.tel1 = abc db_session.query(Zone)[0] 2009-12-17 00:11:35,161 INFO sqlalchemy.engine.base.Engine.0x...c550 UPDATE logement SET date_modif=%s, tel1=%s WHERE logement.id_logement = %s 2009-12-17 00:11:35,162 INFO sqlalchemy.engine.base.Engine.0x...c550 [datetime.datetime(2009, 12, 17, 0, 11, 35, 161436), 'abc', 4L] 2009-12-17 00:11:35,167 INFO sqlalchemy.engine.base.Engine.0x...c550 SELECT zone.id_zone AS zone_id_zone, zone.cp AS zone_cp FROM zone LIMIT 0, 1 2009-12-17 00:11:35,168 INFO sqlalchemy.engine.base.Engine.0x...c550 [] xxx.Zone object at 0x3309150 (as you see, an UPDATE is issued before the SELECT for the query) Regards Antoine. -- 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] Re: performance issues
I would have to produce anonymized mappings, but I will do so if it's
What do you mean by incorrect placement of a flag like
`remote_side`? I do have one (exactly one) relation with a
`remote_side` flag, but the class it is defined on isn't involved in
the script I have timed here. (it is on a recursive relation)
if its backwards versus a backref that is also present, it can create an
overly complex dependency chain.
I'm not sure I understand. Does it also apply when the backref is
created implicitly, as follows:
class OT(DeclarativeBase):
[ snip ]
id_ot = Column(Integer, autoincrement=True, primary_key=True)
id_parent = Column(Integer,
ForeignKey('ot.id_ot', use_alter=True,
name='fk_ot_parent'))
OT.parent = relation(OT, remote_side=[OT.id_ot],
backref=backref('children'))
the configure only takes effect for the next session created. so if you
said Session.remove(); Session.configure(); Session(), that would do it.
Ok, thank you.
Regards
Antoine.
--
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: Warning when relation on mapper supercedes the same relation on inherited mapper
On Dec 16, 4:08 pm, Michael Bayer mike...@zzzcomputing.com wrote: - there's two tables, dataset_table and datasetslice_table. - these two tables have *two* foreign key references to each other - an inheritance relationship (on unknown columns since they aren't displayed here) and another on parent_id to id, which is a one-to-many association. Yes, sorry I was unclear. The inheritance is simple joined-table inheritance, on columns named id in both tables, the subclass id being a foreign key referencing the superclass id. There is a discriminator column type in the dataset table with the value 'slice' for DataSetSlice datasets, and 'base' for the regular ones. - for each row in dataset_table, a row in dataset_dimension_association may exist. - *however*, if a row in dataset_table also references datasetslice_table via the inheritance relation, then there may *not* be a row in dataset_dimension_association with that dataset_table's id - because the mapping says that dimensions now needs to reference dataset_dimension_association via *another* row in dataset_table which it references via parent_id. This is a constraint that isn't expressed in the schema but is implied by the mapping I see. Yes --- or rather if there *is* such a row, it is ignored (on the dataset side, while the backref property on the dimension will still contain the dataset) because the dataset_table row has type='slice'. But you're right (of course), this is bad relational design because of the join inconsistency and the asymmetry in the many-to-many relation. The hypothetical issue as far as flush() is that both dimension relations would have some interaction when working out dependencies and association row values, even for a DataSetSlice object, and would incur conflicting data. I'm not sure if that is the current behavior and would have to verify, though the warning stays in place since with inheritance, you need to look at relations from table hierarchy A to table B in terms of the bottommost table in the A hierarchy - relational inheritance is not really polymorphic in that way, for the join reasons I mention above. Very true. Thanks heaps for putting that warning in the code; I would have made do with that flawed design if you hadn't. This was driven by the desire to (a) let slices be datasets in their own right (fitting them naturally into our system's inventory, category hierarchy, browsing UI, etc.) and (b) avoid the duplication of explicitly associating each of the slices with the same set of dimensions as its parent dataset. So I wanted to override the dimensions property in the DataSetSlice subclass, delegating to the parent dataset (the OO way), but that property was injected by the mapper, so I strayed down the path of overriding it there. I could consider this dimensions property the “raw dimensions” (maybe rename it as such) and define a separate (non-ORM) property for the “dimensions to use”, delegating to self.dimensions in DataSet and to self.parent.dimensions in DataSetSlice. But then still (a) dimension associations for a type='slice' dataset do not make sense and should be constrained not to exist, and (b) querying for datasets having a given dimension will only find non-slice datasets (though an outer- join would draw in the slices). So the relational design flaw is still there. Instead I will probably just add the duplicate dimension associations, a lesser evil. Additions/removals of dimensions of existing datasets will probably not be common anyway. And possibly there will be reason to let slice dimensions differ from parent dataset dimensions later on. (A dataset sliced to just one value of a given dimension could be considered not to have that dimension, for instance.) Thank you for your characteristically helpful response! (And for all the work you put into SQLAlchemy in general, and into supporting its users.) Regards, - Gulli -- 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] lazy instrumented attributes and pickle
I use pickle to serialise unsaved objects in a user session. Normally
this works fine, except that for development I use an auto-reloading
server, and pickling some objects is hitting a case where some lazy
attribute isn't fully compiled.
...
File '/home/avdd/work/careflight/src/intranet.ops2/carenet/lib/
python2.5/site-packages/sqlalchemy/orm/collections.py', line 607 in
__getstate__
return {'key': self.attr.key,
AttributeError: 'NoneType' object has no attribute 'key'
This is when using an ordering_list.
I would have assumed that calling orm.compile_mappers() is enough to
prevent this problem, but that is not so.
Is there some hook that I can call when my application is fully
initialised to ensure that all attributes are fully instrumented and
avoid this pickling problem? Or can I just do a sweep of all my
mapper attributes at startup?
While the problem is nothing more than an inconvenience for me, I
intend to make heavy use of pickled objects for draft object storage
and don't want my users losing data across system restarts.
a.
--
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.
