I have been having major issues with getting SQLite to work from within my CGI scripts. I am able to read from the database perfectly fine, but I cannot write to the database from within the CGI environment. When I am outside of the CGI execution environment it works fine. I have tried everything I and many others can think of. I even set up a fresh Fedora vm just to test this out and it exhibited the same problems. Here is a test case of my issue. The database in question can be created with “CREATE TABLE test (x);” entered into the command “sqlite3 /tmp/db.sqlite”
#!/usr/bin/env pythonimport sqlite3import cgiimport osprint "Content-type: text/html\n"try: con = sqlite3.connect('/tmp/db.sqlite') cur = con.cursor() cur.execute("select * from test;") for line in cur: print line cur.execute("""INSERT INTO test VALUES(1);""") con.commit() con.close()except: cgi.print_exception()
Either way, I have decided to focus my efforts on working with Postgres. I have done this because I have spent hours and hours trying to get this to work with no progress. I just get an “OperationalError: unable to open database” message. I feel that in the long term Postgres will be the better solution. Each time unit tests are run for a build on the build bot cluster there will be approximately 40-60 thousand tests that will need to be inserted.
My next challenge was to get Postgres working on my Macbook. I have been working on my Macbook because it makes my workflow very simple. I just save my python scripts and they are live in my CGI environment. To install Postgres I had a choice of using macports or the semi-official EnterpriseDB installer. I ended up going with the EnterpriseDB version for no particular reason. The next step in converting to Postgres was to install a Postgres driver for python. I am using the system default python installation located at /Library/Python/2.5. This means that when I try to install python modules, I cannot use macports as it uses its own python installation in /opt/local. This has worked really well for me so far, but while trying to install the database driver I kept getting a file not found error. This was a very cryptic and highly annoying error. Turns out that the problem I was having was that the driver, called psycopg2, uses a C extension to interface with Postgres. This C extension requires that the ‘pg_config’ executable be in the PATH variable during build time. I solved this by changing the pg_config attribute in setup.cfg for psycopg2 to pg_config=/Library/PostgreSQL/8.3/bin/pg_config. I am assuming that I could also accomplish this by adding my Postgres installation’s bin directory to my path.
Once I had this, I checked that I had a working module by launching the interactive python interpreter and running import psycopg2. It didn’t blow up and that makes me happy!
Here is a sample of code for Postgres which works in the CGI environment:
#!/usr/bin/env pythonimport psycopg2import cgi#import osprint "Content-type: text/html\n"print "Hello"try: con = psycopg2.connect("dbname='logfribulator' user='logfribulator' password='mozilla'") cur = con.cursor() try: cur.execute("""INSERT INTO log (build_number, start_time, end_time, builder, factory, slave, machine_name, log_url)VALUES(1,timestamp '2001-09-28 01:00',timestamp '2001-09-28 01:00','builder', 'factory', 'slave', 'machine', 'url4');""") except psycopg2.IntegrityError: con.rollback() else: con.commit() cur.execute('select * from log;') for line in cur: print line con.close()except: cgi.print_exception()
This script will only do the insert if there is no issue. If there is an issue only the select query will be done.