Nagging problem with MySQLdb

Hi.
I hate the way one has to jump through countless hoops to put data in a
db and get it out again. The straightforward MySQLdb Interface
requireing this SQL stuff being a point in case (against SQL and those
RDBs that is). Since other programms have to access this data I'm forced
to use a classical DB and actually have managed to set up Mysql and a
presumably working connection from Python to it.

I've gotten so far as to avoid errors with this sucky SQL language.
(Guess I've gotten to pampered by Python lately :-) )

However, for about 20 hrs. now I've been trying to write data into an
existing table. Until now no success. I hope somebody can help me with
this. Here's the relevant code:
[color=blue][color=green][color=darkred]
>>>>>>[/color][/color][/color]
....
#Setting up DB connection, initializing DB cursor
elefantDb = MySQLdb.connect(user="runbase",db="elefant")
baseFeed = elefantDb.cursor()

# 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'],
data04 = line['order-id'],
data05 = line['order-item-id'],
data06 = line['payments-date'],
data07 = line['payments-t-id'],
data08 = line['item-name'],
data09 = line['listing-id'],
data10 = line['sku'],
data11 = float(line['price'].replace(',','.',1)),
data12 = float(line['shipping-fee'].replace(',','.',1)),
data13 = line['quantity-purchased'],
data14 = float(line['total-price'].replace(',','.',1)),
data15 = line['purchase-date'],
data16 = int(line['batch-id']),
data17 = line['buyer-email'],
data18 = line['buyer-name'],
data19 = line['recipient-name'],
data20 = line['ship-address-1'],
data21 = line['ship-address-2'],
data22 = line['ship-city'],
data23 = line['ship-state'],
data24 = int(line['ship-zip']),
data25 = line['ship-country'],
data26 = line['special-comments'],
data27 = line['upc'],
data28 = float(line['VAT'].replace(',','.',1)),
data29 = 'fresh-unassigned'

baseFeed.execute(
"""INSERT INTO position
(plattform,
position_id,
payments-status,
order-id,
order-item-id,
payments-date,
payments-t-id,
item-name,
listing-id,
sku,
price,
shipping-fee,
quantity-purchased,
total-price,
purchase-date,
batch-id,
buyer-email,
buyer-name,
recipient-name,
ship-address-1,
ship-address-2,
ship-city,
ship-state,
ship-zip,
ship-country,
special-comments,
upc,
vat,
elefant-signal)
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]
)
<<<<<<

The error I get with this one is:
"TypeError: string indices must be integers"

The other wariant I've tried (using a tuple) with[color=blue][color=green][color=darkred]
>>>>>>[/color][/color][/color]
....# the same stuff as above and:
(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)
<<<<<<

returns:
"TypeError: 'str' object is not callable"

baseFeed.execute() function causing these errors.
I'm totaly void of possible answers now. The problem apparently also
being scarce and false MySQL and MySQLdb documentation *shrug*.
Can anyone help?
Thanks a lot in advance.

Phillip

re: Nagging problem with MySQLdb

Phillip wrote:

...[color=blue]
> The other wariant I've tried (using a tuple) with[color=green][color=darkred]
> >>>>>>[/color][/color]
> ...# the same stuff as above and:
> (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)
> <<<<<<
>
> returns:
> "TypeError: 'str' object is not callable"[/color]

Right now you're just writing the equivalent of

"%s is %d years old" (name, age)

which is attempting to call a string like a function. You meant:

"%s is %d years old" % (name, age)

--
Erik Max Francis && max@alcyone.com && http://www.alcyone.com/max/
San Jose, CA, USA && 37 20 N 121 53 W && AIM erikmaxfrancis
Have you fell in love with somebody / Who didn't know
-- Zhane

re: Nagging problem with MySQLdb

Phillip wrote:[color=blue]
> Hi.
> However, for about 20 hrs. now I've been trying to write data into an
> existing table. Until now no success. I hope somebody can help me with
> this. Here's the relevant code:
>[color=green][color=darkred]
> >>>>>>[/color][/color]
> ...
> 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);"""[/color]

I think you may need a comma here to separate the two arguments to execute.
[color=blue]
> [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]
> )
> <<<<<<
>
> The error I get with this one is:
> "TypeError: string indices must be integers"
>[/color]

re: Nagging problem with MySQLdb

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

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.
[color=blue]
> # 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'],[/color]
.....[color=blue]
> data10 = line['sku'],
> data11 = float(line['price'].replace(',','.',1)),[/color]
.....[color=blue]
> data16 = int(line['batch-id']),[/color]

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".
[color=blue]
> baseFeed.execute(
> """INSERT INTO position
> (plattform,
> position_id,
> payments-status,
> order-id,[/color]
....[color=blue]
> 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]
> )[/color]

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

re: Nagging problem with MySQLdb

Phillip wrote:
[color=blue]
> Hi.
> I hate the way one has to jump through countless hoops to put data in a
> db and get it out again. The straightforward MySQLdb Interface
> requireing this SQL stuff being a point in case (against SQL and those
> RDBs that is). Since other programms have to access this data I'm forced
> to use a classical DB and actually have managed to set up Mysql and a
> presumably working connection from Python to it.
>
> I've gotten so far as to avoid errors with this sucky SQL language.
> (Guess I've gotten to pampered by Python lately :-) )
>
> However, for about 20 hrs. now I've been trying to write data into an
> existing table. Until now no success. I hope somebody can help me with
> this. Here's the relevant code:
>[color=green][color=darkred]
> >>>>>>[/color][/color]
> ...
> #Setting up DB connection, initializing DB cursor
> elefantDb = MySQLdb.connect(user="runbase",db="elefant")
> baseFeed = elefantDb.cursor()
>
> # 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'],
> data04 = line['order-id'],
> data05 = line['order-item-id'],
> data06 = line['payments-date'],
> data07 = line['payments-t-id'],
> data08 = line['item-name'],
> data09 = line['listing-id'],
> data10 = line['sku'],
> data11 = float(line['price'].replace(',','.',1)),
> data12 = float(line['shipping-fee'].replace(',','.',1)),
> data13 = line['quantity-purchased'],
> data14 = float(line['total-price'].replace(',','.',1)),
> data15 = line['purchase-date'],
> data16 = int(line['batch-id']),
> data17 = line['buyer-email'],
> data18 = line['buyer-name'],
> data19 = line['recipient-name'],
> data20 = line['ship-address-1'],
> data21 = line['ship-address-2'],
> data22 = line['ship-city'],
> data23 = line['ship-state'],
> data24 = int(line['ship-zip']),
> data25 = line['ship-country'],
> data26 = line['special-comments'],
> data27 = line['upc'],
> data28 = float(line['VAT'].replace(',','.',1)),
> data29 = 'fresh-unassigned'
>
> baseFeed.execute(
> """INSERT INTO position
> (plattform,
> position_id,
> payments-status,
> order-id,
> order-item-id,
> payments-date,
> payments-t-id,
> item-name,
> listing-id,
> sku,
> price,
> shipping-fee,
> quantity-purchased,
> total-price,
> purchase-date,
> batch-id,
> buyer-email,
> buyer-name,
> recipient-name,
> ship-address-1,
> ship-address-2,
> ship-city,
> ship-state,
> ship-zip,
> ship-country,
> special-comments,
> upc,
> vat,
> elefant-signal)
> 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]
> )
> <<<<<<
>
> The error I get with this one is:
> "TypeError: string indices must be integers"
>
> The other wariant I've tried (using a tuple) with[color=green][color=darkred]
> >>>>>>[/color][/color]
> ...# the same stuff as above and:
> (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)
> <<<<<<
>
> returns:
> "TypeError: 'str' object is not callable"
>
> baseFeed.execute() function causing these errors.
> I'm totaly void of possible answers now. The problem apparently also
> being scarce and false MySQL and MySQLdb documentation *shrug*.
> Can anyone help?
> Thanks a lot in advance.
>
> Phillip[/color]

One issue
it should be "insert into table (sum fields) values (%s...), % vars"

I'm a newbie too so I might be wrong.
John

re: Nagging problem with MySQLdb

As much as I can see
[color=blue]
> 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);"""[/color]

there's a missing comma here
[color=blue]
> [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]
> )[/color]

--
damjan