PDA

View Full Version : Python CGI with MySQL - how



kipb
10-09-2005, 09:49 PM
If you want to use Python as CGI with MySQL, here are a few tricks.

Make sure you have Python and MySQL enabled. (Site Manager under Site Applications)

*Later Note* See next message in thread for better technique using /etc/ld.so.conf

Make the file test.py like these 6 lines:

#!/bin/sh
export LD_LIBRARY_PATH=/ftp/usr/lib:/ftp/usr/lib/mysql
/usr/local/bin/python <<PYTHONEOF
print "content-type: text/html\n\n"
print "--<b>Python</b> program goes here--\n"
PYTHONEOF

Make sure the protection includes execute: chmod 755 test.py

If you have this file in your ~/www/cgi-bin directory, you should be able to run it from the web using http://yourdomain/cgi-bin/test.py

Within the Python program, you can "import MySQLdb" and then something like this function:

def all_usertable():
# Create a connection object and create a cursor
db=MySQLdb.connect(user="youruname",passwd="yourpw",db="yourdb")
Cursor=db.cursor()
sql = """SELECT * FROM usertable"""
Cursor.execute(sql)
# Fetch all results from the cursor into a sequence
Results = Cursor.fetchall()
db.close()
return Results #sequence


You may also wish to "import cgi, Cookie" which lets you get at CGI functions such as

form = cgi.FieldStorage(keep_blank_values=1)
form.getvalue('fieldname','default')


If you're not using MySQL then you can make the script simpler:


#!/usr/local/bin/python
print "content-type: text/html\n\n<h1>Stuff</h1>\n"


The above /bin/sh is just so the environment variable is properly defined so that Python can load the MySQL libraries. This environment variable is defined just fine when you use the shell, but not (by default) when you use cgi (or probably cron or editcron -e).

Good luck!

kipb
10-14-2005, 11:57 AM
One "little" problem with the above -- and a solution:

The CGI module can't read the form values from method=post.
That is, if you have an html form, and you click SUBMIT, and the values are
passed invisibly (not on the URL), then with this technique you can't
retrieve them using the cgi module.

What is needed is LD_LIBRARY_PATH=/ftp/usr/lib:/ftp/usr/lib/mysql
to be defined when Python starts up and then you don't need the
whole #!/bin/sh gimmick, so you get this:
ImportError: libmysqlclient_r.so.10: cannot open shared object file: No such file or directory

So long as you use <form method=get> you're OK.

Here is a fix: edit file /etc/ld.so.conf and add these two lines:
/ftp/usr/lib
/ftp/usr/lib/mysql

and then execute this shell command to re-load the cache: ldconfig

This makes it so you don't need my whole suggestion in the parent message with #!/bin/sh, and everything should work OK with cgi and MySQLdb.