Thanks for your thoughts! I was wondering which will be more economical in terms of memory and cpu
1. Let SQLA manage cascades. - query.all() brings the entire table into memory. I am trying to figure out how to manage it with cursors. - There is a processing overhead for SQLA to convert RDBMS records to Objects. -> More CPU 2. Triggers on SQLite. - http://www.sqlite.org/tempfiles.html, seems that SQLite maintains transaction logs in a journal file. I am doing more testing to find out the best option. Regards, Harish On Fri, May 29, 2009 at 9:35 PM, Randy Syring <ra...@rcs-comp.com> wrote: > > Ah...missed that part. Well, the triggers are fired per-row I > believe. So, if you don't use a transaction explicitly, and just let > SQLite run without one, it will be slower, but the resource usage > should be much less. > > On May 29, 10:31 am, Mike Conley <mconl...@gmail.com> wrote: > > Harish said it was an embedded system, probably all resources are pretty > > severely restricted. > > -- > > Mike Conley > > > > On Fri, May 29, 2009 at 9:44 AM, Randy Syring <ra...@rcs-comp.com> > wrote: > > > > > Mike, > > > > > Well...I am not sure. I thought SQLite held transaction details in > > > a .journal file and not in memory. I thought that the memory use > > > might actually be a Python problem and not a result of SQLite. If my > > > thoughts are correct, using the FK approach should keep deleting the > > > children in SQLite, which should use a journal file, which should > > > reduce memory usage. > > > > > But I could be wrong. :) > > > > > On May 29, 9:28 am, Mike Conley <mconl...@gmail.com> wrote: > > > > Randy, > > > > > > Interesting approach to foreign key management. Harish indicates he > is > > > > having a problem with restricted memory. Won't that still be true > with > > > > triggers? After all, if the problem is that the transaction is too > big, > > > it > > > > will still be too big with all the pending deletes executed in a > trigger. > > > > > > -- > > > > Mike Conley > > > > > > On Fri, May 29, 2009 at 8:47 AM, Randy Syring <ra...@rcs-comp.com> > > > wrote: > > > > > > > Another solution is to use triggers in SQLite to enforce FK > > > > > relationships. > > > > > > >http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers > > > > >http://code.google.com/p/sqlitefktg4sa/ > > > > > > > On May 29, 6:59 am, Mike Conley <mconl...@gmail.com> wrote: > > > > > > One solution is to change the commit strategy; issue commits > > > periodically > > > > > > during the loop. > > > > > > > > parentlist = session.query(Parent).all() > > > > > > count = 0 > > > > > > for parent in parentlist: > > > > > > session.delete(parent) > > > > > > count += 1 > > > > > > if count % 100 == 0 # use whatever frequency is needed > > > > > > count = 0 > > > > > > session.commit() > > > > > > if count > 0: > > > > > > session.commit() # this gets the last group of deletes > > > > > > > > The disadvantage of this approach is that you lose the ability to > > > > > rollback > > > > > > the entire delete process, and now must handle that problem with > > > > > application > > > > > > design. > > > > > > > > -- > > > > > > Mike Conley > > > > > > > > On Fri, May 29, 2009 at 4:08 AM, Harish Vishwanath < > > > > > harish.shas...@gmail.com > > > > > > > > > wrote: > > > > > > > Hello, > > > > > > > > > I am running Sqlite/SQLA/Elixir on an embedded system. I have > > > different > > > > > > > classes with OneToMany relationships and I have configured > cascade > > > = > > > > > "all, > > > > > > > delete, delete-orphan" correctly on them. However, for this to > > > work, I > > > > > > > should do something like : > > > > > > > > > parentlist = session.query(Parent).all() > > > > > > > for parent in parentlist: > > > > > > > session.delete(parent) # > > > > > > > session.commit() > > > > > > > > > The above chokes the system since it has limited memory. > > > > > > > > > The statement below : > > > > > > > > > session.query(Parent).delete(), issues DELETE FROM PARENT; > > > > > > > > > This is memory efficient, but it doesn't delete the child > objects > > > since > > > > > > > Sqlite doesn't impose FK constraints. > > > > > > > > > Is there any way to solve this problem? > > > > > > > > > Regards, > > > > > > > Harish > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
