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
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 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
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: automatically grading small programming assignments
On Fri, Dec 15, 2006 at 06:44:37AM +, Dennis Lee Bieber wrote: > On Thu, 14 Dec 2006 12:27:07 -0500, Brian Blais <[EMAIL PROTECTED]> > declaimed the following in gmane.comp.python.general: > > > > I envision a number of possible solutions. In one solution, I provide a > > function > > template with a docstring, and they have to fill it in to past a doctest. > > Is there a > > good (and safe) way to do that online? Something like having a student > > post code, > > and the doctest returns. I'd love to allow them to submit until they get > > it, logging > > each attempt. > > > I have some problems with the concept behind the last sentence... It > encourages brute-force trial&error coding (unless you are going to tell > them that each submittal gets logged, AND that multiple submittals will > reduce the final score they get for the assignment). its been decades since I was in a programming course... salt accordingly. Whenever I learn a new language, I spend a LOT of time just hacking stuff and seeing what it does -- learning syntax and effects by trial and error. Since I already know (okay, knew) good coding practice, the resulting code would not look like it had been hacked together in such a manner, but if I was graded on how many times I executed a bit of code, I'd fail right out. Now, maybe in the second or third semester of a particular language, that might make sense -- the student should already understand syntax and effects well enough to avoid that stuff. .02 from a python newb. A signature.asc Description: Digital signature -- http://mail.python.org/mailman/listinfo/python-list
Re: speed of python vs matlab.
On Wed, Dec 13, 2006 at 04:07:20PM -0800, Chao wrote: > I've been trying to develop some numerical codes with python, however > got disappointed. > > A very simple test, > > a = 1.0 > > for i in range(1000): > for j in range(1000): >a = a+1 > > unfortunately, it took 4.5 seconds to finish(my machines is fine. P4 > 3.0G, 1G RAM, it varies according to machine configuration, but should > be in the same level) somethings not right there. [EMAIL PROTECTED]:~$ cat pytimetest.py a=1.0 for i in range (1000): for j in range (1000): a=a+1 [EMAIL PROTECTED]:~$ time python pytimetest.py real0m0.534s user0m0.528s sys 0m0.000s [EMAIL PROTECTED]:~$ cat /proc/cpuinfo | grep name model name : Intel(R) Celeron(R) CPU 2.53GHz [EMAIL PROTECTED]:~$ uname -a Linux debian 2.6.18-3-686 #1 SMP Mon Dec 4 16:41:14 UTC 2006 i686 GNU/Linux A signature.asc Description: Digital signature -- http://mail.python.org/mailman/listinfo/python-list
Re: ooopy: newbie cannot get basic functionality to work
On Sat, Dec 09, 2006 at 09:30:32PM -0800, John Machin wrote: > > Andrew Sackville-West wrote: > > > > >>> o = OOoPy (infile='/home/andrew/monthly.ods') > > Traceback (most recent call last): > > File "", line 1, in ? > > TypeError: 'module' object is not callable > > >>> > > OK, so that tells you that ooopy.OOoPy is a *module*, and it contains > *another* gizmoid named OOoPy. ah ha! I knew it was something fundamental. its those technical terms that throw me ;-) (gizmoid). > > [snip] > > >>> sys.modules > [snip] > > 'ooopy.OOoPy': > '/usr/lib/python2.4/ooopy/OOoPy.py'>, > > Uh-huh. Confirmation that ooopy.OOoPy is a module. > > Try: > > from ooopy import OOoPy > o = OOoPy.OOoPy(infile='/home/andrew/monthly.ods') > > BTW, is that package being maintained? Did you ask the maintainer? Any > response? > I wondered about that when I was looking at it. It was last updated about a year ago which isn't bad as far as some projects go... heh. No I didn't go after the maintainer. I'm also looking at maybe using ElementTree directly as ooopy is a pretty thin wrapper anyway. anyway, thanks for the help. I can now do fun things like for n in o.getiterator(): and so forth. A signature.asc Description: Digital signature -- http://mail.python.org/mailman/listinfo/python-list
Re: ooopy: newbie cannot get basic functionality to work
On Sat, Dec 09, 2006 at 09:42:40PM -0600, Paul Watson wrote: > Andrew Sackville-West wrote: > > Hi list, > > > > I am new to python and old to coding (as in I did it a long time > > ago). I've got a task that cries out for a scripted solution -- > > importing chunks of ASCII data dumps from a point-of-sale system into > > an openoffice.org spreadsheet. What a great chance for me to get my > > coding skills back and learn python too! > > The openoffice.org spreadsheet tool can read a CSV file. How about > producing that first, just so you have something that works. I agree, > this sounds like a nice way to dive into Python, but you might want > something that works first, even if it is not elegant. absolutely a good idea. I'm just thinking back to when I automated this under windows/quattro pro/whatever-corel's-script is and wanted to return those blissful days. But I suppose, modularising the thing properly, I could but whatever "backend" into it I want. (seems funny to use the idea of a backend with something so trivial. And I still want to get ooopy working. I feel there is something simple and fundamental I am missing here in getting this working at the most basic level. thanks A signature.asc Description: Digital signature -- http://mail.python.org/mailman/listinfo/python-list
ooopy: newbie cannot get basic functionality to work
Hi list,
I am new to python and old to coding (as in I did it a long time
ago). I've got a task that cries out for a scripted solution --
importing chunks of ASCII data dumps from a point-of-sale system into
an openoffice.org spreadsheet. What a great chance for me to get my
coding skills back and learn python too!
I have attempted to get ooopy to work for me and have run into
problems at the first step. Google returns not much on ooopy and the
common example, from help (OOoPy) is:
| from OOoPy import OOoPy
| >>> o = OOoPy (infile = 'test.sxw', outfile = 'out.sxw')
| >>> e = o.read ('content.xml')
| >>> e.write ()
| >>> o.close ()
okay here goes:
>>> from OOoPy import OOoPy
Traceback (most recent call last):
File "", line 1, in ?
ImportError: No module named OOoPy
hmm... okay how about:
>>> from ooopy import OOoPy
>>> dir (OOoPy)
['ElementTree', 'OOoElementTree', 'OOoPy', 'StringIO', 'VERSION',
'ZIP_DEFLATED', 'ZipFile', '__builtins__', '__doc__', '__file__',
'__name__', '_autosuper', 'autosuper', 'fromstring', 'mkstemp', 'os']
okay that works. now:
>>> o = OOoPy (infile='/home/andrew/monthly.ods')
Traceback (most recent call last):
File "", line 1, in ?
TypeError: 'module' object is not callable
>>>
that's not good. at this point, I'm stuck. I've tried various
permutations of this with no other results. It also fails on the
sample file provided with ooopy, test.sxw.
I have seen one solution using zip to get the contents of the oo.o
file which would probably work but would like to figure this out.
I have installed ooopy using setup.py provided with the package on
debian sid, up-to-date. And I've tried it using python 2.3 and 2.4.
any help appreciated. thanks
A
>>>import sys
>>> sys.path
['', '/usr/lib/python24.zip', '/usr/lib/python2.4',
'/usr/lib/python2.4/plat-linux2', '/usr/lib/python2.4/lib-tk',
'/usr/lib/python2.4/lib-dynload',
'/usr/local/lib/python2.4/site-packages',
'/usr/lib/python2.4/site-packages',
'/usr/lib/python2.4/site-packages/PIL',
'/var/lib/python-support/python2.4']
>>> sys.modules
{'tokenize': , 'copy_reg': , 'sre_compile': ,
'ooopy.StringIO': None, '_sre': ,
'__main__': , 'ooopy.os': None, 'site':
, '__builtin__':
, 'elementtree.ElementTree': ,
'encodings': , 'array': , 'pydoc': , 'posixpath': , '_random':
,
'imp': , 'tempfile': , 'errno': , 'binascii': , 'encodings.codecs':
None, 'sre_constants': , 're': , 'encodings.latin_1': ,
'collections': , 'os.path': , '_codecs':
, 'opcode': , 'encodings.exceptions': None, 'sre':
, 'math': , 'fcntl':
,
'stat': ,
'zipimport': , 'string': , 'inspect': , 'warnings': , 'encodings.types':
None, 'UserDict': , 'ooopy.Version': ,
'zipfile': ,
'repr': , 'sys':
, 'ooopy.elementtree': None,
'ooopy.tempfile': None, 'itertools': , 'codecs': , 'readline': ,
'elementtree': ,
'ooopy.OOoPy': , 'types': , 'struct': , 'elementtree.string':
None, 'thread': , 'StringIO': , 'ooopy.zipfile':
None, 'strop': , 'signal': , 'zlib': , 'elementtree.ElementPath':
,
'random':
signature.asc
Description: Digital signature
--
http://mail.python.org/mailman/listinfo/python-list
