MySQLdb, lots of columns and newb-ness

[Andrew Sackville-West]

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
> 
-------------- 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/090005a3/attachment.sig>