SQLite Insert command in Python script Doesn't work on web

Question

I'm trying to use an SQLite insert operation in a python script, it works when I execute it manually on the command line but when I try to access it on the web it won't insert it in the database. Here is my function:

def insertdb(unique_id,number_of_days):
        conn = sqlite3.connect('database.db')
        print "Opened database successfully";
        conn.execute("INSERT INTO IDENT (ID_NUM,DAYS_LEFT) VALUES (?,?)",(unique_id,number_of_days));
        conn.commit()
        print "Records created successfully";
        conn.close()

When it is executed on the web, it only shows the output "Opened database successfully" but does not seem to insert the value into the database. What am I missing? Is this a server configuration issue? I have checked the database permissions on writing and they are correctly set.

Answer 1

The problem is almost certainly that you're trying to create or open a database named database.db in whatever happens to be the current working directory, and one of the following is true:

• The database exists and you don't have permission to write to it. So, everything works until you try to do something that requires write access (like commiting an INSERT).
• The database exists, and you have permission to write to it, but you don't have permission to create new files in the directory. So, everything works until sqlite needs to create a temporary file (which it almost always will for execute-ing an INSERT).

Meanwhile, you don't mention what web server/container/etc. you're using, but apparently you have it configured to just swallow all errors silently, which is a really, really bad idea for any debugging. Configure it to report the errors in some way. Otherwise, you will never figure out what's going on with anything that goes wrong.

If you don't have control over the server configuration, you can at least wrap all your code in a try/except and manually log exceptions to some file you have write access to (ideally via the logging module, or just open and write if worst comes to worst).

Or, you can just do that with dumb print statements, as you're already doing:

def insertdb(unique_id,number_of_days):
        conn = sqlite3.connect('database.db')
        print "Opened database successfully";
        try:
            conn.execute("INSERT INTO IDENT (ID_NUM,DAYS_LEFT) VALUES (?,?)",(unique_id,number_of_days));
            conn.commit()
            print "Records created successfully";
        except Exception as e:
            print e # or, better, traceback.print_exc()
        conn.close()