[sqlalchemy] SQLAlchemy - Problem with an association table and dates in primary join
Hi,
I am working on defining my mapping with SQLAlchemy and I am pretty
much done except one thing. I have a 'resource' object and an
association table 'relation' with several properties and a
relationship between 2 resources. What I have been trying to do almost
successfully so far, is to provide on the resource object 2
properties: parent and children to traverse the tree stored by the
association table. A relation between 2 properties only last for a
while, so there is a start and end date. Only one resource can be the
parent of another resource at a time.
My problem is that if I expire one relation and create a new one, the
parent property is not refreshed. I am thinking maybe there an issue
with the primaryjoin for the parent property of resource.
Here is some code:
resource_table = model.tables['resource']
relation_table = model.tables['resource_relation']
mapper(Resource, resource_table,properties = {'type' :
relation(ResourceType,lazy = False), 'groups' : relation(Group,
secondary = model.tables['resource_group'], backref = 'resources'),
'parent' : relation(Relation, uselist=False, primaryjoin =
and_(relation_table.c.res_id == resource_table.c.res_id,
relation_table.c.end_date func.now())),
'children' : relation(Relation, primaryjoin =
and_(relation_table.c.parent_id == resource_table.c.res_id,
relation_table.c.end_date func.now()))})
mapper(Relation, relation_table, properties = {'resource' :
relation(Resource, primaryjoin = (relation_table.c.res_id ==
resource_table.c.res_id)), 'parent' : relation(Resource, primaryjoin =
(relation_table.c.parent_id == resource_table.c.res_id))})
oldrelation = resource.parent
oldrelation.end_date = datetime.today()
relation = self.createRelation(parent, resource)
# Here the relation object has not replaced oldrelation in the
resource object
Hi,
I am working on defining my mapping with SQLAlchemy and I am pretty
much done except one thing. I have a 'resource' object and an
association table 'relation' with several properties and a
relationship between 2 resources. What I have been trying to do almost
successfully so far, is to provide on the resource object 2
properties: parent and children to traverse the tree stored by the
association table. A relation between 2 properties only last for a
while, so there is a start and end date. Only one resource can be the
parent of another resource at a time.
My problem is that if I expire one relation and create a new one, the
parent property is not refreshed. I am thinking maybe there an issue
with the primaryjoin for the parent property of resource.
Here is some code:
resource_table = model.tables['resource']
relation_table = model.tables['resource_relation']
mapper(Resource, resource_table,properties = {'type' :
relation(ResourceType,lazy = False), 'groups' : relation(Group,
secondary = model.tables['resource_group'], backref = 'resources'),
'parent' : relation(Relation, uselist=False, primaryjoin =
and_(relation_table.c.res_id == resource_table.c.res_id,
relation_table.c.end_date func.now())),
'children' : relation(Relation, primaryjoin =
and_(relation_table.c.parent_id == resource_table.c.res_id,
relation_table.c.end_date func.now()))})
mapper(Relation, relation_table, properties = {'resource' :
relation(Resource, primaryjoin = (relation_table.c.res_id ==
resource_table.c.res_id)), 'parent' : relation(Resource, primaryjoin =
(relation_table.c.parent_id == resource_table.c.res_id))})
oldrelation = resource.parent
oldrelation.end_date = datetime.today()
relation = self.createRelation(parent, resource)
# Here the relation object has not replaced oldrelation in the
resource object
Any idea ?
Thanks,
Richard Lopes
--
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] [PROPOSE] Integrate migration support in SQLAlchemy
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Bayer ha scritto: I'm pretty much -1 on this since I think migrations need to have absolute flexibility - the listener system within create_all() is not at all designed to be used to that degree. Another solution is to add a completely new interface. The engine will have new `upgrade` and `downgrade` methods, and SQLAlchemy will have a new engine.migrate module, with custom Visitors for migration events. You can migrate a MetaData or single Tables. For starters, its an entirely in-memory system - if you have hundreds of migration scripts its quite cumbersome to read them all into memory for a single migration. The idea was to use functions, and not scripts. I would add the migration functions in the same module where the metadata is defined. I don't see problems with this. Only a single function is called at one time. Similarly, the concept of a version as an integer number is not really flexible enough - The idea was to keep it simple. I would like to investigate the creation of migration scripts between branches as well. The version can then be a string, but it needs to sortable (see setuptool as an example). The mechanism of schema versioning is not at all something that belongs in SQLAlchemy core. Yes. But it is a simple metadata. It can also be stored in the `info` dictionary (and this is what I plan to do if I have to write the support by myself). I would like to implement an alternate version of Migrate at some point as an example or recipe - the ALTER constructs themselves could eventually live within SQLAlchemy.schema, since those are non-controversial atomic units of functionality. +1 The versioning would be a simple script system that starts with the integer version but could later work off of md5 hashes perhaps - but this would just be a usage recipe, that like the PostGIS example would launch into a real product. the main point would be that the scripting system would be cleanly separated from the system of interaction with the database. The existing Migrate project is burdened by three things - the monkeypatching legacy of older SQLA versions, an overly rigid and complex system of creating engines that makes it almost impossible to control the transactional scope of a migration, and its superfluous features, namely the whole schema comparison system that I could do without. Another problem, for me, is that migration scripts apply to the whole database (unless I'm missing something - I have only read the documentation and some of the code). However in my projects I have several metadata objects. Some of these metadata objects are defined in external packages. This is the reason why I think that migration should be implemented as normal callback functions. And it should possible to register several event listeners on the same event, so that an application can add its own behaviour when a schema in an external package is upgraded/downgraded. [...] Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuP854ACgkQscQJ24LbaUTmiACePF2aKGBYbWgVLle5B5aHuqdV HNkAoJe2czsAXtTF1AFAvyUvMs/qJ+2z =bNMq -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.
[sqlalchemy] case sensitive Unicode and String columns
Hi All, I'm looking to create a model with a unicode or string column type that is case sensitive. I'm looking to do this in the model in such a way that the code in the model doesn't know or care about what backend database is used, but that barfs if it's ever used with a backend that doesn't actually support case-sensitive strings or unicodes. The current set of back ends we're targeting is (MySQL,sqlite)... How do I do this? Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] bug in sqllite dialect?
Hi Michael, Thanks for this, I thought I asked this separately but I can't find the mail now... How would you recommend I work this now in 0.5.8 until I can move to 0.6.0? (which will take some months :-S) I seem to remember you suggesting a custom type. Where can I find examples of those to work against? Has anyone (hi, list, talking to you too!) already done a custom type for this specific problem? cheers, Chris Michael Bayer wrote: fixed in r6859. please don't use those crappy pysqlite converters. On Feb 26, 2010, at 8:50 AM, Chris Withers wrote: from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.session import Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.schema import Column from sqlalchemy.types import String, Numeric, Integer import unittest from decimal import Decimal class Test(unittest.TestCase): def test_truncate(self): # setup engine = create_engine(sqlite://) self.Session = sessionmaker( bind=engine, autoflush=True, autocommit=False ) Base = declarative_base(bind=engine) class MyModel(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36,scale=12)) Base.metadata.create_all() session = self.Session() # precision=36 scale=12 should mean this can handle 12 decimal places # and this has 12 decimal places. session.add(MyModel(value=152.737826714556)) session.commit() obj = session.query(MyModel).one() # this will fail with the output, it shouldn't # Decimal(152.737826715) != Decimal(152.737826714556) self.assertEqual(obj.value, Decimal(152.737826714556)) -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] case sensitive Unicode and String columns
On Thu, Mar 4, 2010 at 11:34 AM, Chris Withers ch...@simplistix.co.uk wrote: Hi All, I'm looking to create a model with a unicode or string column type that is case sensitive. I'm looking to do this in the model in such a way that the code in the model doesn't know or care about what backend database is used, but that barfs if it's ever used with a backend that doesn't actually support case-sensitive strings or unicodes. The current set of back ends we're targeting is (MySQL,sqlite)... How do I do this? I think all databases that SQLAlchemy supports (in fact, likely all databases in use today) support case-sensitive strings by default, so I don't know if this something you'll need to worry about in your code. Maybe I am misunderstanding what you are trying to do? -Daniel -- 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] preserving uselist relation instances in a session.refresh()
If I use session.refresh(obj) to re-load an obj that has a one-to-many relational property, the objects in the list are *replaced* instead of *refreshed* if they already exist. Suppose department has a list of employees: suppose dept.employees = [ emp1, emp2 ] session.refresh(dept) the dept.employees list's elements are replaced with new objects instead of reusing those that existed and refreshing them. Is it possible to have those same objects re-used and simply refreshed instead of replaced? -- 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] [PROPOSE] Integrate migration support in SQLAlchemy
Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- But it is a simple metadata. It can also be stored in the `info` dictionary (and this is what I plan to do if I have to write the support by myself). yeah I dont really want any migration aware in core. I don't consider create_all() to be broken. So you are free to use info for your own needs. Another problem, for me, is that migration scripts apply to the whole database (unless I'm missing something - I have only read the documentation and some of the code). if it were more configurable, it could track versioning in any number of version tables or columns. -- 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] insert defaults
Hey,
I'm trying to create dynamic defaults for columns ala http://
www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-functions.
MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to
leverage. I don't want to compress with python's zlib because I have
legacy tables that were compressed using MySQL (which has a weird non-
standard zip header and body), and I need to interface with them.
Anyway, during an insert or update, I want to grab the 'text' variable
from the instance object and insert it into the database like:
COMPRESS(the text value). Obviously context.current_parameters is
not the appropriate object, but I can't figure out if it's possible to
access the instance being inserted/updated.
def compress_text(context):
return COMPRESS('%s') % context.current_parameters['text']
class Tree(BaseStruct, Base):
__tablename__ = 'tree'
__table_args__ = (
{'autoload':True}
)
compressed =
deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text))
text =
column_property(select([UNCOMPRESS(compressed)]),deferred=True)
Is this possible with 0.5.7?
--
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] preserving uselist relation instances in a session.refresh()
Kent wrote: If I use session.refresh(obj) to re-load an obj that has a one-to-many relational property, the objects in the list are *replaced* instead of *refreshed* if they already exist. Suppose department has a list of employees: suppose dept.employees = [ emp1, emp2 ] session.refresh(dept) the dept.employees list's elements are replaced with new objects instead of reusing those that existed and refreshing them. Is it possible to have those same objects re-used and simply refreshed instead of replaced? you can only turn off refresh-expire cascade, which will prohibit the operation from traveling into the child objects. the collection is still refreshed for obvious reasons, its one of the attributes on your mapped object. To achieve your specified behavior, use session.refresh() given as its second argument the set of attribute names which are safe to be reloaded completely (in this case the scalars). Then for each uselist attribute, iterate the collection of each and call the desired version of session.refresh() for those. This is an easy refresh() function to create in a generalized way by inspecting the class-level attributes of the incoming object. -- 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] case sensitive Unicode and String columns
Daniel Robbins wrote: On Thu, Mar 4, 2010 at 11:34 AM, Chris Withers ch...@simplistix.co.uk wrote: Hi All, I'm looking to create a model with a unicode or string column type that is case sensitive. I'm looking to do this in the model in such a way that the code in the model doesn't know or care about what backend database is used, but that barfs if it's ever used with a backend that doesn't actually support case-sensitive strings or unicodes. The current set of back ends we're targeting is (MySQL,sqlite)... How do I do this? I think all databases that SQLAlchemy supports (in fact, likely all databases in use today) support case-sensitive strings by default, so I don't know if this something you'll need to worry about in your code. Maybe I am misunderstanding what you are trying to do? hes likely referring to case-sensitive collation support. Build a TypeDecorator and intercept the dialect in the process_bind_param() method. -Daniel -- 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] bug in sqllite dialect?
Chris Withers wrote: Hi Michael, Thanks for this, I thought I asked this separately but I can't find the mail now... How would you recommend I work this now in 0.5.8 until I can move to 0.6.0? (which will take some months :-S) I seem to remember you suggesting a custom type. Where can I find examples of those to work against? Has anyone (hi, list, talking to you too!) already done a custom type for this specific problem? people do custom types for all sorts of things. In the case of the Decimal here I'd likely subclass sqlalchemy.databases.sqlite.SLNumeric which should ensure that your own bind_processor() and result_processor() methods can be called. Or as in the doc below you can subclass TypeEngine directly. http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html#custom-types cheers, Chris Michael Bayer wrote: fixed in r6859. please don't use those crappy pysqlite converters. On Feb 26, 2010, at 8:50 AM, Chris Withers wrote: from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.session import Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.schema import Column from sqlalchemy.types import String, Numeric, Integer import unittest from decimal import Decimal class Test(unittest.TestCase): def test_truncate(self): # setup engine = create_engine(sqlite://) self.Session = sessionmaker( bind=engine, autoflush=True, autocommit=False ) Base = declarative_base(bind=engine) class MyModel(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) value = Column(Numeric(precision=36,scale=12)) Base.metadata.create_all() session = self.Session() # precision=36 scale=12 should mean this can handle 12 decimal places # and this has 12 decimal places. session.add(MyModel(value=152.737826714556)) session.commit() obj = session.query(MyModel).one() # this will fail with the output, it shouldn't # Decimal(152.737826715) != Decimal(152.737826714556) self.assertEqual(obj.value, Decimal(152.737826714556)) -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] insert defaults
patrick wrote:
Hey,
I'm trying to create dynamic defaults for columns ala http://
www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-functions.
MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to
leverage. I don't want to compress with python's zlib because I have
legacy tables that were compressed using MySQL (which has a weird non-
standard zip header and body), and I need to interface with them.
Anyway, during an insert or update, I want to grab the 'text' variable
from the instance object and insert it into the database like:
COMPRESS(the text value). Obviously context.current_parameters is
not the appropriate object, but I can't figure out if it's possible to
access the instance being inserted/updated.
are you trying to create a *default* value for an INSERT/UPDATE when NULL
would otherwise be passed, or are you trying to run all incoming/outgoing
data through a SQL function ? those are two completely separate topics.
def compress_text(context):
return COMPRESS('%s') % context.current_parameters['text']
class Tree(BaseStruct, Base):
__tablename__ = 'tree'
__table_args__ = (
{'autoload':True}
)
compressed =
deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text))
text =
column_property(select([UNCOMPRESS(compressed)]),deferred=True)
Is this possible with 0.5.7?
--
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] Re: preserving uselist relation instances in a session.refresh()
What's strange is that I can't recreate the problem on more simple stage. Every time I refresh() on the parent object, the list objects remain the same. In other words, *sometimes* it behaves as I hope it to (by apparently refreshing the list's objects) and *sometimes* if throws them out and creates new ones. The mystery to me is what determines when it will create new instances vs. refreshing the existing ones? On Mar 4, 3:24 pm, Michael Bayer mike...@zzzcomputing.com wrote: Kent wrote: If I use session.refresh(obj) to re-load an obj that has a one-to-many relational property, the objects in the list are *replaced* instead of *refreshed* if they already exist. Suppose department has a list of employees: suppose dept.employees = [ emp1, emp2 ] session.refresh(dept) the dept.employees list's elements are replaced with new objects instead of reusing those that existed and refreshing them. Is it possible to have those same objects re-used and simply refreshed instead of replaced? you can only turn off refresh-expire cascade, which will prohibit the operation from traveling into the child objects. the collection is still refreshed for obvious reasons, its one of the attributes on your mapped object. To achieve your specified behavior, use session.refresh() given as its second argument the set of attribute names which are safe to be reloaded completely (in this case the scalars). Then for each uselist attribute, iterate the collection of each and call the desired version of session.refresh() for those. This is an easy refresh() function to create in a generalized way by inspecting the class-level attributes of the incoming object. -- 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: preserving uselist relation instances in a session.refresh()
refresh doesn't remove any objects from the session so its a matter of what is present in the session, not marked as dirty, and strongly referenced on the outside. if you're using refresh you shouldn't care about how it gets data back into the collection. Kent wrote: What's strange is that I can't recreate the problem on more simple stage. Every time I refresh() on the parent object, the list objects remain the same. In other words, *sometimes* it behaves as I hope it to (by apparently refreshing the list's objects) and *sometimes* if throws them out and creates new ones. The mystery to me is what determines when it will create new instances vs. refreshing the existing ones? On Mar 4, 3:24 pm, Michael Bayer mike...@zzzcomputing.com wrote: Kent wrote: If I use session.refresh(obj) to re-load an obj that has a one-to-many relational property, the objects in the list are *replaced* instead of *refreshed* if they already exist. Suppose department has a list of employees: suppose dept.employees = [ emp1, emp2 ] session.refresh(dept) the dept.employees list's elements are replaced with new objects instead of reusing those that existed and refreshing them. Is it possible to have those same objects re-used and simply refreshed instead of replaced? you can only turn off refresh-expire cascade, which will prohibit the operation from traveling into the child objects. the collection is still refreshed for obvious reasons, its one of the attributes on your mapped object. To achieve your specified behavior, use session.refresh() given as its second argument the set of attribute names which are safe to be reloaded completely (in this case the scalars). Then for each uselist attribute, iterate the collection of each and call the desired version of session.refresh() for those. This is an easy refresh() function to create in a generalized way by inspecting the class-level attributes of the incoming object. -- 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] Re: preserving uselist relation instances in a session.refresh()
I agree I shouldn't care, so maybe there is another way to attack my problem. The reason I care is because I've extended the python object with some auxiliary information that I need. After the refresh() in this case, I still need access to that data that is tied to the object, but not present in the database (it is transient data). If sqla creates a new instance, I loose that data. Is there a better mechanism for doing that? On Mar 4, 3:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: refresh doesn't remove any objects from the session so its a matter of what is present in the session, not marked as dirty, and strongly referenced on the outside. if you're using refresh you shouldn't care about how it gets data back into the collection. Kent wrote: What's strange is that I can't recreate the problem on more simple stage. Every time I refresh() on the parent object, the list objects remain the same. In other words, *sometimes* it behaves as I hope it to (by apparently refreshing the list's objects) and *sometimes* if throws them out and creates new ones. The mystery to me is what determines when it will create new instances vs. refreshing the existing ones? On Mar 4, 3:24 pm, Michael Bayer mike...@zzzcomputing.com wrote: Kent wrote: If I use session.refresh(obj) to re-load an obj that has a one-to-many relational property, the objects in the list are *replaced* instead of *refreshed* if they already exist. Suppose department has a list of employees: suppose dept.employees = [ emp1, emp2 ] session.refresh(dept) the dept.employees list's elements are replaced with new objects instead of reusing those that existed and refreshing them. Is it possible to have those same objects re-used and simply refreshed instead of replaced? you can only turn off refresh-expire cascade, which will prohibit the operation from traveling into the child objects. the collection is still refreshed for obvious reasons, its one of the attributes on your mapped object. To achieve your specified behavior, use session.refresh() given as its second argument the set of attribute names which are safe to be reloaded completely (in this case the scalars). Then for each uselist attribute, iterate the collection of each and call the desired version of session.refresh() for those. This is an easy refresh() function to create in a generalized way by inspecting the class-level attributes of the incoming object. -- 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.
Re: [sqlalchemy] Re: preserving uselist relation instances in a session.refresh()
Kent wrote: I agree I shouldn't care, so maybe there is another way to attack my problem. The reason I care is because I've extended the python object with some auxiliary information that I need. After the refresh() in this case, I still need access to that data that is tied to the object, but not present in the database (it is transient data). If sqla creates a new instance, I loose that data. Is there a better mechanism for doing that? You need to either manually keep strong references to each object that has the auxiliary information or disable the weak identity map. See http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-questions or http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-questions for more information. -Conor On Mar 4, 3:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: refresh doesn't remove any objects from the session so its a matter of what is present in the session, not marked as dirty, and strongly referenced on the outside. if you're using refresh you shouldn't care about how it gets data back into the collection. Kent wrote: What's strange is that I can't recreate the problem on more simple stage. Every time I refresh() on the parent object, the list objects remain the same. In other words, *sometimes* it behaves as I hope it to (by apparently refreshing the list's objects) and *sometimes* if throws them out and creates new ones. The mystery to me is what determines when it will create new instances vs. refreshing the existing ones? On Mar 4, 3:24 pm, Michael Bayer mike...@zzzcomputing.com wrote: Kent wrote: If I use session.refresh(obj) to re-load an obj that has a one-to-many relational property, the objects in the list are *replaced* instead of *refreshed* if they already exist. Suppose department has a list of employees: suppose dept.employees = [ emp1, emp2 ] session.refresh(dept) the dept.employees list's elements are replaced with new objects instead of reusing those that existed and refreshing them. Is it possible to have those same objects re-used and simply refreshed instead of replaced? you can only turn off refresh-expire cascade, which will prohibit the operation from traveling into the child objects. the collection is still refreshed for obvious reasons, its one of the attributes on your mapped object. To achieve your specified behavior, use session.refresh() given as its second argument the set of attribute names which are safe to be reloaded completely (in this case the scalars). Then for each uselist attribute, iterate the collection of each and call the desired version of session.refresh() for those. This is an easy refresh() function to create in a generalized way by inspecting the class-level attributes of the incoming object. -- 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: preserving uselist relation instances in a session.refresh()
Conor wrote: Kent wrote: I agree I shouldn't care, so maybe there is another way to attack my problem. The reason I care is because I've extended the python object with some auxiliary information that I need. After the refresh() in this case, I still need access to that data that is tied to the object, but not present in the database (it is transient data). If sqla creates a new instance, I loose that data. Is there a better mechanism for doing that? You need to either manually keep strong references to each object that has the auxiliary information or disable the weak identity map. See http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-questions or http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-questions for more information. That second link should be http://www.sqlalchemy.org/docs/05/session.html#session-attributes. Oops. On Mar 4, 3:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: refresh doesn't remove any objects from the session so its a matter of what is present in the session, not marked as dirty, and strongly referenced on the outside. if you're using refresh you shouldn't care about how it gets data back into the collection. Kent wrote: What's strange is that I can't recreate the problem on more simple stage. Every time I refresh() on the parent object, the list objects remain the same. In other words, *sometimes* it behaves as I hope it to (by apparently refreshing the list's objects) and *sometimes* if throws them out and creates new ones. The mystery to me is what determines when it will create new instances vs. refreshing the existing ones? On Mar 4, 3:24 pm, Michael Bayer mike...@zzzcomputing.com wrote: Kent wrote: If I use session.refresh(obj) to re-load an obj that has a one-to-many relational property, the objects in the list are *replaced* instead of *refreshed* if they already exist. Suppose department has a list of employees: suppose dept.employees = [ emp1, emp2 ] session.refresh(dept) the dept.employees list's elements are replaced with new objects instead of reusing those that existed and refreshing them. Is it possible to have those same objects re-used and simply refreshed instead of replaced? you can only turn off refresh-expire cascade, which will prohibit the operation from traveling into the child objects. the collection is still refreshed for obvious reasons, its one of the attributes on your mapped object. To achieve your specified behavior, use session.refresh() given as its second argument the set of attribute names which are safe to be reloaded completely (in this case the scalars). Then for each uselist attribute, iterate the collection of each and call the desired version of session.refresh() for those. This is an easy refresh() function to create in a generalized way by inspecting the class-level attributes of the incoming object. -- 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: preserving uselist relation instances in a session.refresh()
Thanks... that is very helpful. I could keep references to these. If I choose the apparently lazier route and set weak_identity_map=False, then does any other action besides explicitly expunging free this memory, such as when the session goes out of scope, I assume? Or do I need to carefully expunge them? On Mar 4, 4:09 pm, Conor conor.edward.da...@gmail.com wrote: Conor wrote: Kent wrote: I agree I shouldn't care, so maybe there is another way to attack my problem. The reason I care is because I've extended the python object with some auxiliary information that I need. After the refresh() in this case, I still need access to that data that is tied to the object, but not present in the database (it is transient data). If sqla creates a new instance, I loose that data. Is there a better mechanism for doing that? You need to either manually keep strong references to each object that has the auxiliary information or disable the weak identity map. See http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-quest... or http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-quest... for more information. That second link should behttp://www.sqlalchemy.org/docs/05/session.html#session-attributes. Oops. On Mar 4, 3:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: refresh doesn't remove any objects from the session so its a matter of what is present in the session, not marked as dirty, and strongly referenced on the outside. if you're using refresh you shouldn't care about how it gets data back into the collection. Kent wrote: What's strange is that I can't recreate the problem on more simple stage. Every time I refresh() on the parent object, the list objects remain the same. In other words, *sometimes* it behaves as I hope it to (by apparently refreshing the list's objects) and *sometimes* if throws them out and creates new ones. The mystery to me is what determines when it will create new instances vs. refreshing the existing ones? On Mar 4, 3:24 pm, Michael Bayer mike...@zzzcomputing.com wrote: Kent wrote: If I use session.refresh(obj) to re-load an obj that has a one-to-many relational property, the objects in the list are *replaced* instead of *refreshed* if they already exist. Suppose department has a list of employees: suppose dept.employees = [ emp1, emp2 ] session.refresh(dept) the dept.employees list's elements are replaced with new objects instead of reusing those that existed and refreshing them. Is it possible to have those same objects re-used and simply refreshed instead of replaced? you can only turn off refresh-expire cascade, which will prohibit the operation from traveling into the child objects. the collection is still refreshed for obvious reasons, its one of the attributes on your mapped object. To achieve your specified behavior, use session.refresh() given as its second argument the set of attribute names which are safe to be reloaded completely (in this case the scalars). Then for each uselist attribute, iterate the collection of each and call the desired version of session.refresh() for those. This is an easy refresh() function to create in a generalized way by inspecting the class-level attributes of the incoming object. -- 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: preserving uselist relation instances in a session.refresh()
Kent wrote: Thanks... that is very helpful. I could keep references to these. If I choose the apparently lazier route and set weak_identity_map=False, then does any other action besides explicitly expunging free this memory, such as when the session goes out of scope, I assume? Or do I need to carefully expunge them? don't disable the weak identity map. just maintain your own collection of all the objects you care about. On Mar 4, 4:09 pm, Conor conor.edward.da...@gmail.com wrote: Conor wrote: Kent wrote: I agree I shouldn't care, so maybe there is another way to attack my problem. The reason I care is because I've extended the python object with some auxiliary information that I need. After the refresh() in this case, I still need access to that data that is tied to the object, but not present in the database (it is transient data). If sqla creates a new instance, I loose that data. Is there a better mechanism for doing that? You need to either manually keep strong references to each object that has the auxiliary information or disable the weak identity map. See http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-quest... or http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-quest... for more information. That second link should behttp://www.sqlalchemy.org/docs/05/session.html#session-attributes. Oops. On Mar 4, 3:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: refresh doesn't remove any objects from the session so its a matter of what is present in the session, not marked as dirty, and strongly referenced on the outside. if you're using refresh you shouldn't care about how it gets data back into the collection. Kent wrote: What's strange is that I can't recreate the problem on more simple stage. Every time I refresh() on the parent object, the list objects remain the same. In other words, *sometimes* it behaves as I hope it to (by apparently refreshing the list's objects) and *sometimes* if throws them out and creates new ones. The mystery to me is what determines when it will create new instances vs. refreshing the existing ones? On Mar 4, 3:24 pm, Michael Bayer mike...@zzzcomputing.com wrote: Kent wrote: If I use session.refresh(obj) to re-load an obj that has a one-to-many relational property, the objects in the list are *replaced* instead of *refreshed* if they already exist. Suppose department has a list of employees: suppose dept.employees = [ emp1, emp2 ] session.refresh(dept) the dept.employees list's elements are replaced with new objects instead of reusing those that existed and refreshing them. Is it possible to have those same objects re-used and simply refreshed instead of replaced? you can only turn off refresh-expire cascade, which will prohibit the operation from traveling into the child objects. the collection is still refreshed for obvious reasons, its one of the attributes on your mapped object. To achieve your specified behavior, use session.refresh() given as its second argument the set of attribute names which are safe to be reloaded completely (in this case the scalars). Then for each uselist attribute, iterate the collection of each and call the desired version of session.refresh() for those. This is an easy refresh() function to create in a generalized way by inspecting the class-level attributes of the incoming object. -- 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] Base class with primary key and sequence (Mix-in?)
Hi All, I have created a base declarative object that has a pre-made primary key, so I don't have to add it to all my child tables: class ClassDefaults(DeclarativeMeta): def __init__(cls,classname, bases, dict_): dict_['id'] = Column(Integer, Sequence(id_seq, optional=True), primary_key=True) return DeclarativeMeta.__init__(cls, classname, bases, dict_) Base = declarative_base(metaclass=ClassDefaults) This allows me to create a table as follows, and have an implicit primary key named id: class UserGroup(Base): __tablename__ = 'usergroup' name = Column(String(80), nullable=False, unique=True, index=True) However, my base class currently uses the *same* sequence for all primary keys. I would like to create a new sequence for each primary key. I was thinking of naming the sequence based on the name of the table, so that UserGroup's sequence would be called usergroup_id_seq, etc. I am wondering how this is possible, using the above approach, or using Mix-ins, as documented here (Michael Bayer pointed me in this direction) -- http://www.sqlalchemy.org/docs/reference/ext/declarative.html#mix-in-classes While mix-ins look cool, I am not sure how I would reference the __tablename__ of the child class from the Mixin. I can probably work around this by *not* naming the sequences after the table name, but instead use an incrementing global variable to create the unique sequence names, but it seems like a better practice to base the sequence name on the name of the table itself. Michael says that Chris Withers may know how to do this with Mix-ins. Chris, you out there? :) Regards, Daniel -- 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: preserving uselist relation instances in a session.refresh()
Ok, I wonder the reasons, but I trust your answer completely, so I won't disable it. Thanks for your help again. On Mar 4, 4:26 pm, Michael Bayer mike...@zzzcomputing.com wrote: Kent wrote: Thanks... that is very helpful. I could keep references to these. If I choose the apparently lazier route and set weak_identity_map=False, then does any other action besides explicitly expunging free this memory, such as when the session goes out of scope, I assume? Or do I need to carefully expunge them? don't disable the weak identity map. just maintain your own collection of all the objects you care about. On Mar 4, 4:09 pm, Conor conor.edward.da...@gmail.com wrote: Conor wrote: Kent wrote: I agree I shouldn't care, so maybe there is another way to attack my problem. The reason I care is because I've extended the python object with some auxiliary information that I need. After the refresh() in this case, I still need access to that data that is tied to the object, but not present in the database (it is transient data). If sqla creates a new instance, I loose that data. Is there a better mechanism for doing that? You need to either manually keep strong references to each object that has the auxiliary information or disable the weak identity map. See http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-quest... or http://www.sqlalchemy.org/docs/05/session.html#frequently-asked-quest... for more information. That second link should behttp://www.sqlalchemy.org/docs/05/session.html#session-attributes. Oops. On Mar 4, 3:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: refresh doesn't remove any objects from the session so its a matter of what is present in the session, not marked as dirty, and strongly referenced on the outside. if you're using refresh you shouldn't care about how it gets data back into the collection. Kent wrote: What's strange is that I can't recreate the problem on more simple stage. Every time I refresh() on the parent object, the list objects remain the same. In other words, *sometimes* it behaves as I hope it to (by apparently refreshing the list's objects) and *sometimes* if throws them out and creates new ones. The mystery to me is what determines when it will create new instances vs. refreshing the existing ones? On Mar 4, 3:24 pm, Michael Bayer mike...@zzzcomputing.com wrote: Kent wrote: If I use session.refresh(obj) to re-load an obj that has a one-to-many relational property, the objects in the list are *replaced* instead of *refreshed* if they already exist. Suppose department has a list of employees: suppose dept.employees = [ emp1, emp2 ] session.refresh(dept) the dept.employees list's elements are replaced with new objects instead of reusing those that existed and refreshing them. Is it possible to have those same objects re-used and simply refreshed instead of replaced? you can only turn off refresh-expire cascade, which will prohibit the operation from traveling into the child objects. the collection is still refreshed for obvious reasons, its one of the attributes on your mapped object. To achieve your specified behavior, use session.refresh() given as its second argument the set of attribute names which are safe to be reloaded completely (in this case the scalars). Then for each uselist attribute, iterate the collection of each and call the desired version of session.refresh() for those. This is an easy refresh() function to create in a generalized way by inspecting the class-level attributes of the incoming object. -- 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] Re: oracle insert problem
Thanks a lot Michael, now it works! this is my final script, maybe it
could help others:
engine = create_engine('oracle://user:passw...@host:1521/dbname',
echo=True)
metadata = MetaData()
pushmail_table = Table('pushmail', metadata,
Column('telefono', String, primary_key=True),
Column('fecha', DateTime, default=datetime.datetime.now ),
Column('correo', String),
schema='manpushmail'
)
On sqlplus I do a : SELECT * FROM MANPUSHMAIL.PUSHMAIL;
On 2 mar, 16:34, Michael Bayer mike...@zzzcomputing.com wrote:
additionally don't shove a schema/owner name into your table's name field.
Use the schema kw arg for that.
On Mar 2, 2010, at 4:23 PM, celord wrote:
Thanks Michael, I have changed all UPERCASE to lowercase
http://www.pastebin.org/100149, the output has changed a bit but the
table name is still quoted and the insert does not work
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00942: table or view
does not exist
'INSERT INTO manpushmail.pushmail (telefono, fecha, correo) VALUES
(:telefono, :fecha, :correo)' {'correo': 'cgar...@ice.co.cr', 'fecha':
' ', 'telefono': '87445511'}
On 2 mar, 12:39, Michael Bayer mike...@zzzcomputing.com wrote:
read the note about case sensitivity in
http://www.sqlalchemy.org/docs/reference/dialects/oracle.html?highlig...
celord wrote:
Hello guys, I have the table on an oracle db:
SQL desc MANPUSHMAIL.PUSHMAIL;
Nombre Nulo? Tipo
-
TELEFONO NOT NULL VARCHAR2(12)
FECHA DATE
CORREO VARCHAR2(40)
ESTADO NUMBER(1)
FECHA_EJECUCION DATE
FECHA_EJECUTADO DATE
RESPUESTA VARCHAR2(500)
I can insert data succesfully via sqlplus like this:
insert into MANPUSHMAIL.PUSHMAIL values
('',sysdate,'t...@domain.con','','','','');
but using this scripthttp://www.pastebin.org/100014viasqlalchemy I
get this:
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00942: table or view
does not exist
'INSERT INTO MANPUSHMAIL.PUSHMAIL (FECHA, CORREO) VALUES
(:FECHA, :CORREO) RETURNING MANPUSHMAIL.PUSHMAIL.TELEFONO
INTO :ret_0' {'CORREO': None, 'FECHA': None, 'ret_0':
cx_Oracle.STRING with value None}
I do not know if the error could be here: 'INSERT INTO
MANPUSHMAIL.PUSHMAIL, because when I try to do the insert with the
table name between quotes I get the same error:
insert into MANPUSHMAIL.PUSHMAIL values
('',sysdate,'te...@domain.com','','','','')
ORA-00942: table or view does not exist
I am using sqlalchemy version: 0.6beta1 , Python 2.6 on a Solaris 10
box, and Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Thanks a lot!!!
--
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
athttp://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] Primary key Mix-in not working with adjacency list
Hi all,
I tried to convert some existing code containing an adjacency list to
mix-ins, and the mix-in version doesn't seem to be liked by SQLAlchemy
0.6_beta1:
Original code that works:
class ClassDefaults(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
dict_['id'] = Column(Integer, Sequence(id_seq,
optional=True), primary_key=True)
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base = declarative_base(metaclass=ClassDefaults)
class Location(Base):
__tablename__ = 'location'
parent_id = Column(Integer, ForeignKey('location.id'))
parent = relation('Location', backref=backref('children'),
remote_side='location.c.id')
name = UniqueString(25)
desc = Column(String(80))
New Mix-In code that doesn't work:
Base = declarative_base()
class Common(object):
id = Column(Integer, Sequence('id_seq', optional=True),
primary_key=True)
class Location(Base,Common):
__tablename__ = 'location'
parent_id = Column(Integer, ForeignKey('location.id'))
parent = relation('Location', backref=backref('children'),
remote_side='location.c.id')
name = UniqueString(25)
desc = Column(String(80))
SQLAlchemy complains:
Traceback (most recent call last):
File base.py, line 60, in module
class Location(Base,Common):
File /usr/lib64/python2.6/site-packages/sqlalchemy/ext/declarative.py,
line 561, in __init__
_as_declarative(cls, classname, dict_)
File /usr/lib64/python2.6/site-packages/sqlalchemy/ext/declarative.py,
line 554, in _as_declarative
cls.__mapper__ = mapper_cls(cls, table, properties=our_stuff, **mapper_args)
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/__init__.py,
line 778, in mapper
return Mapper(class_, local_table, *args, **params)
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 189, in __init__
self._configure_pks()
File /usr/lib64/python2.6/site-packages/sqlalchemy/orm/mapper.py,
line 435, in _configure_pks
key columns for mapped table '%s' % (self, self.mapped_table.description))
sqlalchemy.exc.ArgumentError: Mapper Mapper|Location|location could
not assemble any primary key columns for mapped table 'location'
Question: do Mix-ins complicate the mechanism by which adjacency lists
are defined? If so, how does one work around this (and maybe update
the Mix-in docs to show an example of how to work around this issue?)
Thanks,
Daniel
--
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: Base class with primary key and sequence (Mix-in?)
On Thu, Mar 4, 2010 at 2:31 PM, Daniel Robbins drobb...@funtoo.org wrote:
Hi All,
I have created a base declarative object that has a pre-made primary
key, so I don't have to add it to all my child tables:
I figured out how to do this, using the following code:
seqnum=0
def PrimaryKey(seqprefix=None):
global seqnum
if not seqprefix:
seqnum += 1
seqname = id_seq_%s % seqnum
else:
seqname = %s_id_seq % seqprefix
return Column(Integer, Sequence(seqname, optional=True),
primary_key=True)
class ClassDefaults(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
if not ( dict_.has_key('__mapper_args__') and
dict_['__mapper_args__'].has_key('polymorphic_identity') ):
# Only add the key if we are not creating a
polymorphic SQLAlchemy object, because SQLAlchemy
# does not want a separate 'id' key added in that case.
# seqprefix can be None
seqprefix = getattr(cls,'__tablename__',None)
dict_['id'] = PrimaryKey(seqprefix=seqprefix)
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base = declarative_base(metaclass=ClassDefaults)
class Location(Base):
__tablename__ = 'location'
parent_id = Column(Integer, ForeignKey('location.id'))
parent = relation('Location', backref=backref('children'),
remote_side='location.c.id')
name = UniqueString(25)
desc = Column(String(80))
This code above allows my adjacency list table Location to be handled
correctly, and also allows my Single Table inheritance (not included
in the above code) to work too. The PrimaryKey() function will
generate numerically increasing sequence names with no argument, or a
specific sequence name if supplied with an argument. ClassDefaults
calls it with the __tablename__ if one is available to create a
sequence that has a name similar to the underlying table (with a
_seq suffix.)
Regards,
Daniel
--
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] mapping to existing table with no primary key
here's my issue...I have to map to an existing Oracle db table with 33million rows (yeah, I know). This table has no primary key and worse, nothing, and I mean nothing to uniquely identify a row (fabulous). as a backup, I realize that I can manually issue statements to this thing via SA, but I'd really like to use it through the ORM like I am everything else. this table is a transaction table, so 99% of the time, it's only inserts. However, there is a flag on the table to indicate that it's been processed by a batch process so that flag does get updated. Is there some way I can map rowid or something as the primary key? I think it's mostly safe since we aren't using partitioned tables and since these records are effectively never deleted while the system is running, I don't have to worry about the rowid getting reassigned on me mid-transaciton. My issue is that on insert or update, I don't want SA trying to generate and/or insert into rowid for obvious reasons Am I on the right track, any other ideas?? -- 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] mapping to existing table with no primary key
robneville73 wrote: here's my issue...I have to map to an existing Oracle db table with 33million rows (yeah, I know). This table has no primary key and worse, nothing, and I mean nothing to uniquely identify a row (fabulous). as a backup, I realize that I can manually issue statements to this thing via SA, but I'd really like to use it through the ORM like I am everything else. this table is a transaction table, so 99% of the time, it's only inserts. However, there is a flag on the table to indicate that it's been processed by a batch process so that flag does get updated. Is there some way I can map rowid or something as the primary key? I think it's mostly safe since we aren't using partitioned tables and since these records are effectively never deleted while the system is running, I don't have to worry about the rowid getting reassigned on me mid-transaciton. My issue is that on insert or update, I don't want SA trying to generate and/or insert into rowid for obvious reasons Am I on the right track, any other ideas?? perhaps map to a view that adds in rowid as a surrogate primary key. The ORM can't do writes on this table, however. It needs to issue an UPDATE or a DELETE, for example, therefore needs a pk. -- 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] Re: mapping to existing table with no primary key
Ahhh! Perhaps such a view coupled with an instead of trigger might work...I'd need to think about that, but that might work. Thanks Michael. On Mar 4, 6:12 pm, Michael Bayer mike...@zzzcomputing.com wrote: robneville73 wrote: here's my issue...I have to map to an existing Oracle db table with 33million rows (yeah, I know). This table has no primary key and worse, nothing, and I mean nothing to uniquely identify a row (fabulous). as a backup, I realize that I can manually issue statements to this thing via SA, but I'd really like to use it through the ORM like I am everything else. this table is a transaction table, so 99% of the time, it's only inserts. However, there is a flag on the table to indicate that it's been processed by a batch process so that flag does get updated. Is there some way I can map rowid or something as the primary key? I think it's mostly safe since we aren't using partitioned tables and since these records are effectively never deleted while the system is running, I don't have to worry about the rowid getting reassigned on me mid-transaciton. My issue is that on insert or update, I don't want SA trying to generate and/or insert into rowid for obvious reasons Am I on the right track, any other ideas?? perhaps map to a view that adds in rowid as a surrogate primary key. The ORM can't do writes on this table, however. It needs to issue an UPDATE or a DELETE, for example, therefore needs a pk. -- 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] Re: [PROPOSE] Integrate migration support in SQLAlchemy
Manlio Perillo manlio.peri...@gmail.com writes: Michael Bayer ha scritto: Similarly, the concept of a version as an integer number is not really flexible enough - The idea was to keep it simple. IMHE, there's no such a beast! I would like to investigate the creation of migration scripts between branches as well. The version can then be a string, but it needs to sortable (see setuptool as an example). The mechanism of schema versioning is not at all something that belongs in SQLAlchemy core. I'm all with Michael here. Sure, for *very* simple projects, it'd be nice to have a mechanism that spits out the needed statements, but at least in my experience (going from very little to moderately big and complex schemas), maintaining an upgradable path is something better done at another level. Some steps needs an hand-crafted solution, that maybe involve dropping/recreating dependencies, temporary data-space to upgrade existing contents and so on. I'm an SQL man when it comes to maintaining the schema, so my bias brought me to write a docutils based solution, so my databases are built by a tool that extract the various pieces, reorder them resolving dependencies (a DAG, introduced by script's metadata) (thanks to Michael's topological sort, btw!) and execute the missing one on a target database. Double value: good documentation, and automatic upgrade of custom's databases. It's GPL, just ask if interested! ciao, lele. -- nickname: Lele Gaifax| Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas| comincerò ad aver paura di chi mi copia. l...@nautilus.homeip.net | -- Fortunato Depero, 1929. -- 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] Can we use dates to define a relation with the mapper ?
Hi,
I have this mapper defined:
mapper(Resource, resource_table,
properties = {'type' : relation(ResourceType,lazy = False),
'groups' : relation(Group, secondary =
model.tables['resource_group'], backref = 'resources'),
'parent' : relation(Relation, uselist=False, primaryjoin =
and_(relation_table.c.res_id == resource_table.c.res_id,
relation_table.c.end_date datetime.now())),
'children' : relation(Relation, primaryjoin =
and_(relation_table.c.parent_id == resource_table.c.res_id,
relation_table.c.end_date func.now()))})
But for some reason, if I create a new row in the relation table and
change the end_date of the old row in the relation to an old date, the
property parent is not updated.
Also if a reload the resource row, the old relation with the old date
is displayed, so I am pretty sure it has to do with the date
comparison in the mapper.
If I replace the end_date by a flag column string or integer and do a
comparison on the flag I get the proper behaviour, but I do want to
use dates.
Any help is welcome.
Thanks,
Richard Lopes
--
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] Can we use dates to define a relation with the mapper ?
Hello,
On Mar 4, 2010, at 10:50 PM, Richard Lopes wrote:
Hi,
I have this mapper defined:
mapper(Resource, resource_table,
properties = {'type' : relation(ResourceType,lazy = False),
'groups' : relation(Group, secondary =
model.tables['resource_group'], backref = 'resources'),
'parent' : relation(Relation, uselist=False, primaryjoin =
and_(relation_table.c.res_id == resource_table.c.res_id,
relation_table.c.end_date datetime.now())),
'children' : relation(Relation, primaryjoin =
and_(relation_table.c.parent_id == resource_table.c.res_id,
relation_table.c.end_date func.now()))})
But for some reason, if I create a new row in the relation table and
change the end_date of the old row in the relation to an old date, the
property parent is not updated.
Also if a reload the resource row, the old relation with the old date
is displayed, so I am pretty sure it has to do with the date
comparison in the mapper.
If I replace the end_date by a flag column string or integer and do a
comparison on the flag I get the proper behaviour, but I do want to
use dates.
I imagine you're getting bitten because your datetime.now() is getting
evaluated at compile time. You might need to make it a callable. That said I'm
unsure about whether or not a callable will work with SQLAlchemy. I might be
able to write a test case tomorrow.
Michael
--
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] Can we use dates to define a relation with the mapper ?
Hi,
Thanks for the help but I think I got it working.
Look here:
http://stackoverflow.com/questions/2384438/sqlalchemy-can-we-use-date-comparison-in-relation-definition
Cheers,
Richard
2010/3/5 Michael Trier mtr...@gmail.com
Hello,
On Mar 4, 2010, at 10:50 PM, Richard Lopes wrote:
Hi,
I have this mapper defined:
mapper(Resource, resource_table,
properties = {'type' : relation(ResourceType,lazy = False),
'groups' : relation(Group, secondary =
model.tables['resource_group'], backref = 'resources'),
'parent' : relation(Relation, uselist=False, primaryjoin =
and_(relation_table.c.res_id == resource_table.c.res_id,
relation_table.c.end_date datetime.now())),
'children' : relation(Relation, primaryjoin =
and_(relation_table.c.parent_id == resource_table.c.res_id,
relation_table.c.end_date func.now()))})
But for some reason, if I create a new row in the relation table and
change the end_date of the old row in the relation to an old date, the
property parent is not updated.
Also if a reload the resource row, the old relation with the old date
is displayed, so I am pretty sure it has to do with the date
comparison in the mapper.
If I replace the end_date by a flag column string or integer and do a
comparison on the flag I get the proper behaviour, but I do want to
use dates.
I imagine you're getting bitten because your datetime.now() is getting
evaluated at compile time. You might need to make it a callable. That said
I'm unsure about whether or not a callable will work with SQLAlchemy. I
might be able to write a test case tomorrow.
Michael
--
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.comsqlalchemy%2bunsubscr...@googlegroups.com
.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
R. LOPES
--
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.
