Python Forum

I’m trying to learn Python so I am trying to recreate an app that I did previously with Filemaker as well as with Purebasic. The app uses SQLite3 to get data from a database. The first column in the database contains the record number of the record found when searched. However, in Python I am having a problem returning the value of a variable from a function.

I have a variable called recNo which I initialize with the value of 0. If I do not define the code as a function, it works fine and the value in row[0] is assigned to the variable recNo. If, however, I make the code a function, it runs with no errors but while the value in row[0] is assigned to recNo it is not being returned.

Probably something simple but I am missing it. I am using Print statements in the code for testing purposes but in the app I will be assigning each row in the database to a variable and trying to return them all.

Here is the code that I am using.

import sqlite3

# Connect to the database
conn = sqlite3.connect('dinobase.db')

c = conn.cursor()

def getRecord(srchName, recNo):
# Execute the query
    c.execute('SELECT * FROM dino WHERE name = :srchName',{'srchName': srchName})

    # Retrieving data
    for row in c:
        recNo = row[0]
        print("Record : ", row[0])
        print("Name : ", row[1])
        print("meaning : ", row[2])
        print("pronounce : ", row[3])
        print("period : ", row[4])
        print("group : ", row[5])
        print("size : ", row[6])
        print("lived : ", row[7])
        print("diet : ", row[8])
        print("fossils : ", row[9])
        print("factfile : ", row[10])
        print("\n")
        return recNo

# Close the connection
conn.close

recNo = 0
searchString = "Entelodon"

getRecord(searchString, recNo)    #Call the search function

print(recNo)

Any help is appreciated.

When you call a function to capture its return you need to assign it to a variable.

def some_function():
    return "From some function"


function_return = some_function()
print(function_return)

Output:
From some function

I must still be doing it wrong because recNo is still displaying as 0. Here is the code I tried.

def getRecord(srchName, recNo):
# Execute the query
    c.execute('SELECT * FROM dino WHERE name = :srchName',{'srchName': srchName})

    # Retrieving data
    for row in c:
        print("Record : ", row[0])
        print("Name : ", row[1])
        print("meaning : ", row[2])
        print("pronounce : ", row[3])
        print("period : ", row[4])
        print("group : ", row[5])
        print("size : ", row[6])
        print("lived : ", row[7])
        print("diet : ", row[8])
        print("fossils : ", row[9])
        print("factfile : ", row[10])
        print("\n")
        recNo = row[0]
        return recNo

# Close the connection
conn.close

recNo = 0
searchString = "Entelodon"

getRecord(searchString, recNo)    #Call the search function

function_return = recNo
print(recNo)

The code is still calling getRecord(searchString, recNo) but not capturing the result

---

The following

recNo = getRecord(searchString, recNo)    #Call the search function
 
print(recNo)

the result of the function getRecord will now be assigned to recNo

I don't think the problem is the return statement. You only return the first field of the first record. Even if you kept the return value in a variable, what good is that?

What do you want the query to return? Do you want a list of all the records that match the query? For that use

return c.fetchall()

This makes your function really simple. I don't know what recNo was supposed to do, so I left it out.

def getlist(srchName):
    c.execute('SELECT * FROM dino WHERE name = ?', (srchName,))
    return c.fetchall()

for result in getlist("Entelodon"):
    print(result)

This will gather all the search results into a local list and returns the list. You can iterate through the list and print the results, or you can print the entire list, or you can index through the list.

You could also do this. Cursor is iterable, so you can use c to iterate through the results.

def getiter(srchNameo):
    c.execute('SELECT * FROM dino WHERE name = ?', (srchName,))
    return c
 
for result in getiter("Entelodon"):
    print(result)

The main difference between the two approaches is fetchall() gets all the results at once while iterating over the cursor uses lazy retrieval and gets the results as needed.

The two approaches look very different if you print the return value. print(getlist()) will print a list containing all the results. print(getiter()) will print

Output:
<sqlite3.Cursor object at 0x000002327F59F340>

Things are also very different if you try to index over the results. print(getlist()[0]) prints the first result. print(getiter()[0]) raises a TypeError.

You could also make your function a generator. This is a lot like returning an iterator.

def getgen(srchName):
    c.execute('SELECT * FROM dino WHERE name = ?', (srchName,))
    for row in c:
        yield row
  
for result in getgen("Entelodon"):
    print(result)

The difference between an iterator and a generator is mostly a matter of implementation. Using them it is hard to see any difference. The main difference between a function and a generator is a function returns a single value and a generator returns a series of values.

Thanks Yoriz. That works great!
Thanks again.
Cheers.

Thanks deanhystad. recNo holds the value of the first column of the database which is the record number of the record found using a search. The code that I used has print statements because I was just testing to see if the query retrieved the data from the database. What I really want to do when I code the actual app is to retrieve and assign each column in the record found to variables and return them so that I can print them in entry boxes. Yoriz' suggestion works for 1 column (recNo) which solved my question about returning values but not for returning variables for many columns .

For example assign variables like this:

recNo = row[0]
name = row[1]
meaning = row[2]
pronounce = row[3]
etc...

Then return the variables so that they can be displayed in entry boxes or textboxes.

Thanks

You really don't want to do this

(Sep-15-2022, 08:03 PM)Columbo Wrote: [ -> ]What I really want to do when I code the actual app is to retrieve and assign each column in the record found to variables and return them so that I can print them in entry boxes. Yoriz' suggestion works for 1 column (recNo) which solved my question about returning values but not for returning variables for many columns .

For example assign variables like this:

recNo = row[0]
name = row[1]
meaning = row[2]
pronounce = row[3]
etc...

Then return the variables so that they can be displayed in entry boxes or textboxes.

It is conceivable that you want to do this:

def getRecord(srchName):
    c.execute('SELECT recNo, name, meaning, pronounce FROM dino WHERE name = ?', (srcName,)
    return c.fetchall()

recNo, name, meaning, pronounce = getRecord(something)

Where "recNo, name, meaning, pronounce" are the fields you want to return in the order you want to return them. Note that even though I used variable names that match the column names in the table, this is only a good idea, not a requirement. The only important thing is the order of the variables matches the order of the column names in the "SELECT".

I tried your suggestion but I am getting an SQLite3 operational error. Here is the code I tried.

import sqlite3

# Connect to the database
conn = sqlite3.connect('dinobase.db')

c = conn.cursor()

def getRecord(srchName, recNo, name, meaning, pronounce, period, group, size, lived, diet, fossils, factfile):
# Execute the query
    c.execute('SELECT recNo, name, meaning, pronounce, period, group, size, lived, diet, fossils, factfile FROM dino WHERE name = ?', (srchName,))

    # Retrieving data
    for row in c:
        recNo = row[0]
        name = row[1]
        meaning = row[2]
        pronounce = row[3]
        period = row[4]
        group = row[5]
        size = row[6]
        lived = row[7]
        diet = row[8]
        fossils = row[9]
        factfile = row[10]
        return c.fetchall()

# Close the connection
conn.close

srchName = "Dimetrodon"
recNo = 0
name = ""
meaning = ""
pronounce = ""
period = ""
group = ""
size = ""
lived = ""
diet = ""
fossils = ""
factfile = ""

recNo, name, meaning, pronounce, period, group, size, lived, diet, fossils, factfile = getRecord(srchName, recNo, name, meaning, pronounce, period, group, size, lived, diet, fossils, factfile)    #Call the search function

function_return = recNo, name, meaning, pronounce, period, group, size, lived, diet, fossils, factfile
print(recNo)
print("name : " + name)
print("meaning : " + meaning)
print("pronounce : " + pronouce)
print("period : " + period)
print("group : " + group)
print("size : " + size)
print("lived : " + lived)
print("diet : " + diet)
print("fossils : " + fossils)
print("\n")
print("factfile : " + factfile)
print("\n")

A lot wrong here.
It doesn't make any sense passing all these arguments to the function. The only argument you should pass is srchName. This is not C where an argument can be pass by reference. In Python function arguments are pass by value (or pass by object).

def getRecord(srchName, recNo, name, meaning, pronounce, period, group, size, lived, diet, fossils, factfile)

:
There is no reason for any of this:

for row in c:
    recNo = row[0]
    name = row[1]
    meaning = row[2]
    pronounce = row[3]
    period = row[4]
    group = row[5]
    size = row[6]
    lived = row[7]
    diet = row[8]
    fossils = row[9]
    factfile = row[10]

Not only was there no reason to do the above, doing so makes it so this doesn't work.

return c.fetchall()

If you already retrieved all the values using the for loop there is nothing left to return using fetchall().

There is no reason for doing any of this. Assigning a value to recNo inside the getRecord() function does not assign a value recNo outside the function. None of these variable assignments do anything useful.

recNo = 0
name = ""
meaning = ""
pronounce = ""
period = ""
group = ""
size = ""
lived = ""
diet = ""
fossils = ""
factfile = ""

I have no idea why you are doing this. It produces a tuple containing all the variable values, but you have no need for the tuple.

function_return = recNo, name, meaning, pronounce, period, group, size, lived, diet, fossils, factfile

This is how I would write the code.

import sqlite3

def getRecord(srchName, columns):
    """Return columns from first matching record"""
    query = ("?, " * len(columns))[:-2]
    query = f"SELECT {query} FROM dino WHERE name = ?"
    c.execute(query, columns + [srchName])
    return c.fetchone()

conn = sqlite3.connect('dinobase.db')
c = conn.cursor()

columns = ("recNo", "name", "meaning", "pronounce", "period", "group", "size", "lived", "diet", "fossils")
values = getRecord("Dimetrodon", columns)
for column, value in zip(columns, values):
    print(f"{column:>10} : {value}")

conn.close()

There are some assumptions that may be incorrect. I am assuming that you only want to return values for one matching record. In my previous post I was assuming there might be multiple matching records and you wanted them all returned. I also assume that every word in columns is a column in "dino".