For this lesson, we need to have a complete MySQL Server installation so that we can run examples for the Python application we create. Please go through\u00a0the\u00a0MySQL Server Tutorial<\/a>\u00a0lesson to understand how installation can be done with some simple commands to get started with the MySQL operations in a Python<\/a> application. We will be using a Virtual environment<\/a> to install flask so that we don’t have to use it globally on our machine. Please note that to complete and follow along with this lesson, you must install the In this section, we will start by setting up a simple Python application\u00a0with important techniques to establish and close the connection with the server along with database connection pooling techniques which is very important to have in any production-grade application. In this lesson, we will make use of the virtual environment to use Flask in our application. Make sure that virtual environment is installed on your machine. We used the following command to install it via Python and PIP:<\/p>\n Install virtualenv<\/em><\/span><\/p>\n Now that Make new virtual environment<\/em><\/span><\/p>\n Once a new Virtual environment is made as We can now start using this virtual environment with the following command:<\/p>\n Activate virtual environment<\/em><\/span><\/p>\n We can also deactivate a virtual environment once we are done with its use like this:<\/p>\n Deactivate virtual environment<\/em><\/span><\/p>\n We will see the following cursor change to confirm that a virtual environment is active now:<\/p>\n Now, when our virtual environment is active, we will install Flask framework in it so that we can use it in our application:<\/p>\n Install Flask<\/em><\/span><\/p>\n Finally, we will install MySQL driver for Flask with the following command so that we can integrate MySQL in our application as well:<\/p>\n Install MySQL driver for Flask<\/em><\/span><\/p>\n With this command, we are ready to start making our application for Flask and MySQL integration. As our application is pretty simple, we will make a new file with name To start quickly for demonstration, we will add minor code in our application and run a set of commands which will start the flask application with a traditional “Hello World” message. If you want to quickly understand what a route is, just understand that a ‘route’ allows us to call a Python function with the help of a URL. When we deploy a Python application on our local machine, this URL points to our local web server we make use of. In our case, we will make use of Flask’s embedded web server which runs on Port 5000 by default. Here is the code we will be using in our application for a simple traditional message:<\/p>\n Hello World from Flask<\/em><\/span><\/p>\n Once we save this code, we can run a set of commands to run the application. Here is what we need to run:<\/p>\n Run Flask application<\/em><\/span><\/p>\n We will see the following output on terminal:<\/p>\n When we make use of a REST client like Postman<\/a> to make the request to provided path, we will see the following output:<\/p>\n Now, we will add some routes for each of the CRUD operations we will performing in our Python application. These routes will look like: Routes for CRUD operations<\/em><\/span><\/p>\n Once we run the above application again and try to get POST some JSON in the insert student route, we will see the following output in Postman app:<\/p>\n As this is not a Flask based lesson, we won’t dive into much detail about how we are accepting JSON data in above defined routes and how we access tge attributes which are passed in the request object as JSON data. This is a nice place to start a Flask application in a development mode because that allows us to quickly make changes to the application without having to restart your app again and again. Run the following command to activate the debugger and live reload in your Flask application:<\/p>\n Run Flask application in Development mode<\/em><\/span><\/p>\n Once this is done, we are free to make any changes in our application without having to restart our app again and again. Let’s move forward to integrating some MySQL based dependencies in our application.<\/p>\n In the last section, we completed our setup of the basic Flask application in which we will integrate some MySQL related operations like saving and retrieving Student related data. Now, we need to define MySQL connection properties in our application with which we can establish a connection and execute MySQL properties with the connection. Here, we define those connection properties:<\/p>\n Define connection properties<\/em><\/span><\/p>\n Now that we have defined the connection properties for MySQL connection with the Make Student table<\/em><\/span><\/p>\n We could have integrated this SQL query to create a new Table in the application itself if we want because that way, we won’t have to be depend on an individual SQL query to create our table when the code for the app itself could have created that table for us when the app was run. We can now add the following line of code to make sure that the connection is made to MySQL:<\/p>\n Making connection to MySQL server<\/em><\/span><\/p>\n Please note that as this is just a demonstration lesson and not a production-grade application, we do not manage the database connection pooling here. In any application, once we focus on database connection pooling for productionisation of our application, it makes a huge difference because database connections are expensive<\/strong> to make and keep.<\/p>\n Once the connection is made, we can define a simple function which will insert the provided JSON data into the Student table. Here is the definition for the said function:<\/p>\n Insert new student function<\/em><\/span><\/p>\n Note that we just pass a simple SQL query. Even though we have defined the query above, the data will still not be inserted into the database, at least not yet. For this to happen, we must commit the transaction:<\/p>\n Committing a Transaction<\/em><\/span><\/p>\n If you do not commit the transaction, the insert query will be rolled-back once the current session with the application is terminated. This is not something we need in our apps, so always commit the transactions you do.<\/p>\n Let’s look at the POST Postman call we made to insert a new Student table:<\/p>\n Now that we have a Student in our Table, we can move to other operations as well. For now, the complete function for insert<\/strong> functionality will look like:<\/p>\n INSERT function<\/em><\/span><\/p>\n We see that we are not setting the update and where values in the query directly because that is a security vulnerability we can avoid just by using Prepared statements. That is what we have done by defining a Tuple We were able to insert data into our table using the Python Flask application. Please note that we can perform any database operation just like what we did above, may it be a Delete or Update operation. We can even use a UI Framework which binds to Flask which will help us to view all the data we have in our table into a nice UI view but for now, we will stick to an API based calls to operate on the queries we define.<\/p>\n Now that we have some data in our table, we can display the same by making a GET call on the app route we defined to get a student with a specific ID. Please note that IDs are auto-generated because of the fact we defined them like that when we ran an SQL query to construct the Student table.<\/p>\n To show data from the database, we need to run a SELECT query for the data. Let us define the SELECT statement here:<\/p>\n SELECT statement<\/em><\/span><\/p>\n Note that we have defined a cursor object to go over the data we fetch from the above query. We can run this command now:<\/p>\n Run SELECT command<\/em><\/span><\/p>\n When we run the above command, we will get back the following JSON containing the data for the Student for which we passed the ID to the application route:<\/p>\n Note that we do not exactly get the JSON we expected in the form of key-value pair. Instead, we get back a cursor representation of the data which is in the form of an array itself where each item represents the value for a column and is ordered in manner we defined them in the table. The complete function for insert functionality will look like:<\/p>\n Complete SELECT command<\/em><\/span><\/p>\n If you want to get back a more sophisticated JSON based result which correct key-value pair, feel free to explore some JSON based libraries for the same and mention them in the comments below.<\/p>\n Now that there is some data present for our Student in the database, we can move to update an existing tuple in our table Student. This can be done by passing in the JSON containing data related to our Student. Let us define the function and query which will perform this, note that we also include the commit call to the connection object as well so that this query is executed and the data for the corresponding Student is updated:<\/p>\n Update a Student function<\/em><\/span><\/p>\n Let us execute the udpate call in Postman with the following data:<\/p>\n Now if we call the GET method for the same student, we will get the following output:<\/p>\n The complete function for insert functionality will look like:<\/p>\n Complete UPDATE command<\/em><\/span><\/p>\n One of the most important operation in SQL is DELETE operation. This is what we will be demonstrating in this section by executing one of the DELETE statement in the application. We have defined a simple DELETE method which accepts an ID of a single student and now, we will define the helper method which will delete the student with the provided ID:<\/p>\n Delete a Student<\/em><\/span><\/p>\n Once we run the delete method call, we will see the following output:<\/p>\n If we try to GET that same student again, we will see an error in Postman:<\/p>\n At this stage, our application is complete. Here is the final code for our only Python file we made for the complete working project:<\/p>\n Complete Code<\/em><\/span><\/p>\n The above code can be run by first setting up a virtual environment and installing all the packages we talked about in the beginning of this lesson. I highly recommend you to look at SQLAlachemy<\/a> which provides us a way to use Model ORM mapping which is mostly used in a production-ready application.<\/p>\n In this lesson, we looked at a very simple yet effective example of setting up a connection to a MySQL server from a Python application. We started with important techniques to establish and close the connection with the server along with database connection pooling techniques which are very important to have in any production-grade application based on any framework and not just Python. We encourage you to study more about Python and MySQL to run more complex examples which involve Prepared statements and result sets. One of the more important things which can be done inside a Python application is calling stored procedures,\u00a0define triggers<\/a>\u00a0and\u00a0adding full-text search capabilities<\/a>\u00a0to the application.<\/p>\n MySQL Stored procedures are a very powerful tool with very high performance (usually) but the bad thing is that they can grow very large very easily and quickly once your application starts to grow. The main database consistency should be managed at the application level wherever and whenever possible. Still, MySQL Stored procedures make a very good companion if you are a database administrator and just want to make things consistent without relying on too much code because after all, the performance of Stored procedures is very high because database operations are very fast on the database server itself. Don\u2019t forget to check the examples of using Java with\u00a0SQL: JDBC Best Practices Tutorial<\/a>\u00a0and\u00a0Java JDBC ResultSet Example<\/a>. These lessons explain how to effectively use SQL commands with Java &\u00a0JDBC drivers<\/a>. There are many more examples present on MySQL which can be studied for a deeper understanding of the database. Finally, do read about some of\u00a0the most popular MySQL functions<\/a>\u00a0which you will find interesting and useful in your daily life when you deal with MySQL. There are many more examples present on MySQL which can be studied for a deeper understanding of the database.<\/p>\n Finally, even though we made use of a Python framework known as Flask to make this application, you could have used any other framework with very similar working. Other frameworks can be Django etc. or you could have made a standalone Python application as well which is not bound to any framework. That is a decision which can be made easily for a learning. Please feel free to share your feedback for the lesson in the comments below.<\/p>\n This was an example of MySQL with Python programming language with Flask framework.<\/p>\nvirtualenv<\/code> and Flask on your machine.<\/p>\nTable Of Contents<\/h3>\n
\n
2. Setting up Python application<\/h2>\n
python -m pip install --user virtualenv<\/pre>\n
virtualenv<\/code> is installed on your machine, it is time we make a new virtualenv<\/code> environment on our machine:<\/p>\npython -m virtualenv jcg-python-mysql-app<\/pre>\n
jcg-python-mysql-app<\/code>, we will see the following output, describing that the virtual environment setup some things for us in advance which helps us to install more tools and softwares inside it:<\/p>\n![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/new-virtual-environment.png\")
<\/a>source jcg-python-mysql-app\/bin\/activate<\/pre>\n
deactivate<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/virtual-environment-active-deactivate.png\")
<\/a>pip install Flask<\/pre>\n
pip install flask-mysql<\/pre>\n
mysql_app.py<\/code> and put our complete code in that file itself. Even though we will make use of a Python framework known as Flask to make this application, we could have used any other framework with very similar working. Other frameworks can be Django etc. or we can make a standalone Python application as well which is not bound to any framework. But for now, to keep things simple, we will make use of Flask which allows us to keep things simple.<\/p>\n3. Making routes in Flask application<\/h2>\n
from flask import Flask\r\napp = Flask(__name__)\r\n\r\n@app.route('\/')\r\ndef hello_world():\r\n return 'Hello, Geeks!'\r\n<\/pre>\nexport FLASK_APP=mysql_app.py\r\nflask run\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/flask-hello-world.png\")
<\/a>![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/flask-hello-geeks.png\")
<\/a>from flask import Flask, request\r\napp = Flask(__name__)\r\n\r\n@app.route('\/hello')\r\ndef hello_world():\r\n return 'Hello, World!'\r\n\r\n@app.route('\/student', methods=['POST'])\r\ndef insert_student():\r\n new_student = request.get_json()\r\n return 'Insert Student data: %s' % new_student['name']\r\n\r\n@app.route('\/student\/<int:student_id>', methods=['GET'])\r\ndef get_student(student_id):\r\n return 'Get Student data with ID: %s' % student_id\r\n\r\n@app.route('\/student', methods=['PUT'])\r\ndef update_student():\r\n updated_student = request.get_json()\r\n return 'Update Student data: %s' % updated_student['name']\r\n\r\n@app.route('\/student\/<int:student_id>', methods=['DELETE'])\r\ndef delete_student(student_id):\r\n return 'Delete Student data with ID: %s' % student_id\r\n<\/pre>\n![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/python-example-insert-data.png\")
<\/a>export FLASK_APP=mysql_app.py FLASK_ENV=development\r\nflask run\r\n<\/pre>\n
4. Adding MySQL dependencies and parameters<\/h2>\n
from flask import Flask, request\r\nfrom flaskext.mysql import MySQL\r\n\r\napp = Flask(__name__)\r\nmysql = MySQL()\r\n\r\napp.config['MYSQL_DATABASE_USER'] = 'root'\r\napp.config['MYSQL_DATABASE_PASSWORD'] = 'root'\r\napp.config['MYSQL_DATABASE_DB'] = 'jcg_schema'\r\napp.config['MYSQL_DATABASE_HOST'] = 'localhost'\r\nmysql.init_app(app)\r\n<\/pre>\n
jcg_schema<\/code> in which we have a Table called Student. We can make this table with the following SQL command:<\/p>\nCREATE Table Student IF NOT EXISTS (\r\n id INT PRIMARY KEY AUTO_INCREMENT,\r\n name VARCHAR(100),\r\n roll_no INT NOT NULL\r\n);\r\n<\/pre>\n
connection = mysql.connect()\r\n<\/pre>\n
5. Insert new records<\/h2>\n
insert_sql_query = \"INSERT INTO Student (`name`, `roll_no`) VALUES (%s, %s)\"\r\ndata = (new_student['name'], new_student['roll_no'])\r\ncursor = connection.cursor()\r\ncursor.execute(insert_sql_query, data)\r\n<\/pre>\n
connection.commit()\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/insert-new-student-mysql.png\")
<\/a>@app.route('\/student', methods=['POST'])\r\ndef insert_student():\r\n new_student = request.get_json()\r\n insert_sql_query = \"INSERT INTO Student (`name`, `roll_no`) VALUES (%s, %s)\"\r\n data = (new_student['name'], new_student['roll_no'])\r\n cursor = connection.cursor()\r\n cursor.execute(insert_sql_query, data)\r\n connection.commit()\r\n return 'Student inserted with name: %s' % new_student['name']\r\n<\/pre>\ndata<\/code> which contains the actual values to be inserted. This is really important because, without this, the application can easily become a victim of SQL Injection<\/strong> attacks which must be avoided. If you are interested in exploring more, read\u00a0Using SQL Injection Vulnerabilities to Dump Your Database<\/a>\u00a0for much more information and knowledge.<\/p>\n6. Display data from MySQL<\/h2>\n
cursor = connection.cursor()\r\ncursor.execute(\"SELECT * from Student WHERE id='\" + student_id + \"'\")\r\n<\/pre>\n
student_data = cursor.fetchone()\r\nif student_data is None:\r\n return \"Wrong ID passed.\"\r\nelse:\r\n return jsonify(student_data)\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/get-student-mysql.png\")
<\/a>@app.route('\/student\/<student_id>', methods=['GET'])\r\ndef get_student(student_id):\r\n cursor = connection.cursor()\r\n cursor.execute(\"SELECT * from Student WHERE id='\" + student_id + \"'\")\r\n student_data = cursor.fetchone()\r\n if student_data is None:\r\n return \"Wrong ID passed.\"\r\n else:\r\n return jsonify(student_data)\r\n<\/pre>\n7. Update existing records in MySQL<\/h2>\n
update_sql_query = \"Update Student SET name=%s, roll_no=%s WHERE id=%s\"\r\ndata = (updated_student['name'], updated_student['roll_no'], updated_student['id'])\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/update-student-mysql.png\")
<\/a>![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/get-updated-student-mysql.png\")
<\/a>@app.route('\/student', methods=['PUT'])\r\ndef update_student():\r\n updated_student = request.get_json()\r\n update_sql_query = \"Update Student SET name=%s, roll_no=%s WHERE id=%s\"\r\n data = (updated_student['name'], updated_student['roll_no'], updated_student['id'])\r\n cursor = connection.cursor()\r\n cursor.execute(update_sql_query, data)\r\n connection.commit()\r\n return 'Student updated with ID: %s' % updated_student['id']\r\n<\/pre>\n8. Delete data in MySQL<\/h2>\n
<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/delete-student-mysql.png\")
<\/a>![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/deleted-student-error-mysql.png\")
<\/a>from flask import Flask, request, jsonify\r\nfrom flaskext.mysql import MySQL\r\n\r\napp = Flask(__name__)\r\nmysql = MySQL()\r\n\r\napp.config['MYSQL_DATABASE_USER'] = 'root'\r\napp.config['MYSQL_DATABASE_PASSWORD'] = 'root'\r\napp.config['MYSQL_DATABASE_DB'] = 'jcg_schema'\r\napp.config['MYSQL_DATABASE_HOST'] = 'localhost'\r\nmysql.init_app(app)\r\nconnection = mysql.connect()\r\n\r\n@app.route('\/hello')\r\ndef hello_world():\r\n return 'Hello, World!'\r\n\r\n@app.route('\/student', methods=['POST'])\r\ndef insert_student():\r\n new_student = request.get_json()\r\n insert_sql_query = \"INSERT INTO Student (`name`, `roll_no`) VALUES (%s, %s)\"\r\n data = (new_student['name'], new_student['roll_no'])\r\n cursor = connection.cursor()\r\n cursor.execute(insert_sql_query, data)\r\n connection.commit()\r\n return 'Student inserted with name: %s' % new_student['name']\r\n\r\n@app.route('\/student\/<student_id>', methods=['GET'])\r\ndef get_student(student_id):\r\n cursor = connection.cursor()\r\n cursor.execute(\"SELECT * from Student WHERE id='\" + student_id + \"'\")\r\n student_data = cursor.fetchone()\r\n if student_data is None:\r\n return \"Wrong ID passed.\"\r\n else:\r\n return jsonify(student_data)\r\n\r\n@app.route('\/student', methods=['PUT'])\r\ndef update_student():\r\n updated_student = request.get_json()\r\n update_sql_query = \"Update Student SET name=%s, roll_no=%s WHERE id=%s\"\r\n data = (updated_student['name'], updated_student['roll_no'], updated_student['id'])\r\n cursor = connection.cursor()\r\n cursor.execute(update_sql_query, data)\r\n connection.commit()\r\n return 'Student updated with ID: %s' % updated_student['id']\r\n\r\n@app.route('\/student\/<int:student_id>', methods=['DELETE'])\r\ndef delete_student(student_id):\r\n cursor = connection.cursor()\r\n cursor.execute(\"DELETE FROM Student WHERE id=%s\", (student_id,))\r\n connection.commit()\r\n return 'Deleted Student data with ID: %s' % student_id\r\n<\/pre>\n9. Conclusion<\/h2>\n
10. Download the Source Code<\/h2>\n