Python/Flask mysql cursor: Why it doesn't work?

The problem is in :

app.config['MYSQL_CURSORCLASS'] = 'DictCursor'

Looking at flaskext.mysql source , that feature is not yet implemented .

And since flaskext.mysql is using pymysql , you can use DictCursor from pymysql.cursors

Example :

from flaskext.mysql import MySQL 
from flask import Flask 
from pymysql import cursors

app=Flask(__name__)

mysql = MySQL(cursorclass=cursors.DictCursor)
mysql.init_app(app)
cursor = mysql.connect().cursor()

You are constructing cursor based on flask_mysqldb , and Flask app won't be constructed itself up until the first route is hit, which means the Flask app will be constructed inside a Flask Function, and that is when your MySQL connection also can be constructed based on your app.config params, and then your cursor can be constructed based on MySQL connection: Flask Construction > MySQL Connection Construction > Cursor Construction.

So you have to use your cursor constructor inside a Flask Function: Instead of:

cur = mysql.connection.cursor()

Put:

@app.route("/")
def index():
   cur = mysql.connection.cursor()

I used the connect() method instead of get_db() and it works with Python 3.5.5. I had the same error when I used get_db

from flask import Flask
from flaskext.mysql import MySQL


app = Flask(__name__)


app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'test_db'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'

mysql = MySQL()
mysql.init_app(app)

# cursor = mysql.get_db().cursor()
cursor = mysql.connect().cursor()
print(cursor)

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_DATABASE_PORT'] = 3308 #here your port

Today I had this problem and I was searching for the solution. As none of the answers here solved my problem, I got it solved with the module mysql-connector-python.

Install mysql-connector-python

pip3 install mysql-connector-python

Flask File:

from flask import Flask, request, jsonify
from mysql.connector import connect

app = Flask(__name__)

# Replace with your own database credentials
config = {
    "user": "username",
    "password": "password",
    "host": "localhost",
    "database": "database_name",
}

connection = connect(**config)

@app.route('/check_connection', methods=['GET'])
def check_connection():
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute("SELECT VERSION()")
        data = cursor.fetchone()
        cursor.close()
        return jsonify({'status': 'success', 'version': data})
    except Exception as e:
        return jsonify({'status': 'failed', 'error': str(e)})
        
if __name__ == '__main__': app.run(debug=True)

Use your mysql queries as follows:

cursor.execute("<Your Mysql Query>")

It maybe that you need to init the app for MySQL context.

from flask import Flask
from flask_mysqldb import MySQL

app = Flask(__name__)
mysql = MySQL()
mysql.config['MYSQL_HOST'] = 'localhost'
mysql.config['MYSQL_USER'] = 'root'
mysql.config['MYSQL_PASSWORD'] = 'password'
mysql.config['MYSQL_DB'] = 'todoapp'
mysql.config['MYSQL_CURSORCLASS'] = 'DictCursor'
mysql.init_app(app)
cur = mysql.connection.cursor()

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