I have a products database which is daily syncronized with an external
source via a csv file. There are several thousand rows in question. The
synchronization does two things:
1. Update only price if changed for existing products
2. Insert new products if they don't exist with all fields from csv
But basically, for each row in the csv, after the row is processed (one
of the above two things is done), I don't need the object in session
anymore. Memory and performance are of course an issue, and I can't find
a way to test memory consumption with or without expunge_all() so my
questions are:
1. Do I need to session.expunge_all() after each csv row is processed,
or are they automatically garbage collected?
2. Is there any significant overhead inherent in expunge_all() that I'm
not seeing right now?
Performance-wise, it seems the task is complete in more or less same
time with or without expunge_all()
While I'm at it, I also need to delete rows in the database that do not
have corresponding row in the csv file (say linked by csv_key field),
the first solution that comes to mind is building a list of keys in the
csv file (few thousand keys) and then doing:
session.query(Product).filter(not_(Product.product_id.in_(csv_keys))).delete()
I believe there is less overhead in sending such a large (but single!)
query to the database and leaving it to determine what to delete by
itself, than selecting each row in the database and checking if its
csv_key exists in the csv_keys list on the application side and then
issuing delete statements for rows that matched the criteria. Am I wrong?
(I can't truncate table and reinsert products for other reasons)
The database is Postgresql.
Thanks!
--
.oO V Oo.
--
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.
