first need to create a database. what you supplied was a text file, not a database.
I said I would write (stupid me) a program to create the database from a .csv file.
if a quiz is entered into an excel spread sheet as displayed, and then exported as a .csv file,
the following script will convert into an sqlite3 database with three tables:
There's still one bug to fix:
Error:
Traceback (most recent call last):
File ".../Projects/T-Z/T/TryStuff/src/Hezza23/CreateQuestionsDb.py", line 104, in <module>
CreateQuestionsDb(dbname='Questions.db', inputfilename='Quiz.csv')
File ".../Projects/T-Z/T/TryStuff/src/Hezza23/CreateQuestionsDb.py", line 26, in __init__
self.make_questions_database()
File ".../Projects/T-Z/T/TryStuff/src/Hezza23/CreateQuestionsDb.py", line 31, in make_questions_database
self.prepair_data()
File ".../Projects/T-Z/T/TryStuff/src/Hezza23/CreateQuestionsDb.py", line 41, in prepair_data
self.insert_data('Questions', row)
File ".../Projects/T-Z/T/TryStuff/src/Hezza23/CreateQuestionsDb.py", line 60, in insert_data
self.dbcur.execute(sqlstr)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and there are 0 supplied.
The query causing the error (in sqlstr) is:
Output:
sqlstr: INSERT INTO Questions VALUES (?, ?, ?, ?), ('1', 'Q', '', 'Which Of these describes an element best?');
import sqlite3
from pathlib import Path
import os
import csv
import sys
class CreateQuestionsDb:
def __init__(self, dbname, inputfilename):
# create directory anchor
os.chdir(os.path.abspath(os.path.dirname(__file__)))
homepath = Path('.')
datadir = homepath / 'QuestionsDatabase'
datadir.mkdir(exist_ok = True)
self.database = datadir / dbname
self.datafile = datadir / inputfilename
self.sqlstr = None
self.dbcon = None
self.dbcur = None
self.question_fields = ['QuestionId', 'QuizCode', 'unused', 'Question']
self.choice_fields = ['QuestionId', 'QuizCode', 'SeqLetter', 'Choice']
self.answer_fields = ['QuestionId', 'QuizCode', 'Answer']
self.make_questions_database()
def make_questions_database(self):
self.db_connect()
self.create_tables()
self.prepair_data()
self.db_close()
def prepair_data(self):
with self.datafile.open() as fp:
crdr = csv.reader(fp, delimiter=',')
next(crdr)
for row in crdr:
print(f'row[1]: {row[1]}')
if row[1] == 'Q':
self.insert_data('Questions', row)
elif row[1] == 'C':
self.insert_data('Choices', row)
elif row[1] == 'A':
self.insert_data('self.answer_fields', row)
else:
print(f'row has invalid QuizCode: {row}')
def insert_data(self, tablename, row):
sqlstr = None
qmarks = (f"?, " * len(row))[:-2]
dbcolumns = '('
for item in row:
dbcolumns = f"{dbcolumns}'{item}', "
dbcolumns = f"{dbcolumns[:-2]})"
# print(f'\ntablename: {tablename}, qmarks: {qmarks}, dbcolumns: {dbcolumns}')
sqlstr = f'INSERT INTO {tablename} VALUES ({qmarks}), {dbcolumns};'
print(f'sqlstr: {sqlstr}')
# print(f'type: {type(sqlstr)}, self.sqlstr: {sqlstr}')
self.dbcur.execute(sqlstr)
def db_connect(self):
try:
self.dbcon = sqlite3.connect(self.database)
self.dbcur = self.dbcon.cursor()
except sqlite3.Error as e:
print(e)
def drop_table(self, tablename):
self.sqlstr = f'DROP TABLE IF EXISTS {tablename};'
def add_table(self, tablename, column_list):
columns = ', '.join(column_list)
self.sqlstr = f'CREATE TABLE IF NOT EXISTS {tablename} ({columns});'
self.dbcur.execute(self.sqlstr)
def create_tables(self):
tablename = 'Questions'
self.drop_table(tablename)
self.add_table(tablename, self.question_fields)
tablename = 'Choices'
self.drop_table(tablename)
self.add_table(tablename, self.choice_fields)
tablename = 'Answers'
self.drop_table(tablename)
self.add_table(tablename, self.answer_fields)
self.db_commit()
def db_close(self, rollback=False):
if rollback:
self.dbcon.rollback()
else:
self.dbcon.commit()
self.dbcon.close()
def db_commit(self):
self.dbcon.commit()
if __name__ == '__main__':
CreateQuestionsDb(dbname='Questions.db', inputfilename='Quiz.csv')
Once you have a database, then it can be added to your script.
This is close, perhaps someone will take a look at it while I have my supper, I've seen this error before, but solution escapes me at the moment. I'll be back in a few hours.
getting it into your script should be simple, but can't do it until the database exists.
Database schema looks like this:
Output:
sqlite> .schema
CREATE TABLE Questions (QuestionId, QuizCode, unused, Question);
CREATE TABLE Choices (QuestionId, QuizCode, SeqLetter, Choice);
CREATE TABLE Answers (QuestionId, QuizCode, Answer);
sqlite>