Issue with basic SQLite database access in Python

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 py开发者_运维问答sqlite2 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)

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.

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.

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

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)