56

I am trying to store data about pupils at a school. I've done a few tables before, such as one for passwords and Teachers which I will later bring together in one program.

I have pretty much copied the create table function from one of these and changed the values to for the Pupil's information. It works fine on the other programs but I keep getting:

sqlite3.OperationalError: no such table: PupilPremiumTable

when I try to add a pupil to the table, it occurs on the line:

cursor.execute("select MAX(RecordID) from PupilPremiumTable")

I look in the folder and there is a file called PupilPremiumTable.db and the table has already been created before, so I don't know why it isn't working.

Here is some of my code, if you need more feel free to tell me so, as I said it worked before so I have no clue why it isn't working or even what isn't working:

with sqlite3.connect("PupilPremiumTable.db") as db:
    cursor = db.cursor()
    cursor.execute("select MAX(RecordID) from PupilPremiumTable")
    Value = cursor.fetchone()
    Value = str('.'.join(str(x) for x in Value))
    if Value == "None":
        Value = int(0)
    else:
        Value = int('.'.join(str(x) for x in Value))
    if Value == 'None,':
        Value = 0
    TeacherID = Value + 1
    print("This RecordID is: ",RecordID)
Improve this question
7
  • 2
    Have you created the table PupilPremiumTable or not ? Commented Jan 24, 2015 at 13:48
  • Yes the table has already been created before, sorry if this was not clear. It is in the same folder as this python file. Commented Jan 24, 2015 at 13:50
  • 1
    Note that if you have a file called PupilPremiumTable.db it doesn't mean that you have a table called PupilPremiumTable Commented Jan 24, 2015 at 13:51
  • I've tried connecting to both PupilPremiumTable and PupilPremiumTable.db. When I changed line: with sqlite3.connect("PupilPremiumTable.db") as db: to with sqlite3.connect("PupilPremiumTable") as db: It didn't throw up an error, but still got stuck in the same place, and the same thing when I changed cursor.execute("select MAX(RecordID) from PupilPremiumTable") to cursor.execute("select MAX(RecordID) from PupilPremiumTable.db") Commented Jan 24, 2015 at 13:55
  • 2
    @BhargavRao: the table exists, the database exists, but by using a relative path you open it in the current working directory. The current working directory can be anything, and depends on how the script was started. Commented Jan 24, 2015 at 14:06

8 Answers 8

Reset to default
106

You are assuming that the current working directory is the same as the directory your script lives in. It is not an assumption you can make. Your script is opening a new database in a different directory, one that is empty.

Use an absolute path for your database file. You can base it on the absolute path of your script:

import os.path

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
db_path = os.path.join(BASE_DIR, "PupilPremiumTable.db")
with sqlite3.connect(db_path) as db:

You can verify what the current working directory is with os.getcwd() if you want to figure out where instead you are opening the new database file; you probably want to clean up the extra file you created there.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

8

I had the same problem and here's how I solved it.

  1. I killed the server by pressing Ctrl+C
  2. I deleted the pychache folder. You'll find this folder in your project folder.
  3. I deleted the sqlite db.
  4. I made migrations with python manage.py makemigrations <app_name> where <app_name> is the specific app that contains the model that's causing the error. In my case it was the mail app so I ran python manage.py makemigrations app.
  5. I migrated in the normal way.
  6. Then I started the server and it was all solved.

I believe the issue is as Jorge Cardenas said:

Maybe you are loading views or queries to database but you haven´t granted enough time for Django to migrate the models to DB. That's why the "table doesn't exist".

This solution is based on this youtube video

Improve this answer

2 Comments

After updating python within conda env, there was still a db instance in the old python version. So deleting pycache and recreating db was helpful find . -type f -name '*.py[co]' -delete -o -type d -name __pycache__ -delete
deleting pycache file was my case. I changed the sqlite3 table structure but the pycache file still contained the previous table structure, which led kind of conflicts(operational exceptions) unintentionally.
1

First, you need to check if that table 100% exist in the database. You can use sqlite viewer for that: https://inloop.github.io/sqlite-viewer/.

If the table exists, then you can write your table name in '', for example:

Select * from 'TableName'

Whatever your query is, I am just using Select * as an example.

Improve this answer

Comments

0

I have to face same issue and there are a couple of approaches, but the one I think is the most probable one.

Maybe you are loading views or queries to database but you haven´t granted enough time for Django to migrate the models to DB. That's why the "table doesn't exist".

Make sure you use this sort of initialization in you view's code:

form RegisterForm(forms.Form):

    def __init__(self, *args, **kwargs):
        super(RegisterForm, self).__init__(*args, **kwargs)

A second approach is you clean previous migrations, delete the database and start over the migration process.

Improve this answer

Comments

0

I had the same issue when I was following the flask blog tutorial. I had initialized the database one time when it started giving me the sqlite3.OperationalError: then I tried to initialize again and turns out I had lots of errors in my schema and db.py file. Fixed them and initialized again and it worked.

Improve this answer

2 Comments

The specific error was about "no such table". This answer would be more helpful if you described what "lots of errors" you got and how exactly you "fixed them".
sqlite3.OperationalError: no such table: user was the error I had. I was getting this error because in my schema.sql I had syntax errors in my user table. I forgot to add a coma. Fixed the syntax error and initialized the database again and error went away.
0

Adding this worked for me:

import os.path

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
db_dir = (BASE_DIR + '\\PupilPremiumTable.db')

Note the need for \\ before PupilPremiumTable.bd for the code to work .

Improve this answer

Comments

0

Just check the name of the table that you have created in the first execute command. It has to match the name of the table where you want to insert an entry.

Improve this answer

Comments

-1

In my case the problem was syntax errors (such as missing comas) in the schema file.

Initially, I was able to bypass this error by printing out the schema before executing (for some reason sqlite didn't throw error). But then harder to debug errors such as sqlite3.OperationalError: no such table: occurred later on in the app.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.