Python Database Access

This guide provides an overview of how to access databases using Python. It covers the basics of connecting to a database, executing SQL commands, and managing database data.

Key Concepts

• Database: A structured collection of data that can be easily accessed, managed, and updated.
• SQL (Structured Query Language): A standard language for managing and manipulating databases.
• Database Management System (DBMS): Software that interacts with databases to facilitate the storage, retrieval, and management of data.

Connecting to a Database

To work with databases in Python, you typically use a library that provides a connection interface. The most commonly used libraries include:

• SQLite: A lightweight database that comes built-in with Python.
• MySQL: A popular open-source relational database.
• PostgreSQL: An advanced open-source relational database system.

Example: Connecting to SQLite

import sqlite3

# Create a connection to the database
conn = sqlite3.connect('example.db')

# Create a cursor object
cursor = conn.cursor()

Executing SQL Commands

Once connected, you can execute SQL commands using the cursor object. Common SQL operations include:

• CREATE: To create a table.
• INSERT: To add data to a table.
• SELECT: To retrieve data from a table.
• UPDATE: To modify existing data.
• DELETE: To remove data from a table.

Example: Creating a Table

# Create a new table
cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
''')

Example: Inserting Data

# Insert data into the table
cursor.execute('''
INSERT INTO users (name, age) VALUES ('Alice', 30)
''')

Example: Retrieving Data

# Retrieve data from the table
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

for row in rows:
    print(row)

Closing the Connection

Always remember to close the database connection after completing your operations to free up resources.

# Close the connection
conn.close()

Summary

• Python provides various libraries for database access, with SQLite being the simplest for beginners.
• You can perform various SQL operations like creating tables, inserting, selecting, updating, and deleting data.
• Always ensure to close your database connections after use.

This guide serves as a foundation for beginner developers to start working with databases in Python. As you advance, you can explore more complex database systems and ORM (Object-Relational Mapping) frameworks like SQLAlchemy or Django ORM.