Is this thread even close to being on track?
It is not really relevant to argue yet on whether to use EXCEL directly or a data.base. Many ways can be used to solve a problem and if the EXCEL sheet will never be updated manually or by some other program, it is sort of moot as you can ONE TIME transfer the existing data to any form you want and from then on work with that.
The higher level requirement of the project is not clear.
Is there a single application that wakes up and processes requests and eventually exits? If this is true, then there can be a discussion on how to do it, and of course the user can hire someone to do all the work if they are not willing to spend the time and learn. Getting it all done for free by volunteers is not something many are willing to do.
If multiple processes can dip in and out of the data, often concurrently, that is a whole new ball of wax. Others ahve mentioned that. It does not matter where the data is as in all cases you need ways to guarantee the data will not get messed up
But consider the simple case. Your program starts and runs ALONE. It initializes and reads in the data needed and stores it IN MEMORY. It might be in one or more pandas DataFrame structures, for example.
Now it processes requests and all the work in searching and updating data is done in memory. You can of course save a snapshot, or whatever logging, along the way in case of disaster. There are all kinds of ways to handle your needs such as searching for a record/row/whatever that contains the book you want, checking if it has been lent out, marking it appropriately and so on. The next request continues to operate on the in-memory data and your program will notice if someone else tries to take out a book no longer marked as available and so on. You can add all kinds of functionality to this loop that processes requests. Again, you design what requests look like and how they are seen by your process and what replies it gives.
When done, or when you feel like it, you can save your data wherever you want. Here is an example of how to write to EXCEL:
https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html Of course, many of us would suggest writing to a .CSV file that EXCEL can easily open and even update.
Now if you are not trusting, and want to save everything after each transaction and maybe even read it back in, fine. You may also want to not overwrite the previous file and that may mean renaming the old file or whatever makes you happy. If you have a lot of data and serious activity, as many libraries have, it could be a bit silly to read in a gigabyte of data all the time and it may indeed be a better idea to have a database back end that you send queries to and updates that typically are short and quick.
I am not sure why you are even talking about changing column names as normal ones can generally be the same. What some may have suggested is calculating additional columns from others just in Memory.
As with any tool, you need to learn enough to be dangerous. just copying snippets from the web may have drawbacks.
It sounds like one source of confusion is that your EXCEL data takes some shortcuts including lists of comma-separated values with multiple names, or something similar. That is not necessarily a barrier but may require more programming as searching for a specific user embedded within a larger amount of text may be harder than parsing the text into some other data structures that can more easily be searched individually. If you cannot change what you start with, that may be lots of extra work.
And it is not python alone what might require work but other languages like R too.
Perhaps I missed a better description of the design.
-----Original Message-----
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
To: python-list@python.org
Sent: Sun, Jan 9, 2022 1:08 pm
Subject: Re: What to write or search on github to get the code for what is written below:
On Sun, 9 Jan 2022 08:53:58 -0800 (PST), NArshad <narshad.380@gmail.com>
declaimed the following:
>
>I cannot change the column names.
>
But you can change the values under those columns? And that implies...
>
>
>No need to save the CSV file once the work is done CSV file will be exited without saving. If the above is going to work then I will be requiring something to see how to do all this that’s why written tutorial. For a more thorough analysis I have written once again what I want to do.
>
You probably won't find "A tutorial"... You might find some examples
but you will have to figure out how to use the contents. For example, there
are multiple ways to have Python access Excel -- from simple read and write
modules xlrd/xlwt, (which is out-dated; xlrd does not work with .xlsx
format, only the older .xls format). Please review:
http://www.python-excel.org/ There is also the, on Windows, the potential
to use the pythonwin extensions OR ctypes module to make low-level calls
directly to the Excel DLLs (the pythonwin calls would look similar to how
Visual BASIC or C# would access Excel, and also VBA and VBS).
>Only the four functions that I have written below I have to make and that’s it. All the input will be entered by the user on a website:
>1. First, I have to check in the Excel sheet or table whether the book user has entered is present in the book bank or not.
>
Where does this requirement come from? Who is providing this "Excel
sheet or table"?
I saw nothing in your originally linked flask tutorial referencing
Excel or a book check-out system (I didn't even see much for interaction on
that tutorial -- you'll need to handle input forms for a web site of this
nature, which may get into tracking user sessions, cookie management, etc.)
The PythonHow site also does not, from what I read, host your finished
example -- they give instructions on creating a hosting account on another
service..
If this entire project is something you've dreamed up, then I'd suggest
dropping the Excel requirement, and going directly to a database system. Of
course, that will require, at a minimum, learning the basics of SQL and
then mapping that to the dialect used by the RDBM selected -- for
simplicity I'd suggest SQLite3. Python includes a module for that.
SQLite3 is a file-server engine -- your application directly links to
the engine library and has access to the database file; versus a
client-server engine where your application sends request to the database
engine, and the engine handles the access to the database. If there is only
one instance of the Flask application the only thing you have to handle in
SQLite3 is transaction control -- rather than also being concerned about
multiple applications all opening the same database. With client-server
(MySQL/MariaDB, SQL Server, PostgreSQL...) you have to manage the server
itself (create user account for the application, etc.).
Learning SQL alone does not ensure a decent database design -- you
should also learn at least the first three Codd Normal Forms to decompose
your data into relations.
>2. If a book is present and the quantity of the required book is greater than 0 (COPIES_LEFT column in excel file) and if the user wants the book, it will be assigned to the user which he will take from the book bank physically. When COPIES_LEFT is less than or equal to 0 the message will be “Book finished or not present”.
>
>3. The quantity of the book in the Excel file will be reduced by 1 in the COPIES_LEFT column and the name of the borrower or user will be entered/added in the Excel file table or sheet already made and the column name is BORROWER’S NAME.
Which means you need to update this Excel file. That could present race
conditions if multiple users try to remove the same book (or even different
books). Excel does not have transaction control, so either you keep ALL the
data in memory (or copy the entire contents to a database system on
start-up AND copy the database back to Excel on application shut-down, you
run the risk of data corruption [.and even the copy-in/copy-out could fail
if the application crashes at some stage]).
A database using transaction control should, at the least, mean on a
crash, the uncommitted transactions never took place (may require some time
on restart for the engine to clean up garbage workspace records).
Transaction control should also prevent the application from having two
sessions trying to update the same records at once -- the second session to
attempt an update will get an error, so you code needs to have a way to
delay/retry the update (possibly advising that user of new count value).
Note: many of the Python DB-API modules default to auto-commit for each
SQL statement executed. You'll need to ensure you've turned that off so
that multiple statement transactions can be performed. Read the
documentation for the module in use:
https://docs.python.org/3/library/sqlite3.html https://www.google.com/search?q=sqlite+python+tutorial+pdf >
>4. The borrower’s or user name can be more than one so they will be separated with a comma in the Excel file BORROWER’S NAME column.
This is one of those spots where database normalization is pretty much
mandated. A normalized database table does not have "columns" that hold
multiple values. Instead one uses a separate table where only one value per
row is stored, but that row has an identifier (known as a foreign key) that
identifies the row in the other table carrying the rest of the information.
INVENTORY (*ID*, book details in multiple columns, Copies-on-Hand)
CHECKEDOUT (*ID*, _Inventory-ID_, name)
*ID* primary (unique) key for the relation
_Inventory-ID_ foreign key pointing to inventory record
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com
http://wlfraed.microdiversity.freeddns.org/ --
https://mail.python.org/mailman/listinfo/python-list --
https://mail.python.org/mailman/listinfo/python-list
