[sqlalchemy] Re: delete failure with foreign key relations
Well, this error says that you have rows in other(or same) tables
referring to this row you are going to delete,
and you should delete referring rows first. If you want SQLA to do it
automatically,
you need to use sessions and mappers (not raw SQL expression engine),
more info here:
http://www.sqlalchemy.org/docs/05/ormtutorial.html#configuring-delete-delete-orphan-cascade
Regards,
Alex
On Jan 29, 8:33 am, n00b pyn...@gmail.com wrote:
back again, sorry.
i specified a model with a few one-to-many and one many-to-many
relations using SA 0.51 in MySql 5.1.25 rc; tables are all INNODB. all
works well and as expected in the ORM realm. however, when i'm trying
to use SQL Expression for a delete (row) operation, i get the dreaded
IntegrityError: (IntegrityError) (1451, 'Cannot delete or update a
parent row: a foreign key constraint fails
specifically, i'm using:
engine = create_engine('mysql://root:@localhost:3306/test2')
connection = engine.connect()
metadata = MetaData(bind=engine)
main_table = Table('main_table', metadata, autoload=True)
target_id = 1 #for illustrative purposes, primary key to delete
connection.execute(main_table.delete().where(main_table.c.id
==target_id))
where main_table is my main table and all other tables link to its id
(primary key int) via
foreign keys.
thx for you help
--~--~-~--~~~---~--~~
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: joining to child, and using child in relation
Tested with trunk. Works, thanks.
On Jan 29, 6:42 am, Michael Bayer mike...@zzzcomputing.com wrote:
OK, well that was painful but we are stronger for the effort, thanks
for bringing up the issue. r5740 of trunk will allow your original
mapper(A.join(B))-mapper(B) to configure properly.
On Jan 28, 2009, at 11:28 PM, Michael Bayer wrote:
a join is of the form:
table1.join(table2, onclause)
such as
subscriber_table.join(address_table,
and_(address_table.c.subscriber_id==subscriber.c.id,
address_table.c.type=='MAIN'))
but unfortunately current relation() code does not support a join of
X/
Y to Y, unless the join of X/Y is assembled via joined table
inheritance. As a workaround, you can wrap your join() in an
aliased select(). A fix may be available in the next 10 minutes or
maybe not.
You also could forego the complexity of mapping to a join and just
modify your Subscriber class to break up the addresses collection
amongst a proxy of the MAIN element and a list of the remaining
elements. an attribute_mapped_collection could help to accomplish
this nicely.
On Jan 28, 2009, at 7:22 PM, GHZ wrote:
I have a subscriber and address table.
a subscriber will have one and only one 'MAIN' address.
I want the subscriber and MAIN address to be represented by one class
'Subscriber'. However, I want that class to have a collection
'addresses' which contains other addresses (e.g. old addresses) - (it
can include the 'MAIN' address too .. or not.. I don't care)
subscriber_table = Table('subscriber', metadata,
Column('id', primary_key=True),
autoload=True)
address_table = Table('address',
metadata,
Column('subscriber_id', ForeignKey
('subscriber.id'), primary_key=True),
Column('address_type', primary_key=True),
autoload=True)
subscriber_with_default_address = sql.join( subscriber_table.c.id
== address_table.c.subscriber_id).??? - something to say
address_table.type is 'MAIN'
mapper(Address, address_table)
mapper(Subscriber, subscriber_and_address, properties={
'id':[subscriber_table.c.id, address_table.c.subscriber_id],
'addresses' : relation(Address, collection_class=Addresses,
backref='customer')
})
a) I can't quite figure out how to say (address.type is default)
b) even without this I get:
sqlalchemy.exc.ArgumentError: Can't determine relation direction for
relationshi
p 'Subscriber.addresses' - foreign key columns are present in both
the
parent an
d the child's mapped tables. Specify 'foreign_keys' argument.
if I do specify foreign_keys parameter to the relation function, then
I still get the same.
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: autoflush during instanciation
I quite sure I'm not using it... Look at that code that reproduce the bug http://utilitybase.com/paste/11481 The last line (p2 = Parent(p)) leads to a load of the children, so an autoflush. I have a failure with 0.5.2: sqlalchemy.exc.IntegrityError: (IntegrityError) parent.dumb may not be NULL u'INSERT INTO parent (dumb) VALUES (?)' [None] which let me think the object was somehow added to the autoflush... I hope that will help to solve this. GustaV --~--~-~--~~~---~--~~ 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] Correct way of moving relation
I have some items which are related but I need to change it so they
related to another item.
Before getting myself in a mess (as I need to do this for a bunch of
tables) I wanted to check if the following approach is fine.
I am using SA 0.5, ORM and declarative and the model is:
class Cbbottle(Base):
__table__ = sa.Table(u'cbbottle', metadata,
sa.Column(u'cbbottleid', sa.Integer(),
sa.Sequence('gen_cbbottle_cbbottleid'), primary_key=True, nullable=False),
etc
)
purchase = sao.relation('Purchase', cascade=all, delete,
delete-orphan, backref='cbbottle')
aItem = session.query(db.Cbbottle).get(keyno)
bItem = session.query(db.Cbbottle).get(anotherkeyno)
for purchase in aItem.purchase:
purchase.cbbottle = bItem
session.commit()
At this point I expect that aItem has no more purchase relations and
they are all related to bItem.
Werner
--~--~-~--~~~---~--~~
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] what is wrong with my update query
Hi folks,
I have recently started to learn ORM tools in python. I am trying to
write an update query based on a where clause. This is the original
SQL:
UPDATE mail SET fl=20 WHERE f =1 and uid =2 ;
This is what I did in SA:
flTot = 19
fNo = 2
uidNo = 5
a = column('f') + bindparam('fNo')
b = column('uid') + bindparam('uidNo')
ac = a.params({'fNo':fNo})
bc = b.params({'uidNo':uidNo})
ae = ClauseElement.params(ac)
be = ClauseElement.params(bc)
print and_(ae,be)
cache_fl = mail.update(and_(ae,be), values={mail.c.fl:135})
print mysql_db.execute(cache_fl)
The problem is it sets the value of flag for every row in the table
and not just for the where clause condition. Also, I wanted to pick up
the value from variables, so I used bindparams. Is it correct?
Regards,
abhi
--~--~-~--~~~---~--~~
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: autoflush during instanciation
that's the cascade of p2 being added to the session upon being associated with the in-session items in p.children. So yes, cascade rules do add things to the session. setting cascade=None on Child.parent would prevent it, or alternatively you can turn off autoflush on the session for the span of the __init__ method by just setting the .autoflush attribute to False. On Jan 29, 2009, at 5:55 AM, GustaV wrote: I quite sure I'm not using it... Look at that code that reproduce the bug http://utilitybase.com/paste/11481 The last line (p2 = Parent(p)) leads to a load of the children, so an autoflush. I have a failure with 0.5.2: sqlalchemy.exc.IntegrityError: (IntegrityError) parent.dumb may not be NULL u'INSERT INTO parent (dumb) VALUES (?)' [None] which let me think the object was somehow added to the autoflush... I hope that will help to solve this. GustaV --~--~-~--~~~---~--~~ 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] Getting ForeignKey Before Commit
Hi, My code is like that: try: for some_val in some_values: rec = SomeModel() rec.some_val = some_val session.save(rec) session.commit() except: session.rollback() For each record that I'm creating, I need to send an email right after. And each email includes some data about these records, especially the rec.id. Everything looks fine but the problem is getting the rec.id. I've tried: try: for some_val in some_values: rec = SomeModel() rec.some_val = some_val session.save(rec) email(rec=rec) # rec doesn't have id yet so it doesn't work session.commit() except: session.rollback() for some_val in some_values: rec = SomeModel() rec.some_val = some_val session.save() session.commit() email(rec=rec) # It works, but now I don't have a chance to rollback() anymore It seems like I need something whcih will trigger right after a new record is posted. I couldn't find any good solution. Now I need to learn what is the best way to do that. --~--~-~--~~~---~--~~ 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: Correct way of moving relation
Werner F. Bruhin wrote:
I have some items which are related but I need to change it so they
related to another item.
Before getting myself in a mess (as I need to do this for a bunch of
tables) I wanted to check if the following approach is fine.
I am using SA 0.5, ORM and declarative and the model is:
class Cbbottle(Base):
__table__ = sa.Table(u'cbbottle', metadata,
sa.Column(u'cbbottleid', sa.Integer(),
sa.Sequence('gen_cbbottle_cbbottleid'), primary_key=True, nullable=False),
etc
)
purchase = sao.relation('Purchase', cascade=all, delete,
delete-orphan, backref='cbbottle')
aItem = session.query(db.Cbbottle).get(keyno)
bItem = session.query(db.Cbbottle).get(anotherkeyno)
for purchase in aItem.purchase:
purchase.cbbottle = bItem
session.commit()
At this point I expect that aItem has no more purchase relations and
they are all related to bItem.
I had a go at it and get this error on a few tables:
c:\python25\lib\site-packages\sqlalchemy-0.5.2-py2.5.egg\sqlalchemy\orm\properties.py:711:
SAWarning: On Bottag.bothist, delete-orphan cascade is not supported on
a many-to-many or many-to-one relationship when single_parent is not
set. Set single_parent=True on the relation().
self._determine_direction()
Changing the relations then makes it run without error but there is one
record being missed.
I changed it slightly:
aItem = session.query(db.Cbbottle).get(keyno)
bItem = session.query(db.Cbbottle).get(anotherkeyno)
for purchase in aItem.purchase:
purchase.cbbottle = bItem
purchase.fk_cbbottleid = bItem.cbbottleid
session.commit()
But for some reason one record is still not reassigned. So, what I do
is obviously not quit right.
Werner
--~--~-~--~~~---~--~~
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: Correct way of moving relation
On Jan 29, 2009, at 11:32 AM, Werner F. Bruhin wrote:
Werner F. Bruhin wrote:
I have some items which are related but I need to change it so they
related to another item.
Before getting myself in a mess (as I need to do this for a bunch of
tables) I wanted to check if the following approach is fine.
I am using SA 0.5, ORM and declarative and the model is:
class Cbbottle(Base):
__table__ = sa.Table(u'cbbottle', metadata,
sa.Column(u'cbbottleid', sa.Integer(),
sa.Sequence('gen_cbbottle_cbbottleid'), primary_key=True,
nullable=False),
etc
)
purchase = sao.relation('Purchase', cascade=all, delete,
delete-orphan, backref='cbbottle')
aItem = session.query(db.Cbbottle).get(keyno)
bItem = session.query(db.Cbbottle).get(anotherkeyno)
for purchase in aItem.purchase:
purchase.cbbottle = bItem
session.commit()
At this point I expect that aItem has no more purchase relations
and
they are all related to bItem.
I had a go at it and get this error on a few tables:
c:\python25\lib\site-packages\sqlalchemy-0.5.2-py2.5.egg\sqlalchemy
\orm\properties.py:711:
SAWarning: On Bottag.bothist, delete-orphan cascade is not supported
on
a many-to-many or many-to-one relationship when single_parent is not
set. Set single_parent=True on the relation().
self._determine_direction()
Changing the relations then makes it run without error but there is
one
record being missed.
I changed it slightly:
aItem = session.query(db.Cbbottle).get(keyno)
bItem = session.query(db.Cbbottle).get(anotherkeyno)
for purchase in aItem.purchase:
purchase.cbbottle = bItem
purchase.fk_cbbottleid = bItem.cbbottleid
session.commit()
But for some reason one record is still not reassigned. So, what I do
is obviously not quit right.
there's no need to reassign the FK column yourself and the original
pattern you're using is correct. that only one item in the list is
the exception suggests something else is changing its state again
further down the road.
--~--~-~--~~~---~--~~
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: Correct way of moving relation
Michael, Michael Bayer wrote: ... there's no need to reassign the FK column yourself and the original pattern you're using is correct. that only one item in the list is the exception suggests something else is changing its state again further down the road. Thanks for the quick reply, will setup an isolated test script and see if I can figure out what I am doing wrong. Werner --~--~-~--~~~---~--~~ 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] Postgres and count() weirdness
Hi, query.count().scalar() This code works fine with sqlite and Oracle, but with PG it goes nuts and complains that an explicit AS must be used in a subselect, i.e. it's doing this: select count(*) as blah from (select ...) But PG wants this: select count(*) as blah from (select ...) as boom Doing this fixes the problem: query.alias().count().scalar() But to me this seems like a deficiency in the dialect - shouldn't the AS be added automagically ? sqlalchemy.__version__ '0.5.0rc4' It's no biggie but seems worth pointing out in case you weren't aware of it. Thanks. :-) -- -- Bob Farrell pH, an Experian Company www.phgroup.com Office Line: 020 7598 0310 Fax: 020 7598 0311 -- --~--~-~--~~~---~--~~ 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: delete failure with foreign key relations
sorry. i obviously didn't explain too well. it DOES work with ORM. i
don't have a problem at all.
however, i can't work with the tables using engine.conect or straight
MySQLdb, for that matter.
in fact, closer examination suggests that not only are the onupdate,
ondelete = CASCADE not set
in the tables but trying to set them manually also results in an
error:
Can't create table 'test2.#sql-a6_fb' (errno: 121)
following below, a stylized version of the code (which works with
ORM). however, i need to be able to
use 'straight' sql on all tables created with ORM.
thx
import
engine =
...
main_table = Table('main_table', meta,
Column(u'id', Integer, primary_key=True),
Column(u'data', Unicode(255)),
mysql_engine='InnoDB'
)
relation_table = Table('relation_table', meta,
Column(u'id', Integer, primary_key=True),
Column(u'data', Unicode(255)),
mysql_engine='InnoDB'
)
main_relation_table = Table('main_relation_table', meta,
Column(u'id', Integer, primary_key=True),
Column(u'main_id', Integer, ForeignKey('main_table.id')),
Column(u'relation_id', Integer, ForeignKey('relation_table.id')),
mysql_engine='InnoDB'
)
class MainObj(object): pass
class RelObj(object): pass
mapper(MainObj, main_table, properties={
'relations': relation(RelObj, secondary=main_relation_table,
backref='main_obj',\
cascade=all, delete, delete-orphan)
})
mapper(RelObj, relation_table)
Session = sessionmaker(bind=engine)
meta.create_all()
session = Session()
mainobj = MainObj()
mainobj.data = u'test main'
relobj = RelObj()
relobj.data = u'relation object instance 1'
mainobj.relations.append(relobj)
session.add(mainobj)
session.commit()
session.close()
if __name__=='__main__':
session = Session()
obj = session.query(MainObj).all()
for o in obj:
#print o.id, o.relations
pass
obj = session.query(MainObj).filter(MainObj.id==1).all()
for m in obj:
print m.id, m.data, m.relations
obj = session.query(RelObj).filter(RelObj.id==1).all()
for r in obj:
print r.id, r.main_obj
session.close()
meta.drop_all()
sys.exit()
On Jan 29, 4:27 am, Alex K klizhen...@gmail.com wrote:
Well, this error says that you have rows in other(or same) tables
referring to this row you are going to delete,
and you should delete referring rows first. If you want SQLA to do it
automatically,
you need to use sessions and mappers (not raw SQL expression engine),
more info here:
http://www.sqlalchemy.org/docs/05/ormtutorial.html#configuring-delete...
Regards,
Alex
On Jan 29, 8:33 am, n00b pyn...@gmail.com wrote:
back again, sorry.
i specified a model with a few one-to-many and one many-to-many
relations using SA 0.51 in MySql 5.1.25 rc; tables are all INNODB. all
works well and as expected in the ORM realm. however, when i'm trying
to use SQL Expression for a delete (row) operation, i get the dreaded
IntegrityError: (IntegrityError) (1451, 'Cannot delete or update a
parent row: a foreign key constraint fails
specifically, i'm using:
engine = create_engine('mysql://root:@localhost:3306/test2')
connection = engine.connect()
metadata = MetaData(bind=engine)
main_table = Table('main_table', metadata, autoload=True)
target_id = 1 #for illustrative purposes, primary key to delete
connection.execute(main_table.delete().where(main_table.c.id
==target_id))
where main_table is my main table and all other tables link to its id
(primary key int) via
foreign keys.
thx for you help
--~--~-~--~~~---~--~~
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: Postgres and count() weirdness
for the SQL expression language we try not to guess things like that. the difference between a select() and an alias() is pretty big. On Jan 29, 2009, at 12:36 PM, Bob Farrell wrote: Hi, query.count().scalar() This code works fine with sqlite and Oracle, but with PG it goes nuts and complains that an explicit AS must be used in a subselect, i.e. it's doing this: select count(*) as blah from (select ...) But PG wants this: select count(*) as blah from (select ...) as boom Doing this fixes the problem: query.alias().count().scalar() But to me this seems like a deficiency in the dialect - shouldn't the AS be added automagically ? sqlalchemy.__version__ '0.5.0rc4' It's no biggie but seems worth pointing out in case you weren't aware of it. Thanks. :-) -- -- Bob Farrell pH, an Experian Company www.phgroup.com Office Line: 020 7598 0310 Fax: 020 7598 0311 -- --~--~-~--~~~---~--~~ 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: Correct way of moving relation
Michael, I run the following script and initially had the either my application and/or the IB Expert database tool (for Firebird SQL v 2.1) open at the same time. Now the following tests are done without any other task accessing the database. script: engine = db.sa.create_engine(dburl, encoding='utf8', echo=False) Session = db.sao.sessionmaker() Session.configure(bind=engine) session = Session() keyA = 174 keyB = 175 itemB = session.query(db.Cbbottle).get(keyB) print 'before move from B to A' print 'itemB id: %s' % itemB.cbbottleid for purch in itemB.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid session.flush() itemA = session.query(db.Cbbottle).get(keyA) itemB = session.query(db.Cbbottle).get(keyB) print 'start to move from B to A' print 'itemA id: %s' % itemA.cbbottleid for purch in itemB.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid purch.cbbottle = itemA session.commit() itemA = session.query(db.Cbbottle).get(keyA) print 'after move from B to A' print 'itemA id: %s' % itemA.cbbottleid for purch in itemA.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid The following is the output, note that purchasid 80 is not being moved. before move from B to A itemB id: 175 purchasid: 79 fk_cbbottleid: 175 purchasid: 80 fk_cbbottleid: 175 purchasid: 81 fk_cbbottleid: 175 start to move from B to A itemA id: 174 purchasid: 79 fk_cbbottleid: 175 purchasid: 81 fk_cbbottleid: 175 after move from B to A itemA id: 174 purchasid: 79 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 Without doing other tasks on the database I run the same script again, and get this output (80 is now moved). before move from B to A itemB id: 175 purchasid: 80 fk_cbbottleid: 175 start to move from B to A itemA id: 174 purchasid: 80 fk_cbbottleid: 175 after move from B to A itemA id: 174 purchasid: 79 fk_cbbottleid: 174 purchasid: 80 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 If I run the script again nothing is moved (which is obviously correct) and all shows under fk_cbbottleid 174, now I change the keyA and keyB variable and reverse the values as follows: keyA = 175 keyB = 174 Now I get this and again 80 is not moved. before move from B to A itemB id: 174 purchasid: 79 fk_cbbottleid: 174 purchasid: 80 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 start to move from B to A itemA id: 175 purchasid: 79 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 after move from B to A itemA id: 175 purchasid: 79 fk_cbbottleid: 175 purchasid: 81 fk_cbbottleid: 175 I can repeat this again and again and it is always 80 which does not move the first time. Any ideas on what I can do to find out what is causing this row not to move would be very much appreciated. Werner --~--~-~--~~~---~--~~ 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: Correct way of moving relation
that sess.fulsh() in the middle there... if u move it up/down/out, will behaviour change? e.g. if u print the things in itemB.purchase just _After that flush - is 80 there or not? On Thursday 29 January 2009 20:19:59 Werner F. Bruhin wrote: Michael, I run the following script and initially had the either my application and/or the IB Expert database tool (for Firebird SQL v 2.1) open at the same time. Now the following tests are done without any other task accessing the database. script: engine = db.sa.create_engine(dburl, encoding='utf8', echo=False) Session = db.sao.sessionmaker() Session.configure(bind=engine) session = Session() keyA = 174 keyB = 175 itemB = session.query(db.Cbbottle).get(keyB) print 'before move from B to A' print 'itemB id: %s' % itemB.cbbottleid for purch in itemB.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid session.flush() itemA = session.query(db.Cbbottle).get(keyA) itemB = session.query(db.Cbbottle).get(keyB) print 'start to move from B to A' print 'itemA id: %s' % itemA.cbbottleid for purch in itemB.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid purch.cbbottle = itemA session.commit() itemA = session.query(db.Cbbottle).get(keyA) print 'after move from B to A' print 'itemA id: %s' % itemA.cbbottleid for purch in itemA.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid The following is the output, note that purchasid 80 is not being moved. before move from B to A itemB id: 175 purchasid: 79 fk_cbbottleid: 175 purchasid: 80 fk_cbbottleid: 175 purchasid: 81 fk_cbbottleid: 175 start to move from B to A itemA id: 174 purchasid: 79 fk_cbbottleid: 175 purchasid: 81 fk_cbbottleid: 175 after move from B to A itemA id: 174 purchasid: 79 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 Without doing other tasks on the database I run the same script again, and get this output (80 is now moved). before move from B to A itemB id: 175 purchasid: 80 fk_cbbottleid: 175 start to move from B to A itemA id: 174 purchasid: 80 fk_cbbottleid: 175 after move from B to A itemA id: 174 purchasid: 79 fk_cbbottleid: 174 purchasid: 80 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 If I run the script again nothing is moved (which is obviously correct) and all shows under fk_cbbottleid 174, now I change the keyA and keyB variable and reverse the values as follows: keyA = 175 keyB = 174 Now I get this and again 80 is not moved. before move from B to A itemB id: 174 purchasid: 79 fk_cbbottleid: 174 purchasid: 80 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 start to move from B to A itemA id: 175 purchasid: 79 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 after move from B to A itemA id: 175 purchasid: 79 fk_cbbottleid: 175 purchasid: 81 fk_cbbottleid: 175 I can repeat this again and again and it is always 80 which does not move the first time. Any ideas on what I can do to find out what is causing this row not to move would be very much appreciated. Werner --~--~-~--~~~---~--~~ 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: Correct way of moving relation
On Jan 29, 2009, at 1:19 PM, Werner F. Bruhin wrote: Michael, I run the following script and initially had the either my application and/or the IB Expert database tool (for Firebird SQL v 2.1) open at the same time. Now the following tests are done without any other task accessing the database. script: engine = db.sa.create_engine(dburl, encoding='utf8', echo=False) Session = db.sao.sessionmaker() Session.configure(bind=engine) session = Session() keyA = 174 keyB = 175 itemB = session.query(db.Cbbottle).get(keyB) print 'before move from B to A' print 'itemB id: %s' % itemB.cbbottleid for purch in itemB.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid session.flush() itemA = session.query(db.Cbbottle).get(keyA) itemB = session.query(db.Cbbottle).get(keyB) print 'start to move from B to A' print 'itemA id: %s' % itemA.cbbottleid for purch in itemB.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid purch.cbbottle = itemA session.commit() itemA = session.query(db.Cbbottle).get(keyA) print 'after move from B to A' print 'itemA id: %s' % itemA.cbbottleid for purch in itemA.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid The following is the output, note that purchasid 80 is not being moved. before move from B to A itemB id: 175 purchasid: 79 fk_cbbottleid: 175 purchasid: 80 fk_cbbottleid: 175 purchasid: 81 fk_cbbottleid: 175 start to move from B to A itemA id: 174 purchasid: 79 fk_cbbottleid: 175 purchasid: 81 fk_cbbottleid: 175 right there, purchasid 80 is not even in the list of items anymore. This is basically iterate the list, 80 is there, then flush(), then 80 is not there. this is all before anything has been moved. so either the flush() does something, or just the move of item #79 affects something with #80. try it without the flush(), and alternatively try iterating through the list a second time without moving the items, see if 80 disappears. try without the delete- orphan too, perhaps thats triggering an event that is problematic. --~--~-~--~~~---~--~~ 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: Full connection pool close
I worked up a test case that simulates your usage, and checks the number of
open MSSQL connections using a call to the system stored proc sp_who, so
it can run in a more automated fashion.
I originally got mixed results on this, it would pass about 50% of the time
and fail about 50% of the time.
So I then added some options that would force a GC collection (the idea
being to force any finalizers for the pyodbc sockets to close them), which
increased the percentage of the time the test would pass, but not eliminate
the failures.
I then added a wait option which simply sleeps for brief period after
closing the SA connections, and then does the connection count check. With a
1/2 second delay between the closing of the SA connection pool and the check
for all connections closed, I get pretty reliable results for closing all
connections.
Please try the attached test on your machine and see if you get similar
results.
Rick
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
# -*- encoding: utf-8
This test checks that SQLAlchemy fully closes all pooled pyodbc connection when we dispose() the engine.
For this test to be meaningful, this test should be the only process opening and closing connections on the server.
# Import pyodbc first and force off ODBC connection pooling
import pyodbc
pyodbc.pooling = False
import sqlalchemy as sa
import sqlalchemy.orm
user = ''
pwd = ''
dsn = ''
dbname = 'satest'
server = 'localhost'
wait_time = .5
force_gc = False
# establish a baseline # of connections
c_pyodbc = pyodbc.connect('DRIVER={Sql Server}; SERVER=%s; DATABASE=%s; UID=%s; PWD=%s' %(server, dbname, user, pwd))
initial_connections = c_pyodbc.execute('sp_who %s' % user).fetchall()
# open the pooled connections from SA
dburi = 'mssql://%s:%...@%s' % (user, pwd, dsn)
sa_engine = sa.create_engine(dburi, echo=False, strategy='threadlocal')
sa_Session = sa.orm.scoped_session( sa.orm.sessionmaker( bind=sa_engine ) )
metadata = sa.MetaData(sa_engine)
sa_session = sa_Session()
t = sa.Table(closetest, metadata,
sa.Column('id', sa.INT, primary_key=True),
sa.Column('nm', sa.VARCHAR(20))
)
# TODO: try with autoload table
# exercise the connection
metadata.create_all()
t.insert().execute(nm='test')
tlist = t.select().execute().fetchall()
assert len(tlist) == 1
metadata.drop_all()
# close the connection
sa_session.close()
sa_Session.close_all()
sa_engine.dispose()
del sa_session
del sa_Session
del sa_engine
if wait_time:
import time
time.sleep(wait_time)
if force_gc:
import gc
gc.collect()
# ensure the number of connections has not grown
post_test_connections = c_pyodbc.execute('sp_who %s' % user).fetchall()
try:
assert len(post_test_connections) == len(initial_connections)
print 'Passed'
except:
print 'Open connections!: ', len(post_test_connections), len(initial_connections)
[sqlalchemy] Re: Correct way of moving relation
Michael Bayer wrote: .. right there, purchasid 80 is not even in the list of items anymore. This is basically iterate the list, 80 is there, then flush(), then 80 is not there. this is all before anything has been moved. so either the flush() does something, or just the move of item #79 affects something with #80. try it without the flush(), and alternatively try iterating through the list a second time without moving the items, see if 80 disappears. try without the delete- orphan too, perhaps thats triggering an event that is problematic. Tried it with removing the flush() and not using the delete-orphan and still the same behavior. I then added two more purchase records and now a pattern starts to show, i.e. every second one does not get moved. The slightly updated script (added a print itemB.purchase and removed the flush) and the output: itemA = session.query(db.Cbbottle).get(keyA) itemB = session.query(db.Cbbottle).get(keyB) print 'start to move from B to A' print 'itemA id: %s' % itemA.cbbottleid print 'no purch: %s' % len(itemB.purchase) print itemB.purchase itemB = session.query(db.Cbbottle).get(keyB) for purch in itemB.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid purch.cbbottle = itemA session.commit() itemA = session.query(db.Cbbottle).get(keyA) print 'after move from B to A' print 'itemA id: %s' % itemA.cbbottleid for purch in itemA.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid before move from B to A itemB id: 174 purchasid: 79 fk_cbbottleid: 174 purchasid: 80 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 purchasid: 82 fk_cbbottleid: 174 purchasid: 83 fk_cbbottleid: 174 start to move from B to A itemA id: 175 no purch: 5 [Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=79, purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), purchasevalue=Decimal(0), quantity=1, remarks=None, transferid=None, updated=datetime.date(2009, 1, 29)), Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=80, purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), purchasevalue=Decimal(0), quantity=1, remarks=None, transferid=None, updated=datetime.date(2009, 1, 29)), Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=81, purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), purchasevalue=Decimal(0), quantity=5, remarks=None, transferid=None, updated=datetime.date(2009, 1, 29)), Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=82, purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), purchasevalue=Decimal(0), quantity=2, remarks=None, transferid=None, updated=datetime.date(2009, 1, 29)), Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=83, purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), purchasevalue=Decimal(0), quantity=3, remarks=None, transferid=None, updated=datetime.date(2009, 1, 29))] purchasid: 79 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 purchasid: 83 fk_cbbottleid: 174 after move from B to A itemA id: 175 purchasid: 79 fk_cbbottleid: 175 purchasid: 81 fk_cbbottleid: 175 purchasid: 83 fk_cbbottleid: 175 My work around is iterate over the purchase relation and get the primary keys, then do a query on the purchase table and move them. Following script does the trick but it is not quit as nice. keyA = 174 keyB = 175 itemB = session.query(db.Cbbottle).get(keyB) print 'before move from B to A' print 'itemB id: %s' % itemB.cbbottleid allPurch = [] for purch in itemB.purchase: allPurch.append(purch.purchaseid) print 'allPurch: %s' % allPurch itemA = session.query(db.Cbbottle).get(keyA) itemB = session.query(db.Cbbottle).get(keyB) print 'start to move from B to A' print 'itemA id: %s' % itemA.cbbottleid print 'no purch: %s' % len(itemB.purchase) for purchId in allPurch: purch = session.query(db.Purchase).get(purchId) print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid purch.cbbottle = itemA session.commit() itemA = session.query(db.Cbbottle).get(keyA) print 'after move from B to A' print 'itemA id: %s' % itemA.cbbottleid for purch in itemA.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid Werner
[sqlalchemy] Re: Correct way of moving relation
oh, duh. do it like this: for purchase in list(aItem.purchase): purchase.cbbottle = bItem I leave it to you as an exercise why this is the case. On Jan 29, 2009, at 3:08 PM, Werner F. Bruhin wrote: Michael Bayer wrote: .. right there, purchasid 80 is not even in the list of items anymore. This is basically iterate the list, 80 is there, then flush(), then 80 is not there. this is all before anything has been moved. so either the flush() does something, or just the move of item #79 affects something with #80. try it without the flush(), and alternatively try iterating through the list a second time without moving the items, see if 80 disappears. try without the delete- orphan too, perhaps thats triggering an event that is problematic. Tried it with removing the flush() and not using the delete-orphan and still the same behavior. I then added two more purchase records and now a pattern starts to show, i.e. every second one does not get moved. The slightly updated script (added a print itemB.purchase and removed the flush) and the output: itemA = session.query(db.Cbbottle).get(keyA) itemB = session.query(db.Cbbottle).get(keyB) print 'start to move from B to A' print 'itemA id: %s' % itemA.cbbottleid print 'no purch: %s' % len(itemB.purchase) print itemB.purchase itemB = session.query(db.Cbbottle).get(keyB) for purch in itemB.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid purch.cbbottle = itemA session.commit() itemA = session.query(db.Cbbottle).get(keyA) print 'after move from B to A' print 'itemA id: %s' % itemA.cbbottleid for purch in itemA.purchase: print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid before move from B to A itemB id: 174 purchasid: 79 fk_cbbottleid: 174 purchasid: 80 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 purchasid: 82 fk_cbbottleid: 174 purchasid: 83 fk_cbbottleid: 174 start to move from B to A itemA id: 175 no purch: 5 [Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=79, purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), purchasevalue=Decimal(0), quantity=1, remarks=None, transferid=None, updated=datetime.date(2009, 1, 29)), Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=80, purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), purchasevalue=Decimal(0), quantity=1, remarks=None, transferid=None, updated=datetime.date(2009, 1, 29)), Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=81, purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), purchasevalue=Decimal(0), quantity=5, remarks=None, transferid=None, updated=datetime.date(2009, 1, 29)), Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=82, purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), purchasevalue=Decimal(0), quantity=2, remarks=None, transferid=None, updated=datetime.date(2009, 1, 29)), Purchase(created=datetime.date(2009, 1, 29), fk_cbbottleid=174, fk_cellarid=None, fk_currencyid=1, fk_supplierid=None, purchased=datetime.datetime(2009, 1, 29, 0, 0), purchaseid=83, purchaseprice=Decimal(0), purchasepriceforeign=Decimal(0), purchasevalue=Decimal(0), quantity=3, remarks=None, transferid=None, updated=datetime.date(2009, 1, 29))] purchasid: 79 fk_cbbottleid: 174 purchasid: 81 fk_cbbottleid: 174 purchasid: 83 fk_cbbottleid: 174 after move from B to A itemA id: 175 purchasid: 79 fk_cbbottleid: 175 purchasid: 81 fk_cbbottleid: 175 purchasid: 83 fk_cbbottleid: 175 My work around is iterate over the purchase relation and get the primary keys, then do a query on the purchase table and move them. Following script does the trick but it is not quit as nice. keyA = 174 keyB = 175 itemB = session.query(db.Cbbottle).get(keyB) print 'before move from B to A' print 'itemB id: %s' % itemB.cbbottleid allPurch = [] for purch in itemB.purchase: allPurch.append(purch.purchaseid) print 'allPurch: %s' % allPurch itemA = session.query(db.Cbbottle).get(keyA) itemB = session.query(db.Cbbottle).get(keyB) print 'start to move from B to A' print 'itemA id: %s' % itemA.cbbottleid print 'no purch: %s' % len(itemB.purchase) for purchId in allPurch: purch = session.query(db.Purchase).get(purchId) print 'purchasid: %s' % purch.purchaseid print 'fk_cbbottleid: %s' % purch.fk_cbbottleid purch.cbbottle = itemA session.commit() itemA =
[sqlalchemy] Re: Getting ForeignKey Before Commit
why don't you work off the las/previous committed rec id? On Jan 29, 4:05 am, Dejan Mayo dejan.m...@gmail.com wrote: Hi, My code is like that: try: for some_val in some_values: rec = SomeModel() rec.some_val = some_val session.save(rec) session.commit() except: session.rollback() For each record that I'm creating, I need to send an email right after. And each email includes some data about these records, especially the rec.id. Everything looks fine but the problem is getting the rec.id. I've tried: try: for some_val in some_values: rec = SomeModel() rec.some_val = some_val session.save(rec) email(rec=rec) # rec doesn't have id yet so it doesn't work session.commit() except: session.rollback() for some_val in some_values: rec = SomeModel() rec.some_val = some_val session.save() session.commit() email(rec=rec) # It works, but now I don't have a chance to rollback() anymore It seems like I need something whcih will trigger right after a new record is posted. I couldn't find any good solution. Now I need to learn what is the best way to do that. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
