Python and Databases

We'll explore how Python can interact with databases. We'll cover everything from basic database concepts to advanced database operations using Python. By the end of this topic, you'll have a thorough understanding of how to work with databases in Python, from establishing connections to executing queries and performing data manipulation.

Introduction to Databases

What is a Database?

A database is a structured collection of data that is organized and stored for easy access, retrieval, and management. It provides a systematic way of managing and storing data efficiently.

Types of Databases

  1. Relational Databases: Data is stored in tables with rows and columns, and relationships between tables are established using keys.
  2. NoSQL Databases: Data is stored in flexible, schema-less formats, and relationships can be established in various ways.

Connecting to Databases in Python

Establishing Database Connections

To interact with databases in Python, we need to establish a connection using appropriate database drivers.

Example: Connecting to a MySQL Database

				
					import mysql.connector

# Establish connection
conn = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="mydatabase"
)

# Check if connection is successful
if conn.is_connected():
    print("Connected to MySQL database")
else:
    print("Failed to connect")

Explanation:

  • We use the mysql.connector module to connect to a MySQL database.
  • Parameters such as host, user, password, and database name are provided to establish the connection.

Executing SQL Queries

Basic Query Execution

Once connected, we can execute SQL queries to retrieve, insert, update, or delete data from the database.

Example: Executing a Select Query

				
					# Create cursor
cursor = conn.cursor()

# Execute query
cursor.execute("SELECT * FROM users")

# Fetch and print results
for row in cursor.fetchall():
    print(row)

# Close cursor
cursor.close()

Explanation:

  • We create a cursor object to execute SQL queries.
  • We execute a SELECT query to retrieve all records from the “users” table.
  • We fetch and print the results obtained from the query.

Data Manipulation

Inserting Data

We can use SQL INSERT statements to add new records to a database table.

Example: Inserting Data into a Table

				
					# Create cursor
cursor = conn.cursor()

# Execute INSERT query
cursor.execute("INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')")

# Commit changes
conn.commit()

# Close cursor
cursor.close()

Explanation:

  • We execute an INSERT query to add a new record to the “users” table with the specified name and email.
  • Changes are committed to the database using conn.commit() to make them permanent.

Updating Data

We can use SQL UPDATE statements to modify existing records in a database table.

Example: Updating Data in a Table

				
					# Create cursor
cursor = conn.cursor()

# Execute UPDATE query
cursor.execute("UPDATE users SET email = 'johndoe@example.com' WHERE name = 'John Doe'")

# Commit changes
conn.commit()

# Close cursor
cursor.close()

Explanation:

  • We execute an UPDATE query to change the email address of a user named “John Doe” in the “users” table.
  • Changes are committed to the database to make them permanent.

Deleting Data

We can use SQL DELETE statements to remove records from a database table.

Example: Deleting Data from a Table

				
					# Create cursor
cursor = conn.cursor()

# Execute DELETE query
cursor.execute("DELETE FROM users WHERE name = 'John Doe'")

# Commit changes
conn.commit()

# Close cursor
cursor.close()

Explanation:

  • We execute a DELETE query to remove the user named “John Doe” from the “users” table.
  • Changes are committed to the database to make them permanent.

Advanced Database Operations

Transactions

Transactions allow us to execute a sequence of SQL statements as a single unit of work. This ensures data consistency and integrity.

Example: Performing a Transaction

				
					try:
    # Start transaction
    conn.start_transaction()

    # Execute multiple SQL statements
    cursor.execute("UPDATE users SET status = 'active' WHERE id = 1")
    cursor.execute("INSERT INTO logs (user_id, action) VALUES (1, 'Updated status')")

    # Commit transaction
    conn.commit()
except mysql.connector.Error as e:
    # Rollback transaction if an error occurs
    conn.rollback()
    print("Transaction failed:", e)
finally:
    # Close cursor
    cursor.close()

Explanation:

  • We start a transaction using conn.start_transaction().
  • We execute multiple SQL statements as part of the transaction.
  • If any error occurs during the transaction, we roll back changes using conn.rollback().
  • Finally, we close the cursor.

Working with Specific Database Management Systems

MySQL

Introduction to MySQL

MySQL is a popular open-source relational database management system (RDBMS) known for its reliability, scalability, and ease of use.

Installing MySQL Connector for Python

Before connecting Python to MySQL, you need to install the MySQL Connector module using pip:

				
					pip install mysql-connector-python

Example: Connecting to MySQL Database

				
					import mysql.connector

# Establish connection
conn = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="mydatabase"
)

# Check if connection is successful
if conn.is_connected():
    print("Connected to MySQL database")
else:
    print("Failed to connect")

Explanation:

  • We import the mysql.connector module to work with MySQL databases.
  • We establish a connection by providing the host, user, password, and database name.

SQLite

Introduction to SQLite

SQLite is a lightweight, serverless, self-contained SQL database engine. It’s widely used in embedded systems and mobile applications.

Installing SQLite3 for Python

SQLite comes pre-installed with Python, so you don’t need to install any additional modules.

Example: Connecting to SQLite Database

				
					import sqlite3

# Establish connection
conn = sqlite3.connect("mydatabase.db")

# Check if connection is successful
if conn:
    print("Connected to SQLite database")
else:
    print("Failed to connect")

Explanation:

  • We import the sqlite3 module to work with SQLite databases.
  • We establish a connection by providing the path to the SQLite database file.

We've covered the basics of working with databases in Python. We learned how to establish connections to databases, execute SQL queries, perform data manipulation operations such as insertion, updating, and deletion, and handle advanced database operations including transactions.
Understanding how to interact with databases in Python is essential for building data-driven applications and performing data analysis tasks. With the knowledge gained from this topic, you'll be well-equipped to work with various types of databases and leverage the power of Python for managing and manipulating data effectively. Happy Coding!❤️

Table of Contents

Home
Roadmap
ATS Resume
Playground
Resources