MySQL data when retrieved with Python

Question

The data from MySQL is being displayed with extra characters that should not be there. I am using Python 2.7 and Django.

I tried to search how to fix this but I didn't even know what to call the issue..

This is how the Data is showing: ('Royal Ashburn',) for a CharField or (5L,) for an IntegerField (should just show 5)

I have a feeling this has to do with Json or something of that effect but I am unsure (I am still very new to this) Is there a way to fix this?

View.py

def getCourses(request):

db = MySQLdb.connect(host="localhost", user="***", passwd="***", db="golf")
cur = db.cursor()
cur.execute("SELECT course_name FROM golfapp_golfcourses")
data = cur.fetchall()

"""cur.close()
connection.close()
sys.exit()"""

return render_to_response('DBTest.html', {'data':data})

course.html

{% include "base.html" %}
<html>
<body>

<select name="list_courses">
{% for data in data %}
 <option>{{ data }} </option>
{% endfor %}
</select>


</body>
</html>

EDIT As per comment on answer: Views.py

def GetAllCourses(request):
    db = MySQLdb.connect(host="localhost", user="edwardb", passwd="edwards17",         db="golfapp")
    cur = db.cursor()
    cur.execute("SELECT course_name, par_front_9, par_back_9, total_par FROM     golfapp_golfcourses")
    data1 = [course_name for (course_name,) in cur.fetchall()]
    data2 = [par_front_9 for (par_front_9,) in cur.fetchall()]
    data3 = [par_back_9 for (par_back_9,) in cur.fetchall()]
    data4 = [total_par for (total_par,) in cur.fetchall()]

"""cur.close()
connection.close()
sys.exit()"""

    return render_to_response('golfcourses.html', {'data1':data1}, {'data2':data2}, {'data3':data3}, {'data4':data4},)

Template:

{% extends "base.html" %}

{% block content %}

<table style="margin-left:15%" class="table table-bordered">
  <tbody>
  <th>
    <td>Golf Course</td>
    <td>Front 9</td>
    <td>Back 9</td>
    <td>Total Par</td>
  </th>

  <tr>
    {% for data1 in data1 %}
    <td>{{ data1 }} </td>
    {% endfor %}
    {% for data2 in data2 %}
    <td>{{ data1 }} </td>
    {% endfor %}
    {% for data3 in data3 %}
    <td>{{ data3 }} </td>
    {% endfor %}
    {% for data4 in data4 %}
    <td>{{ data4 }} </td>
    {% endfor %}
  </tr>
 </tbody

</table>
</div>
 {% endblock %}

The "L" just means its an integer, it won't be displayed on screen. The () is the tuple of results. — user1971598
– user1971598, Commented Mar 25, 2014 at 1:34
The L is actually displayed in the html template list box along the the brackets and commas. — edwards17
– edwards17, Commented Mar 25, 2014 at 1:55

univerio · Accepted Answer · 2014-03-26 21:44:47Z

1

fetchall() returns a tuple of the columns, for each row. Even if you requested a single column, each row in data is still a tuple, just of a single element. You want:

data = [course_name for (course_name,) in cur.fetchall()]

EDIT: To render multiple columns, you don't need to "clean up" at all:

data = cur.fetchall()

You just need to access each column in your template:

{% for course_name, par_front_9, par_back_9, total_par in data %}
<tr>
  <td>{{ course_name }}</td>
  <td>{{ par_front_9 }}</td>
  <td>{{ par_back_9 }}</td>
  <td>{{ total_par }}</td>
</tr>
{% endfor %}

edited Mar 26, 2014 at 21:44

answered Mar 25, 2014 at 2:10

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

edwards17 Over a year ago

How would I cleanup multiple columns of data and display in a table? see edited question for an idea of what I mean.

edwards17 Over a year ago

Worked perfectly! I assumed that wouldnt work based on the original question.. When doing this, it seems as if the data from course_name starts its own column and offsets all rows one to the left, leaving total_par with no data. I tried removing all css to see if that was the issue, didnt solve the problem though. Any thoughts? (sorry for so many questions)

univerio Over a year ago

@edwards17 Your HTML markup is not entirely valid. It's difficult to say whether the template or the code is the issue. You should try to correct the markup first and see if that fixes it. (e.g. <th> is a heading cell, not a heading row, the row should still be <tr>; closing </tbody> does not have the final >)

edwards17 Over a year ago

Changing <th> to <tr> did the trick, thanks for the help! Gotta pay more attention to the markup.

edwards17 Over a year ago

I am getting a similar issue when using teecolor = GolfCourseTees.objects.values('Tee_Color').filter(Course_Name=course) When setting color1= teecolor[0] color 1 will show as: {'Tee_Color': u'Blue'}. Any thoughts? I am trying to use color1 to filter another table but wont be able to filter until it is just equal to "blue"

|

Collectives™ on Stack Overflow

MySQL data when retrieved with Python

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related