Python Forum

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)

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.

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 

(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.

>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()