Re: MySQLdb, lots of columns and newb-ness
Andrew Sackville-West wrote: 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. on the other hand, hackers just *love* people who think they're too clever to do things in a safe and robust way: http://en.wikipedia.org/wiki/SQL_injection using parameterized inserts also speeds things up for many databases, since the database engine don't have to parse and and analyze the sql statement over and over and over again. to quickly generate the parameter list, use string repeat to create the parameter list: params = ( + ,.join([%s]*len(values)) + ) cursor.execute(insert into daily values + params, values) you probably want to do some normalization work on your database too, but that's another story. /F -- http://mail.python.org/mailman/listinfo/python-list
Re: MySQLdb, lots of columns and newb-ness
On Dec 20, 5:20 am, Andrew Sackville-West [EMAIL PROTECTED] 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: MySQLdb, lots of columns and newb-ness
On Wed, Dec 20, 2006 at 07:00:38AM -0800, Ant wrote: On Dec 20, 5:20 am, Andrew Sackville-West [EMAIL PROTECTED] 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 signature.asc Description: Digital signature -- http://mail.python.org/mailman/listinfo/python-list
Re: MySQLdb, lots of columns and newb-ness
On Wed, Dec 20, 2006 at 09:22:59AM +0100, Fredrik Lundh wrote: Andrew Sackville-West wrote: 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. on the other hand, hackers just *love* people who think they're too clever to do things in a safe and robust way: http://en.wikipedia.org/wiki/SQL_injection good point. when I go for world domination and put this thing on the web, I'll watch for that ;-). seriously though, this is merely an internal operation in my one man show where I can more easily access historical sales data. using parameterized inserts also speeds things up for many databases, since the database engine don't have to parse and and analyze the sql statement over and over and over again. to quickly generate the parameter list, use string repeat to create the parameter list: params = ( + ,.join([%s]*len(values)) + ) cursor.execute(insert into daily values + params, values) okay. this is great. thanks! you probably want to do some normalization work on your database too, but that's another story. indeed. there is definitely some duplicated data, or rather derived data (if that's the right term), but not a whole lot. I suppose I will get to that sooner or later. first thing, for me, is to get the data into something more useable than a bunch of flat files on another machine on my lan. thanks a bunch for your pointers. A signature.asc Description: Digital signature -- http://mail.python.org/mailman/listinfo/python-list
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
signature.asc
Description: Digital signature
--
http://mail.python.org/mailman/listinfo/python-list
Re: MySQLdb, lots of columns and newb-ness
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: MySQLdb, lots of columns and newb-ness
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 signature.asc Description: Digital signature -- http://mail.python.org/mailman/listinfo/python-list
Re: MySQLdb, lots of columns and newb-ness
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. -- http://mail.python.org/mailman/listinfo/python-list
