sqlite3 db and table design

[pythonNoob]

I currently use an Excel sheet to run a library inside of a prison but I think I'd like a better tool for the job so I'm trying to create it. My current plan is to write the program as a command line only program then come back later and write a GUI for it using Tk.
I'v pasted below the table layout I think I need but I'm having trouble figuring out how to link the Books table and the Authors Table considering that each book can have more than one author and each author can write more than one book. Any pointers here?
Also, I'm not a skilled programmer or database admin by any stretch, so even a few examples on how to create my tables with primary and foreign keys would be much appreciated. Thanks.

 def build_db(self): conn = sqlite3.connect(r"C:\\Users\\bi18avi\\librarydb\\booksdb") c = conn.cursor() c.execute('''CREATE TABLE Books (book_id INT, author_id INT, book_title TEXT, genre TEXT, type TEXT, cover INT, price REAL)''') conn.commit()

DATABASE
Books

*book_id(PK) unique numeric id for each book, exampe, 0001, 0002, 0048, etc...
*author_id(FK -> Authors) unique id for each author
*book_titletitle of the book
*genregenre of the book
*typehardback, softback, or paperback
*coverY, N, S, or -
*priceprice of the book
*countholds the number of times the book and been checked out, increments on each checkout

Authors

*author_id(PK) unique id for each author
*first_namefirst name of author, (note that books can have more than one author)
*last_namelast name of author, (note that books can have more than one author)

Checkout

*book_id (FK -> Books) unique numeric id for each book, exampe, 0001, 0002, 0048, etc...
*inmate_numberinmates 8 digit prison number (00123456)
*checkout_datedate book was checked out
*due_date due date, number of days from checkout, can be hard coded or read from a config file


Charged

*book_id the id of the book when it was charged, not a FK as the IDs will be reused after a set period of time
*checkout_date date of checkout not a FK
*due_date due date, not a FK
*charge_datedate charge was entered
*inmate_numberinmates 8 digit prison number (00123456) not a FK

Discarded

*book_id hold the ID's of the books I have retired for wear and tear, IDs can be reused immediately, not a FK
*book_title title of book, not a FK
*date date book was discarded

VisitLog

*inmate_numberinmates 8 digit prison number (00123456), not a FK, entered at time of checkout from the checkout table
*date date of visit, entered at time of check from the checkout table

MainDisplay
(This is the main screen I would be looking at when opening up the program, the sheet you would see if all this was being done from Excel, I think this table should run in memory)

*book_id(FK -> Books) unique numeric id for each book, exampe, 0001, 0002, 0048, etc...
*first_name first name of author (read from authors table)
*last_name last name of author (read from authors table)
*book_titletitle of book (read from Books table)
*genregenre of book (read from Books table)
*coverY, N, S, or - (read from Books table)
*priceprice of book (read from Books table)
*inmate_numberinmate_number (read from checkout table)
*checkout_datedate book was checked out (read from checkout table)
*due_datedue date (read from checkout table)

[woooee]

out how to link the Books table and the Authors Table

I am assuming that each Book ID, et al, is unique, so you would include that in every table and can then reference Book to Author(s) by the Author ID in the Book table. If an Author has written more than one book, there will be more than one Book entry, each with the same Author ID. Searching for the Author ID will give you a list of books by that Author. I have some canned SQLite code and will try to work up some examples this evening.

[woooee]

This is admittedly a hack and is code generated when I want a quick and dirty SQL layout, but will give you a basic idea, and this is all the time I have for now. When using a GUI, I almost always start with the GUI because it is event driven. I will try to work up a better, basic example with GUI for books and authors tomorrow afternoon and evening.

import os import sqlite3 as sqlite import test_tkinter ##====================================================================== class Books: def __init__(self) : self.SQL_filename = './Test_Books' self.open_files() ## END __init__() ##---------------------------------------------------------------------- def add_book_rec( self ) : val_tuple=(self.book_id, self.author_id, self.book_title, self.genre, self.cover_type, self.cover, self.price, self.ct) self.cur.execute('INSERT INTO Books values (?,?,?,?,?,?,?,?)', val_tuple) self.con.commit() ## END add_book_rec() ##---------------------------------------------------------------------- def copy_to_struct( self, rec ) : self.book_id = rec[0] self.author_id = rec[1] self.book_title = rec[2] self.genre = rec[3] self.cover_type = rec[4] self.cover = rec[5] self.price = rec[6] self.ct = rec[7] ## END copy_to_struct() ##---------------------------------------------------------------------- def del_rec( self, value_to_delete ) : self.cur.execute("DELETE FROM Books WHERE SQL_field=:name_dict", {"name_dict":value_to_delete}) ## END del_rec() ##---------------------------------------------------------------------- def list_all_recs( self ) : self.cur.execute("select * from Books") recs_list = self.cur.fetchall() for rec in recs_list: print(rec) ## END list_all_recs ##---------------------------------------------------------------------- def lookup_first_field( self ) : self.cur.execute("select * from Books where book_id==:dic_lookup", {"dic_lookup":"test_A_0"}) recs_list = self.cur.fetchall() print("\nlookup_first_field") if len(recs_list): for rec in recs_list: self.copy_to_struct(rec) self.print_rec() else: print("No records found") ## END lookup_first_field() ##---------------------------------------------------------------------- def lookup_first_2_fields( self, lookup_dic ) : self.cur.execute("select * from Books where book_id==:dic_field_1 and author_id==:dic_field_2", lookup_dic) recs_list = self.cur.fetchall() print("\nlookup_first_2_fields") if len(recs_list): for rec in recs_list: self.copy_to_struct(rec) self.print_rec() else: print("No records found") ## END lookup_first_2_field() ##---------------------------------------------------------------------- def open_files( self ) : ## a connection to the database file self.con = sqlite.connect(self.SQL_filename) # Get a Cursor object that operates in the context of Connection con self.cur = self.con.cursor() ##--- CREATE FILE ONLY IF IT DOESN'T EXIST self.cur.execute('''CREATE TABLE IF NOT EXISTS Books(book_id int, author_id int, book_title varchar, genre varchar, cover_type varchar, cover varchar, price real, ct int)''') self.cur.execute('''CREATE TABLE IF NOT EXISTS Authors(author_id int, first_name varchar, last_name var_char)''') ## END open_files() ##---------------------------------------------------------------------- def print_rec( self ) : spaces = "" print(spaces, "book_id =", self.book_id) spaces = " " print(spaces, "author_id =", self.author_id) print(spaces, "book_title =", self.book_title) print(spaces, "genre =", self.genre) print(spaces, "cover_type =", self.cover_type) print(spaces, "cover =", self.cover) print(spaces, "price =", self.price) print(spaces, "count =", self.ct) ## END rec_struct() ##---------------------------------------------------------------------- def rec_struct( self ) : self.book_id = "" self.author_id = "" self.book_title = "" self.genre = "" self.cover_type = "" self.cover = "" self.price = "" self.ct = "" ## END rec_struct() ##====================================================================== class Authors: """ same as books only with Authors Will do add a rec and lookup author only """ def __init__(self, cur, con): self.cur=cur self.con=con ## END __init__() ##---------------------------------------------------------------------- def add_author_rec(self, author_id, first_name, last_name ) : val_tuple=(author_id, first_name, last_name) self.cur.execute('INSERT INTO Authors values (?,?,?)', val_tuple) self.con.commit() ## END add_book_rec() ##---------------------------------------------------------------------- def list_all_recs( self ) : self.cur.execute("select * from Authors") recs_list = self.cur.fetchall() for rec in recs_list: print(rec) ## END list_all_recs ##---------------------------------------------------------------------- def lookup_first_field(self, author ) : self.cur.execute("select * from Authors where author_id==:dic_lookup", {"dic_lookup":author}) recs_list = self.cur.fetchall() print("\nlookup_first_field") if len(recs_list): for rec in recs_list: self.print_rec() else: print("No records found") ## END lookup_first_field() ##====================================================== ##====================================================== if __name__ == '__main__': try : DM=Books() A=Authors(DM.cur, DM.con) A.add_author_rec(2, "Joe", "Smith") A.add_author_rec(3, "Ray", "Jones") A.list_all_recs() DM.book_id = 102 DM.author_id = 2 DM.book_title = "Title 1" DM.genre = "Sports" DM.cover_type = "Cloth" DM.cover = "S" DM.price = "25.99" DM.ct = "1" DM.add_book_rec() DM.book_id = 103 DM.author_id = 3 DM.book_title = "Title 2" DM.genre = "History" DM.cover_type = "Cloth" DM.cover = "S" DM.price = "25.99" DM.ct = "1" DM.add_book_rec() DM.book_id = 104 DM.author_id = 2 DM.book_title = "Title 3" DM.genre = "Sports" DM.cover_type = "Cloth" DM.cover = "S" DM.price = "25.99" DM.ct = "1" DM.add_book_rec() DM.list_all_recs() except : import traceback traceback.print_exc() raise 

[pythonNoob]

This is admittedly a hack and is code generated when I want a quick and dirty SQL layout, but will give you a basic idea, and this is all the time I have for now. When using a GUI, I almost always start with the GUI because it is event driven. I will try to work up a better, basic example with GUI for books and authors tomorrow afternoon and evening.

Your code looks impressive, my head is starting to hurt.

[woooee]

>my head is starting to hurt

Since you are not under any time constraints, take your time and tackle the code a little bit at a time. It is well worth it to do a GUI IMHO. I only had time to do the book add GUI, but it shows what to do. Note that GUIs are event driven so when the add book button is pressed, the calling program continually checks for an event from the add_book program telling it to add a book or to cancel and go on.

#! /usr/bin/python3 try: import Tkinter as tk ## Python 2.x except ImportError: import tkinter as tk ## Python 3.x import sqlite3 as sqlite import test_SQL import add_book class StartGUI(): def __init__(self, master): """ run this program it calls the others """ self.master=master self.SQL_filename = './Test_Books' self.open_files() ## instance of Books and Authors SQL database self.book_sql=test_SQL.Books(self.con, self.cur) self.author_sql=test_SQL.Authors(self.con, self.cur) tk.Button(self.master, text="Add a book title", bg="lightblue", command=self.add_book).grid(row=1, column=1) tk.Button(self.master, text="Exit the book program", bg="orange", command=self.master.quit).grid(row=100, column=1) ##---------------------------------------------------------------------- def add_book(self): self.top=tk.Toplevel(master) self.AB=add_book.AddBook(tk, self.top) self.check_for_add() def check_for_add(self): """ loop until add button is clicked or Toplevel is destroyed """ if not self.AB.success and self.top: ## success variable not set and top window ## still open so check after one second self.master.after(1000, self.check_for_add) elif self.AB.success: ## ready to add val_tuple=[] for ent in self.AB.entry_vars: val_tuple.append(ent.get()) self.book_sql.copy_to_struct(val_tuple) self.book_sql.add_book_rec() ## print to verify rec was added self.book_sql.list_all_recs() if self.top: self.top.destroy() ##---------------------------------------------------------------------- def open_files(self) : ## a connection to the database file self.con = sqlite.connect(self.SQL_filename) # Get a Cursor object that operates in the context of Connection con self.cur = self.con.cursor() ##--- CREATE TABLE ONLY IF IT DOESN'T EXIST self.cur.execute('''CREATE TABLE IF NOT EXISTS Books(book_id int, author_id int, book_title varchar, genre varchar, cover_type varchar, cover varchar, price real, ct int)''') self.cur.execute('''CREATE TABLE IF NOT EXISTS Authors(author_id int, first_name varchar, last_name var_char)''') master=tk.Tk() SG=StartGUI(master) master.mainloop() 

## program name = test_SQL.py class Books: def __init__(self, con, cur) : self.con=con self.cur=cur ## END __init__() ##---------------------------------------------------------------------- def add_book_rec(self): val_tuple=(self.book_id, self.author_id, self.book_title, self.genre, self.cover_type, self.cover, self.price, self.ct) self.cur.execute('INSERT INTO Books values (?,?,?,?,?,?,?,?)', val_tuple) self.con.commit() ## END add_book_rec() ##---------------------------------------------------------------------- def copy_to_struct( self, rec ) : print(type(rec), rec) self.book_id = rec[0] self.author_id = rec[1] self.book_title = rec[2] self.genre = rec[3] self.cover_type = rec[4] self.cover = rec[5] self.price = rec[6] self.ct = rec[7] ## END copy_to_struct() ##---------------------------------------------------------------------- def del_rec( self, value_to_delete ) : self.cur.execute("DELETE FROM Books WHERE SQL_field=:name_dict", {"name_dict":value_to_delete}) ## END del_rec() ##---------------------------------------------------------------------- def list_all_recs( self ) : self.cur.execute("select * from Books") recs_list = self.cur.fetchall() for rec in recs_list: print(rec) ## END list_all_recs ##---------------------------------------------------------------------- def lookup_first_field( self ) : self.cur.execute("select * from Books where book_id==:dic_lookup", {"dic_lookup":"test_A_0"}) recs_list = self.cur.fetchall() print("\nlookup_first_field") if len(recs_list): for rec in recs_list: self.copy_to_struct(rec) self.print_rec() else: print("No records found") ## END lookup_first_field() ##---------------------------------------------------------------------- def lookup_first_2_fields( self, lookup_dic ) : self.cur.execute("select * from Books where book_id==:dic_field_1 and author_id==:dic_field_2", lookup_dic) recs_list = self.cur.fetchall() print("\nlookup_first_2_fields") if len(recs_list): for rec in recs_list: self.copy_to_struct(rec) self.print_rec() else: print("No records found") ## END lookup_first_2_field() ##---------------------------------------------------------------------- def open_files( self ) : ## a connection to the database file self.con = sqlite.connect(self.SQL_filename) # Get a Cursor object that operates in the context of Connection con self.cur = self.con.cursor() ##--- CREATE FILE ONLY IF IT DOESN'T EXIST self.cur.execute('''CREATE TABLE IF NOT EXISTS Books(book_id int, author_id int, book_title varchar, genre varchar, cover_type varchar, cover varchar, price real, ct int)''') self.cur.execute('''CREATE TABLE IF NOT EXISTS Authors(author_id int, first_name varchar, last_name var_char)''') ## END open_files() ##---------------------------------------------------------------------- def print_rec( self ) : spaces = "" print(spaces, "book_id =", self.book_id) spaces = " " print(spaces, "author_id =", self.author_id) print(spaces, "book_title =", self.book_title) print(spaces, "genre =", self.genre) print(spaces, "cover_type =", self.cover_type) print(spaces, "cover =", self.cover) print(spaces, "price =", self.price) print(spaces, "count =", self.ct) ## END rec_struct() ##---------------------------------------------------------------------- def rec_struct( self ) : self.book_id = "" self.author_id = "" self.book_title = "" self.genre = "" self.cover_type = "" self.cover = "" self.price = "" self.ct = "" ## END rec_struct() ##====================================================================== class Authors: """ same as books only with Authors Will do add a rec and lookup author only """ def __init__(self, con, cur): self.cur=cur self.con=con ## END __init__() ##---------------------------------------------------------------------- def add_author_rec(self, author_id, first_name, last_name ) : val_tuple=(author_id, first_name, last_name) self.cur.execute('INSERT INTO Authors values (?,?,?)', val_tuple) self.con.commit() ## END add_book_rec() ##---------------------------------------------------------------------- def list_all_recs( self ) : self.cur.execute("select * from Authors") recs_list = self.cur.fetchall() for rec in recs_list: print(rec) ## END list_all_recs ##---------------------------------------------------------------------- def lookup_first_field(self, author ) : self.cur.execute("select * from Authors where author_id==:dic_lookup", {"dic_lookup":author}) recs_list = self.cur.fetchall() print("\nlookup_first_field") if len(recs_list): for rec in recs_list: self.print_rec() else: print("No records found") 

## program name = add_book.py class AddBook(): """ add anything that is entered There is no checking for legitimate Book ID, price, etc. """ def __init__(self, tk, top): self.top=top self.entry_vars=[] self.add_book_val_tuple=[] self.success=False for this_row, txt in enumerate(("Book ID", "Author ID", "Book Title", "Genre", "Cover Type", "Cover", "Price", "Count")): tk.Label(top, text=txt, width=12).grid(row=this_row, column=0, sticky="e") this_var=tk.StringVar() tk.Entry(top, textvariable=this_var, width=15, takefocus=1).grid( row=this_row, column=1, sticky="w") self.entry_vars.append(this_var) tk.Button(top, text="Add this book", command=self.add_it).grid( row=10, columnspan=2) tk.Button(top, text="Cancel", command=self.cancel).grid( row=11, columnspan=2) def add_it(self): ## indicator to add this book self.success=True def cancel(self): self.top.destroy()