Why am I suddenly getting "OperationalError: no such table"?

Question

I am trying to do various things with my database. I've connected and pulled data out and out data in, successfully, no problems. I've been debugging other issues, and then suddenly I can no longer get anything from my database table - I'm getting "OperationalError: no such table: article".

I'm really stumped here - this was working just fine, I was querying the db with no problems and inserting data, etc etc. Then suddenly I'm getting this error. The changes I made immediately before the error started appearing would seem to be totally unrelated - I undid them and still get this error. Here's the start of my script where I'm getting the error:

import sqlite3

database='mydatabase'
db=sqlite3.connect(database)
c=db.cursor()

sql_command='SELECT id FROM article'
idlist=c.execute(sql_command)

I can open that database in SQLite Administrator and verify the table is there. Plus it was working before. I've also tried to verify that the table is in there by:

>>c.execute('select name from sqlite_master where type="table"').fetchall()
[]

so something is really wacky.

I've also tried closing and reopening the db connection and cursor. And closing the Python session. No dice. Help!

Answer 1

Did you move your code to another place?

SQLite stores the database into a file, and when you call connect, if a file with the name 'mydatabase' exists, it will be loaded. Otherwise, a new fresh database file will be created automatically.

Search for your old file with the name 'mydatabase' and put it within your code.

Answer 2

I had exactly the same problem with sqlite3 and flask accessing a database. I changed the path to the database in my code to its full path and this solved the problem of data disappearing and tables not being found after restarting my flask application. I can recommend SQLite Manager firefox plugin, which helps with viewing records in your database tables. Follow this link to see a similar problem solved using full path references to your database Django beginner problem: manage.py dbsync

Python does not do path expansion.so you might want to use Environment variables to store paths to your database. Link to WingWare's Environment Variable expansion

Answer 3

I had the the problem. In my case, before trying to access the database I have this line:

 os.chdir("other/dir")
 conn = sqlite3.connect("database.db")

So sqlite3 create an empty database in that dir instead.