-2

First, I understand the value of using ORM solutions, and will use SQL-Alchemy later.

I have installed Flask and am using flask-mysql.

I do not know how to get the results for a SQL "desc " command.

Here is the code I'm working with:

from flask import Flask, render_template, request, redirect, jsonify
import requests
from flaskext.mysql import MySQL

app = Flask(__name__)

@app.route("/")
def main():
    return render_template('login.html')

@app.route("/login", methods=['POST'])
def login():
    #username = request.form['username']   #not using the form fields yet
    #password = request.form['password']
    mysql = MySQL()
    app.config['MYSQL_DATABASE_USER'] = '<my user here>'
    app.config['MYSQL_DATABASE_PASSWORD'] = '<password here>'
    app.config['MYSQL_DATABASE_DB'] = '<database name here>'
    app.config['MYSQL_DATABASE_HOST'] = '<database server IP address here>'
    mysql.init_app(app)

    conn = mysql.connect()
    cursor = conn.cursor()
    #cursor = mysql.connection.cursor()  #invalid code, at least for this version of flask-mysql
    cursor.execute("desc user;")
    result = jsonify(cursor.fetchall())

    #row = cursor.fetchone()
    return "<!DOCTYPE html><html><body>" + str(result)+ "</body></html>"

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

It appears to be connecting to the database, logging in, and sending the desc command OK because result's contents are "Response 268 bytes [200 OK]" (can see that by looking at the page source code after getting the response in the browser).

Is there any way to get the results (table description) and not just an "OK I ran this command"?

Thank you.

JakeJ
  • 2,361
  • 5
  • 23
  • 35
  • The template is just a simple HTML page with a form that does the post action. The form has a username and password field and a button. – JakeJ Oct 21 '17 at 20:18
  • User is a tablename in my database. – JakeJ Oct 21 '17 at 20:21
  • are you trying to do some AJAX ? or you just need to render the columns properties in a simple way ? – PRMoureu Oct 21 '17 at 21:38
  • No, this isn't AJAX because the form itself posts to the /login page which is handled by the Flask app (it's a Python server program, you can see the source above). – JakeJ Oct 21 '17 at 21:40
  • In this case, I'm not actually using the values in the form - you can see how you would get them in commented out lines. – JakeJ Oct 21 '17 at 21:44
  • You guys seriously downvoted this? Do you not see how getting a description of tables could be very useful? Wow, stackoverflow has gone WAY downhill lately. I'm going to have to find another community. – JakeJ Oct 22 '17 at 18:27
  • In any case, I found this other question that seems to have an answer along the lines of what I was trying to do here. Instead of using flask-mysql, they are importing mysqldb instead: https://stackoverflow.com/questions/23786674/python-mysqldb-how-to-get-columns-name-without-executing-select-in-a-big-tab alecxe gave a good answer. – JakeJ Oct 23 '17 at 23:34

1 Answers1

0

I suppose you would like to get all records and sort them in desc order. You may try this.

from flask_mysqldb import MySQL

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 
app.config['MYSQL_DB'] = 
app.config['MYSQL_CURSORCLASS'] = 'DictCursor'


# initialize mysql
mysql = MySQL(app)

...

@app.route('/posts')
def posts():
    cur = mysql.connection.cursor()
    result = cur.execute('SELECT * FROM posts ORDER BY postdate DESC ')
    posts = cur.fetchall()

    if result > 0:
        return render_template('posts.html', posts=posts)
    else:
        message = 'I shouldn't find any post'
        return render_template('posts.html', message=message)

    cur.close
  • OK. I do have admin on the MySQL server and can connect a different client to it in order to look at the tables while I make the user template. I guess I just have a habit of automatically doing a desc on any table before I query it for some reason, even if I made the table myself earlier. Definitely a strange habit. I just found it interesting that I couldn't get results back from the desc table command. – JakeJ Oct 21 '17 at 21:43
  • Of course, you have to know that your table has a column named "postdate" in this case. If you don't know the table structure, you are out of luck. – JakeJ Oct 21 '17 at 22:02