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 PYTHON FLASK MYSQL CONECTION, 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))’

Once the execution is done you can commit and close the connection
mysql.connection.commit()
cur.close()

  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.
  2. Open the database and run the following query..
SELECT * FROM MyUsers;

  1. You will be seeing the following output…..
> mysql> select * from MyUsers;

+-----------+----------+

| firstname | lastname |

+-----------+----------+

| Aditya    | Malviya  |

+-----------+----------+

1 row in set (0.00 sec)

Easy !!! This was all about MySQL connection using Flask. In the next tutorial we will be playing with multiple request and multiple tables….
Do Comment if you have any queries, I’ll be more than happy to help you.



Categories: Flask, Python

Tags: , , , , ,

2 replies

  1. if i want to host a flask app in linux server with flask-mysqldb what should be the value for MYSQL_HOST sir?

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: