Open Database Connectivity (ODBC)

Summary

Open Database Connectivity (ODBC) serves as a translation layer between a program and various database vendors. Programming languages like i.e. Python with its pyodbc and psycopg packages facilitate database operations, particularly with PostgreSQL.

Details

Definition: ODBC enables applications to connect to databases, but can cause vendor lock-in when dependent on a particular database system’s features, limiting flexibility in switching databases later.

Python and PostgreSQL

Packages: pyodbc: A library that facilitates ODBC-based database connections, making it easy to switch between databases. psycopg: A PostgreSQL adapter tailored for efficient database access in Python, providing methods like connect(), cursor(), and execute() for managing queries.

Installation: python -m pip install psycopg Basic API Functions: connect(): Establishes a connection. cursor(): Manages the context of SQL operations. execute(): Executes SQL queries. fetchone(), fetchmany(), fetchall(): Retrieve query results.

import psycopg as pg
from psycopg.rows import dict_row
 
conn_string = "host=localhost dbname=Sports user=postgres password=******"
# Connection option one
conn = pg.connect(conn_string, autocommit=True, row_factory=dict_row)
# Execute Queries using Cursors
conn.close()
# Connection option two
with pg.connect(conn_string, autocommit=True, row_factory=dict_row) as conn:
    # Execute Queries using Cursors
    # Closes connection

Querying to DB

Python allows seamless querying of PostgreSQL databases with execute(). Cursors retrieve query results using methods like fetchone(), fetchmany(), or fetchall(). These operations return rows, which can be processed in a Pythonic way (like dictionaries for easy access to column data).

# Get all athletes
athletes: list[dict] = conn.execute("SELECT * FROM Athletes").fetchall()
# Get five athletes
athletes: list[dict] = conn.execute("SELECT * FROM Athletes").fetchmany(5)
# Get one athlete
athletes: list[dict] = conn.execute("SELECT * FROM Athletes").fetchone()

Cursors

Cursors allow you to interact with the database in chunks (i.e., retrieving part of a query result). You can maintain multiple cursors simultaneously, executing different queries. This is beneficial for complex operations across various datasets. Example: One cursor can fetch athletes’ data while another retrieves sports data concurrently.

# Get query cursor
athletes_cursor = conn.execute("SELECT * FROM Athletes")
# Get five athletes
athletes: list[dict] = athletes_cursor.fetchmany(5)
# Get sixth athlete
athlete_six = athletes_cursor.fetchone()
# Get the next 10 athletes
athlete_next_ten = athletes_cursor.fetchmany(10)

Remember to close cursors properly:

sports_cursor = conn.execute("SELECT * FROM Sports")
sports: list[dict] = sports_cursor.fetchall()
athletes_cursor.close()
sports_cursor.close()

Creating a Table

The CREATE TABLE statement in SQL is used to define new tables within a database. Using psycopg, this can be achieved through the execute() method in Python, allowing for database schema creation directly from the script.

with pg.connect(conn_string, autocommit=True, row_factory=dict_row) as conn:
    conn.execute("DROP TABLE IF EXISTS Quotes")
    conn.execute(
    """
    CREATE TABLE Quotes (
        x INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        y VARCHAR(250) NOT NULL
    )
    """
    )

Insert Statement

SQL INSERT commands add new data to tables. The example shows how to insert records into a Quotes table and demonstrates Python’s approach to managing database connections and cursor lifecycle. Note: Ensure connections and cursors are properly closed to prevent resource leakage.

# Won’t run since the connection is closed
conn = pg.connect(conn_string, autocommit=True, row_factory=dict_row)
with conn:
    conn.execute("INSERT INTO Quotes (y) VALUES ('I am Vengeance')")
    nope = conn.execute("SELECT 42 as universe").fetchone()
 
# Will run since only the cursor is closed
with conn.cursor() as cur:
    cur.execute("INSERT INTO Quotes (y) VALUES ('I am the Night')")
    yes = conn.execute("SELECT 42 as universe").fetchone()
conn.close()

Querying with Variables

Variables can be safely used in SQL queries using placeholders (%s) to avoid SQL injection attacks. This prevents malicious users from altering query logic by inserting harmful code. Be cautious with SQL Injection risks.

in_text = 'I am Batman'
sel_text = "'%Batman%'"
 
with pg.connect(conn_string, autocommit=True, row_factory=dict_row) as conn:
    conn.execute("INSERT INTO Quotes (y) VALUES (%s)", [in_text])
    conn.execute("SELECT * FROM Quotes WHERE y LIKE %s" % sel_text)

SQL Injection Example

SQL Injection occurs when unsanitized user input is inserted directly into SQL queries, allowing attackers to manipulate the database. This example demonstrates how vulnerable SQL statements can be if not properly handled.

in_text = 'I am Batman'
sel_text = "'%Batman%'; DELETE FROM Quotes WHERE 1=1; INSERT INTO Quotes (y) VALUES ('Joker was here');--"
 
with pg.connect(conn_string, autocommit=True, row_factory=dict_row) as conn:
    conn.execute("INSERT INTO Quotes (y) VALUES (%s)", [in_text])
    conn.execute("SELECT * FROM Quotes WHERE y LIKE %s" % sel_text)

Prepared Statements

Prepared statements offer a safeguard against SQL injection by precompiling SQL queries with placeholders for variables. This separates user input from the query logic, enhancing security. Example: The PREPARE and EXECUTE commands in SQL or using placeholders in psycopg.

PREPARE my_query AS
 SELECT *
 FROM Quotes
 WHERE y = "$1"
EXECUTE my_query ("Probe used SQL Injection... it’s not very effective")

Python Example with Prepared Statements

Prepared statements in Python use parameterized queries with placeholders to prevent SQL injection. This ensures that user input is treated as data, not part of the SQL code.

in_text = 'I am Batman'
sel_text = "'%Batman%'; DELETE FROM Quotes WHERE 1=1; INSERT INTO Quotes (y) VALUES ('Joker was here');--"
 
with pg.connect(conn_string, autocommit=True, row_factory=dict_row) as conn:
    conn.execute("INSERT INTO Quotes (y) VALUES (%s)", [text])
    conn.execute("SELECT * FROM Quotes WHERE y LIKE %s", [sel_text])

Caution: Not all SQL statements are safe against injections. Validate input, especially for ORDER BY clauses.

Autocommit

Autocommit OFF (default): Changes are only saved to the database after explicitly committing them. This provides control but requires manual intervention to ensure data persistence. Autocommit ON: Automatically commits transactions after each query, reducing the need for explicit commit calls. Context Managers: Python’s context managers (with statements) simplify transaction management by automatically committing changes or rolling back if an error occurs.

with pg.connect(conn_string, row_factory=dict_row) as conn:
    conn.execute("INSERT INTO Quotes (y) VALUES ('I am the one who knocks')")
    # BEGIN;
    # INSERT...;
    # COMMIT; (default) / ROLLBACK; (if error is raised)

Cursors in Transactions

Within transactions, cursors allow you to fetch data or perform modifications, and you must explicitly call commit() to save changes. This ensures that operations are atomic (either fully applied or not at all).

conn = pg.connect(conn_string, row_factory=dict_row)
with conn.cursor() as cur:
    tests = cur.execute("SELECT * FROM Quotes")
    # BEGIN;
    # SELECT...;
    # Not committed, manually call conn.commit() inside or after the block
conn.commit()

Transactions with Autocommit ON

When autocommit is enabled, every SQL operation is automatically wrapped in a transaction, which gets committed upon successful execution. This is useful when performing single-step modifications like inserts or updates.

conn = pg.connect(conn_string, autocommit=True, row_factory=dict_row)
conn.execute("INSERT INTO Quotes (y) VALUES ('I am inevitable')")
# BEGIN; SELECT...; COMMIT/ROLLBACK;
with conn.transaction():
    # BEGIN;
    with conn.cursor() as cur:
        cur.execute("INSERT INTO Quotes (y) VALUES ('I am Groot')")
        cur.execute("INSERT INTO Quotes (y) VALUES ('I am Iron Man!')")
    # COMMIT; (default) / ROLLBACK; (if error is raised)

INDBS Noter

Explorer