Using MySQL in Flask
100,814
Solution 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()
Solution 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
Solution 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>
Solution 4
Easy with Mysql,
Create db with following command
CREATE TABLE MyUsers ( firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL);
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()
Solution 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})
Author by
jaysonpryde
Updated on September 28, 2020Comments
-
jaysonpryde over 3 years
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
-
Ken Kinder about 11 yearsyes but that's with sqlalchemy. what about just straight mysql?
-
JAL almost 9 yearsThanks for your answer. Rather than just posting code, can you provide a little bit more about what this code does?
-
ridderhoff almost 5 yearsCould be wrong, but pretty sure there are issues with using pymysql in a multi-threaded environment. Gave me random errors last time I tried this.
-
ZF007 over 4 yearsAs this is an older question: post mysql version to which your answer applies.
-
Sam about 4 yearsI am having this error: ImportError: No module named 'flask_mysqldb'
-
Amanur Rahman about 4 years@Sanya You need to install "flask-mysqldb" module by pip. The command is "pip install flask-mysqldb" without quota mark.