Integrating Databases with Python

Learn how to integrate databases with Python, including connecting to databases, executing queries, and handling transactions.

Integrating Databases with Python  

Integrating databases with Python is essential for data-driven applications. This guide covers connecting to databases, executing queries, and handling transactions in Python.

Connecting to Databases  

Using SQLite  

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

Connecting to SQLite database is straightforward and does not require additional drivers.

Using PostgreSQL  

First, install psycopg2.

pip install psycopg2

import psycopg2

conn = psycopg2.connect(
    dbname="example_db",
    user="user",
    password="password",
    host="localhost"
)
cursor = conn.cursor()

Connecting to PostgreSQL requires providing connection parameters.

Executing Queries  

Creating a Table  

cursor.execute('''CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
conn.commit()

Inserting Data  

cursor.execute('''INSERT INTO users (name, age) VALUES (?, ?)''', ("Alice", 30))
conn.commit()

Using parameterized queries helps prevent SQL injection.

Fetching Data  

cursor.execute('''SELECT * FROM users''')
rows = cursor.fetchall()
for row in rows:
    print(row)

Fetching data and iterating over the results.

Handling Transactions  

Using Commit and Rollback  

try:
    cursor.execute('''INSERT INTO users (name, age) VALUES (?, ?)''', ("Bob", 25))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Transaction failed: {e}")

Handling transactions ensures data integrity.

Closing the Connection  

Always close the connection when done.

conn.close()

Using a context manager to ensure connections are closed properly.

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute('''SELECT * FROM users''')
    rows = cursor.fetchall()
    for row in rows:
        print(row)

Conclusion  

Integrating databases with Python is crucial for developing data-driven applications. Practice connecting to different databases, executing queries, and handling transactions to effectively manage your data.