Issue with basic SQLite database access in Python

Question

I have a database called "default.db" and it is a working database [I can access it through the SQLite utilities and see the tables inside].

I have this code for accessing the database:

from pysqlite2 import dbapi2 as sqlite

conn = sqlite.connect("default.db")
cur = conn.cursor()

#retrieve all database data

cur.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
print "fetch all"
cur.fetchall()

print "printing"
print cur
print conn
for i in cur:
    print "item"
    print i

The output is:

fetch all
printing
<pysqlite2.dbapi2.Cursor object at 0xb74a2de0>
<pysqlite2.dbapi2.Connection object at 0xb74713d8>
done

But if I run the commands individually I get this result from the code:

>>> cur.fetchall()
[(u'cards',), (u'data_for_fact',), (u'fact_views',), (u'fact_views_for_card_type',), (u'facts',), (u'global_variables',), (u'log',), (u'sqlite_sequence',), (u'tags',), (u'tags_for_card',)]

My question is: How can I fix this, and what is going wrong? I'm not getting any errors connecting to the db [its not reporting any].

edit: I tried saving the fetchall to an variable, but the variable prints out as []. (Empty)

cur.fetchall() prints out a list but you did not tell it to print anything. That is a clue that cur.fetchall() returns a value and that your code is NOT assigning that value to a variable. Therefore, you are throwing away the results of the query. Pay attention to documentation, both function arguments AND function results. — Michael Dillon
– Michael Dillon, Commented Feb 14, 2011 at 1:10
I was hoping that it would print out in the script, thats why I didn't assign it a value. It was an later add on to the script. — monksy
– monksy, Commented Feb 14, 2011 at 3:19

Utku Zihnioglu · Accepted Answer · 2011-02-14 09:53:03Z

2

I think you should iterate over cur.fetchall() not the cur -> cursor itself.

It should be like;

for i in cur.fetchall():
    print "item"
    print i

And you should also drop the previous cur.fetcall() command.

edited Feb 14, 2011 at 9:53

answered Feb 14, 2011 at 0:26

Utku Zihnioglu

4,8833 gold badges42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

monksy Over a year ago

Your suggestion does not print anything.

Wang Dingwei Over a year ago

You can only run fetchall once on the same cursor, so you have to makesure the previous cur.fetchall() is removed.

Mark Tolonen Over a year ago

The first cur.fetchall() drains the iterator. Remove the first one or reexecute the select before this one and it will work.

Keith · Accepted Answer · 2011-02-13 23:55:35Z

1

You should probably read the DBAPI docs. But basically, the cursor is not iterable itself. The methods, such as fetchall return lists as your last sample shows. Your first example returns the list but is discarded (not assigned).

From that doc, fetchall does:

Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples). Note that the cursor's arraysize attribute can affect theperformance of this operation.

answered Feb 13, 2011 at 23:55

Keith

43.2k11 gold badges61 silver badges77 bronze badges

4 Comments

Cristian Ciupitu Over a year ago

Iterable cursors are optional, but nevertheless SQLite supports them.

Keith Over a year ago

@cristian The evidence show here would suggest that it doesn't. ;-)

Mark Tolonen Over a year ago

The first cur.fetchall() drains the iterator. for i in cur works if you comment the fetchall out.

Keith Over a year ago

Aha. I didn't see that in the PEP. But I think that having the cursor and the fetchall, two different things, affecting each other like that is non-obvious and a questionable design.

monksy · Accepted Answer · 2011-02-14 05:21:17Z

1

The issue was that the database didn't exist where it was expected to. In turn, it just created an empty file and database.

answered Feb 14, 2011 at 5:21

monksy

14.2k19 gold badges78 silver badges128 bronze badges

Comments

Mark Tolonen · Accepted Answer · 2011-02-14 04:31:45Z

0

cur.fetchall() retrieves all the lines, but you don't save them in a variable. Change to print cur.fetchall() and drop everything after it in your script, or comment the fetchall out and the iterator will work:

Example

import sqlite3
db = sqlite3.connect(':memory:')
cur = db.cursor()
cur.execute('create table  a(a,b,c,d)')
cur.execute('insert into a values(1,2,3,4)')
cur.execute('insert into a values(2,3,4,5)')
cur.execute('insert into a values(3,4,5,6)')
cur.execute('insert into a values(4,5,6,7)')
cur.execute('select * from a')
for i in cur:
    print i

Output

(1, 2, 3, 4)
(2, 3, 4, 5)
(3, 4, 5, 6)
(4, 5, 6, 7)

edited Feb 14, 2011 at 4:31

answered Feb 14, 2011 at 4:20

Mark Tolonen

181k26 gold badges182 silver badges278 bronze badges

Collectives™ on Stack Overflow

Issue with basic SQLite database access in Python

4 Answers 4

3 Comments

4 Comments

Comments

Example

Output

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

4 Comments

Comments

Example

Output

Comments

Your Answer

Sign up or log in

Post as a guest

Related