Nagging problem with MySQLdb

[Phillip]

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:

>> ....
#Setting up DB connection, initializing DB cursor
elefantDb = MySQLdb.connect (user="runbase" ,db="elefant")
baseFeed = elefantDb.curso r()

# going through the DictList and adding the
# datasets into the db
for line in sourceLinesDict List:
# 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.execut e(
"""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>>

....# 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.execut e() 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

[Erik Max Francis]

Phillip wrote:

...

The other wariant I've tried (using a tuple) with

>>>>>>

...# 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"

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 && ma*@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

[Kent Johnson]

Phillip wrote:

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:

>>>>>>

...
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);"""

I think you may need a comma here to separate the two arguments to execute.
[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"

[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 sourceLinesDict List:
# 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 sourceLinesDict List:
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 programmaticall y create variables in the
global namespace, eliminating all duplication in the extraction
of data from "line".
baseFeed.execut e(
"""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

[John Fabiani]

Phillip wrote:

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:

>>>>>> ...
#Setting up DB connection, initializing DB cursor
elefantDb = MySQLdb.connect (user="runbase" ,db="elefant")
baseFeed = elefantDb.curso r()

# going through the DictList and adding the
# datasets into the db
for line in sourceLinesDict List:
# 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.execut e(
"""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 >>>>>>

...# 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.execut e() 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

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

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

[Damjan]

As much as I can see

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);"""
there's a missing comma here
[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]
)

--
damjan