Error when feeding a mysql db with a python-parsed data

Question

I use this bit of code to feed some data i have parsed from a web page to a mysql database

c=db.cursor()
c.executemany(
"""INSERT INTO data (SID, Time, Value1, Level1, Value2, Level2, Value3, Level3, Value4, Level4, Value5, Level5, ObsDate)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
clean_data
)

The parsed data looks like this (there are several hundred such lines)

clean_data = [(161,00:00:00,8.19,1,4.46,4,7.87,4,6.54,null,4.45,6,2010-04-12),(162,00:00:00,7.55,1,9.52,1,1.90,1,4.76,null,0.14,1,2010-04-12),(164,00:00:00,8.01,1,8.09,1,0,null,8.49,null,0.20,2,2010-04-12),(166,00:00:00,8.30,1,4.77,4,10.99,5,9.11,null,0.36,2,2010-04-12)]

if i hard code the data as above mySQL accepts my request (except for some quibbles about formatting)

but if the variable clean_data is instead defined as the result of the parsing code, like this:

cleaner = [("""  $!!'""", ')]'),('  $!!', ') etc etc]
def processThis(str,lst):
    for find, replace in lst:
        str = str.replace(find, replace)
    return str
clean_data = processThis(data,cleaner)

then i get the dreaded "TypeError: not enough arguments for format string"

After playing with formatting options for a few hours (I am very new to this) I am confused... what is the difference between the hard coded data and the result of the processThis function as fas as mySQL is concerned?

Any idea greatly appreciated...

Answer 1

This is a guess...

processThis returns a string. It doesn't return a list of tuples.

What happens if you try repr(clean_data)? I think you will get something like the following:

"[(161,00:00:00,8.19,1,4.46,4,7.87,4,6.54,null,4.45,6,2010-04-12)]"

That is a single string. So you are passing in a single string parameter to the query, rather than 13 values.

Your data parsing will need to be improved. You'll want to split the string and convert values into integers, floats, etc.

Answer 2

Here is a stab at parsing your string into separate values:

import datetime
import time
src = "161,00:00:00,8.19,1,4.46,4,7.87,4,6.54,null,4.45,6,2010-04-12"
asFloat = lambda x : float(x)
asInt = lambda x : int(x)
asTime = lambda x : datetime.datetime(*time.strptime(x,'%H:%M:%S')[:7]).time()
asDate = lambda x : datetime.datetime(*time.strptime(x,'%Y-%m-%d')[:3]).date()
asNone = lambda x : None if x=='null' else int('ValueError')
converters = (asDate,asTime,asInt,asFloat,asNone,)
def convert(x):
  for cnv in converters:
    try:
      return cnv(x)
    except ValueError:
      pass
  return x

clean_data = [convert(s) for s in src.split(',')]
print clean_data

Gives:

[161, datetime.time(0, 0), 8.1899999999999995, 1, 4.46, 4, 
 7.8700000000000001, 4, 6.54, None, 4.4500000000000002, 6, 
 datetime.date(2010, 4, 12)]

which should give a proper list for each row to pass to executemany.

-- Paul