Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Nagging problem with MySQLdb

Reply
Thread Tools

Nagging problem with MySQLdb

 
 
Phillip
Guest
Posts: n/a
 
      11-22-2004
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
 
Reply With Quote
 
 
 
 
Erik Max Francis
Guest
Posts: n/a
 
      11-22-2004
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 && http://www.velocityreviews.com/forums/(E-Mail Removed) && 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
 
Reply With Quote
 
 
 
 
Kent Johnson
Guest
Posts: n/a
 
      11-22-2004
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"
>

 
Reply With Quote
 
Peter Hansen
Guest
Posts: n/a
 
      11-22-2004
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
 
Reply With Quote
 
John Fabiani
Guest
Posts: n/a
 
      11-24-2004
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.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
 
Reply With Quote
 
Damjan
Guest
Posts: n/a
 
      11-24-2004

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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pentax K5 finally overcomes a nagging problem Pentax has had RichA Digital Photography 0 12-18-2010 04:58 PM
Nagging questions Eric Miller Digital Photography 14 12-07-2009 02:49 AM
Nagging problem with scripts in adjacent tables... Barely Audible Javascript 2 07-31-2008 07:26 PM
Nagging Firefox Problems Silverback Computer Support 1 01-11-2007 03:17 PM
Nagging Problem skidaddy Computer Support 1 03-04-2004 05:37 AM



Advertisments