What to write or search on github to get the code for what is written below:

307 views
Skip to first unread message

NArshad

unread,
Jan 6, 2022, 1:55:40 PMJan 6
to
All this is going to be in python’s flask and HTML only

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.

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

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.


- All functions mentioned above are to be deployed on the website pythonhow.com so make according to https://pythonhow.com/python-tutorial/flask/web-development-with-python-and-flask/

- Do you know any other websites to deploy a python web application??

- No time to switch from Excel to anywhere else. Please do not make any changes to the Excel file.

- Tutorials and repositories of the web for such problems are also required. The same is required for python (flask, Django...) also.

alister

unread,
Jan 6, 2022, 4:59:17 PMJan 6
to
On Thu, 6 Jan 2022 10:55:30 -0800 (PST), NArshad wrote:

> All this is going to be in python’s flask and HTML only
>
> 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.
>
Excel is the wrong application for storing this data - it should be in a
database

> 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 will is less than or equal to 0 the
> message will be “Book finished or not present”.
Again Excel is not the correct application for processing this data
>
> 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.
>
Database!
> 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.
>
Database
>
> - All functions mentioned above are to be deployed on the website
> pythonhow.com so make according to
> https://pythonhow.com/python-tutorial/flask/web-development-with-python-
and-flask/
>
> - Do you know any other websites to deploy a python web application??
>
> - No time to switch from Excel to anywhere else. Please do not make any
> changes to the Excel file.
>
> - Tutorials and repositories of the web for such problems are also
> required. The same is required for python (flask, Django...) also.

Sorry did not spot that this was a homework assignment
data should still be imported into a DB (a trivial task) It can be
exported back to a compatible format just as easily if hard copy output is
required




--
"Rembrandt's first name was Beauregard, which is why he never used it."
-- Dave Barry

Chris Angelico

unread,
Jan 7, 2022, 2:57:59 PMJan 7
to
On Sat, Jan 8, 2022 at 6:50 AM NArshad <narsh...@gmail.com> wrote:
> - All functions mentioned above are to be deployed on the website pythonhow.com so make according to https://pythonhow.com/python-tutorial/flask/web-development-with-python-and-flask/
>
> - Do you know any other websites to deploy a python web application??
>
> - No time to switch from Excel to anywhere else. Please do not make any changes to the Excel file.
>
> - Tutorials and repositories of the web for such problems are also required. The same is required for python (flask, Django...) also.
>

Instead of asking how to search GitHub for the code you want, start by
actually writing code.

ChrisA

MRAB

unread,
Jan 7, 2022, 3:26:21 PMJan 7
to
As someone else has already pointed, Excel is the wrong way to do it,
yet it remains a popular method.

Given that, you can read Excel spreadsheets with 'openpyxl', which is on
PyPI.

Avi Gross

unread,
Jan 7, 2022, 4:13:21 PMJan 7
to
This entire thread seems a bit IFFY to me.
It does seme like HW to me but also a bit peripheral.
The fact that the data is in EXCEL is a detail. And unless a spreadheet is complex, it may be trivial to save the file as a .CSV and from then on read from there into Python (or anything) and when done making modifications, save it out again. EXCEL can also be used to open such a CSV and look at it. And, of course, as has been pointed out, there are ways to save data into EXCEL or read directly from it.
But the overall programming ideas required, once the data are in memory, are another story, especially if some fields are in ormats like a comma separated text string. Searching these is not trivial while expanding the data so each such "person" is on their own may simplify things, albeit not make it so easy to save it out.
I do note that unless there is a guarantee that reads and updates to where the data is stored are guaranteed to not collide with other things, there lurks danger here.
So if the request is not made more clear and focused on what is needed in mostly native Python, and accompanied by some evidence the user has shown some code they hope will do most of the work and is just stuck somewhere, I, personally, feel no need to offer any guidance. 


-----Original Message-----
From: Chris Angelico <ros...@gmail.com>
To: pytho...@python.org
Sent: Fri, Jan 7, 2022 2:57 pm
Subject: Re: What to write or search on github to get the code for what is written below:

On Sat, Jan 8, 2022 at 6:50 AM NArshad <narsh...@gmail.com> wrote:
> - All functions mentioned above are to be deployed on the website pythonhow.com so make according to https://pythonhow.com/python-tutorial/flask/web-development-with-python-and-flask/
>
> - Do you know any other websites to deploy a python web application??
>
> - No time to switch from Excel to anywhere else. Please do not make any changes to the Excel file.
>
> - Tutorials and repositories of the web for such problems are also required. The same is required for python (flask, Django...) also.
>

Instead of asking how to search GitHub for the code you want, start by
actually writing code.

ChrisA
--
https://mail.python.org/mailman/listinfo/python-list

NArshad

unread,
Jan 9, 2022, 2:04:31 AMJan 9
to
As written no time to switch from excel to anywhere else but if a certain database is required to make changes in Excel’s cell values then which database to use (example Access or what) and after the right selection of the database, how to import data to a database and then export back to a compatible format that is to Excel cells….

I mean how to do this written below:

“data should still be imported into a DB (a trivial task) it can be exported back to a compatible format just as easily if hard copy output is required”

The reason I have written:

“What to write on GitHub or on google search to find the necessary code to start with? “

I will also be requiring a code to start with just as most people do. The same is the case with tutorials.

This is not homework and I have been checking openpyxl for the last one or two months to find what is required by me when I found nothing of what is required by me then posted on this google group.


Julio Di Egidio

unread,
Jan 9, 2022, 5:29:58 AMJan 9
to
On Sunday, 9 January 2022 at 08:04:31 UTC+1, NArshad wrote:
<snip>
> I will also be requiring a code to start with just as most
> people do. The same is the case with tutorials.
>
> This is not homework and I have been checking openpyxl
> for the last one or two months to find what is required by
> me when I found nothing of what is required by me then
> posted on this google group.

The web is full of tutorials on how to access excel from python (but I would not do that, see below) or on how to use flask. Just you will hardly find an article that does exactly what you want to the letter: you will have to find and compose the pieces to make it up. And show your work if you want help with the practical side...

"No time to switch from Excel to anywhere else. Please do not make any changes to the Excel file." Whose time?? No changes?? That makes very little sense (even for a tutorial): maybe the point is that at any time one has to be able to re-export to the original excel format? (And why?? Do we actually need "reporting": then we can generate a PDF rather, otherwise we can just send out a CSV... etc.) Is that it? Requirements gathering, rationalization, negotiation... Meanwhile keep in mind that using excel directly from an app is hardly the way to go for more than one reason: at least export/import to a CSV file (easily done in excel itself) and use that as your working data.

BTW, I'd probably not go for a full-fledged database here, too simple, it's just one table, max two or three if one wants to do things "normal", still utterly simple: loading the data from the CSV file in memory into a pandas dataframe, working with that, and eventually saving back to the CSV file might be just enough... as long as there is never more than one user concurrently using the system, or you need also implement some concurrency management (you need that with a database, too, anyway): but that's tutorial number 2, and already quite less trivial, since to begin with it requires a more thorough analysis.

HTH,

Julio

alister

unread,
Jan 9, 2022, 5:56:04 AMJan 9
to
1st save the data from excel as a csv file

you will find the csv module makes these much easier to deal with, even if
you do not import into a db (although even then I would import into sql-
lite just for the benefits of the search algorithms)




--
Sometime in 1993 NANCY SINATRA will lead a BLOODLESS COUP on GUAM!!

NArshad

unread,
Jan 9, 2022, 11:54:09 AMJan 9
to
-Whose time??
My time
I do not have not time to switch to a database but if necessary I can use the database to make changes in Excel column entries.

-No changes??

I cannot change the column names.


-“maybe the point is that at any time one has to be able to re-export to the original excel format? (And why?? Do we actually need "reporting": then we can generate a PDF rather, otherwise we can just send out a CSV... etc.) Is that it?”

PDF is not required and I am not doing any reporting at all.


-“loading the data from the CSV file in memory into a pandas dataframe, working with that, and eventually saving back to the CSV file might be just enough... as long as there is never more than one user concurrently using the system, or you need also implement some concurrency management (you need that with a database, too, anyway): but that's tutorial number 2, and already quite less trivial, since to begin with it requires a more thorough analysis”

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.

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.

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.

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.


-1st save the data from excel as a CSV file

you will find the CSV module makes these much easier to deal with, even if
you do not import into a DB (although even then I would import into SQL-
lite just for the benefits of the search algorithms)

If in order to do the required task it is necessary to use SQLite and without using SQLite I cannot do what I want to do then I think I have to go for SQLite otherwise CSV looks to me as fine. To do everything using CSV I will be requiring a tutorial to see how to do all this.

Anything not clear?

Julio Di Egidio

unread,
Jan 9, 2022, 1:34:30 PMJan 9
to
On Sunday, 9 January 2022 at 17:54:09 UTC+1, NArshad wrote:

[You should quote what you are replying to, as I and others are doing.]

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

The web is full of tutorials on this stuff: if you can't even get started, maybe
you are simply not apt to the job. Anyway, I for one won't help any further
unless I see you at least trying. And be ready to do a lot of work...

Julio

alister

unread,
Jan 9, 2022, 2:03:14 PMJan 9
to
Sql-lite is not essential to the task CSV would be sufficient, but you may
find that taking the time to learn it pays off later (even if you wait
untill after this project)




--
Trying to establish voice contact ... please ____ yell into
keyboard.

MRAB

unread,
Jan 9, 2022, 4:05:49 PMJan 9
to
> As written no time to switch from excel to anywhere else but if a certain database is required to make changes in Excel’s cell values then which database to use (example Access or what) and after the right selection of the database, how to import data to a database and then export back to a compatible format that is to Excel cells….
>
> I mean how to do this written below:
>
> “data should still be imported into a DB (a trivial task) it can be exported back to a compatible format just as easily if hard copy output is required”
>
> The reason I have written:
>
> “What to write on GitHub or on google search to find the necessary code to start with? “
>
> I will also be requiring a code to start with just as most people do. The same is the case with tutorials.
>
> This is not homework and I have been checking openpyxl for the last one or two months to find what is required by me when I found nothing of what is required by me then posted on this google group.
>
>
Using openpyxl is pretty straightforward:


from openpyxl import load_workbook
wb = load_workbook(spreadsheet_path)
sheet = wb.active

# Reading the values in cells:
print('Cell A1 contains', sheet['A1'].value)
print('Cell A2 contains', sheet['A2'].value)
print('Cell B1 contains', sheet['B1'].value)

# Alternatively:
print('Cell A1 contains', sheet.cell(1, 1).value)
print('Cell A2 contains', sheet.cell(1, 2).value)
print('Cell B1 contains', sheet.cell(2, 1).value)

# Changing the value in a cell:
sheet.cell(1, 20).value = 'TEST'

# A value of None means that the cell is empty.

wb.save(spreadsheet_path)
wb.close()

Peter J. Holzer

unread,
Jan 9, 2022, 5:25:50 PMJan 9
to
On 2022-01-09 13:08:51 -0500, Dennis Lee Bieber wrote:
> 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).

This is not true. xlrd has supported .xlsx since at least 2014 (when I
started using it). For new projects I would recommend openpyxl though,
which is much more feature-complete.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | h...@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc

Avi Gross

unread,
Jan 9, 2022, 5:29:30 PMJan 9
to
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 <wlf...@ix.netcom.com>
To: pytho...@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 <narsh...@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
    wlf...@ix.netcom.com    http://wlfraed.microdiversity.freeddns.org/
--
https://mail.python.org/mailman/listinfo/python-list

Cameron Simpson

unread,
Jan 9, 2022, 6:23:31 PMJan 9
to
On 09Jan2022 13:08, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
>>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"?

As a spectator, I have the impression that NArshad has to produce a
little web interface to update records kept in a preexisting business
system.

I've a friend who earns her $s doing data work for various corporates.
Anecdotally, _many_ of these use Excel as their storage and work
backend. Awful, but an existing situation. I infer that NArshad has to
deal with such a situation.

Cheers,
Cameron Simpson <c...@cskk.id.au>

NArshad

unread,
Jan 10, 2022, 11:39:32 AMJan 10
to

Using openpyxl is pretty straightforward:


from openpyxl import load_workbook
wb = load_workbook(spreadsheet_path)
sheet = wb.active

# Reading the values in cells:
print('Cell A1 contains', sheet['A1'].value)
print('Cell A2 contains', sheet['A2'].value)
print('Cell B1 contains', sheet['B1'].value)

# Alternatively:
print('Cell A1 contains', sheet.cell(1, 1).value)
print('Cell A2 contains', sheet.cell(1, 2).value)
print('Cell B1 contains', sheet.cell(2, 1).value)

"""
The cell numbers (A1 or A2 or A3 or A4……… ) are not fixed they can be any. I don't know what the cell number is going to be that's what the problem is.

The user is going to enter the book name in an HTML form present on a website then it will be checked whether the book user has entered is present or not in the Excel file. If the book is present in the book bank and the user requires that book then one will be issued to the user and the total number of books will be reduced by 1 (one) and the user or borrower’s name will be entered in the Excel’s table row in which the book name is present separated by a comma by other borrower names. The borrower's name can be more than one because more than one copies of the book are there as these are the books that are taught in schools.

MRAB

unread,
Jan 10, 2022, 2:21:43 PMJan 10
to
On 2022-01-10 16:39, NArshad wrote:
>
> Using openpyxl is pretty straightforward:
>
>
> from openpyxl import load_workbook
> wb = load_workbook(spreadsheet_path)
> sheet = wb.active
>
> # Reading the values in cells:
> print('Cell A1 contains', sheet['A1'].value)
> print('Cell A2 contains', sheet['A2'].value)
> print('Cell B1 contains', sheet['B1'].value)
>
> # Alternatively:
> print('Cell A1 contains', sheet.cell(1, 1).value)
> print('Cell A2 contains', sheet.cell(1, 2).value)
> print('Cell B1 contains', sheet.cell(2, 1).value)
>
> """
> The cell numbers (A1 or A2 or A3 or A4……… ) are not fixed they can be any. I don't know what the cell number is going to be that's what the problem is.
>
> The user is going to enter the book name in an HTML form present on a website then it will be checked whether the book user has entered is present or not in the Excel file. If the book is present in the book bank and the user requires that book then one will be issued to the user and the total number of books will be reduced by 1 (one) and the user or borrower’s name will be entered in the Excel’s table row in which the book name is present separated by a comma by other borrower names. The borrower's name can be more than one because more than one copies of the book are there as these are the books that are taught in schools.
> """
>
[snip]
How are the relevant cells identified in the spreadsheet?

It's often the case that the cells on the first row contain text as
column labels. If that's what you have in your spreadsheet, then read
the cells on the first row for the column labels and put them in a dict
to map from column label to column number.

NArshad

unread,
Jan 11, 2022, 1:31:14 AMJan 11
to
-“How are the relevant cells identified in the spreadsheet?”
The column headings are:
BOOK_NAME
BOOK_AUTHOR
BOOK_ISBN
TOTAL_COPIES
COPIES_LEFT
BORROWER’S_NAME
ISSUE_DATE
RETURN_DATE


-“It's often the case that the cells on the first row contain text as column labels.”

These I have written above.


-“If that's what you have in your spreadsheet, then read the cells on the first row for the column labels and put them in a dict to map from column label to column number.”

This written above I do not understand how to code.

Dennis Lee Bieber

unread,
Jan 11, 2022, 10:06:35 AMJan 11
to

*** Apologies for the repost. Since Gmane made the list a read-only
group, I finally broke down and reinstated Giganews comp.lang.python.
Unfortunately I'd missed that this came back with X-NoArchive active and
Google doesn't even let such messages show up for a day -- so the OP hasn't
seen any of my responses.

As a courtesy, I will NOT be reposting the other four responses I've
made over the last few days. {If I do, it will be as a single consolidated
response} ***

On Mon, 10 Jan 2022 22:31:00 -0800 (PST), NArshad <narsh...@gmail.com>
declaimed the following:

>-“How are the relevant cells identified in the spreadsheet?”
>The column headings are:
>BOOK_NAME
>BOOK_AUTHOR
>BOOK_ISBN
>TOTAL_COPIES
>COPIES_LEFT
>BORROWER’S_NAME
>ISSUE_DATE
>RETURN_DATE
>

So... Besides "BORROWER'S_NAME" you also have a pair of dates you have
to track in parallel, and which should also need to be updated whenever you
change the borrower field. Furthermore, if you plan to separate those with
commas, you'll need to escape any embedded commas or you'll find that names
like "John Doe, Jr" will mess up the correspondence as you'd treat that as
two names on reading the borrower field. Also you need to be aware of the
limits for Excel text cells -- while you could stuff 32kB of text into a
cell, Excel itself will only display the first 1024 characters. That might
be sufficient if the average name is around 31 characters (32 with your
comma separator) as it would allow 32 names to be entered and still display
in Excel itself. Oh, and to track multiple dates in a cell, you'll have to
convert from date to text when writing the cell, and from text back to date
when reading the cell -- since you can't comma separate multiple dates.

Total_Copies - Copies_Left should be equal to the number of names (and
dates). In short, this is a very messy structure to be maintaining. If not
using an RDBM, at the very least borrower/issue date/return date should be
moved to a separate sheet which also has "Book ID" (the row number in the
first sheet with the book). That way you'd have one record per borrower,
and can easily add new records at the bottom of the sheet (might need to
use a "Book ID" of "0" to indicate a deleted record (when a borrower
returns the book) so you can reuse the slot, since you'd need some way to
identify the end of the data -- most likely by a blank record..

>-“If that's what you have in your spreadsheet, then read the cells on the first row for the column labels and put them in a dict to map from column label to column number.”
>
>This written above I do not understand how to code.

Have you gone through the Python Tutorial? Dictionaries are one of
Python's basic data structures. https://docs.python.org/3/tutorial/

You are unlikely to find anything near to your application on-line --
pretty much anyone doing something like a library check-out system will be
using a relational database rather than spread sheets. At worst, they may
have a spread sheet import operation to do initial population of the
database, though even that might be using SQL operations (Windows supports
Excel files as an ODBC data source). See:
https://docs.microsoft.com/en-us/cpp/data/odbc/data-source-managing-connections-odbc?view=msvc-170
They are unlikely to be dong any exports to Excel -- that's the realm of
report logic. According to
https://support.sas.com/documentation/onlinedoc/dfdmstudio/2.5/dmpdmsug/Content/dfDMStd_T_Excel_ODBC.html
"""
Note: You cannot use a DSN to write output to Excel format. You can,
however, use a Text File Output node in a data job to write output in CSV
format. You can then import the CSV file into Excel.
"""
A Java-biased (old Java -- the interface to ODBC has been removed from
current Java) example that doesn't seem to need "named ranges" is
https://querysurge.zendesk.com/hc/en-us/articles/205766136-Writing-SQL-Queries-against-Excel-files-using-ODBC-connection-Deprecated-Excel-SQL-

Or...
https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/getting-data-between-excel-and-sql-server-using-odbc/
(which also indicates that it is possible to update the file via ODBC...
But note the constraints regarding having 64-bit vs 32-bit drivers).
Obviously you'll need to translate the PowerShell syntax into Python's ODBC
DB-API interface (which is a bit archaic as I recall -- does not match
current DP-API specifications).

Dennis Lee Bieber

unread,
Jan 11, 2022, 11:39:47 AMJan 11
to

*** Going back to the post in the thread as I've other concerns (and
have turned off the old X-NoArchive setting ***

On Thu, 6 Jan 2022 10:55:30 -0800 (PST), NArshad <narsh...@gmail.com>
declaimed the following:

>All this is going to be in python’s flask and HTML only
>
>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.
>
>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 will 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.
>
>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.
>
>
>- All functions mentioned above are to be deployed on the website pythonhow.com so make according to https://pythonhow.com/python-tutorial/flask/web-development-with-python-and-flask/
>
>- Do you know any other websites to deploy a python web application??

There are likely plenty -- How much do you want to pay? How much
support do you need? How much traffic do you expect.

Note that the PythonHOW tutorial is suggesting creating a student/hobby
account on Heroku (free, and fairly limited). Heroku provides Linux
containers, and for Python you can only make use of add-ons that can be
installed using PIP. As Linux, none of the Windows specific modules will be
available (no Excel ODBC, no use of pythonwin extensions calling directly
into the Excel DLLs).

Who is going to be using the Excel file? and how are they going to get
to it? Your Heroku container does not run Excel, and I'm not even sure how
you would get it to the Heroku container (possibly it can be done as part
of the Python application upload).

I don't even know if SQLite3 is viable -- as I recall, Linux Python
installs rely upon system installed SQLite3 libraries, not installed via
PIP. Heroku pushes PostgreSQL for data storage. It may cost to add others
(MariaDB/MySQL).


https://elements.heroku.com/buildpacks/heroku/heroku-buildpack-python

>
>- No time to switch from Excel to anywhere else. Please do not make any changes to the Excel file.
>

Again, if you are deploying to something like Heroku for the
application -- the Excel file will have to be deployed also, and no one
except your application will be able to see it there. Under this situation,
there is no reason/excuse to keep the data in the very inefficient format
you've defined in the most recent message. Import into some supported
database and normalize the data to make updates easier.

MRAB

unread,
Jan 12, 2022, 4:42:49 PMJan 12
to
Well, you know how to read the contents of a cell, and how to put items
into a dict (the key will be the cell contents and the value will be the
column number).

The column numbers will go from 1 to sheet.last_column, although some of
them might be empty (their value will be None), which you can remove
from the dict afterwards.

NArshad

unread,
Jan 13, 2022, 1:44:15 PMJan 13
to

- “if you are deploying to something like Heroku for the application -- the Excel file will have to be deployed also, and no one except your application will be able to see it there. Under this situation, there is no reason/excuse to keep the data in the very inefficient format you've defined in the most recent message. Import into some supported database and normalize the data to make updates easier.”

Ok with deploying the Excel file also. Next time I will switch to another database.



-“Well, you know how to read the contents of a cell, and how to put items into a dict (the key will be the cell contents and the value will be the column number).”

No, I don’t know. I tried but still the same.



-“The column numbers will go from 1 to sheet.last_column, although some of them might be empty (their value will be None), which you can remove from the dict afterward.”

No need to do with the whole spreadsheet only the first column is enough

Dennis Lee Bieber

unread,
Jan 13, 2022, 3:23:16 PMJan 13
to
On Thu, 13 Jan 2022 10:44:01 -0800 (PST), NArshad <narsh...@gmail.com>
declaimed the following:

Please arrange to use some client that does proper quote attribution.
It gets difficult to read these when you have snippets from multiple posts
with no attribution of who wrote the snippet, and when it was posted, etc.

>
>- “if you are deploying to something like Heroku for the application -- the Excel file will have to be deployed also, and no one except your application will be able to see it there. Under this situation, there is no reason/excuse to keep the data in the very inefficient format you've defined in the most recent message. Import
into some supported database and normalize the data to make updates easier.”
>
>Ok with deploying the Excel file also. Next time I will switch to another database.
>

There is still that lingering question of who can see the data. Once
deployed, the only way to access that Excel file will be either by the
owner of the Heroku instance (you -- using the deployment process; FTP? I
presume if you can upload, you'll be able to download) or via your
application.

As you've described this system, the only thing your application will
do is record "check-outs" by tracking available copies of books, and the
name (and dates?) of the person doing the check-out. There is no reporting
function -- not even a print-out for the user of which books they've just
reserved, so how does anyone actually verify they are to receive a physical
book after reserving it. Is there any logic to prevent someone from
reserving multiple copies of a book... You'll also need logic to ensure
that multiple borrowers connecting to the application at the same time
can't both reserve the same copy of any book, or that one borrower doesn't
block others from reserving other books at the same time. Spreadsheets are
not "multiple user" files, and if each session (borrower connecting to the
application) results in the application trying to open the spreadsheet
you'll have conflicts.


>
>
>-“Well, you know how to read the contents of a cell, and how to put items into a dict (the key will be the cell contents and the value will be the column number).”
>
>No, I don’t know. I tried but still the same.
>

Tried what?

All throughout this thread you have never shown us a sample of code
you've written. When asking for help about a Python feature, It is common
courtesy to provide a minimal complete example (ie, something that readers
can cut&paste into their environment to test) that reproduces whatever
problem one is having.

Other posters have provided snippets of how to read from an Excel file.

If you don't know how to put stuff into a dictionary ("dict") structure
you need to step back from this application and study a Python tutorial as
dicts, lists, tuples are core data structures available in the language
(whereas in my ancient days, we'd have had to learn how to code a
dictionary from scratch -- look up "hashed head multiply linked list"
someday <G>)



>
>
>-“The column numbers will go from 1 to sheet.last_column, although some of them might be empty (their value will be None), which you can remove from the dict afterward.”
>
>No need to do with the whole spreadsheet only the first column is enough

Really? Per your earlier post

-=-=-=-
On Mon, 10 Jan 2022 22:31:00 -0800 (PST), NArshad <narsh...@gmail.com>
declaimed the following:

>The column headings are:
>BOOK_NAME
>BOOK_AUTHOR
>BOOK_ISBN
>TOTAL_COPIES
>COPIES_LEFT
>BORROWER’S_NAME
>ISSUE_DATE
>RETURN_DATE
>
-=-=-=-

... the first column is just "book names"... Don't you need to access the
various copy count columns along with borrower names (and dates)?

The purpose of the dictionary is to map your column headings to the
actual spreadsheet access (either "A".."<whatever>" letters, or numeric
1..<whatever> indices). That way, later in the code, instead of having to
keep track of cryptic column IDs ("I need the 'copies_left' column -- is
that column 5 or 6?") you'd use, say, column_dict["COPIES_LEFT"] where the
dictionary has something like
{ ..., "COPIES_LEFT" : 5, ... }

Dennis Lee Bieber

unread,
Jan 13, 2022, 3:49:01 PMJan 13
to
On Thu, 13 Jan 2022 15:22:50 -0500, Dennis Lee Bieber
<wlf...@ix.netcom.com> declaimed the following:

Talking to myself in public again... Bad habit...

> As you've described this system, the only thing your application will
>do is record "check-outs" by tracking available copies of books, and the
>name (and dates?) of the person doing the check-out. There is no reporting
>function -- not even a print-out for the user of which books they've just
>reserved, so how does anyone actually verify they are to receive a physical
>book after reserving it. Is there any logic to prevent someone from
>reserving multiple copies of a book... You'll also need logic to ensure
>that multiple borrowers connecting to the application at the same time
>can't both reserve the same copy of any book, or that one borrower doesn't
>block others from reserving other books at the same time. Spreadsheets are
>not "multiple user" files, and if each session (borrower connecting to the
>application) results in the application trying to open the spreadsheet
>you'll have conflicts.
>

Perhaps
https://data-flair.training/blogs/library-management-system-python-project/
can provide some clues -- though it is tkinter based, not a web
application... (Though they don't seem to be tracking issue/due dates <G>).

Or, for a web application (using Django, not Flask) see
https://techvidvan.com/tutorials/python-library-management-system/

If you need more examples to purloin, rather than developing your own
implementation, just hit Google...
https://www.google.com/search?q=python+library+reservation+system which is
how I found the above two examples.

Chris Angelico

unread,
Jan 13, 2022, 4:25:20 PMJan 13
to
On Fri, Jan 14, 2022 at 7:56 AM Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
>
> On Thu, 13 Jan 2022 15:22:50 -0500, Dennis Lee Bieber
> <wlf...@ix.netcom.com> declaimed the following:
>
> Talking to myself in public again... Bad habit...

Not as bad as singing choruses in public, which - or so I'm told, by a
mad girl in opera - is quite, quite mad.

ChrisA

Dennis Lee Bieber

unread,
Jan 13, 2022, 4:37:35 PMJan 13
to
On Fri, 14 Jan 2022 08:24:43 +1100, Chris Angelico <ros...@gmail.com>
declaimed the following:
I usually put "eighth notes" (emulated as o/~) around the "talking to
myself in public"...
https://www.google.com/search?q=talking+to+myself+in+public+lyrics

Avi Gross

unread,
Jan 13, 2022, 6:08:48 PMJan 13
to

I am not replying to anything below so I have removed it.
So I need to remind people of the topic and how it has wandered.
Someone has data in a not particularly great format in an EXCEL spreadsheet. They want to somehow use an external language like Python to manipulate the contents from outside and keep the results inside the same or perhaps another EXCEL file.
Now EXCEL has had programming available for a long time within the spreadsheet and perhaps some of that was once used within the actual spreadsheet and stored as macros or whatever using something like VBA. Or perhaps someone used to update by hand.
So the suggestions to copy the data ONCE into something else (a CSV, database, whatever) that can be used externally are reasonable but seem to be resisted. I would guess some of the "missing" functionality others wonder about such as making reports, may still be available within other sheets or parts of the EXCEL .xlsx or other format file.
An interesting third option that has not been mentioned would be to port the data into another application like Google Sheets which has a fairly similar spreadsheet format with very different internals but many ways for a program to read and adjust the contents such as Google Apps Script that can also do things like send out emails and so on.
Instead, someone suggested Python which indeed, with lots of work, can open just about ANY nonsensical file and diddle around and rewrite it, but WHY? Unless you find the right modules and use them carefully, it seems the hard way to go. Sure, you can in theory do it but the amount of learning and work involved seems excessive  for what you get. And when you are done with what was requested here, you probably need to add other features that will again be painful. If you were hiring someone with experience, you might be paying more and more and end up with a package hard for anyone else to understand, let alone maintain and expand.
An intermediate solution seems to be to copy the darn data from EXCEL and modifies it to be easy to use and make changes  but make available functionality that saves the data when needed back into an EXCEL file, but most of the time manipulates it the easy way. 
As such, if the request continues to be to do it the hard way, i may be among those who ignores this thread any further.

dn

unread,
Jan 13, 2022, 6:23:51 PMJan 13
to
On 14/01/2022 09.48, Dennis Lee Bieber wrote:
> On Thu, 13 Jan 2022 15:22:50 -0500, Dennis Lee Bieber
> <wlf...@ix.netcom.com> declaimed the following:
>
> Talking to myself in public again... Bad habit...

Recommend that you not start any arguments then
- they will be unwinnable!

--
Regards,
=dn

Greg Ewing

unread,
Jan 13, 2022, 11:20:04 PMJan 13
to
But at least they'll be free of charge!

--
Greg

Dennis Lee Bieber

unread,
Jan 14, 2022, 11:34:57 AMJan 14
to
On Thu, 13 Jan 2022 23:08:19 +0000 (UTC), Avi Gross <avig...@verizon.net>
declaimed the following:

>So the suggestions to copy the data ONCE into something else (a CSV, database, whatever) that can be used externally are reasonable but seem to be resisted. I would guess some of the "missing" functionality others wonder about such as making reports, may still be available within other sheets or parts of the EXCEL .xlsx or other format file.

Ignoring the data store itself, the OP has also stated (if poorly) that
this application is supposed to be hosted on Heroku (I suspect this is an
assumption they made, as that minimal "Hello World" Flask tutorial they are
using gives instructions for a limited free/hobbyist Heroku instance). If
this application is really for some client (the book repository staff) one
would think they'd have specified the web hosting system in advance.

That (web hosting) compounds the problem of the lack of reporting...
The defined application is essentially "write-only" -- in that the only
operation available is to reserve/check-out book(s) based upon availability
count. There is no means provided by which these reservations are passed on
to whoever is responsible for physically handing out books, nor to track
who has received their copy. Are any/all "repository" employees supposed to
access the spreadsheet? How? Do they all have the account/password to the
Heroku (or other web hosting service) in order to FTP the data down for
local usage (how many such copies will there be, and how are they
synchronized).

Or is the CONOPS that only the repository employees access the
application, on behalf of the person making the reservation/check-out
request -- and hence the application is merely recording information of who
has physically received a book (in which case the overhead of tracking
availability seems superfluous, as the staff would see that the table(s) of
books does not have the title, so it is unavailable for handing over).

NArshad

unread,
Jan 15, 2022, 5:38:48 AMJan 15
to
What Dennis Lee is saying I will see to it later.

Right now what mrabarrnett is saying is of use.



Why does the code written below is not giving any output?

xls = ExcelFile('ABC.xlsx')
df = xls.parse(xls.sheet_names[0], index_col=1)
x=df.to_dict()
print (x)

Only the contents of the first column and the column number is required in the dictionary

Mats Wichmann

unread,
Jan 15, 2022, 3:05:38 PMJan 15
to
On 1/13/22 16:08, Avi Gross via Python-list wrote:
>
> I am not replying to anything below so I have removed it.

> Instead, someone suggested Python which indeed, with lots of work, can open just about ANY nonsensical file and diddle around and rewrite it, but WHY?

well, the topic *was* raised on a PYTHON list...


Avi Gross

unread,
Jan 15, 2022, 4:25:08 PMJan 15
to
Mats,
Yes, this is a Python mailing list and I welcome people interested in doing something in Python who need a little help or advice but have some idea of what they are doing and present us with enough info more than "something does not work."

Yes, the topic was raised on a Python list but I did not get the impression the person asking necessarily knew a lot about Python or maybe other languages. I may well be wrong. They seem to have been handed something to work on and maybe are searching for some way to do it. You can do what was asked in plenty of languages, some easier and some harder. But some are better set to do things in a browser/server model than others.
One impression is they were suggested to use a web platform of sorts and from there went to Python as compatible for the server side. But the overall tone suggests to me that they want to be given a quick solution for a problem they do not really understand, let alone the tools they want to use. And I do not perceive much willingness to take the time to do much learning. My willingness to help and my time to do so, are limited and I said so. Others, feel free. The reality is many here want to be hired for large and complex jobs.
If someone had suggested to them to use JavaScript written inside a web page to read the file and do searches, and forgot to tell them that writing the file back onto some server might not be trivial, I wonder if they would have gone in another direction.
Again, these are my impressions and why I wondered if there was a GOOD reason they asked about Python. My experience in the past includes seeing posts by people who sent messages to multiple groups I was on for multiple languages and asked how to do something. They just were hoping someone would post complete code and they could then move on without learning anything. Maybe I am a tad jaundiced and prefer honest requests from people willing to listen to what feedback we provide and show some code for us to look at and perhaps help improve.

-----Original Message-----
From: Mats Wichmann <ma...@wichmann.us>
To: pytho...@python.org
Sent: Sat, Jan 15, 2022 3:05 pm
Subject: Re: What to write or search on github to get the code for what is written below:

--
https://mail.python.org/mailman/listinfo/python-list

Cameron Simpson

unread,
Jan 15, 2022, 4:38:29 PMJan 15
to
On 15Jan2022 02:38, NArshad <narsh...@gmail.com> wrote:
>Why does the code written below is not giving any output?
>
> xls = ExcelFile('ABC.xlsx')
> df = xls.parse(xls.sheet_names[0], index_col=1)
> x=df.to_dict()
> print (x)
>
>Only the contents of the first column and the column number is required
>in the dictionary

The code above _must_ produce some output, even if it is very short, or
an error traceback. Is "x" an empty dictionary? Are you getting an
exception? What is in ABC.xlsx, at least in part? What modules are you
importing in order to get the name "ExcelFile"? I'm guessing pandas, but
that is from a web search.

Cheers,
Cameron Simpson <c...@cskk.id.au>

Mats Wichmann

unread,
Jan 16, 2022, 10:42:53 AMJan 16
to
On 1/15/22 14:24, Avi Gross via Python-list wrote:
> Mats,
> Yes, this is a Python mailing list and I welcome people interested in doing something in Python who need a little help or advice but have some idea of what they are doing and present us with enough info more than "something does not work."
>
> Yes, the topic was raised on a Python list but I did not get the impression the person asking necessarily knew a lot about Python or maybe other languages. I may well be wrong. They seem to have been handed something to work on and maybe are searching for some way to do it. You can do what was asked in plenty of languages, some easier and some harder. But some are better set to do things in a browser/server model than others.

I only meant, in response to "someone suggested" in the quoted snip
below, since it was asked here, someone suggesting Python was a pretty
expected outcome...

Dennis Lee Bieber

unread,
Jan 16, 2022, 11:52:09 AMJan 16
to
On Sat, 15 Jan 2022 02:38:34 -0800 (PST), NArshad <narsh...@gmail.com>
declaimed the following:

A bit of an improvement -- actual code...

But... still not a minimal /runnable/ example...
>
>Why does the code written below is not giving any output?
>
> xls = ExcelFile('ABC.xlsx')

Where is "ExcelFile" defined? Best match I could find after spending
time with Google is the pandas package. Pandas is likely overkill for this
situation -- being optimized for analysis of numerical tabular data. In
particular, it expects each column to be of one data type, not mixed data
types, in order to apply aggregate functions (min, max, mean, std.dev.,
etc.) to the columns.

> df = xls.parse(xls.sheet_names[0], index_col=1)

Based upon documentation, pandas.ExcelFile.parse() internally uses the
openpyxl package for files of type .xlsx -- for this application openpyxl
is probably all that is needed.

Documentation for .parse() (actually the underlying .read_excel()
operation) indicates that the desired sheet can be specified by name OR BY
POSITION -- with the default value being "0" [first sheet]. It is somewhat
perverse to be retrieving a list sheet names from the file, only to then
pass the first name back into the function which has to match the name up
against the list to determine the position... Unless the file is using
multiple sheets for data -- in which case you need some logic to select
something other than the first sheet -- you could leave that argument off
and let it default.

index_col=1 says to use the SECOND COLUMN of the sheet to provide
row-labels for the "dataframe" (unless documentation states otherwise, the
first item of a collection is "0", not "1")

> x=df.to_dict()

What do you really expect from this conversion? With no arguments it
will produce a nested dictionary of the form

{ "column1name" : { "row1name" : r1-c1-value ,
"row2name" : r2-c1-value },
"column2name" : { "row1name" : r1-c2-value,
"row2name" : r2-c2-value } }

> print (x)
>
>Only the contents of the first column and the column number is required in the dictionary

You won't get that with .to_dict() -- it uses the column names for the
major grouping, and then uses the row names (which are already garbage as
you told pandas to use the second column for row names) to select the VALUE
stored in that row for that column. Also, while Python implementation of
dictionaries has changed over the years, the theoretical basis (associative
mapping, hash table) does not define an order for the keys of the
dictionary. Unless you are certain of how a dictionary handles
insert/delete of keys, you can not assume the first key out was the first
key that went in.

It would also be helpful to have a snippet of the spreadsheet -- say
the first five or six rows, along with the first five or six columns;
exported as a CSV file (binary attachments get stripped from this forum,
but a CSV is text and can be pasted in -- 5x5 should be small enough that
those trying to assist can then extract it from the post, clean up any line
wrappings, and import into a blank spreadsheet.

NArshad

unread,
Jan 17, 2022, 4:55:28 AMJan 17
to
Avi Gross:

-“They just were hoping someone would post complete code and they could then move on without learning anything.”

This is due to the time factor

Chris Angelico

unread,
Jan 17, 2022, 10:51:55 AMJan 17
to
Then pay someone to write it. There are plenty of contractors out
there. You can't expect this list to be a free consulting programmer
for you.

I strongly encourage everyone to stop helping the OP until there's
some code to help with.

(I'm not surprised, given that the original request was basically
"what should I search for to magically find what I want".)

ChrisA

Dennis Lee Bieber

unread,
Jan 17, 2022, 11:42:18 AMJan 17
to
On Tue, 18 Jan 2022 02:51:22 +1100, Chris Angelico <ros...@gmail.com>
declaimed the following:

>
>I strongly encourage everyone to stop helping the OP until there's
>some code to help with.
>
Well, we did squeeze about four lines of code from the OP -- though
still incomplete (no import statements so we have to research what 3rd
party module the OP might be invoking -- and they seem to have totally
skipped direct usage of an module for reading Excel format files in favor
of loading the massive pandas package [with its need for those direct
reading modules along with SciPy/NumPy -- does it also load matplotlib?]).

>(I'm not surprised, given that the original request was basically
>"what should I search for to magically find what I want".)

To which the closest viable response I've extracted is to Google

python library management system

(presuming Python is a firm requirement). Of course all of those have the
problems

1A They use an RDBM (most examples seem to be MySQL based,
though SQLite3 might show up in some)
1B No sign of Excel spreadsheets anywhere

2A They handle adding/removing books from the inventory
(permanent actions), along with
2B Check-out/Check-in of books from the inventory (OP seems to
only focus on Check-out/Reservation)
2C They may even track who is allowed to run the application
(staff only... for all functions or just some, etc.)

3 Every copy of a book is an individual record in the
database -- if there are 30 copies of a book, there are 30 records for it,
and each copy can only have one check-out at a time; no "decrement" of a
"number available".

4 Some are local/desktop applications using Qt or similar GUI
framework, others might be web-based but likely not Flask


{And, from my personal viewpoint, they may be using SQLAlchemy... I
have the O'Reilly text for that, and find it still makes no sense to me...
I can code direct SQL queries faster than trying to figure out the
equivalent functionality in SQLAlchemy... And if the OP has a fixation on
spreadsheet views of the data, SQLAlchemy will really be a mystery.

Avi Gross

unread,
Jan 17, 2022, 12:47:07 PMJan 17
to

I can appreciate people under time pressure wanting the job DONE first and maybe learning more after. So, yes, it makes perfect sense to delegate the task to others with expertise or ask for advice.
This forum may mean many things to many people but for me, it is a place to offer guidance and small amounts of help as a voluntary activity. Doing a major project for free is not necessarily why I am here. I prefer being hired and compensated, LOL!
But I share the frustration when someone dumps an ill-defined project on you without realizing what they handed you is an old application that cannot trivially be shifted into something new without serious effort and preparation.
The feedback here has included suggestions that we were shown only a part of the task needed and that it would be a good idea to not even try to keep your data in some EXCEL format if it was used heavily in ways perhaps never intended. Obviously, feeling urgency to do something quickly will make it hard to accept our suggestions, but nonetheless, a request here, as has been pointed out, should anticipate a python-oriented answer. Asking us here to make a set of macros for EXCEL to do this task would be inappropriate. Some are suggesting that a solution that keeps reading and writing to an EXCEL file for each transaction is also not necessarily a great design.
I think your answer makes me amend what I wrote. I grant you may be interested in learning but due to the press of time, you want a working set of software first.
Good luck with that. 

-----Original Message-----
From: NArshad <narsh...@gmail.com>
To: pytho...@python.org
Sent: Mon, Jan 17, 2022 4:55 am
Subject: Re: What to write or search on github to get the code for what is written below:

--
https://mail.python.org/mailman/listinfo/python-list

Dennis Lee Bieber

unread,
Jan 17, 2022, 3:39:50 PMJan 17
to
On Mon, 17 Jan 2022 01:55:15 -0800 (PST), NArshad <narsh...@gmail.com>
declaimed the following:


>This is due to the time factor

And that opens another can of worms... Before this "assignment" was
given to you, surely someone made some sort of estimate of how long it
would take to produce, taking into account your (apparent) lack of
familiarity with any of the technology required.

Python, Flask, and web-hosting (on Heroku) all appear to be a result of
stumbling onto a "Hello World" tutorial that does no persist ant data
storage and relies upon a free/hobbyist level account on the hosting
provider -- an obvious flag that this is not a well thought out project for
some actual customer/employer (it might qualify as an internship assignment
at some school looking to automate the reservation/check-out of text
books). The lack of understanding in how Python dictionaries are
manipulated doesn't leave one impressed either...

In one respect, given the limited functionality stated, one gets the
impression of a class GROUP assignment, in which the individual functions
were divvied up to each group member to work, with pretty much no analysis
of suitable data storage, etc. (You only gave some sort of CONOPS for
"users" to "reserve" books; no CONOPS for unreserving books, an implication
that after reserving books the user will go to some desk [in library, in
store {unlikely if there is a return date for books}, some warehouse] and
receive a copy of the reserved book -- but no CONOPS for how this deck
validates that the user has reserved any particular book [no report printed
by user of reservations made during a session, no search function for desk
staff, etc.).

The Excel requirement comes across as "this is how we've been doing it,
and we have a lot of data in the file we don't want to lose". In other
words -- one should have defined a migration path to replace the Excel
spreadsheet with something suited to a multi-user and/or web-based
application. That, or someone in the group has squashed any discussion on
data storage to force their biased viewpoint. (I've been on the fringe of
one such: in a department with over 80 VAX FORTRAN-77 programmers [some of
us quite skilled in working the internals of the language] and some 20-30
PDP Macro-11 programmers, the Macro-11 group was tasked with porting their
application to VAX systems. One person held out for the port to be done in
VAX Pascal "because colleges were teaching Pascal" at the time [TurboPascal
in all likelihood] totally ignoring the massive F-77 expertise available;
he also is rumored to have threatened to leave if Pascal was not chosen and
management caved-in. Even though VAX Pascal was much different from
TurboPascal [no development environment, though it did support separate
compilation and linking], it did not have advanced math
operations/functions -- just the ones found in Jensen&Wirth, try doing
orbital computations with a subset of trig functions -- requiring
programmers to import the F-77 runtime library for the missing functions. A
few years later the manager confessed that submitting to Pascal was a
mistake -- it didn't take long for the VAX Pascal project to be ported to
HPUX workstations using C and X-window system).


I also note that during the course of this thread, the spreadsheet
appears to have gone from a somewhat logical "one-row per title"

TITLE | AUTHORs | INVENTORY | AVAILABLE ...
A Book Someguy 10 9 ..
Book 2 Who Dat 100 100 ...
Popular Madonna 30 1 ...

to the illogical "one-column per title"

TITLE A Book Book 2 Popular
AUTHORs Someguy Who Dat Madonna
INVENTORY 10 100 30
AVAILABLE 9 100 1
...

Consider that Excel only supports some 16K columns, but 1M rows, and
expanding each column to show, say a 200 character title (there are some
long book titles in the world) results in only two or three books being
visible at a time... And there is no "side scroll" page key. In contrast
the first layout would only need one column to be expanded, the rest can be
narrower, and one can use page-up/page-down to scroll through a lot of
books at a time. Oh, and while a cell can hold some 4K text -- Excel itself
can only display the first 255 of those. That's going to put a limit on how
many names you can fit in the reserved-by column (about 10 25char names)
and easily find while scrolling. More than that and you have use "Find"
operations from the menu.

Heroku-Specific note: a recent web-page I encountered searching for
information for a different question indicates that Heroku does not support
SQLite3 and, by extension, ANY file-based dynamic data storage (so, no
Excel files either). An Heroku Python "dyno" [virtualized work container]
is initialized using a requirements file -- that file basically lists all
the 3rd party Python modules that need to be installed using "pip". If the
"dyno" is stopped (application shut down to upload modifications, or maybe
just to remove an application that has been idle for some hours) it, and
any data files, are just deleted -- when someone tries to connect to the
app again, a new "dyno" is spun up and loaded from scratch. One is supposed
to use one of the client server databases provided by Heroku for persist
ant storage (since those are not inside "dyno" container -- the app makes a
network connection to the database server.

Chris Angelico

unread,
Jan 17, 2022, 5:08:03 PMJan 17
to
On Tue, Jan 18, 2022 at 7:44 AM Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
> Heroku-Specific note: a recent web-page I encountered searching for
> information for a different question indicates that Heroku does not support
> SQLite3 and, by extension, ANY file-based dynamic data storage (so, no
> Excel files either). An Heroku Python "dyno" [virtualized work container]
> is initialized using a requirements file -- that file basically lists all
> the 3rd party Python modules that need to be installed using "pip". If the
> "dyno" is stopped (application shut down to upload modifications, or maybe
> just to remove an application that has been idle for some hours) it, and
> any data files, are just deleted -- when someone tries to connect to the
> app again, a new "dyno" is spun up and loaded from scratch. One is supposed
> to use one of the client server databases provided by Heroku for persist
> ant storage (since those are not inside "dyno" container -- the app makes a
> network connection to the database server.

This is correct. I've used Heroku for a number of projects, since it's
easy to deploy there and can be done for zero dollars. If you know how
to use PostgreSQL from Python, it's almost trivially easy to deploy
that to Heroku (just need to arrange your credentials, nothing more).
In contrast, manipulating external files is tricky; in fact, if you
want to do something simple like "upload an image to use as your
avatar", it's probably easier to store that in Postrges than on the
file system.

Part of the reason for the "wipe everything on shutdown" policy is
that it makes Heroku apps extremely easy to scale. You want one
process? Done. Want two dynos so you can serve more clients at once?
Easy. But for that to work, you have to make absolutely sure that
everything is stored externally to the dyno itself.

Like everything else, it requires discipline, a set of rules that you
follow. (And it's a lot easier than some - for instance, the
discipline required for a live-code-update system is a lot harder.)
But while it isn't particularly difficult, it does require some
comprehension. Some time investment. Some actual effort. And that's
what the OP might be running into difficulties.

(For what it's worth, we used Heroku at my last teaching job; it's a
great way to ensure that every student is able to deploy projects, and
since it doesn't cost anything to have a bunch of projects up there,
it doesn't hurt to deploy apps that don't have any "real-world use".
Originally we taught them to use MongoDB, which requires external
hosting, but switched that out for PostgreSQL, which is ever so much
better to work with.)

ChrisA

NArshad

unread,
Jan 18, 2022, 10:37:21 AMJan 18
to
Avi Gross:

What does the website "https://mail.python.org/mailman/listinfo/python-list" do?

Can I use this for the discussions which I require?

Dennis Lee Bieber

unread,
Jan 18, 2022, 2:44:34 PMJan 18
to
On Tue, 18 Jan 2022 07:37:07 -0800 (PST), NArshad <narsh...@gmail.com>
declaimed the following:

>Avi Gross:
>

Not Avi Gross, but that is partly because you replied to Chris
Angelico, who was replying to my post replying to an earlier one of
yours...

>What does the website "https://mail.python.org/mailman/listinfo/python-list" do?
>
>Can I use this for the discussions which I require?

It is most likely the same forum...

The Python mailing list (which does get spam filtered, unlike the
Usenet newsgroup, so doesn't see as much junk injected via Google) is
gatewayed with Usenet comp.lang.python. comp.lang.python is what Google
gateways for its Python group. Anything posted on the Google Python group
is seen by comp.lang.python, and from there gets seen by the Python mailing
list (and things go the other way also). The only difference is message
management -- email vs news reader vs whatever Google's interface of the
week inflicts, and how much spam comes through. For me, mailing lists take
too much management to set up filters to file mailing list traffic to a
special mail box (and then if I need other filters, to ensure they apply
before or after the mail box filing) whereas a decent news reader
automatically files messages by their group.









And you really need to find a client that follows (or properly use the
one you have) email/netnews /threading/ and attribution conventions. Avi
Gross's post was on a different path (there was a three-way split from your
earlier post).

If you are going to be explicitly asking questions of a person, that
post should be a follow-up (reply) to the nearest relevant post made by
that person -- not just tacked onto the end of the posts that came in
during the day with all content removed and ad hoc comments inserted.

Dennis Lee Bieber

unread,
Jan 18, 2022, 4:18:21 PMJan 18
to

o/~ talking to myself in public o/~

On Mon, 17 Jan 2022 15:39:25 -0500, Dennis Lee Bieber
<wlf...@ix.netcom.com> declaimed the following:


> In one respect, given the limited functionality stated, one gets the
>impression of a class GROUP assignment, in which the individual functions
>were divvied up to each group member to work, with pretty much no analysis
>of suitable data storage, etc. (You only gave some sort of CONOPS for
>"users" to "reserve" books; no CONOPS for unreserving books, an implication
>that after reserving books the user will go to some desk [in library, in
>store {unlikely if there is a return date for books}, some warehouse] and
>receive a copy of the reserved book -- but no CONOPS for how this deck
>validates that the user has reserved any particular book [no report printed
>by user of reservations made during a session, no search function for desk
>staff, etc.).

I'm in a fey mood today... The OP (and the rest of you reading) will
likely just skip this message... It's a rambling free-association harangue
on a possible RDBM schema -- which makes a few assumptions (described
following) and looks wildly different from the OP's vague data...

Inferred from various posts is that this
* NOT a library (which may have at most 5 copies of popular
books)
* NOT a book store (which would not have check-out/check-in
dates)

The best use case I can see is for something like a high school in
which textbooks are issued to students at the start of the course, and
collected at the end. For a small high school, say 600 students, there
might be 150 taking, say 3rd year Trigonometry, split among 6 session of 25
students each day. This justifies not tracking every copy of a book by full
title, et al.

I'm going to take that as the overall system CONOPS.

Needed data: book title, book author(s), ISBN and/or other call number
(some texts may be old enough to predate ISBN), # copies, # available,
dates for check-out and return, student name. Probably more as I go down...

Schema format notation:

relation(attribute list)
special constraints description

where
*attribute* is a non-null, unique index, primary key (preferably
autonumber)
_attribute_ is a non-null, duplicates allowed index, foreign key (in
the form relation-attribute)
/attribute/ is an optional/alternate indexed attribute

Publisher(*ID*, name)

Book(*ID*, ISBN, alt-call, title, _Publisher-ID_, copyright-date)
constraint(ISBN not null OR alt-call not null)

Author(*ID*, /last-name/, first-name)
constraint(last-name not null)

Book-Author(*ID*, _Book-ID_, _Author-ID_)
{this links authors to books, and allows for books to have more than
one author}

Copy(*ID*, _Book-ID_, copy-number, status, condition)
constraint(status in "A", "O", "R", status not null, default "A")
constraint(copy-number not null, [Book-ID copy-number] is unique)
{A = available, O = checked out, R = reserved but not yet checked
out}
{condition contains notes on known damage, etc. for this copy}

Student(*ID*, /last-name/, first-name)
constraint(last-name not null)
{I expect this to expand with student ID number, home address,
etc.)

CheckOut(*ID*, _Copy-ID_, _Student-ID_, reservation-date, checkout-date,
due-date, active)
constraint([Copy-ID Student-ID] is unique)
constraint(active not null boolean default true)
{can automate cancelling reservations after n-days, and can also be
used to produce a list of past-due}
{active is used to allow for history of check outs, set to false
when book is returned (and hence made Available again)

SEVEN relations (aka "tables") in an RDBM. While each book /title/
appears only once, it does take a small record to identify each copy of the
book and track availability of the copy. Reserving a book requires
transaction to select the first copy with status = "A" for that title,
update status to "R", insert a checkout record with the currently selected
student and current date for reservation date. When actually given the
book, a transaction to find the active checkout record for the student, for
that title is done, the checkout and due dates are set, the copy record is
updated for status = "O". When returned, a transaction is done to locate
the checkout record, update active to false, update Copy record to set
status to "A", and optionally update the condition field to indicate
damages inflicted by the student while it was checked out. If a reserve
operation fails (another session snagged the same copy record and updated
it) one just repeats the transaction trying for the next available copy
record -- only if there are no copies with status "A" do you have to abort
and tell the student they can't have the book.

There is no "count" of copies, no "count" of available -- these can be
computed based upon SQL cnt() functions; untested

select b.title as Title,
cnt(cpy1.copy_number) as Total,
cnt(cpy2.copy_number) as Available
from Book as b
inner join Copy as cpy1
on b.ID = cpy1.Book-ID
left join Copy as cpy2
on cpy1.ID = cpy2.ID and cpy2.status = "A"
group by b.title
order by b.title;

Avi Gross

unread,
Jan 18, 2022, 11:17:56 PMJan 18
to
I do not manage any python lists or have any say in how they run so I have no idea why I am being asked by name below, as Dennis pointed out.
So I won't reply on whatever I am being asked, but want to point out that many forums may be asked questions and some people on the forum will not respond or will not accept a user that bombards with too many questions or requests for more detailed answers and especially when not given enough but appropriate information.
Not everything in life is free. Python as a free language is but the expertise in ways to solve specific problems using Python, let alone EXCEL, that you want, often is not.
Simple requests like how to read in data from a format like .CSV or a tab in .XLSX files can be easily answered, of course. But correcting what sounds like a horrible data storage without a redesign is often not of much interest to others.
I think this group has already spent way too much time on whatever this issue is and provided lots of useful advice which apparently does not get taken. So don't pull me in again. I have moved on.


-----Original Message-----
From: Dennis Lee Bieber <wlf...@ix.netcom.com>
To: pytho...@python.org
Sent: Tue, Jan 18, 2022 2:44 pm
Subject: Re: What to write or search on github to get the code for what is written below:

--
https://mail.python.org/mailman/listinfo/python-list

NArshad

unread,
Jan 20, 2022, 5:59:53 AMJan 20
to

- I will try to follow all that you people are saying, but it will take time or next time. The chance of concurrency is very less. Kindly don't write big descriptions.

- The weblink Avi Gross has given is very useful.






NArshad

unread,
Jan 22, 2022, 5:22:25 AMJan 22
to

The user is going to enter the book name as the input of an HTML form on a website and I have to check whether the book is present or not in the Excel table. openpyxl preferred pandas is also fine anything left. Case sensitivity is not required. I tried to find code or tutorial on google search for all this but no use that's why..................

This time the choice of HTML is right or not??

Dennis Lee Bieber

unread,
Jan 22, 2022, 12:45:31 PMJan 22
to
On Sat, 22 Jan 2022 02:22:14 -0800 (PST), NArshad <narsh...@gmail.com>
declaimed the following:

>
>The user is going to enter the book name as the input of an HTML form on a website and I have to check whether the book is present or not in the Excel table. openpyxl preferred pandas is also fine anything left. Case sensitivity is not required. I tried to find code or tutorial on google search for all this but no use that's why..................
>
>This time the choice of HTML is right or not??

HTML is the core basis for any web-site presentation -- no HTML, no
web-page.

Interaction with a displayed web-page is via... CGI (individual scripts
that process returned data -- somewhat slow as originally each invocation
requires a full server process startup and shutdown; advances were made to
try to keep a single process running for multiple invocations), AJAX
(Javascript running in the browser making internet requests and modifying
the "document object model" [DOM] in the browser to update the page without
hitting the server for a full update transfer).

Flask, Django, and some others are packages to consolidate what had
been static pages and CGI into a framework that handles an entire "site" --
via templates for look&feel, session management (and cookies) so that
interaction can be tracked to users (HTTP is a fire&forget system -- every
URL sent to the server is treated as a completely separate request).

Above those frameworks are things like Zope -- so-called content
management frameworks.


And again... You will not find anything like you want... NOBODY is
going to write a web application using a spreadsheet as the primary data
storage. A spreadsheet, and custom transformation code, MIGHT be used to
initially populate a database. (M$ SQL Server Integration Services is a
whole system for defining import/transformation/clean-up "functions" for
data sources to data base). A spreadsheet might be available as a
report/extraction format from the database.

Using a web server means you have to be able to handle (near)
simultaneous requests from multiple users and be able to keep those
interactions distinct. That is going to require you to implement some sort
of access control for a spreadsheet, since spreadsheets are single-user
entities (you might get away with shared reading as long as you never need
to update any field in the spreadsheet; as soon as you need to update, you
need to be able to lock records so only one session can update it, and you
need to have a way for other sessions to detect such changes and update
that sessions display for review). On possibility might be to write a
separate process wherein only that process opens the spreadsheet -- all the
web-page stuff will have to generate complete I/O requests to the
spreadsheet process, it will make the changes, and return whatever data is
applicable ("complete" meaning that, if you need to change three fields in
a record, all three commands are provided as ONE request to the spreadsheet
process, and it does all the changes in the request before it goes on to
read the next request). This still falls short of detecting overlapping
changes -- two users want to do something with the same record; they each
read the record, then send commands to change the same field. Which ever
one is received first should complete, and the other needs to be rejected
and resubmitted.



Show us code you've written, and we can assist in debugging it. But you
couldn't afford to have any of us write the application for you! (To cover
insurance, taxes, etc. an independent contractor would probably charge you
$100+ an HOUR -- and the first hours look like they'd be spent getting
detailed requirements from you, discussing design problems [spreadsheet as
data storage], before even getting to any coding. That's real-world
software engineering.)

If you are doing a web application, how are you going to host it? Who
is responsible for managing the web server? Domain name? Firewalls?
Certificates if you need HTTPS rather than plain insecure HTTP.

I have a Raspberry-Pi with Nginx serving static pages over insecure
HTTP as I've never applied for a certificate -- using a dynamic DNS
service. It is not suited for high-demand as it is behind my ISP router,
and my uplink rate is only a tenth of my downlink rate (which isn't the
fastest thing out there to begin with [Ugh -- Hope it's the weather -- my
downlink is down to 10Mbps, when nominal is closer to 14Mbps]). Someday I
may try creating a Flask application for it, just for learning.

Chris Angelico

unread,
Jan 22, 2022, 3:56:35 PMJan 22
to
On Sun, 23 Jan 2022 at 07:47, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
> If you are doing a web application, how are you going to host it? Who
> is responsible for managing the web server? Domain name? Firewalls?
> Certificates if you need HTTPS rather than plain insecure HTTP.
>
> I have a Raspberry-Pi with Nginx serving static pages over insecure
> HTTP as I've never applied for a certificate -- using a dynamic DNS
> service. It is not suited for high-demand as it is behind my ISP router,
> and my uplink rate is only a tenth of my downlink rate (which isn't the
> fastest thing out there to begin with [Ugh -- Hope it's the weather -- my
> downlink is down to 10Mbps, when nominal is closer to 14Mbps]). Someday I
> may try creating a Flask application for it, just for learning.
>

Thanks to LetsEncrypt, certificates shouldn't be too hard for any
public-facing server. The Pi can renew and install its own
certificates in a 100% automated process, as long as you can continue
receiving port 80 traffic as well as the port 443 that the live server
would use.

ChrisA

Dennis Lee Bieber

unread,
Jan 22, 2022, 4:19:13 PMJan 22
to
On Sat, 22 Jan 2022 02:22:14 -0800 (PST), NArshad <narsh...@gmail.com>
declaimed the following:

>
>The user is going to enter the book name as the input of an HTML form on a website and I have to check whether the book is present or not in the Excel table

As written, the user will have to know the exact name of the book, as
it exists in the spreadsheet, and enter exactly that...

Is it going to be case sensitive?

A Matter of Life and Death
is not the same as
A Matter Of Life And Death

and partial entries won't match either

A Matter of Life

Do you intend to implement fuzzy logic for finding the target given
partial input? If you do, how do you plan to differentiate between two or
more books that match the logic? Do you strip punctuation?

Most systems will, instead, display a list of all books (unless a
filter is specified -- and may require the user to start with a filter just
to avoid a massive scrolling listing), and let the user select from the
list (radio button, check box, item number). A filter would be <display all
books with all the words "matter", "life" in the name>.


At the level your questions have been -- my biggest suggestion would be
to forget the web form and multi-user problems and write a text-based
console program that can be run from a command line shell. WHEN you get
that working you can consider keeping the logic that interfaces with the
data store, and replace the "presentation" stuff with HTML generation and
web-server integration (any web application will look quite different in
how the code is structured, since a console application has only one flow
-- start the app, make a selection, move to next phase of activity... A web
application has every action as a distinct connection and needs identifying
tokens [cookies] to let the logic know what was done previously)

Avi Gross

unread,
Jan 22, 2022, 4:27:38 PMJan 22
to

I keep wondering about the questions asked by NArshad here. His message can be read below mine, for context.
This is a place focused on using the Python language. The web page being in HTML is beyond irrelevant and in particular, web pages generally are in HTML even if only as a way to call other functionality.
What I gather is that the web page, irrelevant if in HTML or JAVA or JavaScript or a connection with a server running python or anything else, asks the user to type in a book name to search for. NOTHING is of any importance here except capturing the exact text and sending it, or perhaps a lower case version, back to your server that does the search.
Does that make sense? If not, stop reading as nothing else will.
So, back on the server side, you get a single string of text that might look like "Great Expectations" and you want to find out if that matches a book title exactly or perhaps partially. Again, agreed?
So your program needs to have data available. What if the names of all the books were written in a regular text file one line at a time? Yes, they aren't but WHAT IF?
You would open the file and perhaps read a line at a time and compare the lower-case version of that line to a lower case version of the requested book name. An exact match could mean you close the file and return that you found it, however that is done. No match means read the next line and repeat. If you get to the end of the file without finding it, you failed and close the file and return that result.
Of course some might do a bit more than convert to lower case such as removing multiple white space in both things being compared, or dropping other parts like commas of quotes as in changing "Mine,      didn't I    say!" to "mine didnt i say" so more things match as intended. Plenty you can do if you wish. Python has plenty of ways to do all that and more.
Now forget the plain text file and consider the following. Read the EXCEL file one unit/cell at a time in whatever column and row the data starts. This is doable, albeit not necessarily ideal and basically can be the same algorithm with a substitution in the part that gets the next line to getting the cell below it. Testing for end of file may be different too.
But what I think makes a bit more sense is to setup the server to have a rather long-term process that sits there and waits for requests. It starts by reading in ALL the data and perhaps modifying it once in ways like I suggest above. In python you would now have some data structure such as a list or set or even dictionary or some variation from numpy or pandas. It does not matter much. The data structure holding all books, or maybe all unique book names, would be there and ready and waiting.
Your program now sleeps until woken up and it is given the book name being searched for. It now simply needs to apply whatever transformations you want to the received name and then do one of many kinds of lookup to see if it is in the data structure representing the titles known to the library. Using a set or dictionary means no search as it hashes the result and rapidly finds out if it has previously been stored. Other methods like lists have choices between a brute force linear search if the data remains in the original order or letting python do the work by asking if it is "in" the list to more sophisticated methods like keeping it sorted and doing a binary search.
I am guessing that for your need, a relatively unsophisticated method may work fine and it can be changed if your project scales up to millions of books and multiple people searching at the same time.
And note you are now asking about a very limited thing. As others have pointed out, there can be much more complex requests such as checking if a copy of the book is free, and reserving it so others requesting it see as checked out and logging it back in again and more. And obviously more work is needed if you want to support more of a google-style search engine that matches books that contain only parts of your request.
But note what I am describing can be done by pretty much ANY language as long as it can open and read your data storage format. HTML all by itself can display a form with a text box and some kind of SUBMIT button and when you click on it call a CGI on a server and display what it returns. For the simple scenario you mention, you do not need openpyxl. But my guess is you are making something with additional functionality based on earlier posts and thus need to take care that anything you do can be extended to add functionally, so a raw HTML solution may not easily meet other needs. 
I keep hearing you searching for a solution and you may find one but mostly you need to design your own solution that meets your needs and use the search more for small pieces of the design like "how to efficiently search using a set in python" ...
As has been said repeatedly, if you really need the result soon and really are not yet up to the job or have time to learn, then either tell them you can't do it or get someone hired. But note, in the latter case you need to stop wasting time and tell them in great detail what you want done and let them see the data and so on. Otherwise, you may pay for lots of hours or have people walk away.
AGross

-----Original Message-----
From: NArshad <narsh...@gmail.com>
To: pytho...@python.org
Sent: Sat, Jan 22, 2022 5:22 am
Subject: Re: What to write or search on github to get the code for what is written below:


The user is going to enter the book name as the input of an HTML form on a website and I have to check whether the book is present or not in the Excel table. openpyxl preferred pandas is also fine anything left. Case sensitivity is not required. I tried to find code or tutorial on google search for all this but no use that's why..................

This time the choice of HTML is right or not??
--
https://mail.python.org/mailman/listinfo/python-list

Chris Angelico

unread,
Jan 22, 2022, 5:18:12 PMJan 22
to
On Sun, 23 Jan 2022 at 09:15, Dennis Lee Bieber <wlf...@ix.netcom.com> wrote:
> A web
> application has every action as a distinct connection and needs identifying
> tokens [cookies] to let the logic know what was done previously
>

Usually. Fortunately, we have SOME features that can make life easier,
but in general, yes, web apps need to think in discrete actions with
minimal maintained state.

Absolutely agree with making a console app first. Though I rather
suspect the OP doesn't want to write any code at all.

ChrisA

Dennis Lee Bieber

unread,
Jan 22, 2022, 9:54:46 PMJan 22
to
On Sun, 23 Jan 2022 09:17:38 +1100, Chris Angelico <ros...@gmail.com>
declaimed the following:


>
>Absolutely agree with making a console app first. Though I rather
>suspect the OP doesn't want to write any code at all.
>

Oh, it's gone beyond suspicion -- considering that, at just 10 lines
per day, over the last 16 days, they should have 160 lines of code (whether
it works or not) that could be presented for evaluation. What is considered
industry standard? 10 lines an hour? Assuming the OP isn't spending time on
requirements analysis and documentation. (My best example is something I
did back around 1990: 18 months consisting of ~600 lines F77, ~2000 lines
of C, and ~2500 lines of DECWindow UIL definition; along with learning both
DECWindow and GKS within it -- so, yes, the overall total is about 2 lines
per day, but I had to develop/document requirements, present them to the
customer, get approval, develop/document the design, present /that/ to the
customer, get approval, before even getting to the code and writing a user
manual for the system... I suspect easily half the time was spent just on
paperwork.)

All we've been shown is four lines, and that wasn't complete enough to
run stand-alone (presuming we ever see a sample of the data to be
processed. There's no complete CONOPS on how this application is to be used
(the most complete we've seen is "if the book is found, decrement some
counter, add user's name to another field" -- and? no report, no one else
is going to look at this information?).

On my part (having no transportation, stuck in a neck brace after
having rolled over my late Jeep, etc.) the alternative to wasting time here
would be to dismantle an AR-10 class rifle and install an adjustable target
(but not top-end match) trigger and, if really ambitious, install an
improved trigger in my Ruger MK-II pistol (I have the parts for both --
just need to set up space and time to do the work).

NArshad

unread,
Jan 24, 2022, 3:25:45 AMJan 24
to
On Sun, 23 Jan 2022 09:17:38 +1100, Chris Angelico <>
declaimed the following:


>
>Absolutely agree with making a console app first. Though I rather
>suspect the OP doesn't want to write any code at all.
>

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)

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.



-Dennis Lee Bieber:
>
>And again... You will not find anything like you want... NOBODY is
>going to write a web application using a spreadsheet as the primary data
>storage. A spreadsheet, and custom transformation code, MIGHT be used to
>initially populate a database. (M$ SQL Server Integration Services is a
>whole system for defining import/transformation/clean-up "functions" for
>data sources to data base). A spreadsheet might be available as a
>report/extraction format from the database.
>

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?

How you know so much about guns??

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?



Avi Gross:
>
>Now forget the plain text file and consider the following. Read the EXCEL file one unit/cell at a time
>in whatever column and row the data starts. This is doable, albeit not necessarily ideal, and
>basically can be the same algorithm with a substitution in the part that gets the next line to getting
>the cell below it. Testing for the end of the file may be different too.
>But what I think makes a bit more sense is to set up the server to have a rather long-term process
>that sits there and waits for requests. It starts by reading all the data and perhaps modifying it
>once in the ways I suggest above. In python, you would now have some data structure such as a
>list or set or even dictionary or some variation from numpy or pandas. It does not matter much. The
>data structure holding all books, or maybe all unique book names, would be there and ready and
>waiting.
>Your program now sleeps until woken up and it is given the book name being searched for. It now
>simply needs to apply whatever transformations you want to the received name and then do one of
>many kinds of lookup to see if it is in the data structure representing the titles known to the library.
>Using a set or dictionary means no search as it hashes the result and rapidly finds out if it has
>previously been stored. Other methods like lists have choices between a brute force linear search if
>the data remains in the original order or letting python do the work by asking if it is "in" the list to
>more sophisticated methods like keeping it sorted and doing a binary search.
>I am guessing that for your need, a relatively unsophisticated method may work fine and it can be
>changed if your project scales up to millions of books and multiple people searching at the same
>time.
>

What you have written is difficult to find on google search and others. That's why writing all this to get something for search.

Dennis Lee Bieber

unread,
Jan 24, 2022, 11:30:14 AMJan 24
to
On Mon, 24 Jan 2022 00:25:34 -0800 (PST), NArshad <narsh...@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 <