How to access MySQL database in Python

Hello everyone, welcome back to programminginpython.com. Here I am going to show you how to access MySQL database and perform all database operations on the database in Python. In most of the applications, you need a database to store, update and manage your data. So here I will show you all the CRUD(Create, Read, Update, Delete) operations which can be performed on a database. So let’s get started.

Watch this video on youtube here

First, you need some module to connect to MySQL, So here I will install a module called PyMySQL, which is an interface to connect to a database from python. It implements the Python database API version 2.0. As I cover most of the tutorials in the site using Python 3, am using PyMySQL, for Python 2, there is another module called MySQLdb, which is not supported in Python 3.

Program on Github

You can install pyMySQL using either pip or easy_install for windows.

 

 

Video Explanation

Check this video on youtube here.

 

So before performing these database operations, I will first create a database named ‘python_tutorials’ in phpMyAdmin locally.

How to access MySQL database in python - programminginpython.com
How to access MySQL database in python – programminginpython.com

Create a MySQL database table in Python

First I will import the pyMySQL package, which I installed before.

Now I need to connect to the database, this pymysql has a function called connect('host', 'username', 'password', 'database_name') which takes 4 arguments.

Next, I will prepare a cursor to execute the SQL queries.

So, now I can write queries like cursor.execute("SQL QUERY")

I will create a new database table by executing the following query,

The above code creates a new table.

How to access MySQL database in python - programminginpython.com
How to access MySQL database in python – programminginpython.com

Finally, I will close the connection.

Program on Github

Full Code:

Inserting data into a table

Similar to creating a table, here also I will use a SQL query to insert data, but here  I also use try and except, so if any error or problem occurs while inserting the data, the whole operation can be rolled back.

How to access MySQL database in python - programminginpython.com
How to access MySQL database in python – programminginpython.com

Update data in the table

This update operation is also similar to insert operation, I just will change my SQL query here. So I will change the age column value to 30 if the person is male(M)

How to access MySQL database in python - programminginpython.com
How to access MySQL database in python – programminginpython.com

Program on Github

Read data from the table

For reading the data, I will use a simple query to get all data, but for showing the data, I will loop through all the data and fetch single rows data and print them.

How to access MySQL database in python - programminginpython.com
How to access MySQL database in python – programminginpython.com

Delete data from the table

For deleting I will use a simple query with some condition to delete elements/rows from the table.

So after running the above code, the only row in the table gets deleted as it satisfies the condition I gave i.e age > 29.

How to access MySQL database in python - programminginpython.com
How to access MySQL database in python – programminginpython.com

That’s it for the post. I hope now you learned how to perform CRUD operations on a DB and its tables.

 

Program on Github

Feel free to look at my other Python posts on GUI Programs, Math Programs and Basic Programs in Python.

Leave a Reply