Connect to MySQL Database Using Python

Connect to MySQL Database Using Python

  1. What is MySQL Database
  2. How to Setup a Connection with MySQL Database in Python

This short article talks about how to set up a connection with MySQL database in a Python script and execute some SQL queries.

First, we will explain briefly what a MySQL database is and then discuss the methods to set up the connection with the database.

What is MySQL Database

MySQL is an open-source Relational Database Management System (RDBMS) managed by Oracle. Its name consists of two parts.

“My” is after the name of the daughter ‘My’ of its founder UlfMichael Widenius. SQL stands for Structured Query Language.

MySQL database stores the information in multiple blocks known as tables. The tables may create connections/relations with one another through keys (with foreign keys, more specifically).

MySQL is a secure database that supports transactional processing, which is advantageous when used for money transfers.

MySQL offers data encryption, SSH, and SSL support, as well as data security measures. There are various tools for restricting server access and blocking users.

The language we use to perform operations (read, write, etc.) on a MySQL database is Structured Query Language (SQL).

It is a domain-specific language for managing and manipulating data held in a Relational Database Management System (RDBMS) and stream processing in a Relational Data Stream Management System (RDSMS).

How to Setup a Connection with MySQL Database in Python

Python is a very powerful language that supports you to connect to databases and perform SQL queries. Sometimes, you want to check the database status or get some useful information from a database within a Python script.

In that case, there must be some easy-to-implement functionality. Python provides many such related libraries.

Now, let’s look for some methods to connect with the MySQL database and run SQL queries using Python.

Use mysql-connector-python Library

  1. First, install the mysql-connector-python library using the below command in Google’s Collaboratory. You can use this Installation guide for installation on any OS like Linux or Mac.

    pip install mysql-connector-python
    
  2. Run the below code with database details.

    import mysql.connector
    from mysql.connector import errorcode
    
    #  try block
    try:
     cnx = mysql.connector.connect(user='scott', password='password',
    							 host='127.0.0.1',
    							 database='employees')
     cursor = cnx.cursor()
     query= """select * from your_table"""
     cursor.execute(query)
     result = cursor.fetchall()
     print(result)
     cnx.close()
    
    # except block
    except mysql.connector.Error as err:
     if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
       print("Something's wrong with your user name or password")
     elif err.errno == errorcode.ER_BAD_DB_ERROR:
       print("Database does not exist")
     else:
       print(err)
     cnx.close()
    

Let’s understand the above code. First, we import the required libraries.

Then we used the try-except statement of Python. This statement is mainly used for exception handling in Python.

In the try block, we connect to the MySQL database with the credentials (user, password, host, database). The query variable contains the SQL command.

The cursor object will let you execute all the required queries.

Note: The credentials information must be correct to make a successful connection; otherwise, the except block will be executed. In this block, the exception will be handled.

In the above code, if the try block fails to connect with the database and returns some error, then the except block will tackle this.

While creating the connection, the errors like errorcode.ER_ACCESS_DENIED_ERROR (wrong credentials) or errorcode.ER_BAD_DB_ERROR (database not exists) may occur. If there is any other error, then you print that error.

In the end, you close the earlier established connection with the database using the cnx.close() method.

Use mysqlclient Library

We have another library that supports the connection with the MySQL database in Python.

  1. Install the mysqlclient library using the below command.

    pip install mysqlclient
    
  2. Run the below code to establish a connection with the database.

    import MySQLdb
    
    db = MySQLdb.connect(host="localhost",    # your host, usually localhost
    					 user="john",         # your username
    					 passwd="megajonhy",  # your password
    					 db="jonhydb")        # name of the database
    cur = db.cursor()
    
    # SQL Commands
    cur.execute("SELECT * FROM YOUR_TABLE_NAME")
    
    # print all the first cells of all the rows
    for row in cur.fetchall():
    	print (row[0])
    
    db.close()
    

Similarly, you can connect to other databases (i.e., PostgreSQL) in Python. For further in-depth details, you can visit this page.

Related Article - Python MySQL

  • EnvironmentError: Mysql_config Not Found in Python
  • fetchall() in Python