My Python file connected through the right path with my sqlite database but couldn't identify a table in that database

Question

I want to connect my SQLite database/ or I think I'm connecting my "data.sqlite" (the blue highligted one in the picture below) with my views.py in the core folder (picture below) with:

rPath='../../Upchanges/data.sqlite'
conn = _sqlite3.connect(rPath, check_same_thread=False)  
c = conn.cursor()

Path of the database I want to connect with: Upchanges_desperate/Upchanges/data.sqlite

Path of the Python file I want to connect with the database: Upchanges_desperate/Upchanges/core/views.py

Picture of my file orientation:

So this is my problem: I later use these codes below in my python file:

from Upchanges.models import BlogPost( I set the __tablename__ to 'blog_post' in another python file)
@core.route('/', methods=['GET', 'POST'])
def index():

    search = Blogsearch_form(request.form)
    if request.method == 'POST':
        c.execute("SELECT * FROM blog_post WHERE problem_name LIKE(?)", ('%' + str(search) + '%',))
        results = c.fetchall()
        print(results)
        return render_template('blog_search_result.html', results=results)



    page = request.args.get('page',1,type=int)
    many_posts = BlogPost.query.order_by(BlogPost.date.desc()).paginate(page=page, per_page=10)
    return render_template('index.html', many_posts=many_posts, form=search)

When I input something and click enter in the Blogsearch_form, my website shows this error: "sqlite3.OperationalError: no such table: blog_post". I also tried replacing blog_post with BlogPost but it shows the same error: "sqlite3.OperationalError: no such table: BlogPost"

I promise you I have a blog_post table in my data.sqlite database. I use it also in some codes below and my website query the data and work just fine (show everything later on in the HTML file):

page = request.args.get('page',1,type=int)
    many_posts = BlogPost.query.order_by(BlogPost.date.desc()).paginate(page=page, per_page=10)
    return render_template('index.html', many_posts=many_posts, form=search)

Addtionally, for more clarity, these codes below (which is in the init.py of my app.py file) are those that create my data.sqlite database:

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir,
                                                                    '../Upchanges/data.sqlite')

So, I really think that something is wrong with the path in the _sqlite3.connect command. I would greatly appreciate if you could help me fix the problem above. (Please use the picture when you think about the path as it is easier to imagine)

Thank you!!

P.s/ I tried this from my previous question: https://stackoverflow.com/a/61849867/13097721 but I still couldn't fix this problem.

Answer 1

In fact you do not necessarily know from which directory the application has been started from.

You could test this with

import os
print("My current working directory is", os.getcwd())

However if your database is located relative to a python file, then it is better to determine the path relative to the python file's directory than trying to determine it relative to the current working directory.

You can do this for example with with

import os
MYDIR = os.path.dirname(__file__)
SQLPATH = os.path.join(MYDIR, "..", "..", "data.sqlite")

I also suggest to add following line in your existing code or in your modified code. It will show you exactly where you point to

print("THE REAL SQLPATH is", os.path.realpath(SQLPATH))

Comment after your feedback: I Will add more prints (and remove one "..") but you will see what you need to do with two more prints.

import os
print("MY FILE = ", os.path.realpath(__file__))
MYDIR = os.path.dirname(__file__)
print("MYDIR = ", os.path.realpath(MYDIR))
SQLPATH = os.path.join(MYDIR, "..", "data.sqlite")
print("This gives me SQLPATH = ", os.path.realpath(SQLPATH))

Comment after next Feedback

So it seems how, that the database file is now properly located.

I suggest to inspect your database.

from a shell window type:

sqlite3  /Users/kienletrung/Desktop/Upchanges_desperate/Upchanges/data.sqlite

type then commands like .tables to list all tables .schema <tablename> to look at a tables format. or select * from tablename; to look at all rows of a table.

alternatively just type:

echo .dump | sqlite3 /Users/kienletrung/Desktop/Upchanges_desperate/Upchanges/data.sqlite  > fulldump.txt

to create a full ascii dump of your database.

If you don't have the sqlite command line client installed then just install it. It should simplify debugging.

You can of course use any other tool to inspect your data base in look at what tables / structures your python code expects and what the database really contains.