How to connect MySQL database in Python using MySQL Connector module Edit

INTRODUCTION :

In this article , we will learn how to connect Python with MySQL Database using MySQL. Connector module of Python.

• MySQL is an open-source relational database management system(RDBMS). A relational database organizes data into one or more tables in which datatypes may be related to each other, these relations help Structure the data. As we all know that SQL is a structured query language used to access the data from the relational database.

  
  
• MySQL was created by a Swedish company, MySQL AB. Original development of MySQL is by Widenius & Axmark began in 1994. The first version of MySQL was appeared on 23 may 1995.

• On the other Python is an interpreted and high level programming language. Python is dynamically typed. It supports multiple programming paradigms including structured, object-oriented and functional programming.

  
  

• Python was created in the late 1980's and first released in 1991 ,by Guido Van Rossum. Later new versions of python was released by adding new features. Now, the latest version is Python 3.9.0,released on 5 oct 2020.

ABOUT MYSQL CONNECTOR :

• Now , before establishing the Python-MySQL connection let's learn about MySQL connector. Here MySQL connector enables python programs to access MySQL database using an API. To access the MySQL database from Python, you need a database driver.

  
  

• MySQL Connector/Python is a standardized database driver provided by MySQL. MySQL Connector/Python is designed specifically to MySQL. The MySQL connector is not built-in with python, we need to install it in order to use.

  
  

  NOTE
  Connector does not support the old MySQL server authentication methods, which means that MySQL versions prior to 4.1 will not work.

• It supports all MySQL extensions to standard SQL syntax.
  
  

• MySQL Connector/Python includes support for almost all features provided by MySQL Server up to and including MySQL Server version 8.0.

  
  

CREATING DATABASE CONNECTION :

• The following are the parameters of the MySQL server to perform the connection from Python.
  
         Username : The username that you use to work with MySQL Server.
         Password : Password is given by the user at the time of installing the MySQL database.
         Host Name : Is the server name
         Database Name : Database name to which you want to connect.
    

• Now follow the Steps to connect MySQL database in Python using MySQL Connector Python
  
  1. Install MySQL Connector Python using pip.
  2. Use the mysql.connector.connect() method of MySQL Connector to connect MySQL.
  3. Use the connection object returned by a connect() method to create a cursor object .
  4. The cursor.execute() to execute SQL queries from Python.
  5. Close the Cursor object using a cursor.close() and MySQL database connection using
  connection.close() after your work completes.
  

STEP 1 :

Install MySQL Connector Python using pip

• PIP is most likely already installed in your Python environment.
  
  
• Navigate your command line to the location of PIP, and type the following:
  
  

  >python -m pip install mysql-connector-python

• TESTING MYSQL CONNECTOR:
  
  To check if MySQL connector was installed on your computer then create a .py file with the following content:
  
  import mysql.connector
  
  
• If the you run the above code and executed with no error then MySQL connector is installed and ready to use.

STEP 2 :

Establishing connection

• The following is the syntax for creating connection:
  
  import mysql.connector
  connection = mysql.connector.connect(host='host_name',database='Database_name',
  user='user_name',
  password='user_password')
  
  
• And to check whether the connection is established or not then include the following content with the above code .
  
  if connection.is_connected():
  print("Connection was successfull ")

STEP 3 :

Creating a cursor object

• Now create a cursor object and execute the SQL query by the following code :
  
  cursor = connection.cursor()
  
  
• connection object returned by a connect() method to create a cursor object to perform Database Operations.

STEP 4 :

Executing a SQL query

• execute the required query using execute() method
  
  cursor.execute("show database();")
  
  
• This method executes the given database operation (query or command).

STEP 5 :

Closing the connection

• After execution of SQL query we need to close the cursor object and connection using .close() method.
  
  
• The following is syntax for closing the cursor:
  
  mycursor.close()
  
  The following is syntax for closing the connection
  
  connection.close()
  
  

DETAILED EXEPLANATION OF DATABASE CONNECTION

1. import mysql.connector

This is used to import the MySQL Connector Python module in your program so you can use this module's API to connect MySQL.

We can connect the MySQL Database using mysql.connector.connect() method, this method has four required parameters: Host, Database, User and Password. The connect() method establish a connection to the MySQL database and returns a MySQLConnection object, which is used to perform various operations on the Database. The Connect() method can throw an exception, Database error if one of the required parameters is wrong.

is_connected() is the method of the MySQLConnection class through which returns True if Database connectivity connected successfully.

This method returns a cursor object. Using a cursor object, we can execute SQL queries.

Using the cursor's close method we can close the cursor object. Once we close the cursor object, we can not execute any SQL statement.

We are closing the MySQL database connection using a close() method of MySQLConnection class.

SUMMARY

MySQL Database Connectivity in Python

Now you know how to connect to MySQL server from python. You are good to go with this data. Hope this article is useful for you. Thank you for your time and practice what you have learned in this article.