[FIXED] How to get cursor.execute() results as String (not tuple)?

Issue

I am trying to implement a (simple) forum style web-app. I am currently trying to display the titles of threads (‘topics’) from my database on the web page. I am using a mysql connector cursor to interact with the database, and when returning the results I want to display them in a plain string format instead of the tuple that cursor.fetchall() returns, but I cannot find a way to do this with the cursor. Would anyone know of a way to do this without changing the way in which I interact with the database?

This is my code for fetching the topic titles and passing them into the template I render them in:

 cursor.execute('SELECT title FROM topics;')
    topics = cursor.fetchall()
    return render_template('forumIndex.html', topics=topics)

This is the jinja code in the template that renders the topic titles into the unordered list on the page:

{% for topic in topics %}
<ul class="topic-list" id="topics">
    <li class="topic-list-item">{{ topic }}</li>
</ul>
{% endfor %}

Titles are outputted onto the page in tuple form as follows:

(‘test’,)

(‘This is a topic title’,)

(‘An Amazing Title!’,)

I think I’m not quite understanding how the cursor works no matter how much I read about it. If anyone is able to explain where I’m going wrong or even just point out what areas I’m not understanding, it would be much appreciated.

Thank you.

Solution

The MySql connector conforms to the Python DBAPI specified in PEP249. As such, the fetchall method is required to return a "sequence of sequences", meaning that you will have to receive a list of tuples from that method. What you can do though is manipulate that sequence to display it as you want. There are multiple ways to go about it, but one simple way of doing it is to modify the way that you return your topics variable:

return render_template('forumIndex.html', topics=[topic[0] for topic in topics]) # go from [('item',), ...] to ['item', ...]

If you have a large number of topics, you could probably get more efficient by instead changing it in the render template, so that you don’t iterate over the data twice.

Answered By – Stephen

Answer Checked By – Laura B. (Easybugfix Admin)

Leave a Reply

(*) Required, Your email will not be published