Apr-05-2019, 01:22 AM
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:
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:
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>