Get column type of returned row in python sqlite3

Question

Is there way to get the column type of the return row during select query.

SELECT 1, "text";

for above example col 1 is int() and col 2 is str().

EDITED

Is there any solution without fetching the column, may be using cursor.description.

import sqlite3

conn   = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('SELECT 1, "text"') 
for desc in cursor.description:
    print(desc)

which gives unfortunately doesn't provide the type but just name followed many None.

('1', None, None, None, None, None, None)
('"text"', None, None, None, None, None, None)

Remarks

According to PEP-0249 tuples these sequences contains information describing one result column:

• name
• type_code
• display_size
• internal_size
• precision
• scale
• null_ok

The first two items (name and type_code) are mandatory, the other five are optional and are set to None if no meaningful values can be provided.

But for sqlite type_code is None as shown by @forpas

Answer 1

SQLite's data type system is different than the typical strict system of other databases.

This system, allows the insertion in a column of values with data types considered different than the defined data type for that column in the CREATE TABLE statement.

For example:

CREATE TABLE t(
  id INTEGER PRIMARY KEY, 
  a INTEGER, 
  b TEXT,
  c SOMETHING, -- imaginary data type
  d -- no data type
);

INSERT INTO t(a, b, c, d) VALUES
(100, 'abc', 0, ''), 
('xyz', 'cde', 'AAA', 1), 
(300, 1000, null, null);

The above code is valid but it makes the notion of the data type of a column impossible.

If you use the function typeof() on all columns and rows, you get this:

typeof(a)	typeof(b)	typeof(c)	typeof(d)
integer	text	integer	text
text	text	text	integer
integer	text	null	null

which returns for all columns the data type of the value inserted in each row and this is not what you are after.

If you want the data type of the column as it was defined in the CREATE TABLE statement, you can use the function pragma_table_info():

SELECT name, type FROM pragma_table_info('t') 

which returns this:

name	type
id	INTEGER
a	INTEGER
b	TEXT
c	SOMETHING
d

See the demo.