Python Unicode issues on sql server database result set

Question

I'm trying to retrieve the database results to a webpage using Python 2.7(flask) but stuck with the unicode issues. Below is the code I'm using

def formval():
 # --connection syntax here--
 cursor = connection.cursor()
 if request.method == 'POST':
 x = request.form['weekno']
 cursor.execute("SELECT Document FROM Backlog where custno=?",x)
 result = cursor.fetchall()
 return render_template('home.html',weekno=result)

home.html

{% for docno in weekno %}
 <p>{{ docno }}</p>
{% endfor %}

Output : This gives me the output as below

 u'sp234780'
 u'sd257679'
---------
---------

But when I use return render_template('home.html',weekno=result[0]) I'm getting the output as just sp234780 but only the first row not the entire results.

I've gone through all the posts related to encoding and tried to use encode('utf-8'), sys.setdefaultsetting(utf-8) etc but no luck

Please suggest

Answer 1

The query returns a tuple of tuples. You iterate through the tuple of rows, but each row is itself a tuple, containing a single element. You need to access the element itself.

{% for docno in weekno %}
 <p>{{ docno[0] }}</p>
{% endfor %}

Answer 2

You can add one line after the line

result = cursor.fetchall()

Add this:

result = [str(res) for res in result]