How to make fetch methods return int values for INTEGER columns instead of strings Python sqlite?

Question

I'm tring to use this code to read all temperature values from a sqlite database column, but the output is showing [(u'29',), (u'29',), (u'29',)] and I'm only storing the numeric value in the database. I would like the output to be [29, 29, 29]

import sqlite3

conn = sqlite3.connect("growll.db")
cursor = conn.cursor()

print "\nHere's a listing of all the records in the table:\n"
cursor.execute("select lchar from GrowLLDados")

print cursor.fetchall()

If that is the output then - *sure* that only numbers are being stored? (As opposed to numeric strings.) SQLite will do some basic type-affinity conversions depending upon column type, but otherwise, what you get is what is in the database. — , Dec 27 '12 at 01:14

score 6 · Accepted Answer · answered Dec 26 '12 at 23:03

6

Try this:

import sqlite3

conn = sqlite3.connect("growll.db")
cursor = conn.cursor()

print "\nHere's a listing of all the records in the table:\n"
cursor.execute("select lchar from GrowLLDados")    

print [int(record[0]) for record in cursor.fetchall()]

answered Dec 26 '12 at 23:03

Justin Lewis

1,261
1
15
33

Worked Perfectly! thanks Since I'm learning python would you know how I would do the same if I the data was a char like L instead of 29? – Aman Dec 26 '12 at 23:30
Sure: `print [record[0] for record in cursor.fetchall()]` should work if fetchall() returns `[(u'L',), (u'L',), (u'L',)]` for example. The `u` in front of the strings just mean that they are `unicode` strings, by the way. – Justin Lewis Dec 26 '12 at 23:33
To use this data how I can store it in a new variable because if I trie to read the record varialble there is only the last read variable not the list. Thanks – Aman Dec 26 '12 at 23:55
To get `[29, 29, 29]` into a variable, assign it to one instead of printing it, i.e. `records = [int(record[0]) for record in cursor.fetchall()]`. Is that what you mean? – Justin Lewis Dec 26 '12 at 23:57
Yes thats it I'll use matplot with this data. – Aman Dec 27 '12 at 00:01

hd1 · Answer 2 · 2012-12-27T00:22:33.257

1

print [int(i[0]) for i in cursor.fetchall()]

Let me know how you get on.

edited Dec 27 '12 at 00:22

answered Dec 26 '12 at 23:00

hd1

33,938
5
80
91

Won't this throw a TypeError? `int()` needs a string, not a tuple – Justin Lewis Dec 26 '12 at 23:04
1

yes I have: `TypeError: int() argument must be a string or a number, not 'tuple'` – Justin Lewis Dec 26 '12 at 23:21

How to make fetch methods return int values for INTEGER columns instead of strings Python sqlite?

2 Answers2