MySQLdb, lots of columns and newb-ness

[Andrew Sackville-West]

Hi list, 

I've tried, lots of interpreter testing and google grepping to figure
this out and I think I'm missing something fundamental.

I have an ascii data dump from a POS system that has 131 fields in a
single column in a flat file. I can easily open the file, read in the
data and assemble it into various formats. okay. what I *want* to do
is insert each of these fields into a mysql database that has 132
columns that correspond to the 131 fields in the ascii file (plus one
for the date).

I can successfully connect to mysql and do stuff to my tables my
specific problem is how to efficiently put those 132 fields into the
thing. All I have been able to figure out is really ugly stuff like:
build the mysql statement out of various pieces with appropriate
commas and quote included. stuff like (not tested)

for field in f.read():
    row+=field[:-2]+", "

stmt="insert into daily values "+row")"
cursor.execute(stmt)

(the slice is to kill a cr/lf on each one)

that seems really kludgey to me.

I've also tried building tuples and lists and then using this

cursor.execute("insert into daily values (%s)", values)

with no luck. it appears to me that I have to put in all 132 '%s' in
order to make that work and that just seems stupid. 

I suppose I could build a list of the column names:

columns=('Asales', 'Bsales', 'Csales' ...)

and bring in the data as a list and then 

for col in range(len(columns)):
    cursor.execute("insert into daily (%s) values (%s)",
    (columns[col], data[col]))

but again, that doesn't seem too pythonic. 

any suggestions are greatly appreciated.

A
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 196 bytes
Desc: Digital signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20061219/f116daa6/attachment.sig>