using python 2.7 to query sqlite3 database and getting "sqlite3 operational error no such table"

Question

My simple test code is listed below. I created the table already and can query it using the SQLite Manager add-in on Firefox so I know the table and data exist. When I run the query in python (and using the python shell) I get the no such table error

def TroyTest(self, acctno):

    conn = sqlite3.connect('TroyData.db')
    curs = conn.cursor()

    v1 = curs.execute('''
    SELECT acctvalue 
    FROM balancedata
    WHERE acctno = ? ''', acctno)
    print v1  
    conn.close()

Answer 1

When you pass SQLite a non-existing path, it'll happily open a new database for you, instead of telling you that the file did not exist before. When you do that, it'll be empty and you'll instead get a "No such table" error.

You are using a relative path to the database, meaning it'll try to open the database in the current directory, and that is probably not where you think it is..

The remedy is to use an absolute path instead:

conn = sqlite3.connect('/full/path/to/TroyData.db')

You need to loop over the cursor to see results:

curs.execute('''
    SELECT acctvalue 
    FROM balancedata
    WHERE acctno = ? ''', acctno)

for row in curs:
    print row[0]

or call fetchone():

print curs.fetchone()  # prints whole row tuple