(flask) python mysql - how to pass selected data though a for loop and return it?

Question

In my Flask project, I want to select everything from a table in database and print each row in separate line?

How should I pass data through a for loop in this script below?

And how can I return it?

In app.py:

from flask import Flask, render_template
import MySQLdb

app = Flask(__name__)

@app.route('/')
def db():
    db = MySQLdb.connect("localhost","myusername","mypassword","mydbname" )

    cursor = db.cursor()

    cursor.execute("SELECT * from p_user")

    cursor.fetchall()

    db.close()

    return


if __name__ == '__main__':
    app.run()

===============================================

Even this try didn't work.

I edited 2 lines in app.py like this:

data = str(cursor.fetchall())

return render_template('db.html', data = data)

And created db.html like this:

{% for each in data %}

    print {{ each }}<br>

{% endfor %}

And the output was:

print (
print (
print 1
print ,
print
print '
print s
print h
....

There is a list of tuples, how I can reach each index.

Any more solution?!

niloofar
– niloofar

2016-07-23 13:15:26 +00:00
Commented Jul 23, 2016 at 13:15 — niloofar
– niloofar, Commented Jul 23, 2016 at 13:15

Iron Fist · Accepted Answer · 2016-07-23 14:22:28Z

Normally, cursor.fetchall() returns a list of tuples, so just save that list into a variable and return it, then loop through the returned value, this way

@app.route('/')
def db():
    db = MySQLdb.connect("localhost","myusername","mypassword","mydbname" )

    cursor = db.cursor()

    cursor.execute("SELECT * from p_user")

    rows = cursor.fetchall()

    db.close()

    return rows

rows = db()

for row in rows:
    print(row)

Or even simply and efficiently (by saving memory if you have a huge table) without calling cursor.fetchall at all:

@app.route('/')
def db():
    db = MySQLdb.connect("localhost","myusername","mypassword","mydbname" )

    cursor = db.cursor()

    cursor.execute("SELECT * from p_user")

    rows = []

    for row in cursor:
        rows.append(row)
        print(row)

    return rows

EDIT:

No need to str the results, just pass them as they are(list of tuples) to your template, this way:

data = cursor.fetchall()    
return render_template('db.html', data = data)

And your template should look like:

<table border="1" cellpadding="5" cellspacing="5">
{% for row in data %}
    <tr>
    {% for d in row %}
        <td>{{ d }}</td>
    {% endfor %}
    </tr>
{% endfor %}
</table>

This should print them as a table.

Yes right it returns a list of tuples, of course if I set rows = str(cursor.fetchall()) and return rows, then I will face with a list of tuples. But your script returns this error for me: Internal Server Error. The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.
I have a question here... Those last three lines are placed after return part, so they are not able to fo anything right?! every thing should happen before return part and then should be passed trhough return part in orther to be printed on the webpage, am I right?
Those lines are outside db() function, so they need the result returned from db() to print each row...Is your table of huge size?
@niloofar...I've edited my answer to reflect what you added into your question...

Takezoshi · Accepted Answer · 2016-07-23 09:43:48Z

0

Try something like this:

row=cursor.fetchone()
while row is not None:
    print row
    # you can access to each column by looping over row
    # for column in row:
    #    print row
    row=cursor.fetchone()

answered Jul 23, 2016 at 9:43

Takezoshi

1045 bronze badges

Comments

Leyo R · Accepted Answer · 2016-07-23 17:38:03Z

Use an ORM like Flask-SQLAlchemy, Define your table using the model class and then use it to fetch data

class Post(db.Model):

  id = db.Column(db.Integer, primary_key=True)
  title = db.Column(db.String(128))
  body = db.Column(db.Text)

  def __init__(self, title, body):
    self.title = title
    self.body = body


@app.route('/' )
def index():
 post = Post.query.all()

return render_template('index.html', post=post)

There is great example at https://techarena51.com/index.php/flask-sqlalchemy-tutorial/

Collectives™ on Stack Overflow

(flask) python mysql - how to pass selected data though a for loop and return it?

3 Answers 3

7 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Related