Nagging problem with MySQLdb

[Peter Hansen]

Phillip wrote:
> I hate the way one has to jump through countless hoops to put data in a 
> db and get it out again. 

You would have it much easier if you discovered how to use
more of Python's capabilities to write shorter code... see
some ideas below.

> # going through the DictList and adding the
> # datasets into the db
> for line in sourceLinesDictList:
>     # also adding some data for fields that the DB table has but our
>     # DictList does't (data01,02,29)
>     data01 = 'source-one'
>     data02 = '0',
>     data03 = line['payments-status'],
....
>     data10 = line['sku'],
>     data11 = float(line['price'].replace(',','.',1)),
....
>     data16 = int(line['batch-id']),

For this sort of code, you can often make it more data-driven,
along these lines (I use globals() instead of an object since
I'm not sure you're familiar with OO programming, but an object
would make this even cleaner):

for line in sourceLinesDictList:
     for i, (key, type) in enumerate([
         ('source-one', 'direct'),
         ('0', 'direct'),
         ('payments-status', 'str'),
         ...
         ('sku', 'str'),
         ('price', 'float'),
         ('batch-id', 'int'),
         ...]):
         if type == 'direct':
             val = key
         elif type == 'str':
             val = line[key]
         elif type == 'float':
             val = float(line[key].replace(',','.',1))
         elif type == 'int':
             val = int(line[key])
         globals()['data%02d' % i] = val

In other words, use a list containing the names and "types" of
information, and programmatically create variables in the
global namespace, eliminating all duplication in the extraction
of data from "line".

>     baseFeed.execute(
>         """INSERT INTO position
>         (plattform,
>         position_id,
>         payments-status,
>         order-id,
...
>         VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 
> %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"""
>         [data01, data02, data03, data04, data05, data06, data07, data08, 
> data09, data10, data11, data12, data13, data14, data15, data16, data17, 
> data18, data19, data20, data21, data22, data23, data24, data25, data26, 
> data27, data28, data29]
>     )

Having done the insertion into globals() above, you could now replace
this monster with something like this:

          ... VALUES (%s);""" % ', '.join(dataList)

(in other words, inserting the list of data items, with commas
separating them) where dataList had previously been formed in a
manner something like this:

     dataList = [globals()['data%02d' % i] for i in xrange(1, 30)]

...and similar ideas.

I hope something in here helps you make this task more manageable...

-Peter