Converting Perl Web Report to Python

Sun Sep 24 17:05:29 EDT 2006

Dennis,
I was able to execute the Mysql command line code you created in
"script1.py".  I was not able to test the "preferred" method used in
script2.py.  I will do this later this week when I have more time to
download and install the required MySQLdb module.  In any event, I
would like to know how to search and replace a given string in the sql
to a user supplied value before execution.

For example, lets say that your sql script looks like this:
> -=-=-=-=-=-=-=-=-=-		script1.sql
select name, URL, description, occurs
from comics
where URL like '%MyParam_1%'        # Note that "MyParam_1" represents
a "catch" string.
order by name;
> -=-=-=-=-=-=-=-=-=-

Using the script2.py process, how could I safely convert the above
string, "MyParam_1" to a user supplied argument (ie. "comix") before
the SQL is executed?

This capability would allow each sql script to be extendable.  It would
allow me to embed a URL with specific parameters in report OR in Wiki
pages, emails when I need to.
Thanks!
Pat

Dennis Lee Bieber wrote:
> On 23 Sep 2006 06:04:16 -0700, "pmcgover at gmail.com" <pmcgover at gmail.com>
> declaimed the following in comp.lang.python:
>
> 	Answering bottom up...
> >
> > Could this script be easily converted to Python?  How would you execute
> > the Msql command line and direct the output to a variable for display
> > in the cgi script?  Would it be possible to easily enhance this script
> > by allowing the user to pass in an SQL query parameter to the sql
> > script?  I attempted this in Perl by substituting the string "p_1" in
> > the where clause of the sql code but I could not substitute this string
> > with the value in the cgi code (ie. $query =~ s/p_1/value_variable/;).
> > Perhaps it would be easier in Python?
> >
> > Also, would the user supplied parameter be a security issue?
>
> 	Unless you duplicate the type of checking a db-api module does for
> parameterized queries -- indubitably...
>
> 	I don't do PERL, but from what I see, they are essentially doing the
> equivalent of a popen() call.
>
> -=-=-=-=-=-=-=-=-	script1.py
> import os
>
> USERID = "BestiariaCP"  #this account is a read-only, no password
> DBNAME = "bestiaria"    #used for CherryTemplate page generation
>
> #   lacking a CGI interface, I'm just going to put in a few assignments
> query = "script1.sql"
> title = "This is demonstrably nonsense"
>
> cmdline = "mysql -H -u %s %s < %s" % (USERID, DBNAME, query)
>
> #   I don't know what header and start_html() do, so just a dummy here
> print """
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
>
> <html>
> <head>
> 	<title>%s</title>
> </head>
>
> <body>
> """ % title
>
> rpipe = os.popen(cmdline, "r")
> results = rpipe.readlines()
> rpipe.close()
>
> print '<h3 align="center">%s</h3>' % title
> print "".join(results)
> print '<br><h4 align="center">This was a sample</h4>'
> print "</body>\n</html>"
> -=-=-=-=-=-=-=-=-=-		script1.sql
> select name, URL, description, occurs
> from comics
> order by name;
> -=-=-=-=-=-=-=-=-=-		script1.html (excluded middle)
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
>
> <html>
> <head>
> 	<title>This is demonstrably nonsense</title>
> </head>
>
> <body>
>
> <h3 align="center">This is demonstrably nonsense</h3>
> <TABLE
> BORDER=1><TR><TH>name</TH><TH>URL</TH><TH>description</TH><TH>occurs</TH></TR><TR><TD>A
> Doemain of Our Own</TD><TD>http://www.doemain.com/</TD><TD>The new
> family on the block.</TD><TD>Typically Friday, though some Monday and
> Wednesday
> updates</TD></TR><TR><TD>Aford</TD><TD>http://www.afordturtle.com/</TD><TD>A
> turtle, a snake, and a robin; and their life in the
> forest.</TD><TD>Daily</TD></TR>
>
> <TR><TD>Virtual Comix</TD><TD>http://comix.keenspace.com/</TD><TD>He's
> the insane rabbit next door.</TD><TD>No updates since March of
> 2003</TD></TR><TR><TD>West Corner of the
> Park</TD><TD>http://www.graphxpress.com/</TD><TD>Inspired by FurryMUCK,
> and done by Jim Groat, the creator of <i>Red
> Shetland</i>.</TD><TD>Usually late Sunday, unless a Furry Convention
> happens</TD></TR><TR><TD>Whatever
> USA</TD><TD>http://whateverusa.keenspace.com/</TD><TD>A precocious
> porcupine pup and his pals.</TD><TD>Sporadic</TD></TR><TR><TD>Wild
> Angels</TD><TD>http://www.ottercomics.com/angels/</TD><TD>It'll put the
> fur of God in you.</TD><TD>Irregular (between two and seven times each
> month)</TD></TR><TR><TD>Wyldfire</TD><TD>http://www.morgankeithstudios.com/projects_wyldfire.html</TD><TD>What
> if the cat stuck up a tree <i>is</i> the fireman?</TD><TD>Archives
> available</TD></TR></TABLE>
> <br><h4 align="center">This was a sample</h4>
> </body>
> </html>
>
> 	Seems like a lot of hassle to go through when a Python function can
> generate similar results without losing the safety of parameterized
> queries (I do hope the SQL files the CGI is specifying were pre-defined,
> and not something the user uploads <G>)
>
>
> -=-=-=-=-=-=-=-		script2.py
> import MySQLdb
>
> USERID = "BestiariaCP"  #this account is a read-only, no password
> DBNAME = "bestiaria"    #used for CherryTemplate page generation
>
> #   lacking a CGI interface, I'm just going to put in a few assignments
> queryfile = "script1.sql"
> title = "This is demonstrably nonsense"
>
> cn = MySQLdb.connect(host="localhost", user=USERID, db=DBNAME)
> cr = cn.cursor()
>
> #   I don't know what header and start_html() do, so just a dummy here
> print """
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
>
> <html>
> <head>
> 	<title>%s</title>
> </head>
>
> <body>
> """ % title
>
> qin = open(queryfile, "r")
> query = " ".join(qin.readlines())
> qin.close()
>
> cr.execute(query)   #no parameters assumed
>
> #   THE FOLLOWING BLOCK OF CODE COULD EASILY BE MADE A
> #   FUNCTION IN SOME HTML REPORTING MODULE AS IT IS NOT
> #   DEPENDENT UPON THE ACTUAL QUERY TO KNOW WHAT TO PRODUCE
>
> print '<h3 align="center">%s</h3>' % title
> print '<table border="1">\n<tr>',
> for fld in cr.description:
>     print "<th>%s</th>" % fld[0],
> print "</tr>"
>
> for rec in cr:
>     print "<tr>",
>     for fld in rec:
>         print "<td>%s</td>" % fld,
>     print "</tr>"
> print "</table>"
>
> #	BACK TO ORIGINAL CONTENT
>
> print '<br><h4 align="center">This was a sample</h4>'
> print "</body>\n</html>"
>
> cr.close()
> cn.close()
> -=-=-=-=-=-		USES SAME SQL FILE
> -=-=-=-=-=-=-	script2.html (excluded middle)
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
>
> <html>
> <head>
> 	<title>This is demonstrably nonsense</title>
> </head>
>
> <body>
>
> <h3 align="center">This is demonstrably nonsense</h3>
> <table border="1">
> <tr> <th>name</th> <th>URL</th> <th>description</th> <th>occurs</th>
> </tr>
> <tr> <td>A Doemain of Our Own</td> <td>http://www.doemain.com/</td>
> <td>The new family on the block.</td> <td>Typically Friday, though some
> Monday and Wednesday updates</td> </tr>
> <tr> <td>Aford</td> <td>http://www.afordturtle.com/</td> <td>A turtle, a
> snake, and a robin; and their life in the forest.</td> <td>Daily</td>
> </tr>
>
> <tr> <td>Virtual Comix</td> <td>http://comix.keenspace.com/</td>
> <td>He's the insane rabbit next door.</td> <td>No updates since March of
> 2003</td> </tr>
> <tr> <td>West Corner of the Park</td>
> <td>http://www.graphxpress.com/</td> <td>Inspired by FurryMUCK, and done
> by Jim Groat, the creator of <i>Red Shetland</i>.</td> <td>Usually late
> Sunday, unless a Furry Convention happens</td> </tr>
> <tr> <td>Whatever USA</td> <td>http://whateverusa.keenspace.com/</td>
> <td>A precocious porcupine pup and his pals.</td> <td>Sporadic</td>
> </tr>
> <tr> <td>Wild Angels</td> <td>http://www.ottercomics.com/angels/</td>
> <td>It'll put the fur of God in you.</td> <td>Irregular (between two and
> seven times each month)</td> </tr>
> <tr> <td>Wyldfire</td>
> <td>http://www.morgankeithstudios.com/projects_wyldfire.html</td>
> <td>What if the cat stuck up a tree <i>is</i> the fireman?</td>
> <td>Archives available</td> </tr>
> </table>
> <br><h4 align="center">This was a sample</h4>
> </body>
> </html>
> --
> 	Wulfraed	Dennis Lee Bieber		KD6MOG
> 	wlfraed at ix.netcom.com		wulfraed at bestiaria.com
> 		HTTP://wlfraed.home.netcom.com/
> 	(Bestiaria Support Staff:		web-asst at bestiaria.com)
> 		HTTP://www.bestiaria.com/