Creating a MySQL Database in Python

Databases are crucial for storing and managing data. In this article, you'll learn to create and interact with MySQL database in Python.

Installing PyMySQL

PyMySQL is a MySQL client library written in Python that allows you to create and interact with MySQL databases.

This is a third-party library, therefore you must install it on your system. To install it using pip, run the following line in your terminal.

pip install pymysql

--------------------- OR ---------------------

python -m pip install pymysql

Note: You must have a MySQL server installed in your system.

Creating MySQL Database

To begin, import the PyMySQL library into your project's environment to handle database operations.

# Importing the required lib
import pymysql

PyMySQL includes a connect() function that accepts the necessary arguments, such as host, username, password, database name, and so on, to establish a connection with the database server.

In this step, you will need access to your MySQL server's username and password.

# Initialize connection with server
mysql_db = pymysql.connect(
    host="localhost",
    user="root",
    password="********"
)

In the above code, the host is where your MySQL server is hosted; in this case, it is hosted on a local machine, therefore the value "localhost" is provided.

The user is your MySQL server's username, which is "root" by default, and the password is the one you specified when you first set up the server.

To interact with the MySQL database, you must first create a cursor object for it using the cursor() function.

# Database cursor
cursor = mysql_db.cursor()

This step involves running a MySQL query to create a database on the MySQL server using the cursor (mysql_db.cursor()) object.

# SQL query to create database
cursor.execute("CREATE DATABASE IF NOT EXISTS pokemon_db")
cursor.execute("SHOW DATABASES")

The cursor.execute() executes the SQL query. The first query says "Create a database named pokemon_db if it doesn't exist already" and the second query says "Show all the databases reside on the server".

Finally, disconnect the database connection and cursor object with the close() function.

# Closing the database cursor and connection
cursor.close()
mysql_db.close()

When you run the code, nothing will appear on the console, but your database has been created on the server. You can check in the MySQL Workbench.

To display all of the databases on the server using Python, add the following code to the script.

# Displaying databases
for databases in cursor:
    print(databases)

Now, when you rerun the code, you'll see all the databases residing on the server are displayed on the console.

('books_db',)
('information_schema',)
('mysql',)
('performance_schema',)
('pokemon_db',)
('sys',)

You can see your newly created database (pokemon_db) is being displayed.

Interacting with Database

You may simply interact with this newly generated MySQL database by adding tables and columns and performing CRUD operations.

Creating a Database Table

You have established a MySQL database called pokemon_db. Now you must create a table with some fields to store data related to the fields.

Create a new file in your project directory and place the following code within it.

# Importing PyMySQL and cursors
import pymysql.cursors

# Initialize connection with database
mysql_db = pymysql.connect(
    host="localhost",
    user="root",
    password="********",
    database="pokemon_db",
    cursorclass=pymysql.cursors.DictCursor
)

# Database cursor
cursor = mysql_db.cursor()

# Function to create a table
def create_db_table():
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS pokemon (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(500) NOT NULL UNIQUE,
                    cp INT(50) NOT NULL,
                    hp INT(50) NOT NULL
                )
            ''')
    mysql_db.commit()

if __name__ == "__main__":
    create_db_table()

cursor.close()

This time, the database name (pokemon_db) is supplied in the connect() function. This implies that a connection will be established to the pokemon_db database.

The cursorclass is set to cursors.DictCursor, a cursor that returns results in dictionary format.

The create_db_table() function creates a table named "pokemon" containing the following fields:

id: This field assigns a serial number for each entry made in the database automatically due to "AUTO_INCREMENT".
name: stores the Pokemon name.
cp: stores the combat power of the Pokemon.
hp: stores the high power of the Pokemon.

The changes are saved to the database using mysql_db.commit(). After running the code, the table will be created with the specified fields.

Adding Data to the Database

...

# Adding data to the database
def add_entry():
    # SQL query
    query = '''
            INSERT INTO `pokemon` (`name`, `cp`, `hp`) 
            VALUES (%s, %s, %s)
            '''

    # Adding three entries
    cursor.execute(query, ('Charizard', 120, 200))
    cursor.execute(query, ('Pikachu', 60, 100))
    cursor.execute(query, ('Squirtle', 78, 102))

    # Committing the changes
    mysql_db.commit()

if __name__ == "__main__":
    # create_db_table()
    add_entry()

cursor.close()

The add_entry() function is defined and added to the code from the previous section.

Inside the function, an SQL query is defined to insert data in the pokemon table to the corresponding fields. Next, the function executes the SQL query multiple times, each time with different values for the Pokemon's name, combat power (cp), and high power (hp).

After adding the entries, the function commits the changes to the database using mysql_db.commit().

When you run the function, the data will be added to the database.

Reading Data from the Database

...

# Reading data from the database
def read_entry():
    # SQL query
    query = '''
    SELECT `name`, `cp`, `hp` FROM `pokemon`;
    '''
    cursor.execute(query)

    # Fetching data from the database
    for data in cursor.fetchall():
        print(
            data['name'],
            data['cp'],
            data['hp']
        )


if __name__ == "__main__":
    # create_db_table()
    # add_entry()
    read_entry()

cursor.close()

The read_entry() function executes an SQL query that selects all values from the table pokemon. The data is then fetched using the cursor.fetchall() function.

You'll get all the entries inserted into the database when you run the code.

Charizard 120 200
Pikachu 60 100
Squirtle 78 102

Updating Data in the Database

...

# Function to update an entry
def update_entry():
    query = '''
            UPDATE `pokemon`
            SET `cp` = %s
            WHERE `id` = %s
            '''

    # Executing SQL query with values
    cursor.execute(query, (140, 2))

    # Committing the changes
    mysql_db.commit()


if __name__ == "__main__":
    # create_db_table()
    # add_entry()
    update_entry()
    read_entry()

cursor.close()

The update_entry() function is defined, and within it, an SQL query is written to update the pokemon table by setting the value for the cp field for the supplied id.

The cursor.execute() function executes the query that updates the cp of the Pokemon to 140 whose id is equal to 2.

The changes are then saved to the database using mysql_db.commit(). When you run the code, you'll see the change in the value.

Charizard 140 200
Pikachu 60 100
Squirtle 78 102

You can see that the Charizard's cp has been updated, and it is now 140 because it has an id of 2, which may differ in your situation.

Deleting Data from the Database

# Function to delete the entry
def delete_entry():
    query = '''
            DELETE FROM `pokemon`
            WHERE `id` = %s
            '''

    # Executing SQL query for deletion
    cursor.execute(query, 2)

    # Committing the changes
    mysql_db.commit()


if __name__ == "__main__":
    # create_db_table()
    # add_entry()
    # update_entry()
    delete_entry()
    read_entry()


cursor.close()

The delete_entry() function executes an SQL query to remove the entire record from the pokemon table with the supplied id.

When you run the code, you'll see that the entire record of the id equal to 2 has been deleted.

Pikachu 60 100
Squirtle 78 102

Conclusion

You may work with MySQL databases in Python by using MySQL client libraries, and in this article, you've learned how to create and communicate with MySQL databases using the PyMySQL library.

First, you learned to create a MySQL database using the PyMySQL library in Python.

You interacted and performed the following operations after the database was established:

Created a MySQL database table
Inserted the data into the database
Reading that data from the database
Updating the data in the database
Deleting the data from the database

There are various libraries available for building a MySQL database in Python, and the process of creating and communicating with the database is nearly identical to that described in this article.

🏆Other articles you might be interested in if you liked this one

✅Create and integrate MySQL database in Flask app in Python.

✅Create and connect SQLite database with Flask app.

✅How to use Flask's Blueprint to structure your Flask app much better?

✅What are sessions and how to create a session in Flask?

✅Create a WebSocket server and client in Python.

✅How do decorators in Python work and how to create a custom decorator?

That's all for now

Keep Coding✌✌