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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFiIMsaIeIEqwil4YRArkGAKCHY6UG/NT+9fFTo61/GfVVuPJN3wCbB8mh
pW4FBdDs7FfUD0lEpIm8A4M=
=kAWM
-----END PGP SIGNATURE-----

 

[Todd Neal]

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)

I just started looking into Python myself, so someone can probably
clean this up or suggest a better way, but this may work:


import MySQLdb

fields = ["field1\r\n","field2\r\n","field3\r\n"]

def escapeAndQuote(x):
return "\"%s\"" % MySQLdb.escape_string(x)

values = ", ".join([escapeAndQuote(f[:-2]) for f in fields])
q = "insert into daily values(%s)" % values


In testing I got:

fields = ["field1\r\n","field2\r\n","field3\r\n"]
values = ", ".join([escapeAndQuote(f[:-2]) for f in fields])
values '"field1", "field2", "field3"'
q = "insert into daily values(%s)" % values

'insert into daily values("field1", "field2", "field3")'



Todd

 

[Andrew Sackville-West]

I just started looking into Python myself, so someone can probably
clean this up or suggest a better way, but this may work:

okay, let me run through this and see if I understand:

import MySQLdb

fields = ["field1\r\n","field2\r\n","field3\r\n"]

build a list of data fields to be inserted (whatever method)

def escapeAndQuote(x):
return "\"%s\"" % MySQLdb.escape_string(x)

not at the right machine to read up on this but obviously it cleans up
the strings and inserts the quotes around each field.

values = ", ".join([escapeAndQuote(f[:-2]) for f in fields])

crap. I knew about .join. that was really the part I was missing.

q = "insert into daily values(%s)" % values

make the query statement.

In testing I got:

fields = ["field1\r\n","field2\r\n","field3\r\n"]
values = ", ".join([escapeAndQuote(f[:-2]) for f in fields])
values '"field1", "field2", "field3"'
q = "insert into daily values(%s)" % values

'insert into daily values("field1", "field2", "field3")'

cool! thanks Todd.

A

Todd

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFiMgAaIeIEqwil4YRAiDNAKDmGHmUZCR/9UXARWcL1VTvs/jc8ACgk8FV
WKjfs0lVHjDhxf/EUHv0uLY=
=La5Z
-----END PGP SIGNATURE-----

 

[Felix Benner]

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)

Haven't tested it, but maybe
http://dev.mysql.com/doc/refman/5.0/en/load-data.html is your friend.

 

[Ant]

values = ", ".join([escapeAndQuote(f[:-2]) for f in fields])

Obviously this is the appropriate choice since this is a database app.
In general the strip() group of string methods do what you want in a
safe way - assuming you don't care about whitespace:
'test \r\n'

If you are concerned about whitespace:' test '

strips any \n's or \r's from the ends of the line.

This way it doesn't matter what your line endings are - you won't be
surprised by missing characters if the data dump changes for any
reason.

 

[Andrew Sackville-West]

values = ", ".join([escapeAndQuote(f[:-2]) for f in fields])

Obviously this is the appropriate choice since this is a database app.
In general the strip() group of string methods do what you want in a
safe way - assuming you don't care about whitespace:

'test'

perfect!

[...]

This way it doesn't matter what your line endings are - you won't be
surprised by missing characters if the data dump changes for any
reason.

well, no great chance of the data dump changing, but its a good
point.

thanks
A

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFiYZFaIeIEqwil4YRAmzNAJ9oMpauskfPkli0QDZubDfIAgtF7ACeNNzd
g+++qAbfhLSyfwt+suSvc94=
=rBBV
-----END PGP SIGNATURE-----