sqlfactory

SQLFactory

A zero-dependency SQL builder library!

Convenient classes for building SQL queries in Python. Main purpose of this library is to ease construction of SQL queries in Python code. It is not an ORM (and don't intend to be), just a plain SQL query builder with syntax as similar to actual SQL as possible.

Let's have look at few examples, because examples tell much more than a thousand words:

TL;DR:

sql = Select("column1", "column2", table="books").where(Eq("column1", "value") & In("column2", [1, 2, 3]))

# Will do the args magic for you
sql.execute(cursor)

# OR
cursor.execute(
    str(sql),  # Produces SQL string with value placeholders as %s 
    sql.args   # Returns arguments for the placeholders
)

All the following examples are self-contained. You can copy-paste them into your Python code and they will work.

---

Let's see how to build a SELECT statement like this:

SELECT column1, column2, column3 FROM books WHERE column1 = 'value' AND column2 IN (1, 2, 3);

All the following examples are equivalent and produce the same SQL query:

from sqlfactory import SELECT, Table, Select, And, Eq, In

# The most naive and most explicit approach
Select("column1", "column2", "column3", table="books", where=And(Eq("column1", "value"), In("column2", [1, 2, 3])))

# A little more like a SQL:
SELECT("column1", "column2", "column3", table="books")
    .WHERE(Eq("column1", "value") & In("column2", [1, 2, 3]))

# A little more like a python, but still SQL:
books = Table("books")
SELECT(books.column1, books.column2, books.column3, table=books)
    .WHERE((books.column1 == "value") & In(books.column2, [1, 2, 3]))

---

Inserts are simple, too:

INSERT INTO books (column1, column2, column3) VALUES ('value1', 'value2', 'value3'), ('value4', 'value5', 'value6');

from sqlfactory import Insert, INSERT, Table

Insert.into("books")("column1", "column2", "column3").VALUES(
    ("value1", "value2", "value3"),
    ("value4", "value5", "value6")
)

# Of course, you can use Table object as well
books = Table("books")
INSERT.INTO(books)(books.column1, books.column2, books.column3).VALUES(
    ("value1", "value2", "value3"),
    ("value4", "value5", "value6")
)

# The INTO is not necessary, you can call INSERT constructor directly:
INSERT("books")("column1", "column2", "column3").VALUES(
    ("value1", "value2", "value3"),
    ("value4", "value5", "value6")
)

---

Even updates (and in fact deletes, too):

UPDATE books SET column1 = 'value1', column2 = 'value2' WHERE column3 = 'value3';

from sqlfactory import Update, Table, Eq

Update("books")
    .set("column1", "value1")
    .set("column2", "value2")
    .where(Eq("column3", "value3"))

# Of course, you can use Table object as well
books = Table("books")
Update(books)
    .set(books.column1, "value1")
    .set(books.column2, "value2")
    .where(books.column3 == "value3")

It might seem strange to have so many ways to do the same thing, but it's up to you to choose the one that fits your style the best. The library is designed to be as flexible as possible. You can mix and match different styles in the same codebase, or even in the same query, if you want, as long as it makes sense to you.

By leveraging Python code in constructing SQL, you can use all sorts of Python features to make building SQL an ease. Consider list comprehensions for IN statement, building of complex WHERE clauses, dynamic select columns, call UPDATE only if anything has changed, ... All of that and much more can be done without the hassle of building complex strings together.

Let's have a look at a few more practical examples:

from sqlfactory import Select, In, Direction, Eq, Column, SelectColumn
from dataclasses import dataclass


@dataclass
class Book:
    id: int
    title: str
    author: str
    year: str


def select_books_by_authors(c: DictCursor, authors: list[str], book_properties: set[str] = None, offset: int = 0,
                            limit: int = 10):
    """
    Returns books written by specific authors. Returns list of books paginated by specified offset and limit, ordered
    by book title and author name.
    """

    if book_properties is None:
        book_properties = {"title", "author", "year"}

    property_column = {
        "title": SelectColumn("books.title", alias="title"),
        "author": SelectColumn("authors.name", alias="author"),
        "year": SelectColumn("books.year", alias="year")
    }

    select = (
        # Map dataclass attributes to SQL columns by using mapping table.
        Select(*[property_column[book_property] for book_property in book_properties], table="books")

        # As Eq expects firt argument to be column and second argument to be value, we need to provide hint, that
        # authors.id is a column, not a value.
        .join("authors", on=Eq("books.author", Column("authors.id")))

        # In is intelligent, it will work even when authors list is empty (will produce False, which in turn will
        # return empty result, as no author has been matched).
        .where(In("authors.name", authors))

        # Multiple ORDER BY columns is supported
        .order_by("title", Direction.ASC)
        .order_by("authors.name", Direction.ASC)

        # Limit and offset are supported as well
        .limit(offset, limit)
    )

    select.execute(c)
    return [Book(**row) for row in c.fetchall()]

from sqlfactory import Update, Eq
from dataclasses import dataclass


@dataclass
class BookUpdate:
    id: int
    title: str = None
    author: str = None
    year: int = None


def update_books(c: Cursor, books: list[BookUpdate]):
    """Update multiple books at once. Attributes that has None value won't be modified at all."""
    for book in books:
        update = Update("books", where=Eq("id", book.id))

        if book.title is not None:
            update.set("title", book.title)
        if book.author is not None:
            update.set("author", book.author)
        if book.year is not None:
            update.set("year", book.year)

        # It can even be done as one-liner, but it gets ugly pretty quickly, so it's not recommended for readability:
        # list(map(update.set, [(attr, getattr(book, attr)) for attr in ["title", "author", "year"] if getattr(book, attr) is not None]))

        # Will be executed only if any of the columns should be updated.
        update.execute(c)

Installation

Just install it from PyPi and use:

pip install py-SQLBuilder

Maturity

This library is still very new, but grew from multiple projects where it gradually evolved. So it is already used in production environment successfully. But as always, bugs are expected to be found. On the other hand, the library contains large test suite with 100% code coverage, so it should be quite stable. If you find any bug, please report it.

Implemented SQL features are not complete set of what SQL offers, they are added as-needed. Feel free to open a merge request if you find missing feature that you need.

As we are mainly targeting MySQL / MariaDB, there are some extra features that are on top of SQL standard, that are implemented in the builder. But the library should work with any database that supports standard SQL, when you won't use features that are extensions to the SQL standard.

Contributing

Contributions are always welcome. Just open an issue or a merge request. Keep in mind, that this is not an ORM. So no sessions, no transactions, no lazy loading, no relations, no schema management, no migrations, no database creation.