Simple sqlite question

Question

When I use:

for i in Selection:
    Q = "SELECT columnA FROM DB WHERE wbcode='"+i+"' and commodity='1'"
    cursor.execute(Q)
    ydata[i] = cursor.fetchall()

I get:

ydata = {'GBR': [(u'695022',), (u'774291',), (u'791499',)... ]}

How can I change my code to get:

ydata = {'GBR': [695022, 774291, 791499,...]}

Thank you very much. obs: this is just a a simplified example. try to refrain from making recommendations about sql injection.

Answer 1

[int(x[0]) for x in cursor.fetchall()]

Answer 2

Based on this and another question of yours, you need to understand SQLite's affinity and how you are populating the database. Other databases require that the values stored in a column are all of the same type - eg all strings or all integers. SQLite allows you to store anything so the type in each row can be different.

To a first approximation, if you put in a string for that row then you'll get a string out, put in an integer and you'll get an integer out. In your case you are getting strings out because you put strings in instead of integers.

However you can declare a column affinity and SQLite will try to convert when you insert data. For example if a column has integer affinity then if what you insert can be safely/correctly converted to an integer then SQLite will do so, so the string "1" will indeed be stored as the integer 1 while "1 1" will be stored as the string "1 1".

Read this page to understand the details. You'll find things a lot easier getting data out if you put it in using the correct types.

http://www.sqlite.org/datatype3.html

If you are importing CSV data then start the APSW shell and use ".help import" to get some suggestions on how to deal with this.