sqlbuilder 0.7

Lightweight SQL Builder. Contains also Django integration.

SmartSQL - lightweight sql builder.

You can use SmartSQL separatelly, or with Django, or with super-lightweight Autumn ORM.

SQLBuilder integration to Django also allows to use external sqlbuilders, like SQLBuilder from SQLObject or sqlalchemy.sql.

LICENSE:

• License is BSD

Short manual for sqlbuilder.smartsql

table:

• “T.base” stand for “base”,

• “T.base__a” or “T.base.as_(‘a’)” stand for “base AS a”

field:

• “F.id” stand for “id”,

• “F.base__id” or “T.base.id” stand for “base.id”

• “F.base__id__pk” or “F.base__id.as_(‘pk’)” or “T.base.id__pk” or “T.base.id.as_(‘pk’)” stand for “base.id AS pk”

table operator:

• “&” stand for “INNER JOIN”

• “+” stand for “LEFT OUTER JOIN”

• “-” stand for “RIGHT OUTER JOIN”

• “|” stand for “FULL OUTER JOIN”

• “*” stand for “CROSS JOIN”

condition operator:

• “&” stand for “AND”

• “|” stand for “OR”

usage eg:

QS(T.base + T.grade + T.lottery).on(
    (T.base.type == T.grade.item_type) & (T.base.type == 1),
    T.base.type == T.lottery.item_type
).fields(
    T.base.type, T.grade.grade, T.lottery.grade
).where(
    (T.base.name == "name") & (T.base.status == 0) | (T.base.name == None)
).select()

# step by step

t = T.grade
QS(t).select(F.name)

t = (t * T.base).on(T.grade.item_type == T.base.type)
QS(t).select(T.grade.name, T.base.img)

t = (t + T.lottery).on(T.base.type == T.lottery.item_type)
QS(t).select(T.grade.name, T.base.img, T.lottery.price)

w = (T.base.type == 1)
QS(t).where(w).select(T.grade.name, T.base.img, T.lottery.price)

w = w & (T.grade.status == 0)
QS(t).where(w).select(T.grade.name, T.base.img, T.lottery.price)

w = w | (T.lottery.item_type == None)
QS(t).where(w).select(T.grade.name, T.base.img, T.lottery.price)

w = w & (T.base.status == 1)
QS(t).where(w).select(T.grade.name, T.base.img, T.lottery.price)

Django integration.

Simple add “sqlbuilder.django_sqlbuilder” to your INSTALLED_APPS.

For Django model

class Grade(django.db.models.Model):
    # ...
    class Meta:
        db_table = "grade"

• Grade.ss.t (alias for Grade.ss.table) returns T.grade

• Grade.ss.get_fields() returns [T.grade.id, T.grade.title, …]

• Grade.ss.qs returns QS(T.grade).fields(Grade.ss.get_fields())

So,

QS(T.grade).where(T.grade.item_type == 'type1')

is equal to:

Grade.ss.qs.where(Grade.ss.t.item_type == 'type1')

How to execute?

rows = Grade.ss.qs.where(Grade.ss.t.item_type == 'type1').select()
# Also is possible
rows = Grade.objects.raw(*QS(T.grade).where(T.grade.item_type == 'type1').select(Grade.ss.get_fields()))

Paginator

django.db.models.query.RawQuerySet indexing and slicing are not performed at the database level, so it can cause problems with pagination.

For this reason, SQLBuilder fixes this issue.