Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Python sqlite3.OperationalError: no such table:

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)
like image 423
Ben Avatar asked Jan 24 '15 13:01

Ben


3 Answers

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.

like image 139
Martijn Pieters Avatar answered Oct 21 '22 05:10

Martijn Pieters


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

like image 3
GeoAfrikana Avatar answered Oct 21 '22 04:10

GeoAfrikana


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.

like image 1
Usman Ghani Mughal Avatar answered Oct 21 '22 06:10

Usman Ghani Mughal