Records: SQL for Humans

Warnings

• breaking Version 0.6.0 introduces support for SQLAlchemy 2+ and drops support for Python 2.7, 3.4, and 3.5. Applications targeting these older Python versions or SQLAlchemy 1.x will encounter compatibility issues. SQLAlchemy 2.0 itself includes significant breaking changes in its API from 1.x, which might indirectly affect custom extensions or very specific usage patterns if Records' abstraction leaks.
  Fix: Upgrade your Python environment to 3.6+ and ensure SQLAlchemy is at version 2.x. Review SQLAlchemy's migration guides for any underlying changes that might impact your database setup or custom drivers.
• gotcha While Records supports parameterized queries (e.g., `db.query('...', name='value')`), directly concatenating user-supplied input into SQL strings (SQL injection) remains a severe security vulnerability. This applies to any library allowing raw SQL queries. Always use parameterized queries for all dynamic values.
  Fix: Never concatenate user-provided or untrusted input directly into SQL queries. Always pass dynamic values as parameters to the `query` method, allowing the underlying database driver to safely handle the escaping.
• gotcha Records employs lazy database connection. The connection to the database is not fully established or checked until the first query is executed. This means that errors related to incorrect connection strings or inaccessible databases will only manifest at the time of the first query, not at the `records.Database()` initialization.
  Fix: Implement error handling around your first database query to catch potential connection issues early. Consider a simple `db.query('SELECT 1')` or similar lightweight query immediately after establishing the `Database` object in critical paths to confirm connectivity.
• gotcha Without explicitly using `db.transaction()`, each `db.query()` might operate as an auto-committed transaction, depending on the underlying database and its driver's default behavior. This can lead to unexpected partial commits if multiple queries are intended to be part of a single, atomic operation.
  Fix: For operations involving multiple SQL statements that must be treated as a single atomic unit, explicitly use `db.transaction()` as a context manager (e.g., `with db.transaction(): ...`) to ensure proper commit or rollback behavior.

Install

• pip install records Basic Installation
• pip install records[pandas] With Pandas for DataFrame export

Imports

• Database

  import records
  db = records.Database(...)

Quickstart

This quickstart demonstrates how to connect to a database (using an in-memory SQLite by default or a URL from an environment variable), execute a parameterized query, iterate through results, and access data from a `Record` object. It highlights basic CRUD operations and the importance of parameterized queries for security.

import records
import os

# Example using SQLite in-memory or a database URL from an environment variable
# For other databases, change the connection string, e.g., 
# "postgres://user:pass@host:port/dbname"
database_url = os.environ.get('DATABASE_URL', 'sqlite:///:memory:')
db = records.Database(database_url)

# Create a table and insert data (example for SQLite)
if 'sqlite' in database_url:
    db.query('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
    db.query('INSERT INTO users (name, age) VALUES (:name, :age)', name='Alice', age=30)
    db.query('INSERT INTO users (name, age) VALUES (:name, :age)', name='Bob', age=24)

# Run a query
rows = db.query('SELECT * FROM users WHERE age > :min_age', min_age=25)

# Iterate over results
print('Users over 25:')
for row in rows:
    print(f"  Name: {row.name}, Age: {row.age}")

# Access columns by name or index
first_row = rows.first()
if first_row:
    print(f"First user found: {first_row.name}")

# Export to CSV (requires tablib)
# print(rows.export('csv'))

# Export to Pandas DataFrame (requires pandas)
# df = rows.export('df')
# print(df.head())

# Close the connection (important for some databases)
db.close()