How to fetch data from sqlite using python?

Question

I am using this sample sqlite database and my code is

import sqlite3

conn=sqlite3.connect('chinook.db')
conn.execute("SELECT * FROM tracks")
rows= conn.cursor().fetchall()

for row in rows:
    print row

It should have worked but there is no output? What am I doing wrong here?

Answer 1

The Connection.execute shortcut returns a cursor instance, which you need to use with fetchall. In your code, you're creating a new, independent cursor.

Thus:

from __future__ import print_function
import sqlite3

conn = sqlite3.connect('chinook.db')
cursor = conn.execute("SELECT * FROM tracks")
rows = cursor.fetchall()

for row in rows:
    print(row)

or even shorter (not recommended, but for those who like obscured one-liners):

rows = sqlite3.connect('chinook.db').execute("SELECT * FROM tracks").fetchall()

Or don't use Connection.execute shortcut, to avoid confusion:

from __future__ import print_function
import sqlite3

conn = sqlite3.connect('chinook.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM tracks")
rows = cursor.fetchall()

for row in rows:
    print(row)