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
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.