MysqlDB call TypeError: 'int' object is not iterable python

Question

I am running a query like:

SELECT post_title, post_name FROM wp_posts WHERE post_status='publish' ORDER BY RAND() LIMIT 3

which outputs information like:

ID   post_title       post_name
1    a title here     a-title-here
2    a title here2     a-title-here2
3    a title here3     a-title-here3

and trying to call it in app.py for flask :

@app.route('/', methods=('GET', 'POST'))
def email():
        blogposts = c.execute("SELECT post_title, post_name FROM wp_posts WHERE post_status='publish' ORDER BY RAND() LIMIT 3")

        return render_template('index.html', form=form, blogposts=blogposts)

In my template, I'm calling blogposts like:

    {% for blogpost in blogposts %}
    <li><a href="{{blogpost[1]}}">{{blogpost[0]}}</a></li>
    {% else %}
    <li>no content...</li>
    {% endfor %}

But Im getting an error of :

    {% for blogpost in blogposts %}
TypeError: 'int' object is not iterable

What am i doing wrong here?

Answer 1

The .execute calls in Python MySQL (and other databases) connectors do not return the database results directly. (In this case it is returning the number of matched records).

You have to make a further call to the database driver to retrieve the actual select results. The simpler one is the .fetchall() method that will return you all selected results as a list.

In short, change your call to executefor these two lines:

...
c.execute("SELECT post_title, post_name FROM wp_posts WHERE post_status='publish' ORDER BY RAND() LIMIT 3")
blogposts = c.fetchall()