This is a fairly vague / abstract question because I'm looking for a design
pattern -- I don't yet have any code built (except data importing) toward
the problem.
I'm importing large excel files (using csv.DictReader) (DB is MySQL /
Maria)
There are 3 columns in excel:
user_id (int)
work_units_complete (smallint)
date_worked (date)
and values (work_units_complete) are cumulative across rows for certain
date-windows (eg weekly, monthly, yearly)
A given user (eg # 25) may occur many times in the file, with different
counts for various dates.
And some counts may occur on the same day, for the same user, but at
different rows in the file.
I don't know till the end of the file if there will be more data for user
#25, so its hard to know when to persist?
I need to end up with a DB record that looks like this:
user_id: 25
month_id: 7
year_id: 2014
json_text: {
totWork_Week1: 4, # sum total of counts for the week in question
totWork_Week2: 8,
totWork_Week3: 2,
totWork_Week4: 17,
totWork_CurMonth: 4,
totWork_CurYear: 8,
}
I thinking to store JSON because this is an over-simplified example and new
counts (different types of work) will continue to emerge in the specs...
Also, I get multiple files during the month, regarding the same users....so
new data needs to be cumulative onto the prior counts.....a classic
"get_or_create" pattern...
Can anyone point me to examples, docs or tutorials that will guide me in
how to best do this with SA??
I could brute-force it but it seems like an excessive amount of I/O and
constant committing of transactions without some higher-order design
approach.
Thanks for all tips & suggestions.
D
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
