{"id":6851,"library":"records","title":"Records: SQL for Humans","description":"Records is a simple yet powerful Python library designed for making raw SQL queries to various relational databases with an elegant interface. It aims to reduce boilerplate code associated with common database tasks. The current version, 0.6.0, includes support for SQLAlchemy v2, making it compatible with modern database backends. Releases are infrequent but tend to include significant updates.","status":"active","version":"0.6.0","language":"en","source_language":"en","source_url":"https://github.com/kennethreitz/records","tags":["SQL","database","SQLAlchemy","data access","data export"],"install":[{"cmd":"pip install records","lang":"bash","label":"Basic Installation"},{"cmd":"pip install records[pandas]","lang":"bash","label":"With Pandas for DataFrame export"}],"dependencies":[{"reason":"Core ORM and database abstraction layer. Records is built on top of SQLAlchemy.","package":"SQLAlchemy","optional":false},{"reason":"Used for data export functionality to formats like CSV, JSON, XLS, and YAML.","package":"tablib","optional":false},{"reason":"Required for exporting query results directly to a Pandas DataFrame via `rows.export('df')`.","package":"pandas","optional":true},{"reason":"Records requires an appropriate database driver (e.g., psycopg2 for PostgreSQL, PyMySQL for MySQL) for your specific database, which must be installed separately.","package":"database-driver","optional":false}],"imports":[{"symbol":"Database","correct":"import records\ndb = records.Database(...)"}],"quickstart":{"code":"import records\nimport os\n\n# Example using SQLite in-memory or a database URL from an environment variable\n# For other databases, change the connection string, e.g., \n# \"postgres://user:pass@host:port/dbname\"\ndatabase_url = os.environ.get('DATABASE_URL', 'sqlite:///:memory:')\ndb = records.Database(database_url)\n\n# Create a table and insert data (example for SQLite)\nif 'sqlite' in database_url:\n    db.query('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')\n    db.query('INSERT INTO users (name, age) VALUES (:name, :age)', name='Alice', age=30)\n    db.query('INSERT INTO users (name, age) VALUES (:name, :age)', name='Bob', age=24)\n\n# Run a query\nrows = db.query('SELECT * FROM users WHERE age > :min_age', min_age=25)\n\n# Iterate over results\nprint('Users over 25:')\nfor row in rows:\n    print(f\"  Name: {row.name}, Age: {row.age}\")\n\n# Access columns by name or index\nfirst_row = rows.first()\nif first_row:\n    print(f\"First user found: {first_row.name}\")\n\n# Export to CSV (requires tablib)\n# print(rows.export('csv'))\n\n# Export to Pandas DataFrame (requires pandas)\n# df = rows.export('df')\n# print(df.head())\n\n# Close the connection (important for some databases)\ndb.close()","lang":"python","description":"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."},"warnings":[{"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.","message":"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.","severity":"breaking","affected_versions":"0.6.0 and later"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-15T00:00:00.000Z","next_check":"2026-07-14T00:00:00.000Z","problems":[]}