Python Forum
[Tkinter] TKINTER quiz using sqlite3 database
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[Tkinter] TKINTER quiz using sqlite3 database
#11
if you create a post using New Reply, or edit an existing post (good for this one). You will see a
New Attachment: browse... button at bottom of screen. You can use this to button to navigate to the file, then click add attachment
next set cursor to bottom of your post and click insert into post (may not be exact title) and save edit.
The attachment should show in post.
This doesn't work with quick edit, must be full edit
Reply
#12
Heres the File

Attached Files

.txt   questions.txt (Size: 3.21 KB / Downloads: 192)
Reply
#13
The file you attached is not an sqlite3 database, it's a text file.
So you need to first create the database we normally don't do this but I'll write a quick script to create and load a database.
Back when done.
Reply
#14
This would be better if you use a spreadsheet to enter your questions, choices and answer.
  • This can be improved by adding a code indication content before each line, example:
  • Q - Question
  • C - Choice
  • A - Correct Answer
If you like this idea, It's a simple matter to export as a csv file, and import directly into sqlite3 using Sqliteman or similar product.
Reply
#15
Hi, this would be amaxing if you could help me with this that would be great
Reply
#16
Ok. here's the new file format, and a sample imported into open office calc (you can use excel if on windows)
And a sample csv file

database creator soon

.csv   Quiz.csv (Size: 3.79 KB / Downloads: 219)
   
Reply
#17
Hi thanks for your help so far, are you able to help ajust my program to access this spreadsheet using sqlite3?
Reply
#18
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>
Reply
#19
here's the working database creation software.
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', 'Unused']

        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', self.question_fields, row)
                elif row[1] == 'C':
                    self.insert_data('Choices', self.choice_fields, row)
                elif row[1] == 'A':
                    self.insert_data('Answers', self.answer_fields, row)
                else:
                    print(f'row has invalid QuizCode: {row}')

    def insert_data(self, tablename, fields, row):
        sqlstr = None
        qmarks = (f"?, " * len(row))[:-2]
        dbcolumns = '('
        for item in row:
            dbcolumns = f"{dbcolumns}'{item}', "
        dbcolumns = f"{dbcolumns[:-2]})"
        xfields = ', '.join(fields)
        sqlstr = f''' INSERT INTO {tablename} ({(xfields)}) VALUES ({qmarks}) '''
        cur = self.dbcon.cursor()
        cur.execute(sqlstr, row)

    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')
run it using Quiz.csv which was an attachment on previous post, and it will create the database in a directory named 'QuestionsDatabase' immediately below script directory.
Will take a look at reading it into your GUI on my AM around 10:00 EST
Reply
#20
Hi thanks for this I will run your program thank you so much for your help so far
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  PyQt5 form not displaying my data from SQLite3 Database Linuxdesire 2 4,927 Jan-10-2023, 09:51 PM
Last Post: gradlon93
  Can't get tkinter database aware cascading comboboxes to update properly dford 6 3,535 Jan-11-2022, 08:37 PM
Last Post: deanhystad
Question [Tkinter] data enterred through gui is not storing in sqlite3 database Hilal 21 7,313 Dec-15-2021, 08:48 PM
Last Post: Hilal
  Help with PySimpleGUI INSERT INTO sqlite3 database jrbond 5 7,063 Jul-20-2020, 01:24 PM
Last Post: jrbond
  sQlite3 output to tkinter treeview - how do I set / increase width of the output? dewijones67 5 6,573 Jan-23-2019, 08:45 AM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020