469,363 Members | 2,604 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,363 developers. It's quick & easy.

MySQLdb, lots of columns and newb-ness

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-----

Dec 20 '06 #1
5 1171
Andrew Sackville-West wrote:
>
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

Dec 20 '06 #2
On Tue, Dec 19, 2006 at 07:34:58PM -0800, Todd Neal wrote:
Andrew Sackville-West wrote:

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:
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

--
http://mail.python.org/mailman/listinfo/python-list
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

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

Dec 20 '06 #3
Andrew Sackville-West schrieb:
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.
Dec 20 '06 #4
Ant


On Dec 20, 5:20 am, Andrew Sackville-West <and...@farwestbilliards.com>
wrote:
>>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:
>>s = " test \r\n"
s.strip()
'test'
>>s.rstrip()
' test'
>>s.lstrip()
'test \r\n'

If you are concerned about whitespace:
>>s.strip("\n\r")
' 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.

Dec 20 '06 #5
On Wed, Dec 20, 2006 at 07:00:38AM -0800, Ant wrote:


On Dec 20, 5:20 am, Andrew Sackville-West <and...@farwestbilliards.com>
wrote:
>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:
>s = " test \r\n"
s.strip()
'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)

iD8DBQFFiYZFaIeIEqwil4YRAmzNAJ9oMpauskfPkli0QDZubD fIAgtF7ACeNNzd
g+++qAbfhLSyfwt+suSvc94=
=rBBV
-----END PGP SIGNATURE-----

Dec 20 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Dave Harrison | last post: by
1 post views Thread by Peter Nikolaidis | last post: by
2 posts views Thread by m0226065 | last post: by
1 post views Thread by Achim Domma (Procoders) | last post: by
2 posts views Thread by yaffa | last post: by
1 post views Thread by Steve | last post: by
1 post views Thread by Yi Xing | last post: by
2 posts views Thread by Nikhil | last post: by
reply views Thread by Edwin.Madari | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.