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!

score 10 · Accepted Answer · edited May 15 '22 at 01:34

10

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.

edited May 15 '22 at 01:34

Patrick Yoder

1,065
4
14
19

answered Jan 10 '13 at 16:45

MBarsi

2,417
1
18
18

2

I have not moved the code or the database. they are both in the same directory. Your suggestion did make me think that somehow the python code was not finding my database and was creating a new one instead. I added the full path to the database file and now it works! Weird... – andy Jan 10 '13 at 17:23
1

i was able to fix the problem, but it was not the issue identified here in this answer, since i didn't move anything. i upvoted it for being useful but i'm not sure that this is the "answer" - please correct me if i am wrong in my understanding of the community norm for marking an answer! – andy Jan 11 '13 at 14:14
Could you elaborate on what you did to fix your problem? I'm encountering something similar. – ericso Jul 13 '13 at 22:08
1

@ericso IIRC i had the `database='mydatabase'` and I changed it to `database='full/path/to/mydatabase'`. I still don't know *why* that fixed it but it did. – andy Nov 13 '13 at 15:16
It's quite obvious. Before it was looking for `/mydatabase`, now it's looking for `/full/path/to/mydatabase`. Go to your root dir and you'll find a blank database named "mydatabase.db". – Kevin Dec 26 '13 at 23:10

score 4 · Answer 2 · edited May 23 '17 at 10:28

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

score 2 · Answer 3 · answered Mar 02 '14 at 19:48

2

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.

answered Mar 02 '14 at 19:48

EdgarT

1,080
11
18

score 0 · Answer 4 · answered Jul 02 '20 at 01:12

I had the same problem, in my case i was creating trying to create Tables before tables before creating tables, here is an example

Base.metadata.create_all(bind=engine)  # Wrong placement

class Currency(Base):
    __tablename__ = "currency"
    date = Column(Date, primary_key = True)
    rates = Column(String)

I fixed it with replacing the position of Base Creator

class Currency(Base):
    __tablename__ = "currency"
    date = Column(Date, primary_key = True)
    rates = Column(String)

Base.metadata.create_all(bind=engine)  # Right placement

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

4 Answers4

Linked