I knew I would eventually have to make a database and test the code. Should have done that right away. Sorry.
This works:
import sqlite3
def getRecord(srchName, columns):
"""Return columns from first matching record"""
# Wrap column names in quotes and separete by ", ". Am wrapping
# column names in quotes because column name might have special
# meaning in an SQL query, like "group" for example.
columns = ", ".join([f"\"{column}\"" for column in columns])
query = f"SELECT {columns} FROM dino WHERE name = ?"
c.execute(query, (srchName,))
return c.fetchone()
conn = sqlite3.connect('dinobase.db')
c = conn.cursor()
id, group = getRecord("Entelodon", ["recNo", "group"])
print(id, group)
conn.close()
I learned a couple things getting this to work.
1. You cannot use placeholders for column names. My idea of using "SELECT ?, ? FROM dino WHERE name = ?" did not work because you cannot use a placeholder to specify the columns you want returned.
2. "group" has special meaning. When I tried to execute "SELECT recNo, group FROM dino WHERE name =?" I got an error.
Error:
sqlite3.OperationalError: near "group": syntax error
I think it saw "group" and started looking for "GROUP BY". There are a lot of words that have special meaning in a query, so I decided to wrap all column names in double quotes: "SELECT "recNo", "group" FROM dino WHERE name = ?" works fine.
And the error in my previous post was from trying to add a list to a tuple: columns + [srchName]. Since I cannot use placeholders for column names, this is no longer a problem.