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.
First, install the
mysql-connector-pythonlibrary 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
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.
try block, we connect to the MySQL database with the credentials (
query variable contains the SQL command.
cursor object will let you execute all the required queries.
Note: The credentials information must be correct to make a successful connection; otherwise, the
exceptblock 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
We have another library that supports the connection with the MySQL database in Python.
mysqlclientlibrary using the below command.
pip install mysqlclient
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) db.close()
Similarly, you can connect to other databases (i.e., PostgreSQL) in Python. For further in-depth details, you can visit this page.