[sqlalchemy] Re: remove an object from a property deletes from session
On 26 Mar, 18:04, Michael Bayer mike...@zzzcomputing.com wrote: it would only do that if you have delete-orphan on the relation, and the object was never saved.it will get re-added once you attach it to that's exactly the situation I have. thanks for the explanation. sandro *:-) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Clearing out a scoped session
Hi folks, I'm working on a set of test utilities for functional tests using SA. Because they use a turbogears model, they import the tg model and so get the SA class definitions with the mappers from the TG scoped_session. My problem is that I want to reseed this data on each test. So I have a fixture setup that drops and recreates the tables used, and then it's supposed to put my seed data into the db for each test. The problem is that once the seed data objects ( instantiated model objects) have gone through this once, they won't do it again because the the objects identites conflict with persistent objects in the scoped_session. I don't seem to be able to completely clean out the session in order to resave the objects. Can anyone give me any tips on how I can: - create a model object - save it to the scoped session - flush the session - wipe out the whole mess *except* the model object - resave and reflush those same objects again and again Much appreciated, Iain --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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: Getting relation orphans
On 26 Mar, 18:02, Michael Bayer mike...@zzzcomputing.com wrote: you can check if an object is an orphan related to a certain relation() its supposed to belong to, though there's no automated way to go from that object to the actual collection it was removed from unless you scan through all the potential parents in the session and check each one. the current way to check for an orphan is and internal thing at the moment, and looks like: some_relation.mapper._is_orphan(attributes.instance_state(item)) I see, thanks :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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: Seperate History Tables
The version is ok: 0.5.3
The problem is I don't know how to do the unit tests: no tests package.
from tests import eq_, Comparable
How can i do the unit tests?
Suha
On Thu, Mar 26, 2009 at 19:04, Michael Bayer mike...@zzzcomputing.com wrote:
not sure. make sure you're on 0.5.3. do the unit tests included with
the recipe pass ?
Suha Onay wrote:
Thanks for the recipe.I faced with an error.
The model is now:
--
from history_meta import VersionedMeta
Base = declarative_base(metaclass=VersionedMeta)
class User(Base):
__tablename__ = 'users'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(12))
fullname = sa.Column(sa.Unicode(40))
password = sa.Column(sa.Unicode(20))
active = sa.Column(sa.Boolean())
type = sa.Column(sa.SmallInteger())
note = sa.Column(sa.Text())
date_created = sa.Column(sa.Date())
--
Session = sessionmaker(bind=engine, autocommit=False, autoflush=True,
expire_on_commit=False, extension=VersionedListener())
--
When inserting a new user, the error occurs:
sqlite3: class 'sqlalchemy.exc.IntegrityError' -= ('(IntegrityError)
users.version may not be NULL',)
postgre: class 'sqlalchemy.exc.IntegrityError' -= ('(IntegrityError)
null
value in column version violates not-null constraint\n',)
The code in history_meta creates the column version with default value 1:
cls.version = Column('version', Integer, default=1, nullable=False)
What is the reason?
Thanks.
Suha
On Wed, Mar 25, 2009 at 17:17, Michael Bayer
mike...@zzzcomputing.comwrote:
I've placed a recipe for this on the wiki at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/LogVersions
.
On Mar 25, 2009, at 10:12 AM, Suha Onay wrote:
Hi,
I am using sqlalchemy for a while in a project.
The project has lots of models like User:
--
from mcmodel import MCModel
Base = declarative_base()
class User(MCModel, Base):
__tablename__ = 'users'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(12))
fullname = sa.Column(sa.Unicode(40))
password = sa.Column(sa.Unicode(20))
active = sa.Column(sa.Boolean())
type = sa.Column(sa.SmallInteger())
note = sa.Column(sa.Text())
date_created = sa.Column(sa.Date())
--
All of these models inherit from MCModel (nothing doing special).
I want to save all the changes done to a user in a seperate db table
like _hist_users.
The new inserts do not need to be in the hist table.
When a user is updated, the old data of the user will be copied to the
hist table with a column declaring this is an update operation.
When a user is deleted, the old data of the user will be moved to the
hist table with a column declaring this is a delete operation.
With these operations, it is possible to know who modified what and
when.
How can i achieve in this?
By modifying the MCModel to enable all the models aware of history
backup?
Or using class sqlalchemy.orm.interfaces.MapperExtension.after_update
methods? (i do not know how)
Or anything else?
Thanks in advance.
Suha
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] session.add() or session.update() ? (NEWBIE)
Hello everyone, I have tested that session.add(changed_sqla_object) in at least one context (when the object with id of changed_sqla_object already exists in the db) does issue UPDATE sql query and updates the object in the database. However, there's also session.update() method. Help on this says: update(self, instance) method of sqlalchemy.orm.session.Session instance Bring a detached (saved) instance into this ``Session``. Meaning this updates session with the saved object data, and it's not that the *changed* object's data that is updated in database? Use session.add() To do what? If there is a persistent instance with the same instance key, but different identity already associated with this ``Session``, an InvalidRequestError exception is thrown. This operation cascades the `save_or_update` method to associated instances if the relation is mapped with ``cascade=save-update``. In general, the question: is it safe to use session.add(changed_object) in *all of the circumstances*? Regards, mk --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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: Filtering a relation
Try this:
mapper(Parent, parent_table, properties = {
'boys':relation(Child, backref='parent',
primaryjoin=(and_(child_table.c.pid=parent_table.c.id,
child_table.c.gb=='b'),
'girls':relation(Child, backref='parent',
primaryjoin=(and_(child_table.c.pid=parent_table.c.id,
child_table.c.gb=='g')
})
Mike Conley wrote:
How do I create a relation that filters the list property when
accessing the data?
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
name = Column(String)
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
pid = Column(Integer, ForeignKey('parent.id http://parent.id'))
name = Column(String)
bg = Column(String) # b=boy, g=girl
Parent.children = relation(Child, backref='parent')
Gives me a relation that can be used to get a list of all children
Question is how to specify a relation that gives a list of the boys
Parent.boys = relation(Child, ??)
I suspect it is something pretty straight forward, but can't figure it out
--
Mike Conley
--
David Gardner
Pipeline Tools Programmer, Sid the Science Kid
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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: session.add() or session.update() ? (NEWBIE)
Marcin Krol wrote: Hello everyone, I have tested that session.add(changed_sqla_object) in at least one context (when the object with id of changed_sqla_object already exists in the db) does issue UPDATE sql query and updates the object in the database. However, there's also session.update() method. Help on this says: update(self, instance) method of sqlalchemy.orm.session.Session instance Bring a detached (saved) instance into this ``Session``. Meaning this updates session with the saved object data, and it's not that the *changed* object's data that is updated in database? Use session.add() To do what? If there is a persistent instance with the same instance key, but different identity already associated with this ``Session``, an InvalidRequestError exception is thrown. This operation cascades the `save_or_update` method to associated instances if the relation is mapped with ``cascade=save-update``. In general, the question: is it safe to use session.add(changed_object) in *all of the circumstances*? update(), save_or_update(), save() are all deprecated. add() places an object in the session in all cases, using the persistence information already associated with the object to determine INSERT or UPDATE. this means if you just make a new Foo(id=some id), that's transient - SQLAlchemy didn't load it. It will be INSERTed. Keep in mind that the Session maintains an identity map of all unique primary keys already loaded into memory, as well as the state which was received from the database. For this reason, you generally can't just put an object in the session with some arbitrary data, and expect it to take the place of the actual row that would be loaded by the transaction. SQLAlchemy wouldn't know what to do with it since it has no idea what changes have been made to this row versus what is already present. If you'd like to create a new Foo() with a primary key that may or may not already exist in the database, you want to merge the state of that object with one that is produced by the Session. Use session.merge() for this use case. this will load the existing object from the current transaction, if any, and merge the state of your outside object with it, returning the instance. The instance you pass to it remains unchanged and outside the session. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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: Seperate History Tables
install nose and type nosetests.
Suha Onay wrote:
The version is ok: 0.5.3
The problem is I don't know how to do the unit tests: no tests package.
from tests import eq_, Comparable
How can i do the unit tests?
Suha
On Thu, Mar 26, 2009 at 19:04, Michael Bayer mike...@zzzcomputing.com
wrote:
not sure. make sure you're on 0.5.3. do the unit tests included
with
the recipe pass ?
Suha Onay wrote:
Thanks for the recipe.I faced with an error.
The model is now:
--
from history_meta import VersionedMeta
Base = declarative_base(metaclass=VersionedMeta)
class User(Base):
__tablename__ = 'users'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(12))
fullname = sa.Column(sa.Unicode(40))
password = sa.Column(sa.Unicode(20))
active = sa.Column(sa.Boolean())
type = sa.Column(sa.SmallInteger())
note = sa.Column(sa.Text())
date_created = sa.Column(sa.Date())
--
Session = sessionmaker(bind=engine, autocommit=False, autoflush=True,
expire_on_commit=False, extension=VersionedListener())
--
When inserting a new user, the error occurs:
sqlite3: class 'sqlalchemy.exc.IntegrityError' -= ('(IntegrityError)
users.version may not be NULL',)
postgre: class 'sqlalchemy.exc.IntegrityError' -= ('(IntegrityError)
null
value in column version violates not-null constraint\n',)
The code in history_meta creates the column version with default value
1:
cls.version = Column('version', Integer, default=1, nullable=False)
What is the reason?
Thanks.
Suha
On Wed, Mar 25, 2009 at 17:17, Michael Bayer
mike...@zzzcomputing.comwrote:
I've placed a recipe for this on the wiki at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/LogVersions
.
On Mar 25, 2009, at 10:12 AM, Suha Onay wrote:
Hi,
I am using sqlalchemy for a while in a project.
The project has lots of models like User:
--
from mcmodel import MCModel
Base = declarative_base()
class User(MCModel, Base):
__tablename__ = 'users'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(12))
fullname = sa.Column(sa.Unicode(40))
password = sa.Column(sa.Unicode(20))
active = sa.Column(sa.Boolean())
type = sa.Column(sa.SmallInteger())
note = sa.Column(sa.Text())
date_created = sa.Column(sa.Date())
--
All of these models inherit from MCModel (nothing doing special).
I want to save all the changes done to a user in a seperate db table
like _hist_users.
The new inserts do not need to be in the hist table.
When a user is updated, the old data of the user will be copied to
the
hist table with a column declaring this is an update operation.
When a user is deleted, the old data of the user will be moved to
the
hist table with a column declaring this is a delete operation.
With these operations, it is possible to know who modified what and
when.
How can i achieve in this?
By modifying the MCModel to enable all the models aware of history
backup?
Or using class
sqlalchemy.orm.interfaces.MapperExtension.after_update
methods? (i do not know how)
Or anything else?
Thanks in advance.
Suha
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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: Filtering a relation
Thanks, it is easy and works just as expected.
Using declarative it looks like this:
Parent.boys = relation(Child, viewonly=True,
primaryjoin=(and_(Child.pid==Parent.id, Child.bg=='b')))
Parent.girls = relation(Child, viewonly=True,
primaryjoin=(and_(Child.pid==Parent.id, Child.bg=='g')))
Note that you don't specify backref='parent' because that is already defined
on the children relation. Also added viewonly=True just to make sure SA
won't be tempted to use this relation for persisting anything; not needed
here, but could be important using the same idea in more complex
associations.
--
Mike Conley
On Fri, Mar 27, 2009 at 11:51 AM, David Gardner
dgard...@creatureshop.comwrote:
Try this:
mapper(Parent, parent_table, properties = {
'boys':relation(Child, backref='parent',
primaryjoin=(and_(child_table.c.pid=parent_table.c.id, child_table.c.gb
=='b'),
'girls':relation(Child, backref='parent',
primaryjoin=(and_(child_table.c.pid=parent_table.c.id, child_table.c.gb
=='g')
})
Mike Conley wrote:
How do I create a relation that filters the list property when accessing
the data?
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
name = Column(String)
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
pid = Column(Integer, ForeignKey('parent.id'))
name = Column(String)
bg = Column(String) # b=boy, g=girl
Parent.children = relation(Child, backref='parent')
Gives me a relation that can be used to get a list of all children
Question is how to specify a relation that gives a list of the boys
Parent.boys = relation(Child, ??)
I suspect it is something pretty straight forward, but can't figure it out
--
Mike Conley
--
David Gardner
Pipeline Tools Programmer, Sid the Science Kid
Jim Henson Creature shopdgard...@creatureshop.com
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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] orm query - join on inner query
How can i implement the following query with sqlalchemy.orm objects?
SELECT columns.*
FROM columns AS col
JOIN
(
SELECT object_id, revision
FROM columns
GROUP BY id, revision
HAVING revision = 20
) AS lr ON col.object_id = lr.object_id AND col.revision =
lr.revision;
Non syntactically correct example :
from sqlalchemy import schema, types, orm, create_engine
# Setup everything
engine = create_engine('sqlite://:memory:')
metadata = schema.Metadata(bind=engine)
ColumnsTable = schema.Table('columns'
, schema.Column('id', types.Integer, primary_key=True)
, schema.Column('object_id', types.Integer)
, schema.Column('name', types.String)
, schema.Column('revision', types.Integer)
, metadata)
class ColumnOrm (object):
pass
metadata.create_all()
orm.mapper(ColumnOrm, ColumnsTable)
session = orm.create_session(bind=engine, autocommit=True)
# Now for the query
# lr means latest revision
SELECT columns.*
FROM columns AS col
JOIN
(
SELECT object_id, revision
FROM columns
GROUP BY id, revision
HAVING revision = 20
) AS lr ON col.object_id = lr.object_id AND col.revision =
lr.revision;
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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: orm query - join on inner query
subq = session.query(ColumnOrm.object_id, ColumnOrm.revision).\
group_by(ColumnOrm.id, ColumnOrm.revision).\
having(ColumnOrm.revision=20).subquery()
print session.query(ColumnOrm).join((subq,
and_(ColumnOrm.object_id==subq.c.object_id,
ColumnOrm.revision==subq.c.revision)))
jarrod.ches...@gmail.com wrote:
How can i implement the following query with sqlalchemy.orm objects?
SELECT columns.*
FROM columns AS col
JOIN
(
SELECT object_id, revision
FROM columns
GROUP BY id, revision
HAVING revision = 20
) AS lr ON col.object_id = lr.object_id AND col.revision =
lr.revision;
Non syntactically correct example :
from sqlalchemy import schema, types, orm, create_engine
# Setup everything
engine = create_engine('sqlite://:memory:')
metadata = schema.Metadata(bind=engine)
ColumnsTable = schema.Table('columns'
, schema.Column('id', types.Integer, primary_key=True)
, schema.Column('object_id', types.Integer)
, schema.Column('name', types.String)
, schema.Column('revision', types.Integer)
, metadata)
class ColumnOrm (object):
pass
metadata.create_all()
orm.mapper(ColumnOrm, ColumnsTable)
session = orm.create_session(bind=engine, autocommit=True)
# Now for the query
# lr means latest revision
SELECT columns.*
FROM columns AS col
JOIN
(
SELECT object_id, revision
FROM columns
GROUP BY id, revision
HAVING revision = 20
) AS lr ON col.object_id = lr.object_id AND col.revision =
lr.revision;
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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
-~--~~~~--~~--~--~---
