Codementor Events

PYTHON – FLASK MYSQL CONNECTION

Published Feb 06, 2019Last updated Jun 05, 2020
PYTHON – FLASK MYSQL CONNECTION

If you followed my previous tutorial Python web services using Flask you must be aware on how to create your own REST services, GET and POST the data.

IN THIS TUTORIAL WE’RE GOING TO LEARN HOW TO CONNECT FLASK TO MYSQL DATABASE AND YOU CAN INSERT THE FORM DATA.

This tutorial is completely made for beginner, So the prerequisite of this tutorial is going to be minimum only thing which I will personally recommend it to have a high level overview of Python web services using Flask and have minimum understanding of SQL and Database set up.

To get started you’ll be needing any IDE I’ll be using PyCharm,MySQL DB and that’s it!!! This tutorial, unlike my earlier posts will be steps depicting what and how to do….

  1. Open PyCharm, create new Python file name app.python and type the below code into your app.python file.
from flask import Flask

app = Flask(__name__)

@app.route('/', methods=['GET', 'POST'])
def index():
    return "Hello Nuclear Geeks"

if __name__ == '__main__':
    app.run()
  1. If you’ve gone through Python web services using Flask you would understand 100% of the above code. Simply we’re routing out request and displaying “Hello Nuclear Geeks”, On running the following program type http://127.0.0.1:5000/ on your browser to see the output!! “Hello Nuclear Geeks”

  2. Now you need to create a simple HTML page with two text field First Name, Last Name and submit button. To do this create a folder named Templates inside it create a file index.html and copy the below code.

<HTML>
<BODY bgcolor="cyan">
<form method="POST" action="">
    <center>
    <H1>Enter your details </H1> <br>
    First Name <input type = "text" name= "fname" /> <br>
    Last Name <input type = "text" name = "lname" /> <br>
    <input type = "submit">
    </center>
</form>
</BODY>
</HTML>
  1. Modify our app.python file and add the below code in it.

from flask import Flask, render_template

app = Flask(__name__)

@app.route('/', methods=['GET', 'POST'])
def index():
    return render_template('index.html')

if __name__ == '__main__':
    app.run()
  1. Upon running the above code you must get the page as below.

screen-shot-2018-10-10-at-5-22-08-pm.png

  1. Now we’ve developed our form, the next step is database connection. To create a table use the below query:
CREATE TABLE MyUsers ( firstname VARCHAR(30) NOT NULL,  lastname VARCHAR(30) NOT NULL);
  1. The above query will create a table in the Database with name MyUsers, now copy the following code and paste in app.python 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()
  1. Pretty easy till now!!!

app.config[‘MYSQL_HOST’] = ‘localhost’
app.config[‘MYSQL_USER’] = ‘root’
app.config[‘MYSQL_PASSWORD’] = ‘root’
app.config[‘MYSQL_DB’] = ‘MyDB’

These lines represent the db configuration required for our Flask, the next line ‘mysql = MySQL(app)’ creates an instance which will provide us the access.

The lines ‘firstName = details[‘fname’]’ and ‘lastName = details[‘lname’]’ fetches the entered value in the HTML form.

Establishment of connection is done by ‘cur = mysql.connection.cursor()’ and execution of query by ‘cur.execute(“INSERT INTO MyUsers(firstName, lastName) VALUES (%s, %s)”, (firstName, lastName))’

  1. Heavy Breathing!!! we are all set to run…. Run the program enter the First Name = “Aditya” and Last Name= “Malviya” and tap on submit. You will see success being returned on the screen.

You can checkout the complete post here Python Flask Mysql Connection

You can also reach out to my all posts here
Or you can check out them individually as well
Swift Delegates and Protocols
Introduction to Full Stack Python
[Introduction to Automation Testing]
Data Types in Python
iOS Application Lifecyle
Python Identifiers and reserved keywords

Discover and read more posts from Aditya Malviya
get started
post commentsBe the first to share your opinion
Shashank Mishra
3 years ago

Hi Aditya, I am getting a internal Server error when i hit enter after writing name on the html page.

Coder_Manuel
3 years ago

Thank you…
The Steps are very brief and precise…

robderon
3 years ago

Thank you for your work !

for your information, I had to rename the Templates directory “templates” ( with a small t) for this to work.

Show more replies