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

 

[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)

 

[Kent Johnson]

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]

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

 

[John Fabiani]

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

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]
)