Question Authors' Forum

Python3 Database programming?

Python3 Database programming?

by Johan van Niekerk -
Number of replies: 6

Hi,

I'm teaching a basic database programming course and want my students to use python3 to interact with a SQLite database.

I attach the DB as a support file (similar to how I would create an SQL question). However, if I try to run anything other than SELECT queries I get an error stating that I am trying to write to a read-only database. 


For example (this code works):

import sqlite3

conn = sqlite3.connect('SimpleBooks.db')

cursor = conn.cursor()

for row in cursor.execute('''SELECT * FROM Authors;'''):

    print(row)

cursor.close()


This does not work

import sqlite3

conn = sqlite3.connect('SimpleBooks.db')

cursor = conn.cursor()

cursor.execute('''INSERT INTO Authors (Name, Surname) VALUES ('Stephen','King') ;''')

cursor.close()


Any advice on how I can get this database to behave the same way one in an SQL question type would behave?


In reply to Johan van Niekerk

Re: Python3 Database programming?

by Richard Lobb -

Support files supplied by the question author are always write-locked to prevent inadvertent or deliberate corruption by the student. The solution is to make a copy of the database file at the start of each test; the student code should run using that copy, not the original. You need to make a fresh copy for each test to avoid interactions between the tests. 

You may find it helpful to look at the sql question template, which you can see by creating a question of type sql and clicking the Customise checkbox. The test loop begins

{% for TEST in TESTCASES %}
shutil.copyfile(db_files[0], db_working)  # Copy clean writeable db file

The best approach longer term is to create yourself a new question type but for a start you could experiment by using the standard python3 question type and customising each question, inserting something like the above file-copy line into the test loop.

In reply to Richard Lobb

Re: Python3 Database programming?

by Johan van Niekerk -
Hi Richard,

I'm back to the same issue (I ran out of time previously and never got back to fixing the problem). Do you have a suggestion on how I would now ensure that the python connection uses the copied database? Basically, what should I call the file in the following line to ensure the rest of the code still works?

conn = sqlite3.connect('SimpleBooks.db')

I tried

conn = sqlite3.connect(db_working)

Any advice would be much appreciated.
In reply to Johan van Niekerk

Re: Python3 Database programming?

by Richard Lobb -
I'm not quite sure of what else you've done. If you've customised the template to include an shutil line that copies the file, then whatever name you gave the file copy in the shutil, should work just fine in the test.

Could you export your question with the edited template and attach it to your reply, please?
In reply to Richard Lobb

Re: Python3 Database programming?

by Johan van Niekerk -
Thanks for the help :)

I've attached an example question. I had turn off validate on save to export in the state where I get the error. I also tried running it using db_working as the database name in the connection.
In reply to Johan van Niekerk

Re: Python3 Database programming?

by Matthew Toohey -
Hi Johan

I think you may have taken more than you needed from the sql template. To get you question working we just need to make the .db file writable. To do this we can copy it to a new file. I renamed the support file to '_BookCollection.db' then in the customise field I add the following lines at the top to copy it to a fresh file.
import shutil
shutil.copyfile('_BookCollection.db', 'BookCollection.db')
This copies the support file to a new file with the name the student is expected to use. Because it is a new file it has the appropriate permissions needed to write to the file.

I have attached an export of this question that works for me.

A more general solution, that would work for any filename, might be to loop over all files with the extension .db (maybe starting with an underscore) and copy them to new files.

Hope that helps,
Matthew