Nagging problem with MySQLdb

Discussion in 'Python' started by Phillip, Nov 22, 2004.

  1. Phillip

    Phillip Guest

    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
     
    Phillip, Nov 22, 2004
    #1
    1. Advertising

  2. 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.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
     
    Erik Max Francis, Nov 22, 2004
    #2
    1. Advertising

  3. Phillip

    Kent Johnson Guest

    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"
    >
     
    Kent Johnson, Nov 22, 2004
    #3
  4. Phillip

    Peter Hansen Guest

    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
     
    Peter Hansen, Nov 22, 2004
    #4
  5. Phillip

    John Fabiani Guest

    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
     
    John Fabiani, Nov 24, 2004
    #5
  6. Phillip

    Damjan Guest

    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
     
    Damjan, Nov 24, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Skip Montanaro

    Re: Problem with MySQLdb on Mac OS X...

    Skip Montanaro, Jul 12, 2003, in forum: Python
    Replies:
    5
    Views:
    392
    Michael S. Jessop
    Jul 16, 2003
  2. Bob Swerdlow

    problem installing MySQLdb on Solaris 8

    Bob Swerdlow, May 26, 2004, in forum: Python
    Replies:
    0
    Views:
    449
    Bob Swerdlow
    May 26, 2004
  3. ws Wang
    Replies:
    2
    Views:
    491
    ws Wang
    Nov 30, 2004
  4. Dave Merrill
    Replies:
    7
    Views:
    435
    Dave Merrill
    Nov 29, 2004
  5. Barely Audible
    Replies:
    2
    Views:
    113
    Humbug
    Jul 31, 2008
Loading...

Share This Page