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])

But here you fetch all records, and you are only printing the first one? — Willem Van Onsem, Oct 16 '17 at 17:52

Gord Thompson · Answer 1 · 2017-10-16T19:31:59.787

16

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.

edited Oct 16 '17 at 19:31

answered Oct 16 '17 at 19:23

Gord Thompson

116,920
32
215
418

Sadly, `fetchval` is pyodbc-specific and [pypyodbc doesn't have it](https://github.com/jiangwen365/pypyodbc/search?q=fetchval&unscoped_q=fetchval). – Nickolay Sep 03 '18 at 13:28
2

@Nickolay I believe the OP only asked for pyodbc. – NoName Feb 05 '20 at 02:35

score 2 · Accepted Answer · answered Oct 16 '17 at 17:54

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.

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

2 Answers2