Skip to main content
Help us improve Python packaging – donate today!

SmartSQL - lightweight sql builder.

Project Description

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.

ta = Author.ss
tb = Book.ss
qs = tb.qs

object_list = qs.tables(
    qs.tables() & ta.on(tb.author_id == ta.id)
).where(
    (ta.first_name != 'James') & (ta.last_name != 'Joyce')
)[:10]

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.

Integration of third-party sqlbuilders (deprecated, will be removed).

Integration sqlbuilder.sqlobject to Django

Integration sqlobject to Django:

from sqlobject.sqlbuilder import Select, sqlrepr
from sqlbuilder.models import SQLOBJECT_DIALECT

# Address is subclass of django.db.models.Model
t = Address.so.t
s = Select([t.name, t.state], where=t.name.startswith("sun"))
# or
s = Address.so.qs.newItems(Address.so.get_fields()).filter(t.name.startswith("sun"))
# or simple
s = Address.so.qs.filter(t.name.startswith("sun"))

rows = Address.objects.raw(sqlrepr(s, SQLOBJECT_DIALECT))

Integration sqlalchemy.sql to Django

Example of usage sqlalchemy.sql in Django:

from sqlalchemy.sql import select, table
from sqlbuilder.models import SQLALCHEMY_DIALECT

# User, Profile is subclasses of django.db.models.Model
dialect = User.sa.dialect  # or SQLALCHEMY_DIALECT
u = User.sa.t  # or table('user')
p = Profile.sa.t  # or table('profile')
s = select(['*']).select_from(u.join(p, u.vc.id==p.vc.user_id)).where(p.vc.gender == u'M')
sc = s.compile(dialect=dialect)
rows = User.objects.raw(unicode(sc), sc.params)
for row in rows:
    print row

Release history Release notifications

History Node

0.7.10.18

History Node

0.7.10.17

History Node

0.7.10.16

History Node

0.7.10.15

History Node

0.7.10.14

History Node

0.7.10.13

History Node

0.7.10.12

History Node

0.7.10.11

History Node

0.7.10.10

History Node

0.7.10.9

History Node

0.7.10.8

History Node

0.7.10.6

History Node

0.7.10.5

History Node

0.7.10.4

History Node

0.7.10.3

History Node

0.7.10.2

History Node

0.7.10.1

History Node

0.7.10.0

History Node

0.7.9.53

History Node

0.7.9.52

History Node

0.7.9.51

History Node

0.7.9.50

History Node

0.7.9.49

History Node

0.7.9.48

History Node

0.7.9.47

History Node

0.7.9.46

History Node

0.7.9.45

History Node

0.7.9.44

History Node

0.7.9.43

History Node

0.7.9.42

History Node

0.7.9.41

History Node

0.7.9.40

History Node

0.7.9.39

History Node

0.7.9.38

History Node

0.7.9.37

History Node

0.7.9.36

History Node

0.7.9.35

History Node

0.7.9.34

History Node

0.7.9.33

History Node

0.7.9.32

History Node

0.7.9.31

History Node

0.7.9.30

History Node

0.7.9.29

History Node

0.7.9.28

History Node

0.7.9.27

History Node

0.7.9.26

History Node

0.7.9.25

History Node

0.7.9.24

History Node

0.7.9.23

History Node

0.7.9.22

History Node

0.7.9.21

History Node

0.7.9.20

History Node

0.7.9.19

History Node

0.7.9.18

History Node

0.7.9.17

History Node

0.7.9.16

History Node

0.7.9.15

History Node

0.7.9.14

History Node

0.7.9.13

History Node

0.7.9.12

History Node

0.7.9.11

History Node

0.7.9.10

History Node

0.7.9.9

History Node

0.7.9.8

History Node

0.7.9.7

History Node

0.7.9.6

History Node

0.7.9.5

History Node

0.7.9.4

History Node

0.7.9.3

History Node

0.7.9.2

History Node

0.7.9.1

History Node

0.7.9.0

History Node

0.7.8.0

History Node

0.7.7.7

History Node

0.7.7.6

History Node

0.7.7.5

History Node

0.7.7.4

History Node

0.7.7.3

History Node

0.7.7.2

History Node

0.7.7.1

History Node

0.7.7

History Node

0.7.6

History Node

0.7.5

History Node

0.7.4.3

History Node

0.7.4.2

History Node

0.7.4.1

This version
History Node

0.7.4

History Node

0.7.3

History Node

0.7.2

History Node

0.7.1

History Node

0.7

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Filename, size & hash SHA256 hash help File type Python version Upload date
sqlbuilder-0.7.4.tar.gz (17.4 kB) Copy SHA256 hash SHA256 Source None May 15, 2013

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging CloudAMQP CloudAMQP RabbitMQ AWS AWS Cloud computing Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page