How to get queried results with Python/SQLite?

Question

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

Are you using a Python ORM? Like Django or SQLAlchemy? These have API that should help you get the information you need. — rubayeet
– rubayeet, Commented Sep 10, 2010 at 18:53
@rubayeet : No, I just use python/SQLite as 'from sqlite3 import dbapi2 as sqlite3' — prosseek
– prosseek, Commented Sep 10, 2010 at 18:55

Rafael Sierra · Accepted Answer · 2010-09-10 18:56:30Z

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/

user3451435 · Accepted Answer · 2014-06-19 12:08:41Z

3

Because cursor.rowcount does not work, you will have to get a count back and extract the number using result = cursor.execute('select count(*) from the_table') print "rowcount = ",result.fetchone()[0]

answered Jun 19, 2014 at 12:08

user3451435

2232 silver badges6 bronze badges

Collectives™ on Stack Overflow

How to get queried results with Python/SQLite?

ADDED

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

ADDED

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related