- \n
- MySQL Connector Python \u2013 It is a preferred choice in the developer\u2019s community as it is capable of executing database queries through python. It is python3 compatible and actively maintained<\/li>\n
- pyMySQL<\/li>\n
- MySQLDB<\/li>\n
- MySqlClient<\/li>\n<\/ul>\n
1.1 Setting up Python<\/h3>\n
If someone needs to go through the Python installation on Windows, please watch this<\/a> link. You can download the Python from this<\/a> link.<\/p>\n
1.2 Setting up MySQL Connector Python<\/h3>\n
Once the python is successfully installed on your system you can install the MySQL Connector Python<\/strong> using a simple
pip<\/code> command. You can fire the below command from the command prompt and it will successfully download the module from pypi.org<\/a> and install it.<\/p>\nInstallation command<\/em><\/span><\/p>\n
pip install mysql-connector-python\n<\/pre>\n
1.3 Setting up MySQL database and PhpMyAdmin containers<\/h3>\n
To start with the tutorial, I am hoping that you have the MySQL up and running in your localhost environment. For easy setup, I have the database up and running on the docker environment. Along with the database, we will also bring up the PhpMyAdmin container to connect with the database. You can execute the below command to get the containers running on docker in minutes.<\/p>\n
You can execute the below command to get the container running on docker in minutes.<\/p>\n
Docker commands<\/em><\/span><\/p>\n
docker run --name mysql -e MYSQL_ROOT_PASSWORD=password123 -e MYSQL_DATABASE=user -p 3306:3306 -d mysql\n\ndocker run --name phpmyadmin --link mysql:db -p 8089:80 -d phpmyadmin\/phpMyAdmin\n<\/pre>\n
If everything goes well the containers would be started successfully as shown in Fig. 1. You can use the
docker ps -a<\/code> command to confirm that both containers are started successfully. For further information on docker basics, you can navigate to this tutorial<\/a>.<\/p>\n\n![\"python](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/03\/mysqlconnectorpython-mysql-module.jpeg\")
<\/a>Fig. 1: MySQL database and PhpMyAdmin containers on Docker<\/figcaption><\/figure>\n<\/div>\n Once the PhpMyAdmin container is started successfully you can navigate to the following url –
http:\/\/localhost:8089\/<\/code> in the browser. Enter the login credentials asroot<\/code> andpassword123<\/code> to enter into the console. You are free to keep the mysql login credentials of your choice by changing them in the docker command for the mysql container.<\/p>\n2. Python Execute MySQL Stored Procedure<\/h2>\n
Before going any deeper in the practical let me walk you through a simple architecture diagram where it shows that wherein the mysql connector python module fits in the picture.<\/p>\n
\n![\"python](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/03\/docker-mysql-phpmyadmin-containers-img1.jpg\")
<\/a>Fig. 2: Python MySQL connection<\/figcaption><\/figure>\n<\/div>\n For the MySQL connector python module to work we will supply the following attributes to the mysql from the python application i.e.
<\/div> <\/div><\/p>\n- \n
- user<\/strong> \u2013 Identity user to work with the mysql database. Default mysql user is
root<\/code><\/li>\n- password<\/strong> \u2013 Credential of the identity user<\/li>\n
- host<\/strong> \u2013 Address of the database server. If running on localhost then you can either use localhost or 127.0.0.1<\/li>\n
- port<\/strong> – The port number. Default mysql port is
3306<\/code><\/li>\n- database<\/strong> \u2013 Database to which you want to connect. It can be left blank<\/li>\n<\/ul>\n
2.1 Prerequisite – Create SQL data<\/h3>\n
For this tutorial, we will need to create a table and populate some dummy data into it so that we can use this table for SQL operations. You can use the following SQL script. Make note that the
user<\/code> database was already created with the help of the docker run command for the mysql container.<\/p>\nUser.sql<\/em><\/span><\/p>\n
-- create database\nCREATE DATABASE user;\n\n-- use database\nUSE user;\n\n-- create table\nCREATE TABLE users (\n id INT NOT NULL AUTO_INCREMENT,\n first_name VARCHAR(50),\n last_name VARCHAR(50),\n email VARCHAR(50),\n gender VARCHAR(50),\n PRIMARY KEY(id));\n\n-- insert dummy data into a table\nINSERT INTO users (first_name, last_name, email, gender) VALUES ('Engracia', 'Gwyther', 'egwyther0@redcross.org', 'F');\nINSERT INTO users (first_name, last_name, email, gender) VALUES ('Ximenez', 'MacNeachtain', 'xmacneachtain1@posterous.com', 'NS');\nINSERT INTO users (first_name, last_name, email, gender) VALUES ('Fitzgerald', 'Hartus', 'fhartus2@cdbaby.com', 'M');\nINSERT INTO users (first_name, last_name, email, gender) VALUES ('Beniamino', 'Beirne', 'bbeirne3@engadget.com', 'F');\nINSERT INTO users (first_name, last_name, email, gender) VALUES ('Adriana', 'Armal', 'aarmal4@booking.com', 'NS');\n\n-- get all records from the table\nSELECT * FROM users;\n\n-- sql procedures\n-- CALL get_users()\nDELIMITER &&\nCREATE PROCEDURE get_users ()\nBEGIN\nSELECT * FROM users;\nEND &&\nDELIMITER ;\n\n-- CALL get_user_by_id(11)\nDELIMITER &&\nCREATE PROCEDURE get_user_by_id (IN U_id INT)\nBEGIN\nSELECT first_name, last_name, email, gender FROM users WHERE id = U_id;\nEND &&\nDELIMITER ;\n\n-- CALL insert_user('Jane','Doe','jane.doe@automation.com', 'M')\nDELIMITER &&\nCreate PROCEDURE insert_user (IN U_first_name VARCHAR(50), IN U_last_name VARCHAR(50), IN U_email Varchar(50), IN U_gender Varchar(50))\nBEGIN\nINSERT INTO users (first_name, last_name, email, gender) VALUES (U_first_name, U_last_name, U_email, U_gender);\nEND &&\nDELIMITER ;\n<\/pre>\n3. Code Example<\/h2>\n
Let us dive in with the programming stuff now.<\/p>\n
3.1 Create a configuration file<\/h3>\n
Add the following code to the environment file wherein we will specify the connection and database details. You are free to change these details as per your configuration setup.<\/p>\n
local.env<\/em><\/span><\/p>\n
[DB]\nusername = root\npassword = password123\nhost = localhost\nport = 3306\ndatabase = user\n<\/pre>\n
3.2 Reading the configuration<\/h3>\n
Add the following code to the python script which will read the configuration file created above and return the config object to be used later while connecting to the database. The script will import the
configparser<\/code> module for reading the configuration file. Remember to give the correct path where thelocal.env<\/code> is created.<\/p>\nreaddbconfig.py<\/em><\/span><\/p>\n
import configparser\ndef read_db_params():\n # reading the env file\n config = configparser.ConfigParser()\n config.read('config\/local.env')\n return config\n<\/pre>\n3.3 Connecting to the database<\/h3>\n
Add the following code to the python script which will connect to the mysql database with the help of the
mysql.connector<\/code> module.<\/p>\nconnecttomysqldb.py<\/em><\/span><\/p>\n
# python mysql\n\nimport mysql.connector\nfrom mysql.connector import Error\n\nfrom readdbconfig import *\n\n\ndef connect():\n try:\n # method will read the env file and return the config object\n params = read_db_params()\n\n # connect to database\n # reading the database parameters from the config object\n conn = mysql.connector.connect(\n host=params.get('DB', 'host'),\n database=params.get('DB', 'database'),\n user=params.get('DB', 'username'),\n password=params.get('DB', 'password'),\n port=params.get('DB', 'port')\n )\n\n return conn\n except(Exception, Error) as error:\n print(error)\n<\/pre>\n3.4 Executing the stored procedures<\/h3>\n
Add the following code to the python script which will connect to the mysql database with the help of the
mysql.connector<\/code> module and call the stored procedures.<\/p>\ncallmysqlstoredprocedure.py<\/em><\/span><\/p>\n
# python mysql\n\nfrom connecttomysqldb import *\n\n\n# executing the stored procedure to get all users\ndef get_users(conn):\n # creating a cursor to perform a sql operation\n cursor = conn.cursor()\n\n try:\n # execute the command\n cursor.callproc('get_users')\n\n # print results\n for result in cursor.stored_results():\n records = result.fetchall()\n if len(records) == 0:\n print('Empty table')\n else:\n # print results\n print('Printing user details')\n print('----------------------------')\n for record in records:\n full_name = record[1] + \" \" + record[2]\n print('Id = {}, Name = {}, Email = {}, Gender = {}'.format(record[0], full_name,\n record[3], record[4]))\n except(Exception, Error) as error:\n print(error)\n finally:\n if conn is not None:\n cursor.close()\n conn.close()\n print('\\nConnection closed')\n\n\n# executing the stored procedure to get user by id\ndef get_user_by_id(conn, uid):\n # creating a cursor to perform a sql operation\n cursor = conn.cursor()\n\n try:\n args = [uid]\n # execute the command\n cursor.callproc('get_user_by_id', args)\n\n # print results\n for result in cursor.stored_results():\n records = result.fetchall()\n if len(records) == 0:\n print('Empty table')\n else:\n # print results\n print('Printing user id = {} details'.format(uid))\n print('------------------------------------')\n for record in records:\n full_name = record[0] + \" \" + record[1]\n print('Name = {}, Email = {}, Gender = {}'.format(full_name, record[2], record[3]))\n except(Exception, Error) as error:\n print(error)\n finally:\n if conn is not None:\n cursor.close()\n conn.close()\n print('\\nConnection closed')\n\n\n# driver code\nif __name__ == '__main__':\n get_users(connect())\n # get_user_by_id(connect(), 4)\n<\/pre>\n4. Demo logs<\/h2>\n
The
callmysqlstoredprocedure.py<\/code> script will call theget_users()<\/code> stored procedure to fetch the users from the table and show it on the IDE console. If not the exception message will be shown.<\/p>\nDemo logs<\/em><\/span><\/p>\n
-- CALL get_users()\n\nPrinting user details\n----------------------------\nId = 1, Name = Engracia Gwyther, Email = egwyther0@redcross.org, Gender = F\nId = 2, Name = Ximenez MacNeachtain, Email = xmacneachtain1@posterous.com, Gender = NS\nId = 3, Name = Fitzgerald Hartus, Email = fhartus2@cdbaby.com, Gender = M\nId = 4, Name = Beniamino Beirne, Email = bbeirne3@engadget.com, Gender = F\nId = 5, Name = Adriana Armal, Email = aarmal4@booking.com, Gender = NS\n<\/pre>\n
You can also call the
get_user_by_id(\u2026)<\/code> stored procedure to fetch the details of a particular user. That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!<\/p>\n5. Summary<\/h2>\n
In this tutorial, we learned \u2013<\/p>\n
- \n
- Introduction to mysql connector python module<\/li>\n
- Sample program to connect to the database with the help of mysql connector python module and execute the stored procedures<\/li>\n<\/ul>\n
You can download the source code of this tutorial from the Downloads<\/a> section.<\/p>\n
<\/a>6. Download the Project<\/h2>\n
This was a python programming tutorial to connect to the database with the help of the mysql connector python module.<\/p>\n
- password<\/strong> \u2013 Credential of the identity user<\/li>\n
- user<\/strong> \u2013 Identity user to work with the mysql database. Default mysql user is