Using MySQL in Flask

Question

Can someone share example codes in Flask on how to access a MySQL DB? There have been documents showing how to connect to sqlite but not on MySQL.

Thank you very much in advance

Answer 1

Firstly you need to install Flask-MySQL package. Using pip for example:

pip install flask-mysql

Next you need to add some configuration and initialize MySQL:

from flask import Flask
from flaskext.mysql import MySQL

app = Flask(__name__)
mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'EmpData'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

Now you can get connection and cursor objects and execute raw queries:

conn = mysql.connect()
cursor =conn.cursor()

cursor.execute("SELECT * from User")
data = cursor.fetchone()

Answer 2

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@server/db'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

help link

Answer 3

Pretty simple with pymysql:

from flask import Flask, request, render_template
import pymysql

db = pymysql.connect("localhost", "username", "password", "database")

app = Flask(__name__)
api = Api(app)

@app.route('/')
def someName():
    cursor = db.cursor()
    sql = "SELECT * FROM table"
    cursor.execute(sql)
    results = cursor.fetchall()
    return render_template('index.html', results=results)

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

In your index.html file just do something like:

<div>
<% for row in results %}
      <tr>
         <td>{{ row[0] }}</td>
         <td>{{ row[1] }}</td>
         <td>{{ row[2] }}</td>
         <td>{{ row[3] }}</td>
      </tr>
{% endfor %}
</div>

Answer 4

Easy with Mysql,

1. Create db with following command

   CREATE TABLE MyUsers ( firstname VARCHAR(30) NOT NULL,  lastname VARCHAR(30) NOT NULL);
   

2. Copy paste below code in app.py file

   from flask import Flask, render_template, request 
   from flask_mysqldb import MySQL
   
   app = Flask(__name__)
   
   
   app.config['MYSQL_HOST'] = 'localhost'
   app.config['MYSQL_USER'] = 'root'
   app.config['MYSQL_PASSWORD'] = 'root'
   app.config['MYSQL_DB'] = 'MyDB'
   
       mysql = MySQL(app)
   
   
   @app.route('/', methods=['GET', 'POST'])
   def index():
       if request.method == "POST":
           details = request.form
           firstName = details['fname']
           lastName = details['lname']
           cur = mysql.connection.cursor()
           cur.execute("INSERT INTO MyUsers(firstName, lastName) VALUES (%s, %s)", (firstName, lastName))
           mysql.connection.commit()
           cur.close()
           return 'success'
       return render_template('index.html')
   
   
   if __name__ == '__main__':
          app.run()
   

Answer 5

#!/usr/bin/python
from flask import Flask,jsonify,abort, make_response
import MySQLdb

app = Flask(__name__)

db = MySQLdb.connect("localhost", "root", "yourDbPassWord", "DBname")

@app.route('/api/v1.0/items', methods=['GET'])
def get_items():
    curs = db.cursor()
    try:
        curs.execute("SELECT * FROM items")
        ...

    except:
        print "Error: unable to fetch items"
    return jsonify({"desired: " response})

Answer 6

mysql version: 5.7

Using mysql-connector. To install mysql-connector package in python3.

python3 -m pip install mysql-connector

To Connect with your database and querying, use following lines of code in your python script:

import mysql.connector

database=mysql.connector.connect(host='localhost',user='user',passwd='password',datbase='dbname')
    cursor=database.cursor()
    query="select * from test_table"
    cursor.execute(query)
    database.commit()

Answer 7

Just add pymysql to your app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@server/db'

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@server/db'

All the best.

Answer 8

we can use mysql using app.config to set the configurations. in future you should not use only development environment, you will need to automatically set the env while in production env.

we should always go for oops concept

firstly we should make a file where it will check and set mysql config for production/development env and set the values to app.config.

# config.py you can put this file anywhere in the project
class Config(object):
    DEBUG = False
    TESTING = False


class DevelopmentConfig(Config):
    """
    Development configurations
    """
    MYSQL_DATABASE_USER = 'root'
    MYSQL_DATABASE_PASSWORD = ''
    MYSQL_DATABASE_HOST = '127.0.0.1'
    MYSQL_DATABASE_DB = 'FlaskProject'  # can be any

    DEBUG = True


class ProductionConfig(Config):
    """
    Production configurations
    """
    MYSQL_DATABASE_USER = 'yourusername'
    MYSQL_DATABASE_PASSWORD = 'yourpassword'
    MYSQL_DATABASE_HOST = 'linktoyourdb' # eg to amazone db :- yourdbname.xxxxxxxxxx.us-east-2.rds.amazonaws.com
    MYSQL_DATABASE_DB = 'yourdbname'

    DEBUG = False

after that use pip3 install flask-mysql

# app.py
from flask import Flask,
from flaskext.mysql import MySQL

app = Flask(__name__)
mysql = MySQL()
if app.config['ENV'] == 'production':
    app.config.from_object('config.ProductionConfig')
else:
    app.config.from_object('config.DevelopmentConfig')
mysql.init_app(app)

@app.route('/')
def yrfunname():
    try:
        conn = mysql.connect()
        cursor = conn.cursor()
        cursor.execute("SELECT * from yrtablename")
        data = cursor.fetchone()
        conn.commit()
        cursor.close()
        return 'success', 200
    except Exception as fail:
        print("Something is wrong with your database user name or password {}".format(fail))
    

Now flask will auto handle the env. you only need to push yr code to production. As your project become bigger you dont have to make db conn again and again. You can use another py file for db connection and use that db connection anywhere in the project.

I am also new to this but i have done lot of research then got this.