converting types returned from fetchall()

Question

I'm using psycopg2, and I run the following code:

conn = psycopg2.connect(database = mydb_name, host = mydb_server, user = mydb_uname, password = mydb_pwd)
cur = conn.cursor()
cur.execute("SELECT desiredParams FROM tableOfInterest;")
all_data = cur.fetchall()
nlargest = heapq.nlargest(5, all_data)

This returns a list of tuples with decimals:

[(Decimal('254.000'),), (Decimal('154.000'),), (Decimal('244.000'),), (Decimal('134.000'),), (Decimal('254.000'),)]

How can I convert this into something more like:

[254.000, 154.000, 244.000, 134.000, 254.000]

?

Answer 1

Decimals are a fix point number that match nicely with database number types. They are really useful as is, but if I cant convince you to use them.

largest = [float(d[0]) for d in nlargest]

would give

[254.0, 154.0, 244.0, 134.0, 254.0]

or even better since cur is a iterible

cur.execute("SELECT desiredParams FROM tableOfInterest;")
nlargest = [float(g[0]) for g in heapq.nlargest(5, cur)]

In an attempt to make it pretty

cur.execute("SELECT param FROM tableOfInterest ORDER BY param DESC FIRST 5;")
nlargest = [float(r.param) for r in cur]

or skip the list building and just do it

cur.execute("SELECT param FROM tableOfInterest ORDER BY param DESC FIRST 5;")
for param, in cur:
    stuff_to_do_with_first_5(float(param))

the way to bind that looks good but is really bad (due to pyscopg's mishandling of bind variables). The problem with this is that pysco just % the values into the string and thus hides the ability to inject sql into your string.

cur.execute("SELECT param FROM tableOfInterest ORDER BY param DESC FIRST %s;", (num_results,))

the way that looks bad but is safer then the previous

cur.execute("SELECT param FROM tableOfInterest ORDER BY param DESC FIRST %d;" % num_results)