[sqlalchemy] Re: Query.delete() doesn't cascade on Sqlite
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 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 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 > 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 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 > > > 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 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query.delete() doesn't cascade on Sqlite
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 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 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 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 > > 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 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query.delete() doesn't cascade on Sqlite
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 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 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 > 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 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query.delete() doesn't cascade on Sqlite
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 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 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 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query.delete() doesn't cascade on Sqlite
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 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 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query.delete() doesn't cascade on Sqlite
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 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 > > 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query.delete() doesn't cascade on Sqlite
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 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 -~--~~~~--~~--~--~---
