How to fetch and make calculations with sqlite data in python?

Question

I want to fetch the data in a specific row and be able to perform calculations with it, so far I have this:

import sqlite3 as lite
con=con.connect(mydatabase.db)
cur=con.cursor()
query=cur.execute("SELECT price from items where ID=1")
print(cur.fetchone())

which returns

(13.5,)

I can't make any calculations with that. I decided I would just treat the fetchone() method as a tuple and came up with this:

cur.fetchone()[0]

which returns

13.5

I can work with that, but I think it is a dirty solution is there any other way to fetch the data? I've also tried

for row in cur.fetchall():
   print(row[0])

which returns a real number I can work with, but I don't know why this works.

13.5

A number I can work with. I don't know which of the 2 solutions is better, or if there is something more efficient. Could you also give me a brief explanation of the fetch objects I find them somewhat confusing. Thanks your help is greatly appreciated.

you do know sqlite can do some calculations right (usually people do them in python but you can do some on sqlite queries directly) — Joran Beasley
– Joran Beasley, Commented Jul 22, 2013 at 23:37

freakish · Accepted Answer · 2013-07-22 23:20:44Z

2

It's not dirty. That's how low level sql with Python works. Tuple represents columns (in order defined in select statement, keep in mind that you should not use select * cause there's no guarantee in what order you will receive the data). Thus cur.fetchone()[0] is absolutely fine (as long as price is first in select query).

On the other hand fetchall returns a list of all rows that satisfy the query. So the result of calling fetchall is always a list of tuples. It might be an empty list though. Basically fetchone does the same thing as fetchall except it gets the next row from fetchall (thus multiple calls to fetchone will get you all rows in fetchall). Note that fetchone will return None if there is no more data.

edited Jul 22, 2013 at 23:20

answered Jul 22, 2013 at 23:15

freakish

57k12 gold badges141 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel Roseman · Accepted Answer · 2013-07-22 23:22:18Z

1

I'm not sure why you think it's dirty. One point, though: you say you're treating the method as a tuple, but that's obviously not right: the method returns a tuple. And that's fine: you just access the first element of it with [0], as you would any other tuple. There's no need for any extra loops.

answered Jul 22, 2013 at 23:22

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

Collectives™ on Stack Overflow

How to fetch and make calculations with sqlite data in python?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related