Python MySQL Tutorial : Using MySQL Database with Python

Do you want to use MySQL database in your Python Code? And struggling to make it work? Then this is the right place, in this Python MySQL Tutorial, we will learn using MySQL database in Python. We have a pure python library called PyMySQL, and we can use it to work with MySQL database. Now let’s see how we do this.

Contents

1 What is PyMySQL
- 1.1 Installing PyMySQL
2 Creating Database in MySQL
3 Python MySQL Tutorial : Performing Basic CRUD

What is PyMySQL

You might already know that to use any database we need the database driver. And PyMySQL is a pure python driver for operating MySQL in Python.
Now, remember, this PyMySQL is not available by default. So, first, we will learn how do we download and install this driver.

Installing PyMySQL

Here, I am assuming you have python installed already on your machine. And also you have done with the setting environment variables stuff. If not then you should go to this link first.

To install PyMySQL run the following command, (I am using windows, so I am using cmd, for MAC of Linux use terminal).


pip install pymysql

pip install pymysql

Make sure you are connected with internet and you will see the following output on running the above command.

Now, once we have the PyMySQL installed, we can learn operating MySQL database from Python. In this Python MySQL Tutorial, I will tell you the basic CRUD operations in MySQL database using Python.

Note: CRUD means the Create, Read, Update and Delete operation in a Database.

Creating Database in MySQL

Before starting any operation, we need a database, right? So open your MySQL database, here for MySQL I am using XAMPP, and I can access it with localhost/phpmyadmin.
Though you can use any other software as well, the process will be the same.

Go to localhost/phpmyadmin and create a database. I have created a database named belal, as you can see in the below image.

Now, inside the database we will create the table. So go to SQL and run the following SQL query to create your table.


CREATE TABLE todos(
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT, 
    `title` VARCHAR(100),
    `desc` VARCHAR(500),
    `date` DATE
)

CREATE TABLE todos(

`id` INTEGER PRIMARY KEY AUTO_INCREMENT,

`title` VARCHAR(100),

`desc` VARCHAR(500),

`date` DATE

)

The above query will create the following table.

Python MySQL Tutorial : Performing Basic CRUD

Here, I am using the PyCharm IDE. (I am in love with JetBrains IDEs 😛 ).

Now, let’s start with a new Python Project in PyCharm.

Create Operation

Create a new Python File named CreatTask.py in your project. The first step is importing pymysql.


import pymysql

import pymysql

Then we will connect to the database as.


connection = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    db='belal',
)

connection = pymysql.connect(

host='localhost',

user='root',

password='password',

db='belal',

)

Now we will get the user inputs, as we need to store title, description and date in the table. So we will get these values from the user.


title = input("Enter title of your task: ")
desc = input("Add some description to it: ")
date = input("Enter the date for this task (YYYY-MM-DD): ")

title = input("Enter title of your task: ")

desc = input("Add some description to it: ")

date = input("Enter the date for this task (YYYY-MM-DD): ")

So, we have the values, and the connection to the MySQL database. To insert the values we do it as.


try:
    with connection.cursor() as cursor:
        sql = "INSERT INTO todos (`title`, `desc`, `date`) VALUES (%s, %s, %s)"
        try:
            cursor.execute(sql, (title, desc, date))
            print("Task added successfully")
        except:
            print("Oops! Something wrong")

    connection.commit()
finally:
    connection.close()

try:

with connection.cursor() as cursor:

sql = "INSERT INTO todos (`title`, `desc`, `date`) VALUES (%s, %s, %s)"

try:

cursor.execute(sql, (title, desc, date))

print("Task added successfully")

except:

print("Oops! Something wrong")

connection.commit()

finally:

connection.close()

The final code we have for AddTask.py is.


import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    db='belal',
)

title = input("Enter title of your task: ")
desc = input("Add some description to it: ")
date = input("Enter the date for this task (YYYY-MM-DD): ")

try:
    with connection.cursor() as cursor:
        sql = "INSERT INTO todos (`title`, `desc`, `date`) VALUES (%s, %s, %s)"
        try:
            cursor.execute(sql, (title, desc, date))
            print("Task added successfully")
        except:
            print("Oops! Something wrong")

    connection.commit()
finally:
    connection.close()

import pymysql

connection = pymysql.connect(

host='localhost',

user='root',

password='password',

db='belal',

)

title = input("Enter title of your task: ")

desc = input("Add some description to it: ")

date = input("Enter the date for this task (YYYY-MM-DD): ")

try:

with connection.cursor() as cursor:

sql = "INSERT INTO todos (`title`, `desc`, `date`) VALUES (%s, %s, %s)"

try:

cursor.execute(sql, (title, desc, date))

print("Task added successfully")

except:

print("Oops! Something wrong")

connection.commit()

finally:

connection.close()

You can try executing this code.

python mysql tutorial — Python MySQL Tutorial

If you are getting the output as shown above, you should see the values in your MySQL database.

Read Operation

Again create a new Python file named ReadTasks.py and write the following code in it.


import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    db='belal',
)

try:
    with connection.cursor() as cursor:
        sql = "SELECT `id`, `title`, `desc` FROM todos WHERE `date` = CURDATE()"
        try:
            cursor.execute(sql)
            result = cursor.fetchall()

            print("Id\t\t Title\t\t\t\t\tDescription")
            print("---------------------------------------------------------------------------")
            for row in result:
                print(str(row[0]) + "\t\t" + row[1] + "\t\t\t" + row[2])

        except:
            print("Oops! Something wrong")

    connection.commit()
finally:
    connection.close()

import pymysql

connection = pymysql.connect(

host='localhost',

user='root',

password='password',

db='belal',

)

try:

with connection.cursor() as cursor:

sql = "SELECT `id`, `title`, `desc` FROM todos WHERE `date` = CURDATE()"

try:

cursor.execute(sql)

result = cursor.fetchall()

print("Id\t\t Title\t\t\t\t\tDescription")

print("---------------------------------------------------------------------------")

for row in result:

print(str(row[0]) + "\t\t" + row[1] + "\t\t\t" + row[2])

except:

print("Oops! Something wrong")

connection.commit()

finally:

connection.close()

On running the above code you will see the following output.

Update Operation

I hope now; you can perform all operations by yourself. But still, if you have any confusions here is the code snippet for updating values in MySQL Database.


try:
    with connection.cursor() as cursor:
        sql = "UPDATE todos SET `title`=%s, `desc`=%s WHERE `id` = %s"
        try:
            cursor.execute(sql, ('your new title', 'your new description', 1))
            print("Successfully Updated...")
        except:
            print("Oops! Something wrong")

    connection.commit()
finally:
    connection.close()

try:

with connection.cursor() as cursor:

sql = "UPDATE todos SET `title`=%s, `desc`=%s WHERE `id` = %s"

try:

cursor.execute(sql, ('your new title', 'your new description', 1))

print("Successfully Updated...")

except:

print("Oops! Something wrong")

connection.commit()

finally:

connection.close()

Delete Operation

Finally you can delete a record as.


try:
    with connection.cursor() as cursor:
        sql = "DELETE FROM todos WHERE id = %s"
        try:
            cursor.execute(sql, (1,))
            print("Successfully Deleted...")
        except:
            print("Oops! Something wrong")

    connection.commit()
finally:
    connection.close()

try:

with connection.cursor() as cursor:

sql = "DELETE FROM todos WHERE id = %s"

try:

cursor.execute(sql, (1,))

print("Successfully Deleted...")

except:

print("Oops! Something wrong")

connection.commit()

finally:

connection.close()

And we have done with all the CRUD operations in MySQL. So that’s all for this Python MySQL Tutorial friends. I hope you found it helpful if you did, then please SHARE this post with your friends who are learning python.

And yes, if you have any query regarding this Python MySQL Tutorial then your questions are welcome. Thank You 🙂

Python MySQL Tutorial : Using MySQL Database with Python

What is PyMySQL

Installing PyMySQL

Creating Database in MySQL

Python MySQL Tutorial : Performing Basic CRUD

Create Operation

Read Operation

Update Operation

Delete Operation

1 thought on “Python MySQL Tutorial : Using MySQL Database with Python”

Leave a Comment Cancel reply