How can I successfully pass a variable into my sqlite SELECT statement?

Question

I am writing a small program that lets me search a sqlite database, assets by asset number, serial number, etc. The problem is that I'm not sure has to pass user input, a variable assetNum, into my sqlite SELECT statement. I would like it to take the user-queried assetNum and return that row from the sqlite table.

First, I defined my function:

def search_asset_num(conn):

    cur = conn.cursor()
    cur.execute("SELECT * FROM assets WHERE AssetNumber = '{}'".format(assetNum))

    rows = cur.fetchall()

    for row in rows:
        print(row)

In the program I utilize user-input to move through menus, so later on in the program when I get to the "search by asset menu" I have:

   elif selection == '2':

            menu2 = {}
            menu2['        1'] = "Search Assets by Name"
            menu2['        2'] = "Search Assets by Asset Number"
            menu2['        3'] = "Search Assets by Serial Number"
            menu2['        4'] = "Search Assets by Ownership"
            menu2['        5'] = "Go back!"

            choices2 = menu2.keys()
            for entry in choices2:
                print(entry, menu2[entry])

            selection2 = input("Enter number: ")

            if selection2 == '2':

                assetNum = input("Enter Asset Number: ")

                search_asset_num(conn)

The problem, from what I can tell, is that assetNum is present in the search_asset_num() function above where it is actually defined in the if statement as assetNum = input("Enter Asset Number: "). I'm not sure how to work around this. I have tried making assetNum global at the very top of my code and setting it to equal 0 as a placeholder, but that did not work. It just outputs 0 to the terminal.

Answer 1

Add assetNum as parameter to your function,

def search_asset_num(conn, assetNum):
    cur = conn.cursor()
    cur.execute("SELECT * FROM assets WHERE AssetNumber = ?", (assetNum,))
    rows = cur.fetchall()
    for row in rows:
        print(row)

in your menu, you can change as below:

        if selection2 == '2':
            assetNum = input("Enter Asset Number: ")
            search_asset_num(conn, assetNum)

also use "Parameter Substitution" as stated in Python sqlite3 - DB-API documentation