I'm trying to test a basic connection between Flask and MySQL

My database host address is: cgriffis.mysql.pythonanywhere-services.com

My database is:cgriffis$states I verified my MySQL password.

In MySQL, show tables gives me: mysql> SHOW tables; +---------------------------+ | Tables_in_cgriffis$states | +---------------------------+ | States | +---------------------------+ 1 row in set (0.00 sec)

Describe States gives me: mysql> DESCRIBE States; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | statedate | varchar(32) | NO | PRI | NULL | | | state | varchar(2) | YES | | NULL | | | dates | date | YES | | NULL | | | AvgAnnHNI | decimal(10,7) | YES | | NULL | | | AvgAnnUnemp | decimal(3,1) | YES | | NULL | | | AvgAnnPov | decimal(3,1) | YES | | NULL | | | AvgAnnSNAP | decimal(3,1) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)

SHOW * FROM States gives me records: | WY_43101 | WY | 2018-01-01 | 0.7923838 | 4.1 | 10.7 | 4.9 | | WY_43466 | WY | 2019-01-01 | 0.7997534 | 3.7 | 9.9 | 4.5 | | WY_43831 | WY | 2020-01-01 | 1.0005078 | 5.9 | 9.2 | 4.7 | | WY_44197 | WY | 2021-01-01 | 0.7886803 | 4.6 | 10.6 | NULL | | WY_44562 | WY | 2022-01-01 | 0.8148088 | 3.6 | NULL | NULL | +-----------+-------+------------+-----------+-------------+-----------+------------+ 969 rows in set (0.00 sec)

My error log shows: 2023-06-12 13:47:53,791: Exception on /query [POST] Traceback (most recent call last): File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 2077, in wsgi_app response = self.full_dispatch_request() File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1525, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1523, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1509, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args) File "/home/cgriffis/mysite/flask_app.py", line 44, in execute_query return render_template('queryresults.html', results=results) File "/usr/local/lib/python3.10/site-packages/flask/templating.py", line 148, in render_template return _render( File "/usr/local/lib/python3.10/site-packages/flask/templating.py", line 128, in _render rv = template.render(context) File "/usr/local/lib/python3.10/site-packages/jinja2/environment.py", line 1301, in render self.environment.handle_exception() File "/usr/local/lib/python3.10/site-packages/jinja2/environment.py", line 936, in handle_exception raise rewrite_traceback_stack(source=source) File "/home/cgriffis/mysite/templates/queryresults.html", line 11, in top-level template code {% for column in results[0].keys() %} File "/usr/local/lib/python3.10/site-packages/jinja2/utils.py", line 83, in from_obj if hasattr(obj, "jinja_pass_arg"): jinja2.exceptions.UndefinedError: 'tuple object' has no attribute 'keys'

My Flask app seems simple - I'm just trying to verify a connection before doing some work with matplotlib. from flask import Flask, render_template, request import mysql.connector

app = Flask(name)

MySQL Configuration

mysql_host = 'cgriffis.mysql.pythonanywhere-services.com' mysql_user = 'cgriffis' mysql_password = '*****' #password commented out mysql_database = 'cgriffis$states'

Route for the home page

@app.route('/') def home(): return render_template('index.html')

Route for executing the SQL query

@app.route('/query', methods=['POST']) def execute_query(): # Get the SQL query from the user query = request.form['query']

try:
    # Connect to the MySQL database
    conn = mysql.connector.connect(
        host=mysql_host,
        user=mysql_user,
        password=mysql_password,
        database=mysql_database
    )

    # Execute the SQL query
    cursor = conn.cursor()
    cursor.execute(query)

    # Fetch all the results
    results = cursor.fetchall()

    # Close the cursor and the database connection
    cursor.close()
    conn.close()

    # Pass the results to the queryresults.html template
    return render_template('queryresults.html', results=results)

except mysql.connector.Error as error:
    return f"Error: {error}"

Run the Flask application

if name == 'main': app.run()

I got a basic show all records thing going (and I shut down the goofy box to run sql queries, that's pretty dangerous), so my connection works. I better watch some videos and read some documents, because I don't know why it works and why it didn't. But hey, there's that.

You need to check what you get as results