On Mon, 24 Jan 2022 00:25:34 -0800 (PST), NArshad <narshad.380@gmail.com>
declaimed the following:
>
>I am not writing any code because I don’t know what code to do next. Still, I have made a dictionary now searching for what to do next in which one choice is MS SSIS and the second is numpy or pandas which AGross has written. If you want to see the code of making a dictionary then below it is:
>
>xyz = {
> col[0].value: [cell.value for cell in col[1:]]
> for col in sheet.columns
>}
>print(xyz)
>
Since none of us have seen a reasonable representation of the
spreadsheet (export it as CSV and paste the first 5-10 lines into a post)
we have no idea if the above even produces anything useful.
You appear to be grabbing onto catchphrases in the hope that they will
provide you with some miraculous "I call this, that, and another, and look
-- it's done".
>Now the problem is what to do next. If I had known, I must have submitted the whole project at my earliest convenience without coming over here in google groups.
>
How would you do this assignment on paper? Print out your spreadsheet
and get a large pad of paper... Then write down each step you have to take
to process "one user request" in your system (make mock-ups of any
input/output screens). Make (horrors) a flow-chart showing the branch
points (book was found, book was not found).
When you get the steps (aka "algorithm") documented well enough that
someone else can follow them on paper, you are ready to translate each of
those steps into code.
>The problem is I don’t want Excel spreadsheet as a report/extraction format I want to UPDATE the Excel spreadsheet automatically with the latest values which are only in the two column cells and I don’t know which cells. Is it possible using SSIS?
>
The Excel spreadsheet is almost the WORST data structure for this
assignment (a variable length record flat file would be the worst; fixed
length record flat file is 1960s business processing but would be more
useful as it allows for in-place updates).
" I don’t know which cells" -- So how would you do this by hand, if
someone gave you a print-out of the spreadsheet? When you can describe the
operations in sufficient detail for someone else to follow them, you are
ready to convert them into code.
M$ SSIS, as I mentioned, is a system for importing, TRANSFORMING, and
clean-up of data from external sources -- for inclusion into a M$ SQL
Server database. You insist you don't want to consider database
implementation, so SSIS will do nothing for you (besides, you'd have to
learn how to program ITS work-loads).
>How you know so much about guns??
Irrelevant... Though I've owned firearms since the 1970s (well, late
60s if you count the Christmas gift of a .22 rifle while in the 7th grade).
>
>Why are you not in the favor of pandas if not openpyxl but if my problem is getting solved with MS SSIS then it's fine to leave openpyxl and pandas?
One: pandas is still a case of doing import/transform/export; just
because it has functions to directly read (and I presume, write) .xlsx
format spreadsheet files you are still transforming the data into a pandas
"dataframe" object. pandas is using openpyxl to do that read/write.
Two: pandas is optimized for numerical and "time series" processing
(think time-stamped tables of readings from, say, a weather station) on
which you want to produce smoothed trends. The documentation is explicit
that it is a bit slow when doing ad-hoc row/column indexing.
Three: in terms of increasing complexity openpyxl will be the simplest,
and M$ SSIS is way above anything you might find useful without studying a
book.
>What you have written is difficult to find on google search and others. That's why writing all this to get something for search.
Because you WON'T find it on a search engine -- especially not at your
"tutorial" level.
You need to 1) Know your programming language (since you are here, that
would be Python) including understanding any compound data structures it
supports (lists, dictionaries) and, at the least, procedural language
features (you can get by without having to do OOP, though most all
libraries are object based, you just need to invoke them, not write OOP
objects themselves); 2) Understand how to fit data structures with
algorithms; 3) be able to translate activities (as performed by a person)
into algorithms, and then from algorithms into language specific code.
At a very high level, your assignment requires:
1 obtain a title from a user
2 search a file for that title
3 report results of the search
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
BYSTANDERS WILL WANT TO LOOK AWAY NOW
JUST GO TO THE NEXT POST
The following is something I hacked together yesterday, and is untested
(I don't have the outer framework set up yet). So far, the only thing it
does is INITIALIZE an SQLite3 database (create the schema definition) and
install a default Admin registration for logging into the eventual
application.
The schema is based upon the most complete description we've seen of
your data and intended processing. As such, it has an entry for each copy
of any given title (to track condition and if it is in-stock or
checked-out; check-outs are tracked as a separate relation in order to
allow for historical records).
The use case/CONOPS for regular users is:
The application will present the user with an option to log in using a
username and password, or to register a new log-in name. Registration will
verify the username is not being used by another, after which it will
obtain a password and real name from the user.
Upon a successful user log-in, the application will present the user
with the option to reserve a book, or to cancel a previously made
reservation.
For the "reserve" option, the application will present the user with a
field in which to enter search terms. Search terms are words which may
appear in the book title or author names. If the search returns more than
five items, the count of candidate books will be displayed and the
application will ask the user to enter additional or different search
terms. If the search returns five or less items, the items will be
displayed and the application will ask the user to select one of them. Upon
the user indicating which item is desired, the application will verify that
at least one copy of the item is "Available" and, if available, set the
item to "Reserved", and create a reservation record identifying the copy,
the logged in user, and the date of the reservation.
...
Unreserve is similar, except the search only includes items for which
the user has an active reservation record.
...
ADMIN functions include: granting admin privileges to registered users;
adding new books and copy counts (or increasing copy counts if a new supply
has been obtained); clearing out stale reservations (in this scheme, the
reservation is just the request by a user for a book; if they don't pick up
the book within a few days, the reservation should be cancelled); checking
out books (checking out is when the reserving user actually takes
possession of the specific book copy [numbered] that they reserved);
producing report of overdue books; checking in books (when the book has
been returned). Other functions as the whim takes me...
-=-=-=- database.py
"""
Database.py Encapsulates functions for working with the
BookDepository DataBase
"""
import sqlite3 as db
import os
import os.path as op
import datetime
import creationSql
STANDARDPRAGMAS = [ "PRAGMA foreign_references = ON;" ]
DATABASE = "BookDepository.SQ3"
def initializeDB(replace = False):
status = []
if op.exists(DATABASE):
if replace:
old = DATABASE + "-%s" %
datetime.date.isoformat((datetime.date.today()))
os.rename(DATABASE, old)
status.append("Existing %s renamed to %s" % (DATABASE, old))
else:
status.append("Can not initialize database -- %s already
exists")
return status
con = db.connect(DATABASE, detect_types=db.PARSE_DECLTYPES)
status.append("Created empty database %s" % DATABASE)
for pragma in STANDARDPRAGMAS:
con.execute(pragma)
status.append("Set PRAGMAS")
for tbl in creationSql.TABLES:
con.execute(tbl)
status.append("Created tables")
status.append("Database schema defined")
# TODO: need to create a password hashing function and random password
password = "DbaAdm1n"
user = "BRAdmin"
con.execute("""INSERT INTO USERLOGIN (Username, Password, Last_Name,
First_Name, Admin)
VALUES (%s, %s, %s, %s, %s)""",
(user, password, "Book Depository DB Admin", None, True))
status.append("Database Administrator account created -- Please record
these values")
status.append("\t\tUSER LOGIN:\t%s" % user)
status.append("\t\tPassword:\t%s" % password)
status.append("\n\n*****\tRESTART BookDepositoy application to
login\n")
con.commit()
con.close()
return status
-=-=-=- creationSql.py
"""
creationSql.py Defines SQL statements to create an empty database
SQL syntax is that of SQLite3
"""
USERLOGIN = """
-- UserLogin contains registration information for users of the
-- BookDepository system. This includes a login username,
-- hashed password, and real name (last/first),
-- along with a flag indicating if the user has administrative
-- privileges (non-admin users may only make or cancel
-- reservations for specific books
CREATE TABLE IF NOT EXISTS UserLogin (
ID INTEGER PRIMARY KEY,
UserName TEXT UNIQUE NOT NULL,
Password TEXT NOT NULL,
Last_Name TEXT NOT NULL,
First_Name TEXT,
Admin INTEGER DEFAULT FALSE NOT NULL
);
"""
PUBLISHER = """
-- Publisher contains the common name of book publishers
CREATE TABLE IF NOT EXISTS Publisher (
ID INTEGER PRIMARY KEY,
Publisher TEXT NOT NULL
);
"""
AUTHOR = """
-- Author contains the name (last/first) of authors
CREATE TABLE IF NOT EXISTS Author (
ID INTEGER PRIMARY KEY,
Last_Name TEXT NOT NULL,
First_Name TEXT
);
"""
BOOK = """
-- Book contains the ISBN (or alternate call number) for
-- book titles (SQLite3 generic TEXT type supports both
-- under one field; a more traditional RDBM would be
-- better served by creating an ISBN CHAR(13) [also for
-- ISBN-10 format], and a separate alternate-call number),
-- Title, Publisher reference, and copyright date (as
-- text, as these are normally just month and year)
CREATE TABLE IF NOT EXISTS Book (
ID INTEGER PRIMARY KEY,
ISBN_Call TEXT NOT NULL,
Title TEXT NOT NULL,
Publisher_ID INTEGER NOT NULL
REFERENCES Publisher(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE,
Copyright_Date TEXT
);
"""
COPY = """
-- Copy contains a record for each copy of each book,
-- the check-out status for this copy (available,
-- out, reserved), and notes (condition) of the copy
CREATE TABLE IF NOT EXISTS Copy (
ID INTEGER PRIMARY KEY,
Book_ID INTEGER NOT NULL
REFERENCES Book(ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
Copy_Number INTEGER NOT NULL,
Status TEXT DEFAULT 'A' NOT NULL
CHECK (upper(Status) in ('A', 'O', 'R')),
Notes TEXT,
UNIQUE (Book_ID, Copy_Number)
);
"""
CHECKOUT = """
-- Checkout links specific copies of books to
-- registered users, and tracks reservation date,
-- checked out date (when user received the book),
-- and due date. There is also a flag indicating
-- if the record is active, or historical.
CREATE TABLE IF NOT EXISTS Checkout (
ID INTEGER PRIMARY KEY,
Copy_ID INTEGER
REFERENCES Copy(ID)
ON DELETE SET NULL
ON UPDATE CASCADE,
User_ID INTEGER
REFERENCES UserLogin(ID)
ON DELETE SET NULL
ON UPDATE CASCADE,
Reserved DATE NOT NULL,
Checkedout DATE DEFAULT NULL,
Due DATE DEFAULT NULL,
Active INTEGER DEFAULT TRUE NOT NULL
);
"""
BOOK_AUTHOR = """
-- Book_Author links authors to book titles
CREATE TABLE IF NOT EXISTS Book_Author (
ID INTEGER PRIMARY KEY,
Book_ID INTEGER NOT NULL
REFERENCES Book(ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
Author_ID INTEGER NOT NULL
REFERENCES Author(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE,
UNIQUE (Book_ID, Author_ID)
);
"""
# TABLES lists the individual SQL statements for each
# database table. They are ordered such that
# referenced tables are before the referencing
# table
TABLES = [ USERLOGIN, PUBLISHER, AUTHOR,
BOOK, COPY, CHECKOUT,
BOOK_AUTHOR ]
-=-=-=-
Good thing this is a text only forum, or I'd toss in an
Entity-Relationship diagram for the database.
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com
http://wlfraed.microdiversity.freeddns.org/ --
https://mail.python.org/mailman/listinfo/python-list