时间戳

首页 > 解决方案 > 在python sqlite3中获取返回行的列类型

python - 在python sqlite3中获取返回行的列类型

问题描述

有没有办法在选择查询期间获取返回行的列类型。

SELECT 1, "text";

对于上面的例子 col 1 是int()col 2 是str()。

已编辑

有没有不获取列的解决方案，可能正在使用cursor.description.

import sqlite3

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

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

不幸的是，它没有提供类型，而只是 name 跟随 many None。

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

评论

根据PEP-0249元组，这些序列包含描述一个结果列的信息：

前两项（name 和 type_code）是必需的，其他五项是可选的，如果无法提供有意义的值，则设置为 None。

但是对于sqlite type_codeNone如@forpas 所示

标签： pythonsqlite

解决方案

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.

推荐阅读