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?
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.