Overview

A pure python library inspired by the NuGet library dapper.

pydapper is built on top of the database api (dbapi) 2.0 spec to provide more convenient methods for working with databases in python.

Example

import datetime
from dataclasses import dataclass

from pydapper import connect


@dataclass
class Task:
    id: int
    description: str
    due_date: datetime.date
    owner_id: int


with connect() as commands:
    data = commands.query("select * from task limit 1", model=Task)

print(data)
# [Task(id=1, description='Set up a test database', due_date=datetime.date(2021, 12, 31), owner_id=1)]

(This script is complete, it should run "as is")

See the compatibility and deprecation policy for the public API contract governing pydapper 1.x.

What's going on here?

• connect handles creating a connection and returning the pydapper entrypoint for the dsn you pass in
• the query method is executing the sql string and serializing each item in the result set to the model passed (Task)
• the context manager is a proxy to whatever the underlying dbapi for the specified DSN has implemented (see database support docs)

Rationale

Why would I use pydapper?

ORM queries are great...until they're not

Most ORMs in python (think SQLAlchemy, Django, Pony) provide an interface for mapping database results to python objects, but also implement their own api for interacting with the database. When these queries become complex, they are often hard to read and debug. Why not use SQL instead?

Safe from SQL injection

pydapper provides a consistent syntax for declaring query parameters, and guarantees that parameter values are handed to the dbapi as parameters and are never formatted into the SQL string by pydapper. How a given dbapi then binds those values — server-side parameters, or escaping and quoting them client-side — is the driver's own business. On top of that, pydapper refuses SQL that contains more than one statement, raising MultipleStatementsError before the SQL reaches the driver. That check is a lexical guard rail rather than a parser, so it is defense in depth and not a substitute for parameter binding; see One statement per call for what it does and does not catch. Parameter placeholders use the form ?name?, where name starts with an ASCII letter or underscore and then contains only ASCII letters, numbers, or underscores. Placeholder-shaped text inside single-quoted text, double-quoted text, -- line comments, and /* ... */ block comments is ignored; PostgreSQL dollar-quoted strings are not special-cased by the placeholder scanner. Invalid forms such as ??, ? id?, ?first-name?, ?table.column?, and ?1id? are left unchanged.

You want a framework that lets you BYOC (bring your own connection)

Sometimes ORM frameworks abstract too much from you. pydapper allows you to use your own connection object and pass it into the using entrypoint. This gives you complete control over connection management when you don't want pydapper to manage it for you.

You use python dbapi interfaces often and are tired of writing this code

from psycopg2 import connect

with connect("postgresql://pydapper:pydapper@localhost/pydapper") as conn:
    with conn.cursor() as cursor:
        cursor.execute("select * from task")
        headers = [i[0] for i in cursor.description]
        data = cursor.fetchall()

list_data = [dict(zip(headers, row)) for row in data]

Buy me a coffee

If you find this project useful, consider buying me a coffee!