Queries

2.1.1 · active · verified Thu Apr 16

Queries is a BSD licensed opinionated wrapper of the `psycopg2` library for interacting with PostgreSQL. It aims to reduce the complexity of `psycopg2` while adding features like a simplified API, connection pooling, and asynchronous support for Tornado. The library supports Python 2.7+ and 3.4+ and is currently at version 2.1.1.

Common errors

```
ImportError: No module named 'psycopg2'
```
cause The core dependency `psycopg2` (or `psycopg2-binary`) was not installed alongside `queries`.

fix Install the necessary PostgreSQL client: `pip install queries psycopg2-binary` or `pip install queries psycopg2`.
```
psycopg2.OperationalError: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
```
cause The PostgreSQL database server is not running, is not accessible from the client, or the connection URI is incorrect (e.g., wrong host, port, username, or database name).

fix Ensure the PostgreSQL server is running and accessible. Verify the connection URI provided to `queries.Session` is correct (e.g., `postgresql://user:password@host:5432/dbname`). Check firewall rules if connecting remotely.
```
psycopg2.errors.UndefinedTable: relation "your_table_name" does not exist
```
cause Attempting to query a table that has not been created in the connected database schema.

fix Ensure your SQL `CREATE TABLE` statement has been successfully executed before attempting `SELECT`, `INSERT`, `UPDATE`, or `DELETE` operations on that table.

Warnings

breaking Version 2.0.0 removed official support for Python 2.6. Users on Python 2.6 must remain on `queries < 2.0.0` or upgrade their Python version.
Fix: Upgrade to Python 2.7 or Python 3.4+.
gotcha When using `psycopg2-binary` (the easier-to-install dependency), be aware that it includes its own C libraries (like `libpq` and `libssl`). For production environments, it is often recommended to install `psycopg2` from source to ensure it links against system libraries, providing better control over updates and avoiding potential conflicts.
Fix: For production, `pip install queries psycopg2` and ensure necessary system build tools and `libpq-dev` are installed.
gotcha Prior to version 2.0.0, a CPU pegging bug could occur in `TornadoSession` on connection errors due to improper cleanup of the IOLoop. This was fixed in `queries` 2.0.0.
Fix: Upgrade to `queries` 2.0.0 or later to ensure proper `TornadoSession` cleanup.

Install

pip install queries psycopg2-binary Install with binary Psycopg2 (recommended for quick start)
pip install queries psycopg2 Install with Psycopg2 from source (recommended for production)

Imports

Session
```
from queries import Session
```
TornadoSession
```
from queries import TornadoSession
```
Used for asynchronous operations with the Tornado web framework.

Quickstart

This quickstart demonstrates how to connect to a PostgreSQL database using `queries.Session`, create a table, insert data, and retrieve results. It utilizes environment variables for the database URI for flexible configuration.

import os
from queries import Session

# Configure your PostgreSQL connection URI
# Example: "postgresql://user:password@host:port/database_name"
# If omitted, defaults to "postgresql://<current_os_user>@localhost:5432/<current_os_user>"
DB_URI = os.environ.get('POSTGRES_URI', 'postgresql://postgres@localhost:5432/postgres')

def main():
    try:
        with Session(DB_URI) as session:
            # Create a table
            session.query("CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(255))")
            print("Table 'users' ensured.")

            # Insert data
            result = session.query("INSERT INTO users (name) VALUES (%s) RETURNING id", ('Alice',))
            new_id = result.scalar()
            print(f"Inserted user Alice with ID: {new_id}")

            # Query data
            results = session.query("SELECT id, name FROM users")
            print("Current users:")
            for row in results:
                print(f"  ID: {row.id}, Name: {row.name}")

    except Exception as e:
        print(f"An error occurred: {e}")

if __name__ == '__main__':
    main()

view raw JSON →