如何使用 Python/SQLite 获取查询结果? [英] How to get queried results with Python/SQLite?

问题描述

我使用 Python/SQLite 来访问数据库.运行查询并得到结果后，我想知道查询结果和数据库中的行数、列数和列名.

例如，如果我运行SELECT * from table"，然后我得到

<前>身份证号码--------------------约翰一书 102 杰 20

我能知道我有2行3列，列数是id/name/number?

添加

根据 Rafael SDM Sierra 的回答，我可以得到如下信息.

 description = self.cursor.descriptionqr.numberOfCol = len(description) <-- 列数对于描述中的项目:qr.names.append(item[0]) <-- 列名计数 = 0对于 self.cursor 中的行:计数 += 1qr.result.append(row)qr.numberOfRow = count <-- 行数

解决方案

SQLite3 for Python 不支持 .rowcount 属性并始终返回 -1.

但是要知道哪些列可以使用 .description 属性.

<预><代码>>>>导入 sqlite3>>>c = sqlite3.connect(':memory:')>>>c.execute('CREATE table foo (bar int, baz int)')<sqlite3.Cursor 对象在 0xb76e49e0>>>>c.execute('插入 foo 值 (1,1)')<sqlite3.Cursor 对象在 0xb778c410>>>>c.execute('插入 foo 值 (2,2)')<sqlite3.Cursor 对象在 0xb76e4e30>>>>c.execute('插入 foo 值 (3,3)')<sqlite3.Cursor 对象在 0xb778c410>>>>cursor = c.execute('select * from foo')>>>游标.rowcount-1>>>cursor.fetchone()(1, 1)>>>游标描述(('bar', None, None, None, None, None, None), ('baz', None, None, None, None, None, None))>>>

有关 .description 属性的更多信息，请查看此处:http://www.python.org/dev/peps/pep-0249/

I'm using Python/SQLite for accessing database. After running the query, and getting the result, I want to know the number of rows, the number of columns, and the name of the column from the queried result and database.

For example if I run "SELECT * from table", and I get

id    name    number
--------------------
1     John    10
2     Jay     20

I can I know that I have 2 rows, and 3 columns, and the number of columns are id/name/number?

ADDED

Based on Rafael SDM Sierra's answer, I could get the info as follows.

    description = self.cursor.description
    qr.numberOfCol = len(description) <-- # of column
    for item in description:
        qr.names.append(item[0]) <-- Names of column

    count = 0
    for row in self.cursor:
        count += 1
        qr.result.append(row)

    qr.numberOfRow = count <-- # of row

解决方案

SQLite3 for Python does not suport .rowcount attribute and return always -1.

But to know what are the columns you can use .description attribute.

>>> import sqlite3
>>> c = sqlite3.connect(':memory:')
>>> c.execute('CREATE table foo (bar int, baz int)')
<sqlite3.Cursor object at 0xb76e49e0>
>>> c.execute('insert into foo values (1,1)')
<sqlite3.Cursor object at 0xb778c410>
>>> c.execute('insert into foo values (2,2)')
<sqlite3.Cursor object at 0xb76e4e30>
>>> c.execute('insert into foo values (3,3)')
<sqlite3.Cursor object at 0xb778c410>
>>> cursor = c.execute('select * from foo')
>>> cursor.rowcount
-1
>>> cursor.fetchone()
(1, 1)
>>> cursor.description
(('bar', None, None, None, None, None, None), ('baz', None, None, None, None, None, None))
>>> 

For more information about .description attribute, look here: http://www.python.org/dev/peps/pep-0249/

这篇关于如何使用 Python/SQLite 获取查询结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！