# 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**](https://pymysql.readthedocs.io/en/latest/index.html) 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.

```bash
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.

```python
# 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.

```python
# 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.

```python
# 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.

```python
# 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.

```python
# 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.

```python
# 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.

```bash
('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.

```python
# 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

```python
...

# 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

```python
...

# 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.

```bash
Charizard 120 200
Pikachu 60 100
Squirtle 78 102
```

### Updating Data in the Database

```python
...

# 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.

```bash
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

```python
# 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.

```bash
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](https://geekpython.in/create-and-integrate-mysql-database-with-flask-app).

✅[Create and connect SQLite database with Flask app](https://geekpython.in/connect-sqlite-database-with-flask-app).

✅[How to use Flask's Blueprint to structure your Flask app much better](https://geekpython.in/structure-flask-app-with-blueprint)?

✅[What are sessions and how to create a session in Flask](https://geekpython.in/how-to-use-sessions-in-flask)?

✅[Create a WebSocket server and client in Python](https://geekpython.in/build-websocket-server-and-client-using-python).

✅[How do decorators in Python work and how to create a custom decorator](https://geekpython.in/decorators-in-python)?

---

**That's all for now**

**Keep Coding✌✌**