Database | learnpython.tech

The Python ecosystem provides the necessary modules enabling establish a connection to the most popular and widely used databases. Some of the most popular databases supported are:

IMB DB2
Firebird
MySQL
PostgreSQL
SQLite

You can refer to this article for more information.

In this tutorial, we will briefly discuss connecting Python to the MySQL database.

However, covering MySQL is out of the scope of this tutorial. We will strictly cover Python.

For more information on MySQL , please refer to the following article.

Prerequisite

Python provides the MySQL package available via PIP.

Please download the package mentioned above before proceeding.

The following are the database credentials:

Host:     localhost 
Username: admin 
Password: admin 
Database: my_admin_db

Execute the following statements in MySQL console.

DROP DATABASE IF EXISTS my_admin_db; CREATE DATABASE my_admin_db; CREATE TABLE FRUITS(id INT PRIMARY KEY AUTO_INCREMENT, fruit_name VARCHAR(30) NOT NULL);

Reading from the MySQL Database

Example:

# Import pymysql
import pymysql

# Attempt to establish DB Connection
db = pymysql.connect ("localhost", "admin", "admin_pwd", "my_admin_db")

# Create a cursor object for Python to iterate through to fetch records.
cursor = db.cursor ()

try:
    # Execute SQL Query
    cursor.execute("SELECT * FROM FRUITS;")

    # Fetch Fruits
    results = cursor.fetchall()

    # WIll store all fruits
    fruits = []
    for row in results:
        fruit_id = row[0]
        fruit_name = row[1]

        # Create a tuple and append it
        fruits.append((fruit_id, fruit_name))

    # Print all the fruits
    print(fruits)
except:
    print("Error occurred while fetching data from database")
finally:
    if db:
        db.close()

[(0, "Banana"), (1, "Apple"), (2, "Peach")]

Let us go through every statement and understand what actions they are performing.

#1:

import pymysql

informs Python to load the module pymysql. If Python cannot find the module in the path, it will generate an exception.

#2:

db = pymysql.connect ("localhost", "admin", "admin_pwd_lp_tech", "my_admin_db")

This statement attempts to connect to the MYSQL database using the connect method provided by the pymysql package.

The four arguments are as follows:

localhost: address of the database on the network
admin: this is the username of the database
admin_pwd_lp_tech: this is the password of the database
my_admin_db: is the name of the database the program is going to connect.

#3:

cursor = db.cursor ()

This statement prepares a cursor object to iterate through.

#4:

try:

This statement opens a try-except block. The following except block will handle any generated exception.

#5:

cursor.execute("SELECT * FROM FRUITS;")

This statement informs the cursor object to execute a SQL query.

#6:

results = cursor.fetchall()

The cursor object will fetch all the matching rows in a list and assign them to variable named results.

#7:

fruits = []

This statement declares an empty list. It will store all the fruit's information .

#8:

for row in results:
fruit_id = row[0]
fruit_name = row[1]

These statements will fetch the records from the iterator "results" and assign each accessible item a temporary name row .

fruit_id = row[0]

will set the first value of the table, i.e., id, to the variable fruit_id.

fruit_name = row[1]

will assign the second value of the table, i.e., the fruit name to the variable fruit_name.

#9:

fruits.append((fruit_id, fruit_name))

This statement will create a tuple with fruit_id, fruit_name and append it to a list.

#10:

except:
print("Error occurred while fetching data from database")

These statements will catch any exception generated and display the exception message.

#11:

finally:
if db:
db.close()

These statements will execute irrespective of the exception status. It will close the DB connection if it exists in the first place.

Remember, any non-zero, non-None value evaluates to True. Hence, if the database connection exists, it will execute the

db.close()

statement.

Inserting a record into the MySQL database.

Python allows for inserting records into the MySQL database using the

execute()

methods and committing them using the

commit()

method. Committing the changes is required for the database to finalize the changes and not temporarily alter the changes for the session's duration. Since covering MySQL is beyond the scope of this tutorial, we won't be covering them; if you want, you can read from external sources.

Example:

# Import pymysql
import pymysql

# Attempt to establish DB Connection
db = pymysql.connect ("localhost", "admin", "admin_pwd_lp_tech", "my_admin_db")

# Create a cursor object for Python to iterate through to fetch records.
cursor = db.cursor ()

try:
    # Execute SQL Query
    query = """INSERT INTO FRUITS VALUES("Cherry");"""

    # Execute cursor
    cursor.execute(query)

    # Commit to Database
    db.commit()

    # Show messages
    print("Data inserted successfully")
except:
    print("Error occurred while fetching data from database")
finally:
    if db:
        db.close()

The basic structure of most database programs will remain the same. Hence, the relevant statements are explained.

# Execute SQL Query
query = """INSERT INTO FRUITS VALUES("Cherry");"""

# Execute cursor
cursor.execute(query)

# Commit to Database
db.commit()

The statement

query = """INSERT INTO FRUITS VALUES("Cherry");"""

defines a variable query and assign it a valid MySQL query statement to insert a record with value "Cherry".

The statement

cursor.execute(query)

executes the query and inserts the same into the record.
NOTE: This will only insert the record temporarily(duration of the session).

The statement

db.commit()

will execute the query statement and insert the record into the database.

Updating a record into the MySQL Database

Python allows for updating or editing records using the

execute()

method and

commit()

method to commit those changes permanently.

Example:

# Import pymysql
import pymysql

# Attempt to establish DB Connection
db = pymysql.connect ("localhost", "admin", "admin_pwd_lp_tech", "my_admin_db")

# Create a cursor object for Python to iterate through to fetch records.
cursor = db.cursor ()

try:
    # Execute SQL Query
    query = """UPDATE FRUITS SET fruit_name = 'Red Cherry' WHERE fruit_name='Cherry';"""

    # Execute cursor
    cursor.execute(query)

    # Commit to Database
    db.commit()

    # Show messages
    print("Data updated successfully")
except:
    print("Error occurred while fetching data from database")
finally:
    if db:
        db.close()

This example is the same as the insertion example, and only the query is changed(which is a part of MySQL ). The structure of Python statements remains the same.

Deleting a record from the MySQL Database.

Python allows for deleting the records using the

execute()

method and

commit()

method to commit those changes permanently.

Example

# Import pymysql
import pymysql

# Attempt to establish DB Connection
db = pymysql.connect ("localhost", "admin", "admin_pwd_lp_tech", "my_admin_db")

# Create a cursor object for Python to iterate through to fetch records.
cursor = db.cursor ()

try:
    # Execute SQL Query
    query = """DELETE FROM FRUITS WHERE fruit_name = 'Apple';"""

    # Execute cursor
    cursor.execute(query)

    # Commit to Database
    db.commit()

    # Show messages
    print("Data deleted successfully")
except:
    print("Error occurred while fetching data from database")
finally:
    if db:
        db.close()

Conclusion

This chapter taught us about various popular databases supported by Python, connecting Python applications to MySQL databases.

Additionally, we also learned about the CRUD(Create, Read, Update, Delete) operations on the database and the similarity of the statement when performing different database operations.

Built In