sqlphile 0.2.0.2

SQL Phile

Table of Contents

• Introduce

• SQLPhile

  • Simple Query

  • Templating For Complex and Highly Customized Query

• Filtering & Excluding

  • Q Object

  • F Object

• Ordering & Grouping

• Offset & Limit

• Returning

• Using Template

  • More About filter()

  • Variablize Your Query

    • Feeding Variable Key-Value Pairs

    • Feeding Variable Key-Value Pairs With Escaped SQL Format

  • D Object

Introduce

SQLPhile is a SQL template engine and Python style SQL generator. It looks like Django ORM but it hasn’t any relationship with Django or ORM.

But it is inspired by Django ORM and iBATIS SQL Maps.

SQLPhile might be useful for keeping clean look of your app script. It can make hide SQL statements for your script by using Python functions or/and writing SQL templates to seperated files.

For Example,

conn = psycopg2.connect (...)
cursor = conn.cursor ()

cursor.execute ("""
  SELECT type, org, count(*) cnt FROM rc_file
  WHERE org = {} AND filename LIKE '%{}'
  GROUP BY {}
  ORDER BY {}
  LIMIT {}
  OFFSET {}
""".format (1, 'OCD', 'type', 'org, cnt DESC', 10, 10))

This codes can be written with SQLPhile:

sp = SQLPhile ()

conn = psycopg2.connect (...)
cursor = conn.cursor ()

q = sp.ops.select ("rc_file", "type", "count(*) cnt")
q.filter (org = 1, name__endswith = 'OCD')
q.group_by ("type").order_by ("org", "-cnt")[10:20]
cursor.execute (q.as_sql ())

Or you can use SQL template file: sqlmaps/file.sql:

<sql name="get_stat">
  SELECT type, org, count(*) cnt FROM rc_file
  WHERE {_filters} {_group_by} {_order_by} {_limit} {_offset}
</sql>

Your app code is,

sp = SQLPhile ("sqlmaps")

conn = psycopg2.connect (...)
cursor = conn.cursor ()

q = sp.file.get_stat.filter (org = 1, name__endswith = 'OCD')
q.group_by ("type").order_by ("org", "-cnt")[10:20]
cursor.execute (q.as_sql ())

SQLPhile

SQLPhile is main class of this package.

from sqlphile import SQLPhile

sp = SQLPhile (dir = None, auto_reload = False, engine = "postgresql")

Once SQLPhile is created, you can reuse it through entire your app.

Simple Query

SQLPhile provide ops object for generic SQL operation.

q = sp.ops.insert (table = "rc_file")
q.data (_id = 1, score = 1.3242, name = "file-A", moddate = datetime.date.today ())
cursor.execute (q.as_sql ())

q = sp.ops.update ("rc_file")
q.data (name = "Jenny", modified = datetime.date.today ())
q.filter (...)

q = sp.ops.select ("rc_file")
q.columns ("id", "name", "create", "modified")
q.filter (...)

q = sp.ops.delete ("rc_file")
q.filter (...)

Also shortcuts are available,

q = sp.ops.insert ("rc_file", _id = 1, score = 1.3242, name = "file-A", moddate = datetime.date.today ())
cursor.execute (q.as_sql ())

q = sp.ops.update ("rc_file", name = "Jenny", modified = datetime.date.today ())
q.filter (...)

q = sp.ops.select ("rc_file", "id", "name", "create", "modified")
q.filter (...)

q = sp.ops.delete ("rc_file")
q.filter (...)

If you want to insert or update to NULL value, give None.

q = sp.ops.insert ("rc_file", score = None)

Templating For Complex and Highly Customized Query

For simple example,

from sqlphile import SQLPhile

sp = SQLPhile ()

q = sp.tempate ("SELECT {columns} FROM rc_file WHERE {_filters} {_order_by}")
q.feed (columns = "id, name").filter (id__eq = 6).order_by ("-id")
q.as_sql () # OR q.render ()
>> SELECT id, name FROM rc_file WHERE id = 6 ORDER BY id DESC

If you create SQL templates in specific directory,

from sqlphile import SQLPhile

sp = SQLPhile (dir = "./sqlmaps", auto_reload = True)

SQLPhile will load all of your templates in ./sqlmaps.

If you are under developing phase, set auto_reload True.

Assume there is a template file named ‘file.sql’:

<sqlmap version="1.0">

<sql name="get_stat">
  SELECT type, org, count(*) cnt FROM rc_file
  WHERE {_filters}
  GROUP BY type
  ORDER BY org, cnt DESC
  {_limit} {_offset}
</sql>

It looks like XML file, BUT IT’S NOT. All tags - <sqlmap>, <sql></sql> should be started at first of line. But SQL of inside is at your own mind but I recommend give some indentation.

Now you can access each sql temnplate via filename without extension and query name attribute:

# filename.query name
q = sp.file.get_stat
q.filter (...).order_by (...)

# or
q = sp.file.get_stat.filter (...).order_by (...)

Note: filename is default.sql, you can ommit filename.

q = sp.get_stat
q.filter (...).order_by (...)

Note 2: SHOULD NOT use starts with “ops” or “template” as template filename.

Filtering & Excluding

First of all,

q.filter (id__eq = 1, name = None)
>> id = 1

Please give your attention that name will be ignored. It makes reducing ‘if’ statements.

Otherwise, filter () is very similar with Django ORM.

q = sp.get_stat

q.filter (__all = True)
>> 1 = 1

q.filter (id__all = True)
>> 1 = 1

q.filter (id__all = False)
>> 1 = 0

q.filter (id = 1)
>> id = 1

q.filter (id__exact = 1)
>> id = 1

q.filter (id__eq = 1)
>> id = 1

q.exclude (id = 1)
>> NOT (id = 1)

q.filter (id__neq = 1)
>> id <> 1

q.filter (id__gte = 1)
>> id >= 1

q.filter (id__lt = 1)
>> id < 1

q.filter (id__between = (10, 20))
>> id BETWEEN 10 AND 20

q.filter (name__contains = "fire")
>> name LIKE '%fire%'

q.exclude (name__contains = "fire")
>> NOT name LIKE '%fire%'

q.filter (name__startswith = "fire")
>> name LIKE 'fire%'

# escaping %
q.filter (name__startswith = "fire%20ice")
>> name LIKE 'fire\%20ice%'

q.filter (name__endswith = "fire")
>> name LIKE '%fire'

q.filter (name__isnull = True)
>> name IS NULL

q.filter (name__isnull = False)
>> name IS NOT NULL

Also you can add multiple filters:

q.filter (name__isnull = False, id = 4)
>> name IS NOT NULL AND id = 4

All filters will be joined with “AND” operator.

Q Object

How can add OR operator?

from sqlphile import Q

q.filter (Q (id = 4) | Q (email__contains = "org"), name__isnull = False)
>> name IS NOT NULL AND (id = 4 OR email LIKE '%org%')

Note that Q objects are first, keywords arguments late. Also you can add seperatly.

q.filter (name__isnull = False)
q.filter (Q (id = 4) | Q (email__contains = "org"))
>> (id = 4 OR email LIKE '%org%') AND name IS NOT NULL

If making excluding filter with Q use tilde(~),

q.filter (Q (id = 4) | ~Q (email__contains = "org"))
>> (id = 4 OR NOT email LIKE '%org%')

F Object

All value will be escaped or automatically add single quotes, but for comparing with other fileds use F.

from sqlphile import F

Q (email = F ("b.email"))
>> email = b.email

Q (email__contains = F ("org"))
>> email LIKE '%' || org || '%'

F can be be used for ops.

q = sp.ops.update (tbl, n_view = F ("n_view + 1"))
q.filter (...)
cursor.execute (q.as_sql ())

Ordering & Grouping

For ordering,

q = sp.ops.select (tbl, "id", "name", "create", "modified")
q.filter (...)
q.order_by ("id", "-modified")
>> ORDER BY id, modified DESC

For grouping,

q = sp.ops.select (tbl, "name", "count(*) cnt")
q.filter (...)
q.group_by ("name")
>> GROUP BY name

q.having ("count(*) > 10")
>> GROUP BY name HAVING count(*) > 10

Offset & Limit

For limiting record set,

q = sp.ops.select (tbl, "id", "name", "create", "modified")
q [:100]
>> LIMIT 100

q [10:30]
>> LIMIT 20 OFFSET 10

Be careful for slicing and limit count.

Returning

For Returning columns after insertinig or updating data,

q = sp.ops.insert (tbl, name = "Hans", created = datetime.date.today ())
q.returning ("id", "name")
>> RETURNING id, name

Using Template

Template is like this,

<sqlmap version="1.0">

<sql name="get_stat">
  SELECT type, org, count(*) cnt FROM rc_file
  WHERE {_filters}
  GROUP BY type
  ORDER BY org, cnt DESC
  {_limit} {offset}
</sql>

<sql name="get_file">
  SELECT * cnt FROM rc_file
  WHERE {_filters}
  {_order_by}
  {_limit}
  {_offset}
</sql>

You just fill variables your query reqiures,

q = sp.file.get_file.filter (id__gte = 1000)[:20]
q.order_by ("-id")

Current reserved variables are,

• _filters

• _group_by

• _order_by

• _limit

• _offset

• _having

• _returning

• _columns: comma joined column list fed by data ()

• _values: comma joined value list fed by data ()

• _pairs: comma joined column=value list fed by data ()

More About filter()

In some cases, filter is tricky.

<sqlmap version="1.0">

<sql name="get_stat">
  SELECT type, org, count(*) cnt FROM rc_file
  WHERE isdeleted is false AND {_filters}
</sql>

Above SQL is only when valid {_filters} exists, but filter doesn’t be provided all the time. You can write like this:

q = sp.file.get_file.filter (__all = True, id__gte = None)
>> WHERE isdeleted is false AND 1 = 1

q = sp.file.get_file.filter (__all = True, id__gte = 1)
>> WHERE isdeleted is false AND 1 = 1 AND id >= 1

Variablize Your Query

You can add variable on your sql by feed() and data() and both can be called multiple times.

Feeding Variable Key-Value Pairs

<sql name="get_file">
  SELECT {cols} FROM {tbl}
  WHERE {_filters}
</sql>

Now feed keywords args with feed ():

q = sp.file.get_file
q.feed (cols = "id, name, created", tbl = "rc_file")
q.filter (id__gte = 1000)

Here’s some useful functions for query feeding.

from sqlphile import IN, B

      B (1, 6)
      >> BETWEEN 1 AND 6

      IN (1,3,4,5)
      >> IN (1,3,4,5)

      IN ("red", "blue")
      >> IN ('red', 'blue')

Also you can feed filter.

<sql name="get_file">
  SELECT * FROM rc_file
  WHERE {id} AND {name} AND create BETWEEN {created}
</sql>

q.feed (id = Q (id__in = [1,2,3,4,5]))
>> id IN (1,2,3,4,5)

q.feed (id = Q (id__in = [1,2,3,4,5]), name = "Hans")
>> id IN (1,2,3,4,5) AND name = 'Hans'

q.feed (id = Q (id__in = [1,2,3,4,5]), name = Q (name = None), created = B (1, 4))
# name is ignored by 1 = 1
>> id IN (1,2,3,4,5) AND 1 = 1

Actually, feed () can be omitable,

# like instance constructor
q = sp.file.get_file (cols = "id, name, created", tbl = "rc_file")
q.filter (id__gte = 1000)

Feeding Variable Key-Value Pairs With Escaped SQL Format

In contrast with feed(), data () will escape values for fitting SQL. You needn’t care about sing quotes, escaping or type casting on date time field.

<sql name="get_file">
  UPDATE rc_profile
  SET birth_year = {birth_year}
  WHERE id IN (
    SELECT id FROM rc_member
    WHERE name = {name}
  );
  UPDATE rc_stat SET count = count + 1
  WHERE birth_year = {birth_year};
</sql>

q = sp.file.get_file.data (name = "Hans Roh", birth_year = 2000)

It is useful for long long SQL and variables are repeated over and over in SQL.

data () also creates 3 variables automatically for inserting and updating purpose,

• _pairs

• _columns

• _values

<sql name="update_profile">
  UPDATE rc_profile SET {_pairs} WHERE {_filters};
  INSERT INTO rc_profile ({_columns}) VALUES ({_values});
</sql>

q = sp.update_profile.data (name = "Hans Roh", birth_year = 2000)

D Object

D object convert dictionary into SQL column and value format and can feed them into SQL template.

from sqlphile import D

d = D (name = "Hans", id = 1, email = None)
d.values
>> 'Hans', 1, NULL

d.columns
>> name, id, email

d.pairs
>> name = 'Hans', id = 1, email = NULL

And you can feed to template.

<sql name="get_file">
  INSERT ({_columns}, {additional_columns})
  VALUES ({_valuess}, {additional_values})
  {_returning};
</sql>

In app,

q = sp.file.get_file.data (area = "730", additional = D (name = 'Hans', id = 1))
q.returning ("id")
cursor.execute (q.as_sql ())