How can I get a single column in python and sql?-CodePudding

I am trying to make a book selling system and I am trying to take the book's name from input and find the quantity of the book.But system takes the whole row instead of taking a single column.How can I solve this ?

import sqlite3


print("------------------------------------------")
print("Welcome to the bookshop management system.")
print("------------------------------------------")


print("[1] Add Book")
print("[2] Search for a book")
print("[3] Sell a book")
print("[4] Change stocks")


connect=sqlite3.connect("librarydatabase.db")
cursor=connect.cursor()

Inserter="INSERT INTO books VALUES('booknamevalue','writervalue','DateOfReleasevalue','Quantityvalue','Pagevalue')"
operation=input("Please enter an operation:")


if operation=="1":
    bookname=input("Please enter the book's name")
    writername=input("Please enter the writer's name:")
    DateOfReleaseinput=input("Please enter the Date of release:")
    Quantityvalue=input("Please enter the Quantity of the book:")
    Pagevalue=input("Please enter the Page count of the book:")
    Inserter1=Inserter.replace("booknamevalue",bookname)
    Inserter2=Inserter1.replace("writervalue",writername)
    Inserter3=Inserter2.replace("DateOfReleasevalue",DateOfReleaseinput)
    Inserter4=Inserter3.replace("Quantityvalue",Quantityvalue)
    Inserter5=Inserter4.replace("Pagevalue",Pagevalue)
    cursor.execute(Inserter5)
    connect.commit()


Booksellersearcher="SELECT Quantity FROM Books WHERE Bookname='Booknamevalue'"


#Will be made correctly
if operation=="3":
    Bookname2=input("Please enter the name of the book:")
    Booksellersearcher2=Booksellersearcher.replace("Booknamevalue",Bookname2)
    cursor.execute(Booksellersearcher2)
    BookQuantity=cursor.fetchone()
    if BookQuantity==0:
        print("No Stock On this Book")
        operation=input("Please enter an operation:")
    else:
        print("There are" " " str(BookQuantity) "books" " " "of" " " Bookname2)
        operationinput=input("Do you wanna sell ?(Y/N)")
        if operationinput=="Y":
            SellingQuantityInput=int(input("How many Books do you wanna sell ?"))
            NewQuantity=BookQuantity-SellingQuantityInput
            print(NewQuantity)
        else:
            operation=input("Please enter an operation:")


Booksearcher="SELECT * FROM Books WHERE Bookname='Booknamevalue'"
if operation=="2":
    Booknameinput2=input("Please enter the name of the book:")
    Booksearcher2=Booksearcher.replace("Booknamevalue",Booknameinput2)
    cursor.execute(Booksearcher2)
    Bookvalues=cursor.fetchall()
    print(Bookvalues)


BookQuantitychanger="UPDATE Books SET Quantity='Quantityvalue' WHERE Bookname='Booknamevalue'"
if operation=="4":
    Booknameinput3=input("Please enter the name of the book:")
    BookQuantityinput=int(input("Please enter the quantity of the book:"))
    BookQuantitychanger2=BookQuantitychanger.replace("Quantityvalue",str(BookQuantityinput))
    BookNamereplacer=BookQuantitychanger2.replace("Booknamevalue",Booknameinput3)
    cursor.execute(BookNamereplacer)
    connect.commit()
    print("Book quantity changed successfully!")

CodePudding user response：

I will start with your question, SELECT on a table will return rows, which are put in tuples, even you select a single columns it returns the rows containing just this column, but those are still rows, and so tuples.

But now please read the following.

Using string replace or any other other string templating for any SQL statement is leaving it wide open for SQL injection, what if the user inputs ' ; DROP TABLE books ; -- in the Booksearcher statement ?

You end up with

SELECT * FROM Books WHERE Bookname='' ; DROP TABLE books ; --'

which will destroy you books table.

Please go through the SQLite3 tutorial to see SQL parameterisation.

CodePudding user response：

Your SQL statement is SELECT * which is why you get all columns.

Change your SQL statement to the one below:

Booksearcher="SELECT Quantity FROM Books WHERE Bookname='Booknamevalue'"

But your code is a bit unreadable.

You are using a lot of replacements and for each you're using a new variable. Use f-strings instead.
Your variable names are capitalized. In python that's used to define classes/objects.
Your if statements should be if/elif instead.
There is no main loop, so your code only executes once.
You have what I would call main menu prints inside your submenus. Instead you should go back to the main menu.
Little Bobby Tables

I did a quick rewrite of your code because I'm a bit bored. See below

import sqlite3


connect = sqlite3.connect("librarydatabase.db")
cursor = connect.cursor()


while True:
    print("------------------------------------------")
    print("Welcome to the bookshop management system.")
    print("------------------------------------------")
    print("[1] Add Book")
    print("[2] Search for a book")
    print("[3] Sell a book")
    print("[4] Change stocks")
    print("[0] Exit")
    operation = input("Please enter an operation:\n")


    if operation == "1":
        bookname = input("Please enter the book's name:\n")
        writername = input("Please enter the writer's name:\n")
        dateofrelease = input("Please enter the Date of release:\n")
        quantity = input("Please enter the Quantity of the book:\n")
        pagecount = input("Please enter the Page count of the book:\n")
        cursor.execute(f"INSERT INTO books VALUES('{bookname}','{writername}','{dateofrelease}','{quantity}','{pagecount}')")
        connect.commit()

    elif operation == "2":
        bookname = input("Please enter the name of the book:\n")
        cursor.execute(f"SELECT Quantity FROM Books WHERE Bookname='{bookname}'")
        books = cursor.fetchall()
        print(books)

    elif operation == "3":
        bookname = input("Please enter the name of the book:\n")
        cursor.execute(f"SELECT Quantity FROM Books WHERE Bookname='{bookname}'")
        bookquantity = cursor.fetchone()
        if bookquantity == 0:
            print("No Stock On this Book")
            # go to the start of the while loop
            continue
        else:
            print(f"There are {bookquantity} books of {bookname}")
            operationinput = input("Do you wanna sell ?(Y/N):\n")
            if operationinput.upper() == "Y":
                sellingquantity = int(input("How many Books do you wanna sell ?\n"))
                newquantity = bookquantity - sellingquantity
                print(newquantity)
            else:
                continue

    elif operation == "4":
        bookname = input("Please enter the name of the book:\n")
        bookquantity = int(input("Please enter the quantity of the book:\n"))
        cursor.execute(f"UPDATE Books SET Quantity='{bookquantity}' WHERE Bookname='{bookname}'")
        connect.commit()
        print("Book quantity changed successfully!")

    elif operation == "0":
        # exit while loop
        break

    else:
        print("Operation not recognized.")