Working with Database

A database is an organized collection of inter-related data, generally stored and accessed electronically from a computer system.

• They are structured to facilitate the storage, retrieval, modification, and deletion of data in conjunction with various data-processing operations.

• A database can hold various forms of data, including text, numbers, images, and more, and is designed to manage large amounts of information efficiently.

• It serves as the backbone for many modern applications, such as websites, business systems, and research tools.

Examples:

• Relational databases like MySQL, PostgreSQL, and Oracle.
• NoSQL databases like MongoDB, Cassandra, and Redis.

Working with Database:

Working with databases is an essential part of many Python applications, especially those that require storing and retrieving structured data.

• Python provides built-in and external libraries that support interaction with different types of databases such as SQLite, MySQL, and PostgreSQL.

• These libraries allow developers to use SQL (Structured Query Language) statements directly from Python code to manage database operations.

Commonly Used Database Libraries in Python:

• sqlite3 – Built-in library to work with SQLite databases. It’s lightweight and ideal for local or embedded applications.

• mysql-connector-python – External library used to connect to MySQL databases.

• psycopg2 – A powerful PostgreSQL adapter for Python.

Key Database Operations Supported in Python:

Using any of the database libraries mentioned above, Python enables you to perform core database operations such as:

• Connecting to a Database: Establishing a connection to an existing database or creating a new one.
• Creating Tables: Defining schema using SQL CREATE TABLE statement.
• Inserting Data: Adding records using SQL INSERT INTO.
• Retrieving Data: Querying data using SELECT.
• Updating Records: Modifying existing records with UPDATE.
• Deleting Records: Removing records using DELETE.
• Committing Transactions: Saving changes to the database using commit().
• Closing the Connection: Releasing resources using close().

Example Using sqlite3 (Built-in Python Module):
Below is a complete and simple example demonstrating how to use SQLite in Python:

import sqlite3

# 1. Connect to database (or create if it doesn't exist)
conn = sqlite3.connect("example.db")

# 2. Create a cursor object to interact with the database
cursor = conn.cursor()

# 3. Create a table (if not already exists)
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT)")

# 4. Insert a record
cursor.execute("INSERT INTO users VALUES (1, 'Alice')")

# 5. Retrieve records
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())  # Output: [(1, 'Alice')]

# 6. Commit changes to save data
conn.commit()

# 7. Close the connection
conn.close()

Explanation of the Code

• sqlite3.connect(): Establishes a connection to the database file. If the file doesn’t exist, it will be created.
• cursor(): Creates a cursor object used to execute SQL commands.
• execute(): Runs SQL statements such as CREATE, INSERT, SELECT, etc.
• fetchall(): Retrieves all the rows returned by the SELECT query.
• commit(): Saves changes made to the database.
• close(): Closes the database connection safely.

Use Cases of Database Integration in Python:

• Web applications for storing user data (e.g., Flask/Django apps).
• Data analytics and reporting tools.
• Inventory and record management systems.
• IoT devices that log sensor data locally.

Key Points to Remember:

• SQL is the language used to interact with relational databases.
• Python abstracts SQL operations through database connectors and libraries.
• Always close your database connection to avoid memory leaks.
• Use commit() after any change (INSERT, UPDATE, DELETE) to save it.