Have pyodbc return a simple (scalar) value for a query that only returns one item

Question

I am having an issue when I select data from the SQL database through pyodbc where I end up with this type of result.

[(55.0, )]

I want the pure number (no "[" or "(" so I later can insert it into a different table after calculating new stuff with it. I am sure it's trivial but I just haven't been able to figure out how. Below is the code I am using:

rows = conn.execute("SELECT price from PG").fetchall()
print(rows[:1])

Answer 1

Note for future readers: The pyodbc Cursor object has a fetchval method:

The fetchval() convenience method returns the first column of the first row if there are results, otherwise it returns None.

(It is similar to the .NET ExecuteScalar() method.)

So instead of doing something like

row_count = crsr.execute("SELECT COUNT(*) FROM TableName").fetchone()[0]

we can just do

row_count = crsr.execute("SELECT COUNT(*) FROM TableName").fetchval()

It has the added advantage of returning None (instead of throwing an exception) if the query returns no rows.

Answer 2

You have [(55.0, )] because you have a list of rows (containing a single row in this example), and each row is a tuple (with a single element, since you just selected price). You can do

singlerow = rows[0]
price, = singlerow

However, fetching all rows to select just one seems weird, you should probably re-think your query.