On Thursday, December 18, 2014 12:42:03 PM UTC-5, dewey wrote: > > Per file?? Even if it's a really large file?? Is the only limit on > transaction size available client memory or are their constraints on the > server side as well? >
Usually, yes. If you fail partway through the file, you'll need to rollback the transaction so you can restart. You could write something complicated to handle this situation, and it could be simpler if you created some sort of local "transaction_id" that was tied to the source file. But for processing data files -- it's generally less headaches and less work if you do a transaction per file. I don't remember how MySql handles memory, but there should be server and client maximums. However, your concerns would be handled by the transaction log for your storage engine, which would be used for the rollbacks on a fail. The settings/log are different for each storage engine, but I think the default is around 256MB. On the Python side, if you use the SqlAlchemy ORM, that will use a lot more memory than if you just use the SqlAlchemy Engine -- as a Python object will be created for every entry, and then flushed to the database. If you just use the Engine to write, it will be relatively lightweight as no Python objects will be created/maintained. If you want to minimize io, there are a few things you can do off the top of my head: - pre-process the Excel file in Python; then use that data with SqlAlchemy (I do this often) - use the SqlAlchemy Engine instead of the ORM (minimize reads and writes) But at the end of the day, your proposed solution will likely be messy and require a lot of work. By storing a single entry with year-to-date metrics, you'll have a few problems: • must fetch one or more entries for every operation (you always need the current month, and will need the last month 1/4 times) • you need to ensure that every entry you edit is the latest entry, if it's not you'll need to fetch and recalculate all the successive entries. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
