如何获取我刚刚查询和使用的sqlite表行的blog_id？ （在Python文件中） [英] How can I get the blog_id of the rows of the sqlite table I just query and used? (In a Python file)

问题描述

我有下面的代码，可让我网站上的用户搜索博客。在当前情况下，我的HTML页面将仅显示具有与搜索输入匹配的列的所有行中的数据列表。但是，我想获取所有我刚刚使用c.fetchall（）查询的匹配行的blog_id。

I have the codes below that let users on my website to search for blogs. With the current, my HTML page will only show a list of data from all the row that has columns that match with the search input. But, I want to get the blog_id of all the matching rows that I just query with c.fetchall().

我该怎么做？查询数据后是否应该立即编写一些代码？...如果能为您提供帮助，我将不胜感激。另外，如果可以的话，您能告诉我如何设置以下代码，以仅查询具有与搜索数据匹配的列的行吗？ ：

How would I do it? Should I write some codes right after I query data?... I would greatly appreciate if you help me. Also, if possible, could you show me how can I set the codes below to only query those rows that have a column that match the searching data? :

many_posts = BlogPost.query.order_by(BlogPost.date.desc()).paginate(page=page, per_page=10)
(These codes above are placed inside the "if request.method == 'POST':")

我的代码（Python）：

import os 
import _sqlite3

MYDIR = os.path.dirname(__file__)
SQLPATH = os.path.join(MYDIR, "..", "data.sqlite")
conn = _sqlite3.connect(SQLPATH, check_same_thread=False)  

c = conn.cursor()

@core.route('/', methods=['GET', 'POST'])
def index():
    # Call a function to later use in creating the template
    form = Blogsearch_form(request.form)

    if request.method == 'POST':
        c.execute("SELECT * FROM blog_post WHERE problem_name LIKE(?)", ('%' + str(form.search.data) + '%',))
        results = c.fetchall()
        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('blog_search_result.html', results=results, many_posts=many_posts)



    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=form)

我的BlogPost数据库创建代码：

My codes of the BlogPost's database creation:

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) #users.id  is taken from the tablename(users) and id in its table
    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}"

我的一部分HTML代码：

Part of my HTML codes:

    <h4>Search results:</h4>
    <h5>{{ results }}</h5>
   <p><small class="text-muted">If you see [ ] as the result, it means Upchanges have no relating problems to your search</small></p>

<div class="container row row-cols-1 row-cols-md-2 text-center">
{% for post in many_posts.items%}

    <div class="card border-dark mb-3 " style="width: 20rem;">
     <div class="card-body ">
         <h7><a class="text-warning" href="{{ url_for('users.user_posts', username=post.creator.first_name+post.creator.middle_name+post.creator.last_name) }}"><img class="text-center rounded" src="{{ url_for('static', filename='profile_pics/'+ post.creator.profile_image) }}" width = "35" height = "35" alt=""> {{ post.creator.first_name}} {{ post.creator.middle_name }} {{ post.creator.last_name }}</a></h7>
         <p></p>
         <img class="text-center rounded responsive1" alt="" src="{{ url_for('static', filename='blog_pics/'+ post.blog_image) }}" width = "495" height = "250">
         {# Need caution for post.blog_image on the code above #}
         <p></p>
         <h2><a class="card-tittle text-body problem" href="{{ url_for('blog_posts.blog_view', blog_validated_id=post.blog_id) }}">{{ post.problem_name[0:40]}}..</a></h2>
         <p class="card-text">{{ post.text[0:100] }}</p>
         <p><small class="text-muted">Posted on: {{ post.date.strftime('%Y-%m-%d') }}</small></p>
         <a class="btn btn-warning" href="{{ url_for('blog_posts.blog_view', blog_validated_id=post.blog_id) }}">Read more</a>
     </div>


    </div>

{% endfor %}

此外，我目前所有的代码都工作正常，没有任何错误。

Additionally, all of my current codes are working fine and showing no errors.

推荐答案

不确定我是否理解。你是说类似的东西。

Not sure I understand. Do you mean something like.

c.execute("SELECT blog_id FROM blog_post WHERE problem_name LIKE(?)", ('%' + str(form.search.data) + '%',))
results = c.fetchall()
blog_ids = [entry[0] for entry in results]

这篇关于如何获取我刚刚查询和使用的sqlite表行的blog_id？ （在Python文件中）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！