Question Authors' Forum

Python3 Database programming?

Python3 Database programming?

by Johan van Niekerk -
Number of replies: 1

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.