1

I query data from my "data.sqlite"'s database, run my project and get "sqlite3.OperationalError: no such table: blog_post". It would be great if you could help me fix this error. Thank you!

I created a table called "BlogPost", its table name as "blog_post" in the models.py:

class BlogPost(db.Model):
    __tablename__ = 'blog_post'
    users = db.relationship(User)

    blog_id = db.Column(db.Integer, primary_key=True)

    user_id = db.Column(db.Integer,db.ForeignKey('users.id'), nullable=False) 
    date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)  #
    problem_name = db.Column(db.String(140), nullable=False)
    text = db.Column(db.Text, nullable=False)
    blog_image = db.Column(db.String(140), nullable=False, server_default='default_blog.jpg')


    def __init__(self, text, problem_name, user_id, blog_image):
        self.text = text
        self.problem_name = problem_name
        self.user_id = user_id
        self.blog_image = blog_image



    def __repr__(self):
        return f"Post ID: {self.post_id} -- Date:{self.date}---{self.problem_name}"

Using query console, I checked and see that this table is storing data just fine and I can see that there is no error.

I wanted to query data from this table and show it to the HTML file on my web page so I imported it with the command "myproject.models import BlogPost" into the python file called "views.py" (My Pycharm IDE show that the command is working fine and show no error)

Then I have these codes below: 

from flask import render_template, request, Blueprint
import _sqlite3
...some other import

init_db()

conn = _sqlite3.connect('data.sqlite', check_same_thread=False)
c = conn.cursor()
core = Blueprint('core', __name__)

@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()
        return render_template('blog_search_result.html', results=results)

Picture of my data.sqlite database:

enter image description here

I query to see the table blog_post in the console and it identified that the table name is blog_post. It also shows all the data I inputted in the website:

enter image description here

By the way, this is one of the code that I used to create my "data.sqlite" database inside my project's init.py: 

basedir = os.path.abspath(os.path.dirname(__file__))
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir,
                                                                    'data.sqlite') 
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
davidism
  • 121,510
  • 29
  • 395
  • 339
Upchanges
  • 310
  • 2
  • 14

2 Answers2

0

When you do a request, you do it from a table.

Your command is:

c.execute("SELECT * FROM BlogPost WHERE problem_name LIKE (?)", ('%' + str(search) + '%',))

Your table name seems to be "blog_post" and not "BlogPost" like it is in your request.

I do not have the whole picture of your code so you could try and give the feedback.

you could write your code using format() command something like this to have access the tablename design in your model.

c.execute("SELECT * FROM {0} WHERE problem_name LIKE {1}".format(tablename, '%' + str(search) + '%')))

(tablename represents your tablename in your model used for creation)

Like the problem seems to be related to the path code is trying to access database :

1/ to know the current directory put here the print(os.getcwd()) command :

if request.method == 'POST':
    print("current working path : ", os.getcwd())
    c.execute("SELECT * FROM blog_post WHERE problem_name LIKE (?)", ('%' + str(search) + '%',))

2/ To test if connection works with the real rPath = '.../database'

conn = _sqlite3.connect(rPath, check_same_thread=False)
Laurent B.
  • 1,653
  • 1
  • 7
  • 16
  • Hi Laurent! Thank you for helping me! I tried c.execute("SELECT * FROM blog_post", ('%' + str(search) + '%',)) but it still give out the same error: sqlite3.OperationalError: no such table: blog_post (or BlogPost when I changed to BlogPost in the c execute) – Upchanges May 17 '20 at 09:59
  • Hi please to meet you, could you edit your created sqlite db with a software to identify the real name of the table after creation ? and then you put "real_name" in the format command to see what's happen – Laurent B. May 17 '20 at 10:04
  • Hi Laurent! Really appreciate that you are helping me. I don't think it's the table name that is causing the error as the console of my "data.sqlite" database could still identify the table "blog_post" and query data for me to view.... (I just edited and added a picture that show this in my post) – Upchanges May 17 '20 at 10:17
  • ok maybe it's the access to the database if it has already been edited elsewhere ? You should ensure access is free before use – Laurent B. May 17 '20 at 10:21
  • I didn't edit anything with the database. I create the database with the name "data.sqlite" and it shows its name as "data.sqlite" in my project folder. I just put a new picture that may help you understand more about my "data.sqlite" database. – Upchanges May 17 '20 at 10:26
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/214047/discussion-between-laurent-b-and-upchanges). – Laurent B. May 17 '20 at 10:34
0

I just fixed this! A lot of thanks to Laurent(comment up above) for helping me!

So I just moved the data.sqlite file to the outside, in the biggest folder. The error was because I wrote the wrong path in the importing command;)

But his or her codes also work so check that out!

Now it identified the table just fine:)

Upchanges
  • 310
  • 2
  • 14