Posts: 10
Threads: 4
Joined: May 2018
May-18-2018, 07:16 PM
(This post was last modified: May-18-2018, 07:21 PM by 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_title title of the book
*genre genre of the book
*type hardback, softback, or paperback
*cover Y, N, S, or -
*price price of the book
*count holds the number of times the book and been checked out, increments on each checkout
Authors
*author_id (PK) unique id for each author
*first_name first name of author, (note that books can have more than one author)
*last_name last 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_number inmates 8 digit prison number (00123456)
*checkout_date date 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_date date charge was entered
*inmate_number inmates 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_number inmates 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_title title of book (read from Books table)
*genre genre of book (read from Books table)
*cover Y, N, S, or - (read from Books table)
*price price of book (read from Books table)
*inmate_number inmate_number (read from checkout table)
*checkout_date date book was checked out (read from checkout table)
*due_date due date (read from checkout table)
Posts: 536
Threads: 0
Joined: Feb 2018
May-18-2018, 08:17 PM
(This post was last modified: May-18-2018, 08:17 PM by woooee.)
Quote: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.
Posts: 536
Threads: 0
Joined: Feb 2018
May-19-2018, 03:20 AM
(This post was last modified: May-19-2018, 03:21 AM by 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
Posts: 10
Threads: 4
Joined: May 2018
(May-19-2018, 03:20 AM)woooee Wrote: 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.
Posts: 536
Threads: 0
Joined: Feb 2018
May-22-2018, 11:21 PM
(This post was last modified: May-22-2018, 11:21 PM by 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()
|
