CODEWITHSUNDEEP
pythonmysqldjango
edwards17
edwards17

Reputation: 83

MySQL data when retrieved with Python

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 %}

Upvotes: 0

Views: 97

Answers (1)

univerio
univerio

Reputation: 20548

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 %}

Upvotes: 1

Related Questions