==========
SQLPhile
==========
.. contents:: Table of Contents
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,
.. code:: python
conn = psycopg2.connect (...)
cursor = conn.cursor ()
cursor.execute ("""
SELECT type, org, count(*) cnt FROM tbl_file
WHERE org = {} AND filename LIKE '%{}'
GROUP BY {}
ORDER BY {}
LIMIT {}
OFFSET {}
""".format (1, 'OCD', 'type', 'org, cnt DESC', 10, 10))
cursor.close ()
conn.close ()
This codes can be written with SQLPhile:
.. code:: python
import sqlphile as sp
with sp.postgres ("dbname", "user", "password", "server") as db:
rows = (db.select ("tbl_file").get ("type", "count(*) cnt")
.filter (org = 1, name__endswith = 'OCD')
.group_by ("type").order_by ("org", "-cnt")[10:20]
.execute ().fetchall ())
Or you can use SQL template file: sqlmaps/file.sql:
.. code:: html
<sql name="get_stat">
SELECT type, org, count(*) cnt FROM tbl_file
WHERE {this.filters} {this.group_by} {this.order_by} {this.limit} {this.offset}
</sql>
Your app code is,
.. code:: python
with sp.sqlite3 ("dbname.db3", dir = "./sqlmap") as db:
rows = (db.file.get_stat.filter (org = 1, name__endswith = 'OCD')
.group_by ("type").order_by ("org", "-cnt")[10:20]
.execute ().fetchall ())
Simple Query
--------------
SQLPhile provide select(), update(), insert() and delete() for generic SQL operation.
.. code:: python
import sqlphile as sp
with sp.sqlite3 (r"sqlite3.db3") as db:
q = (db.insert ("tbl_file")
.set (_id = 1, score = 1.3242, name = "file-A", moddate = datetime.date.today ())
.execute ())
q = (db.update ("tbl_file")
.set (name = "Jenny", modified = datetime.date.today ())
.filter (...)
.execute ())
q = (db.delete ("tbl_file")
.filter (...))
q = (db.select ("tbl_file")
.get ("id", "name", "create", "modified")
.filter (...))
for row in q.execute ().fetchall ():
...
If you want to insert or update to NULL value, give None.
.. code:: python
q = db.insert ("tbl_file", score = None)
aggregate () will drop previous get ().
.. code:: python
q = (db.select ("tbl_file")
.get ("id", "name", "create", "modified")
.filter (...))
q.aggregate ('count (id) as c')
>> SELECT count (id) as c FROM tbl_file WHEHE ...
Filtering & Excluding
======================
First of all,
.. code:: python
q.filter (id__eq = 1, name = None)
>> id = 1
q.exclude (id__eq = 1, name = None)
>> NOT (id = 1)
Please give your attention that *name* will be ignored. It makes reducing 'if' statements.
.. code:: python
def (a = None, b = None):
q.filter (a__eq = a, b__contains = b)
if a or b is None, it will be simply ignored, and you can keep simple and consistent statement.
Otherwise, filter () is very similar with Django ORM.
.. code:: python
q = sp.get_stat
q.all ()
>> 1 = 1
q.filter (id = 1)
>> id = 1
q.filter ("id = 1")
>> id = 1
q.filter (id = 1, user__in = ["hansroh", "janedoe"])
>> id = 1 AND user in ("hansroh", "janedoe")
q.filter ("a.id = 1", user__in = ["hansroh", "janedoe"])
>> a.id = 1 AND user in ("hansroh", "janedoe")
q.filter (tbl__name__startswith = "Hans%20Roh"))
>> tbl.name LIKE 'Hans\\%20Roh%'
q.filter (user__in = ["hansroh", "janedoe"])
q.exclude (id__between = (100, 500), deleted = True)
>> user in ("hansroh", "janedoe") AND NOT (id BETWEEN 100 AND 500 AND deleted = true)
q.filter (t1__id = 1)
>> t1.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 (t1__id__neq = 1)
>> t1.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
# PostgrSQL Only
q.filter (name__regex = "^fires?")
>> name ~ '^fires?'
q.filter (tbl__data___name = "Hans Roh")
>> tbl.data->>'name' = 'Hans Roh'
q.filter (tbl__data___age = 20)
>> CAST (tbl.data->>'age' AS int) = 20
q.filter (tbl__data___person___age = 20)
>> CAST (tbl.data->'person'->>'age' AS int) = 20
q.filter (tbl__data___persons__2___age = 20)
>> CAST (tbl.data#>'{persons, 3}'->>'age' AS int) = 20
Also you can add multiple filters:
.. code:: python
q.filter (name__isnull = False, id = 4)
>> name IS NOT NULL AND id = 4
q.filter ("name IS NOT NULL", id = 4)
>> name IS NOT NULL AND id = 4
All filters will be joined with "AND" operator.
Q Object
----------
.. code:: python
f = Q (a__gt = 1)
f = f & Q (b__gt = 1)
>> (a > 1 AND b > 1)
q.filter (f, c__gt 1)
>> (a > 1 AND b > 1) AND c > 1
q.filter ("d > 1", f, c__gt = 1)
>> d > 1 AND (a > 1 AND b > 1) AND c > 1
How can add OR operator?
.. code:: python
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.
.. code:: python
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(*~*),
.. code:: python
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*.
.. code:: python
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 generic operation methods.
.. code:: python
q = (db.update (tbl, n_view = F ("n_view + 1"))
.filter (...))
cursor.execute (q.as_sql ())
Ordering & Grouping
====================
For ordering,
.. code:: python
q = (db.select (tbl).get ("id", "name", "create", "modified")
.filter (...)
.order_by ("id", "-modified"))
>> ORDER BY id, modified DESC
For grouping,
.. code:: python
q = (db.select (tbl).get ("name", "count(*) cnt")
.filter (...)
.group_by ("name"))
>> ... GROUP BY name
q.having ("count(*) > 10")
>> GROUP BY name HAVING count(*) > 10
Offset & Limit
================
For limiting record set,
.. code:: python
q = db.select (tbl).get ("id", "name", "create", "modified")
q [:100]
>> LIMIT 100
q [10:30]
>> LIMIT 20 OFFSET 10
Be careful for slicing and limit count.
Limit 0
---------------------
.limit (0) can be useful for avoiding excution entire query without 'if' statement with sqlphile.db2 or pg2 module.
Returning
============
For Returning columns after insertinig or updating data,
.. code:: python
q = db.insert (tbl).set (name = "Hans", created = datetime.date.today ())
q.returning ("id", "name")
>> RETURNING id, name
Update On Conflict / Upsert
====================================
.. code:: python
q = db.insert (tbl).set (name = "Hans", created = datetime.date.today ())
q.upflict ('name', updated = datetime.date.today ())
>> INSERT INTO tbl (name, created)
VALUES ('Hans', '2020-12-24')
ON CONFLICT (name) DO UPDATE
SET updated = '2020-12-25'
To upserting
.. code:: python
q = db.insert (tbl).set (name = "Hans", created = datetime.date.today ())
q.upflict ('name')
>> INSERT INTO tbl (name, created)
VALUES ('Hans', '2020-12-24')
ON CONFLICT (name) DO NOTHING
*Note*: `name` field must have uniqie index
CTE (Common Table Expression)
============================================
*New in version 0.6*
.. code:: python
cte = db.insert ("human").set (name = "Hans", division = "HR").returning ("*"))
q = (db.insert ("reqs").
.with_ ("inserted", cte)
.set (tbl_id = F ("inserted.id"), req = "vaccation"))
>> WITH inserted AS (INSERT INTO human (name, division) VALUES ('Hans', 'HR') RETURNING *)
INSERT INTO reqs (tbl_id, req) VALUES (inserted.id, 'vaccation')
*New in version 0.6.4*
Starting with\_ is alos possible, that is more clare than above, I think.
.. code:: python
q = db.with_ ("inserted", db.insert ("human").set (name = "Hans", division = "HR").returning ("*")))
q = (db.insert ("reqs").
.set (tbl_id = F ("inserted.id"), req = "vaccation"))
>> WITH inserted AS (INSERT INTO human (name, division) VALUES ('Hans', 'HR') RETURNING *)
INSERT INTO reqs (tbl_id, req) VALUES (inserted.id, 'vaccation')
Multiple CTEs are also possible,
.. code:: python
q = (db.insert ("reqs")
.with\_ ("inserted", cte)
.with\_ ("inserted2", cte)
.set (tbl_id = F ("inserted.id"), req = "vaccation"))
>> WITH inserted AS (INSERT INTO human (name, division) VALUES ('Hans', 'HR') RETURNING *),
inserted2 AS (INSERT INTO human (name, division) VALUES ('Hans', 'HR') RETURNING *)
INSERT INTO reqs (tbl_id, req) VALUES (inserted.id, 'vaccation')
.. code:: python
from sqlphile import D
data = D (a = 1, b = "c")
sql = (
sqlmaps.select ("tbl").with_ ("temp", cte).join ("temp", "true")
)
>> WITH temp (a, b) AS (values (1, 'c'))
SELECT * FROM tbl
INNER JOIN temp ON true
Using Constant
=====================
.. code:: python
from sqlphile import const
sql = (
sqlmaps.select ("temp").const ("a", 1).const ('b', 'c').filter (a = const ("a"))
)
>> WITH a AS (values (1)), b AS (values ('c'))
SELECT * FROM temp
WHERE a = (table a)
Joining
============
For joining tables,
.. code:: python
q = db.select ("tbl_file t1").join ("names t2", "t1.name = t2.name")
q.filter (id__gt > 100)
q.get ("score", "t2.name")
>> SELECT score, t2.name FROM tbl_file AS t1
INNER JOIN names AS t2 ON t1.name = t2.name
WHERE id > 100
For joining with sub query,
.. code:: python
subq = db.select ("tbl_project").get ("name")
q = db.select ("tbl_file t1").join (subq, "t2", "t1.name = t2.name")
q.filter (id__gt = 100)
q.get ("score", "t2.name")
>> SELECT score, t2.name FROM tbl_file AS t1
INNER JOIN (SELECT * FROM tbl_project) AS t2 ON t1.name = t2.name
WHERE id > 100
You can use 'from\_()' for update query,
.. code:: python
q = db.update ("tbl_file", "t1")
q.from_ ("tbl_record t2", "t1.id = t2.id")
q.set (score = F ("t2.score"))
q.filter (t1__id = 1)
>> UPDATE tbl_file AS t1 SET score = t2.score
FROM tbl_record AS t2 ON t1.id = t2.id
WHERE t1.id = 1
Also available,
- left_join ()
- right_join ()
- full_join ()
Union, Intersect, Except
=====================================
.. code:: python
q1 = db.select ("tbl_project").get ("name")
q2 = db.select ("tbl_file t1").get ("name")
q1.union (q2)
Also union_all, intersect and except\_ are available.
INSERT INTO ... SELECT ...
=====================================
.. code:: python
sql = (
db.select ("tbl1")
.get ("name")
.into ("tbl2", "name")
)
>> INSERT INTO tbl2 (name) SELECT name from tbl1
Transaction
====================
.. code:: python
q = (db.tran ()
.update ("tbl_file")
.set (score = 5.0).filter (id = 6)
.execute (True))
>>> BEGIN TRANSACTION;
UPDATE tbl_file SET score = 5.0 WHERE id = 5;
COMMIT;
Multiple Statement
================================
.. code:: python
sql = db.insert ("temp").set (id = 2, comment = 'Comment')
sql.append (db.update ("temp2").set (comment_count = F ('comment_count + 1')).filter (id = 2))
>>> INSERT INTO temp (id, comment) VALUES (2, 'Comment');
UPDATE temp2 SET comment_count = comment_count + 1 WHERE id = 2
Branching
================
You can branch your query branch() method.
.. code:: python
stem = db.select ("tbl_file").filter (...)
q1 = stem.clone ().get ("id, name, create, modified").order_by (-id)
q2 = stem.clone ().get ("counte (*) as cnt")
Using Template
=================
For simple example,
.. code:: python
with sp.sqlite3 (r"sqlite3.db3") as db:
q = (db.tempate ("SELECT {columns} FROM tbl_file WHERE {this.filters} {this.order_by}")
.feed (columns = "id, name")
.filter (id__eq = 6)
.order_by ("-id"))
q.as_sql () # OR q.render ()
>> SELECT id, name FROM tbl_file WHERE id = 6 ORDER BY id DESC
If you create SQL templates in specific directory,
.. code:: python
with sp.sqlite3 ("sqlite3.db3", dir = "./sqlmaps", auto_reload = True) as db:
...
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':
.. code:: html
<sqlmap version="1.0">
<sql name="get_stat">
SELECT type, org, count(*) cnt FROM tbl_file
WHERE {this.filters}
GROUP BY type
ORDER BY org, cnt DESC
{this.limit} {this.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:
.. code:: python
# filename.query name
q = db.file.get_stat
q.filter (...).order_by (...)
# or
q = db.file.get_stat.filter (...).order_by (...)
Note: filename is *default.sql*, you can ommit filename.
.. code:: python
q = db.get_stat
q.filter (...).order_by (...)
Note 2: SHOULD NOT use starts with "select", "update", "insert", "delete" or "template" as template filename.
For another example template is like this,
.. code:: html
<sqlmap version="1.0">
<sql name="get_stat">
SELECT type, org, count(*) cnt FROM tbl_file
WHERE {this.filters}
GROUP BY type
ORDER BY org, cnt DESC
{this.limit} {this.offset}
</sql>
<sql name="get_file">
SELECT * cnt FROM tbl_file
WHERE {this.filters}
{this._order_by}
{this.limit}
{this.offset}
</sql>
You just fill variables your query reqiures,
.. code:: python
q = db.file.get_file.filter (id__gte = 1000)[:20]
q.order_by ("-id")
Current reserved variables are,
- this.filters
- this.group_by
- this.order_by
- this.limit
- this.offset
- this.having
- this.returning
Adding Data
--------------
data () also creates 3 variables automatically for inserting and updating purpose,
- this.pairs
- this.columns
- this.values
.. code:: html
<sql name="update_profile">
UPDATE tbl_profile SET {this.pairs} WHERE {this.filters};
INSERT INTO tbl_profile ({this.columns}) VALUES ({this.values});
</sql>
.. code:: python
q = db.update_profile
q.set (name = "Hans Roh", birth_year = 2000)
q.set (email = None, age = 20)
Will be rendered:
.. code:: python
{this.columns} : name, birth_year, email, age
{this.values} : 'Hans Roh', 2000, NULL, 20
{this.pairs} : name='Hans Roh', birth_year=2000, email=NULL, age=20
D Object
```````````
D object convert dictionary into SQL column and value format and can feed them into SQL template.
.. code:: python
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 with prefix.
.. code:: html
<sql name="get_file">
INSERT ({this.columns}, {additional.columns})
VALUES ({this.values}, {additional.values})
{this.returning};
</sql>
In app,
.. code:: python
q = db.file.get_file.set (area = "730", additional = D (name = 'Hans', id = 1))
q.returning ("id")
q.execute ()
In a conclusion, it will be created 3 variables automatically,
- additional.pairs
- additional.columns
- additional.values
More About filter()
---------------------
In some cases, filter is tricky.
.. code:: html
<sqlmap version="1.0">
<sql name="get_stat">
SELECT type, org, count(*) cnt FROM tbl_file
WHERE isdeleted is false AND {this.filters}
</sql>
Above SQL is only valid when {this.filters} exists, but what if filter doesn't be provided all the time? You can write like this:
.. code:: python
q = db.file.get_file.filter (__all = True, id__gte = None)
>> WHERE isdeleted is false AND 1 = 1
q = db.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
``````````````````````````````````````
.. code:: html
<sql name="get_file">
SELECT {cols} FROM {tbl}
WHERE {this.filters}
</sql>
Now feed keywords args with feed ():
.. code:: python
q = db.file.get_file
q.feed (cols = "id, name, created", tbl = "tbl_file")
q.filter (id__gte = 1000)
Also you can feed filter.
.. code:: html
<sql name="get_file">
SELECT * FROM tbl_file
WHERE {id} AND {name} AND create BETWEEN {created}
</sql>
.. code:: python
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,
.. code:: python
# like instance constructor
q = db.file.get_file (cols = "id, name, created", tbl = "tbl_file")
q.filter (id__gte = 1000)
Actually this template formating use python format function,
.. code:: html
<sql name="get_file">
SELECT * FROM tbl_file
WHERE id = '{id:010d}' AND name = '{name:10s}'
</sql>
q.feed (id = 10000, name = 'hansroh')
>> WHERE id = '0000010000' AND name = 'hansroh '
Feeding V Object
````````````````````
If V will escape values for fitting SQL. You needn't care about sing quotes, escaping or type casting on date time field.
.. code:: python
V (1)
>> 1
V (__eq = 1)
>> 1
V (datetime.date.today ())
>> TIMESTAMP '20171224 00:00:00'
V ("Hans")
>> 'Hans'
V (None)
>> NULL
V ()
>> NULL
V (__eq = "Hans")
>> 'Hans'
V (__contains = "Hans")
>> '%Hans%'
V (__in = [1,2])
>> (1,2)
V (__between = [1,2])
>> 1 AND 2
For example,
.. code:: html
<sql name="get_file">
UPDATE tbl_profile
SET {this.pairs}
WHERE id IN (
SELECT id FROM tbl_member
WHERE name = {name}
);
UPDATE tbl_stat SET count = count + 1
WHERE birth_year IN {birth_year};
</sql>
.. code:: python
q = db.file.get_file.feed (
email = V ("hansroh@email.com"),
birth_year = V (__in = (2000, 2002, 2004))
)
q.set (name = "Hans Roh")
Using SQLPhile as SQL Query Generator
=========================================
If you need just SQL statement, you can use SQLPhile as template engine.
.. code:: python
import sqlphle as sp
template = sp.Template ("postgresql")
q = template.select ("tbl_file").get ("score", "t2.name")
q.as_sql () == str (q)
# specify template file
template = sp.Template ("postgresql", "./sqlmaps/test.sql")
q = template.house (tbl = 'tbl_file')
# specify template directory
template = sp.Template ("postgresql", "./sqlmaps")
q = template.test.house (tbl = 'tbl_file')
Migrating to version 0.5
===================================
In version 0.5 template format string has been changed. most of them are compatable but some aren't.
If you used D (...), look carefully and SHOUD rewrite.
.. code:: html
# default.sql
<sql name="get_file">
UPDATE tbl_profile
SET {mydata_pairs}
WHERE {_filters}
</sql>
At your code,
.. code:: python
template = sp.Template ("postgresql", "./sqlmaps")
q = template.get_file (mydata = D (name = 'Hans Roh'))
In version 0.5, you should change **{mydata_pairs}** into **{mydata.pairs}**.
Also _something has been deprecated, I recommend changes.
- {_filters} => {this.filter}
- {_order_by} => {this.oreder_by}
- {_group_by} => {this.group_by}
- {_having} => {this.having}
- {_returning} => {this.returning}
- {_columns} => {this.columns}
- {_values} => {this.values}
- {_pairs} => {this.pairs}
Change Logs
=============
- 0.8
- deprecated Q (k, v), use Q (k = v) form
- 0.6
- add x_join_related ()
- add fetch1 () and fetchn ()
- add excommit (), exfetch () and exone ()
- add haskey and haskeyin operators for JSON object query
- add JSON query
- add .aggregate ()
- .branch () => .clone ()
- .data () => .set ()
- add upflict (field_name, \*\*data)
- with\_ can be for initiating
- add const
- add multiple statement using .append ()
- with\_ for CTE
- 0.5
- add .with\_ (sql, alias) for common table expression
- add .intersect (sql) and .except\_ (sql)
- change templating format style: this not compatable with version 0.4, see upgrade section
- 0.4.9
- add .union () abd union_all ()
- 0.4
- add .clone ()
- add __regex
- fix exclude
- fix ~Q
- add fetchxxx to SQL class
- fetchxxx (as_dict = True) returns AttrDict
- add sqlphile.Template
- 0.3.5
- add sp.sqlite3 and sp.postgres (== prevous sp.db3.open and qlphile.pg2.open)
- 0.3.4
- extend IN query
- enalbe multiple keyword argument for Q
- 0.3.3
- add db3 and pg2
- 0.3.1
- fix datetime type
- add boolean type casting
Raw data
{
"_id": null,
"home_page": "https://gitlab.com/skitai/sqlphile",
"name": "sqlphile",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "Hans Roh",
"author_email": "hansroh@gmail.com",
"download_url": "https://pypi.python.org/pypi/sqlphile",
"platform": "posix",
"description": "==========\nSQLPhile\n==========\n\n.. contents:: Table of Contents\n\nIntroduce\n=============\n\nSQLPhile 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.\n\nBut it is inspired by Django ORM and iBATIS SQL Maps.\n\nSQLPhile 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.\n\nFor Example,\n\n.. code:: python\n\n conn = psycopg2.connect (...)\n cursor = conn.cursor ()\n cursor.execute (\"\"\"\n SELECT type, org, count(*) cnt FROM tbl_file\n WHERE org = {} AND filename LIKE '%{}'\n GROUP BY {}\n ORDER BY {}\n LIMIT {}\n OFFSET {}\n \"\"\".format (1, 'OCD', 'type', 'org, cnt DESC', 10, 10))\n cursor.close ()\n conn.close ()\n\nThis codes can be written with SQLPhile:\n\n.. code:: python\n\n import sqlphile as sp\n\n with sp.postgres (\"dbname\", \"user\", \"password\", \"server\") as db:\n rows = (db.select (\"tbl_file\").get (\"type\", \"count(*) cnt\")\n .filter (org = 1, name__endswith = 'OCD')\n .group_by (\"type\").order_by (\"org\", \"-cnt\")[10:20]\n .execute ().fetchall ())\n\nOr you can use SQL template file: sqlmaps/file.sql:\n\n.. code:: html\n\n <sql name=\"get_stat\">\n SELECT type, org, count(*) cnt FROM tbl_file\n WHERE {this.filters} {this.group_by} {this.order_by} {this.limit} {this.offset}\n </sql>\n\nYour app code is,\n\n.. code:: python\n\n with sp.sqlite3 (\"dbname.db3\", dir = \"./sqlmap\") as db:\n rows = (db.file.get_stat.filter (org = 1, name__endswith = 'OCD')\n .group_by (\"type\").order_by (\"org\", \"-cnt\")[10:20]\n .execute ().fetchall ())\n\n\nSimple Query\n--------------\n\nSQLPhile provide select(), update(), insert() and delete() for generic SQL operation.\n\n.. code:: python\n\n import sqlphile as sp\n\n with sp.sqlite3 (r\"sqlite3.db3\") as db:\n q = (db.insert (\"tbl_file\")\n .set (_id = 1, score = 1.3242, name = \"file-A\", moddate = datetime.date.today ())\n .execute ())\n\n q = (db.update (\"tbl_file\")\n .set (name = \"Jenny\", modified = datetime.date.today ())\n .filter (...)\n .execute ())\n\n q = (db.delete (\"tbl_file\")\n .filter (...))\n\n q = (db.select (\"tbl_file\")\n .get (\"id\", \"name\", \"create\", \"modified\")\n .filter (...))\n\n for row in q.execute ().fetchall ():\n ...\n\nIf you want to insert or update to NULL value, give None.\n\n.. code:: python\n\n q = db.insert (\"tbl_file\", score = None)\n\naggregate () will drop previous get ().\n\n.. code:: python\n\n q = (db.select (\"tbl_file\")\n .get (\"id\", \"name\", \"create\", \"modified\")\n .filter (...))\n q.aggregate ('count (id) as c')\n >> SELECT count (id) as c FROM tbl_file WHEHE ...\n\n\nFiltering & Excluding\n======================\n\nFirst of all,\n\n.. code:: python\n\n q.filter (id__eq = 1, name = None)\n >> id = 1\n\n q.exclude (id__eq = 1, name = None)\n >> NOT (id = 1)\n\nPlease give your attention that *name* will be ignored. It makes reducing 'if' statements.\n\n.. code:: python\n\n def (a = None, b = None):\n q.filter (a__eq = a, b__contains = b)\n\nif a or b is None, it will be simply ignored, and you can keep simple and consistent statement.\n\n\nOtherwise, filter () is very similar with Django ORM.\n\n.. code:: python\n\n q = sp.get_stat\n\n q.all ()\n >> 1 = 1\n\n q.filter (id = 1)\n >> id = 1\n\n q.filter (\"id = 1\")\n >> id = 1\n\n q.filter (id = 1, user__in = [\"hansroh\", \"janedoe\"])\n >> id = 1 AND user in (\"hansroh\", \"janedoe\")\n\n q.filter (\"a.id = 1\", user__in = [\"hansroh\", \"janedoe\"])\n >> a.id = 1 AND user in (\"hansroh\", \"janedoe\")\n\n q.filter (tbl__name__startswith = \"Hans%20Roh\"))\n >> tbl.name LIKE 'Hans\\\\%20Roh%'\n\n q.filter (user__in = [\"hansroh\", \"janedoe\"])\n q.exclude (id__between = (100, 500), deleted = True)\n >> user in (\"hansroh\", \"janedoe\") AND NOT (id BETWEEN 100 AND 500 AND deleted = true)\n\n q.filter (t1__id = 1)\n >> t1.id = 1\n\n q.filter (id__exact = 1)\n >> id = 1\n\n q.filter (id__eq = 1)\n >> id = 1\n\n q.exclude (id = 1)\n >> NOT (id = 1)\n\n q.filter (id__neq = 1)\n >> id <> 1\n\n q.filter (t1__id__neq = 1)\n >> t1.id <> 1\n\n q.filter (id__gte = 1)\n >> id >= 1\n\n q.filter (id__lt = 1)\n >> id < 1\n\n q.filter (id__between = (10, 20))\n >> id BETWEEN 10 AND 20\n\n q.filter (name__contains = \"fire\")\n >> name LIKE '%fire%'\n\n q.exclude (name__contains = \"fire\")\n >> NOT name LIKE '%fire%'\n\n q.filter (name__startswith = \"fire\")\n >> name LIKE 'fire%'\n\n # escaping %\n q.filter (name__startswith = \"fire%20ice\")\n >> name LIKE 'fire\\%20ice%'\n\n q.filter (name__endswith = \"fire\")\n >> name LIKE '%fire'\n\n q.filter (name__isnull = True)\n >> name IS NULL\n\n q.filter (name__isnull = False)\n >> name IS NOT NULL\n\n # PostgrSQL Only\n q.filter (name__regex = \"^fires?\")\n >> name ~ '^fires?'\n\n\tq.filter (tbl__data___name = \"Hans Roh\")\n >> tbl.data->>'name' = 'Hans Roh'\n\n\tq.filter (tbl__data___age = 20)\n >> CAST (tbl.data->>'age' AS int) = 20\n\n q.filter (tbl__data___person___age = 20)\n >> CAST (tbl.data->'person'->>'age' AS int) = 20\n\n q.filter (tbl__data___persons__2___age = 20)\n >> CAST (tbl.data#>'{persons, 3}'->>'age' AS int) = 20\n\n\nAlso you can add multiple filters:\n\n.. code:: python\n\n q.filter (name__isnull = False, id = 4)\n >> name IS NOT NULL AND id = 4\n\n q.filter (\"name IS NOT NULL\", id = 4)\n >> name IS NOT NULL AND id = 4\n\n\nAll filters will be joined with \"AND\" operator.\n\nQ Object\n----------\n\n.. code:: python\n\n f = Q (a__gt = 1)\n f = f & Q (b__gt = 1)\n >> (a > 1 AND b > 1)\n\n q.filter (f, c__gt 1)\n >> (a > 1 AND b > 1) AND c > 1\n\n q.filter (\"d > 1\", f, c__gt = 1)\n >> d > 1 AND (a > 1 AND b > 1) AND c > 1\n\nHow can add OR operator?\n\n.. code:: python\n\n from sqlphile import Q\n\n q.filter (Q (id = 4) | Q (email__contains = \"org\"), name__isnull = False)\n >> name IS NOT NULL AND (id = 4 OR email LIKE '%org%')\n\nNote that Q objects are first, keywords arguments late. Also you can add seperatly.\n\n.. code:: python\n\n q.filter (name__isnull = False)\n q.filter (Q (id = 4) | Q (email__contains = \"org\"))\n >> (id = 4 OR email LIKE '%org%') AND name IS NOT NULL\n\nIf making excluding filter with Q use tilde(*~*),\n\n.. code:: python\n\n q.filter (Q (id = 4) | ~Q (email__contains = \"org\"))\n >> (id = 4 OR NOT email LIKE '%org%')\n\n\nF Object\n----------\n\nAll value will be escaped or automatically add single quotes, but for comparing with other fileds use *F*.\n\n.. code:: python\n\n from sqlphile import F\n\n Q (email = F (\"b.email\"))\n >> email = b.email\n\n Q (email__contains = F (\"org\"))\n >> email LIKE '%' || org || '%'\n\nF can be be used for generic operation methods.\n\n.. code:: python\n\n q = (db.update (tbl, n_view = F (\"n_view + 1\"))\n .filter (...))\n cursor.execute (q.as_sql ())\n\nOrdering & Grouping\n====================\n\nFor ordering,\n\n.. code:: python\n\n q = (db.select (tbl).get (\"id\", \"name\", \"create\", \"modified\")\n .filter (...)\n .order_by (\"id\", \"-modified\"))\n >> ORDER BY id, modified DESC\n\nFor grouping,\n\n.. code:: python\n\n q = (db.select (tbl).get (\"name\", \"count(*) cnt\")\n .filter (...)\n .group_by (\"name\"))\n >> ... GROUP BY name\n\n q.having (\"count(*) > 10\")\n >> GROUP BY name HAVING count(*) > 10\n\nOffset & Limit\n================\n\nFor limiting record set,\n\n.. code:: python\n\n q = db.select (tbl).get (\"id\", \"name\", \"create\", \"modified\")\n q [:100]\n >> LIMIT 100\n\n q [10:30]\n >> LIMIT 20 OFFSET 10\n\nBe careful for slicing and limit count.\n\n\nLimit 0\n---------------------\n\n.limit (0) can be useful for avoiding excution entire query without 'if' statement with sqlphile.db2 or pg2 module.\n\n\nReturning\n============\n\nFor Returning columns after insertinig or updating data,\n\n.. code:: python\n\n q = db.insert (tbl).set (name = \"Hans\", created = datetime.date.today ())\n q.returning (\"id\", \"name\")\n >> RETURNING id, name\n\n\nUpdate On Conflict / Upsert\n====================================\n\n.. code:: python\n\n q = db.insert (tbl).set (name = \"Hans\", created = datetime.date.today ())\n q.upflict ('name', updated = datetime.date.today ())\n >> INSERT INTO tbl (name, created)\n VALUES ('Hans', '2020-12-24')\n ON CONFLICT (name) DO UPDATE\n SET updated = '2020-12-25'\n\nTo upserting\n\n.. code:: python\n\n q = db.insert (tbl).set (name = \"Hans\", created = datetime.date.today ())\n q.upflict ('name')\n >> INSERT INTO tbl (name, created)\n VALUES ('Hans', '2020-12-24')\n ON CONFLICT (name) DO NOTHING\n\n*Note*: `name` field must have uniqie index\n\n\nCTE (Common Table Expression)\n============================================\n\n*New in version 0.6*\n\n.. code:: python\n\n cte = db.insert (\"human\").set (name = \"Hans\", division = \"HR\").returning (\"*\"))\n q = (db.insert (\"reqs\").\n .with_ (\"inserted\", cte)\n .set (tbl_id = F (\"inserted.id\"), req = \"vaccation\"))\n\n >> WITH inserted AS (INSERT INTO human (name, division) VALUES ('Hans', 'HR') RETURNING *)\n INSERT INTO reqs (tbl_id, req) VALUES (inserted.id, 'vaccation')\n\n\n*New in version 0.6.4*\n\nStarting with\\_ is alos possible, that is more clare than above, I think.\n\n.. code:: python\n\n q = db.with_ (\"inserted\", db.insert (\"human\").set (name = \"Hans\", division = \"HR\").returning (\"*\")))\n q = (db.insert (\"reqs\").\n .set (tbl_id = F (\"inserted.id\"), req = \"vaccation\"))\n\n >> WITH inserted AS (INSERT INTO human (name, division) VALUES ('Hans', 'HR') RETURNING *)\n INSERT INTO reqs (tbl_id, req) VALUES (inserted.id, 'vaccation')\n\nMultiple CTEs are also possible,\n\n.. code:: python\n\n q = (db.insert (\"reqs\")\n .with\\_ (\"inserted\", cte)\n .with\\_ (\"inserted2\", cte)\n .set (tbl_id = F (\"inserted.id\"), req = \"vaccation\"))\n\n >> WITH inserted AS (INSERT INTO human (name, division) VALUES ('Hans', 'HR') RETURNING *),\n inserted2 AS (INSERT INTO human (name, division) VALUES ('Hans', 'HR') RETURNING *)\n INSERT INTO reqs (tbl_id, req) VALUES (inserted.id, 'vaccation')\n\n.. code:: python\n\n from sqlphile import D\n\n data = D (a = 1, b = \"c\")\n sql = (\n sqlmaps.select (\"tbl\").with_ (\"temp\", cte).join (\"temp\", \"true\")\n )\n >> WITH temp (a, b) AS (values (1, 'c'))\n SELECT * FROM tbl\n INNER JOIN temp ON true\n\n\nUsing Constant\n=====================\n\n.. code:: python\n\n from sqlphile import const\n\n sql = (\n sqlmaps.select (\"temp\").const (\"a\", 1).const ('b', 'c').filter (a = const (\"a\"))\n )\n >> WITH a AS (values (1)), b AS (values ('c'))\n SELECT * FROM temp\n WHERE a = (table a)\n\n\nJoining\n============\n\nFor joining tables,\n\n.. code:: python\n\n q = db.select (\"tbl_file t1\").join (\"names t2\", \"t1.name = t2.name\")\n q.filter (id__gt > 100)\n q.get (\"score\", \"t2.name\")\n\n >> SELECT score, t2.name FROM tbl_file AS t1\n INNER JOIN names AS t2 ON t1.name = t2.name\n WHERE id > 100\n\nFor joining with sub query,\n\n.. code:: python\n\n subq = db.select (\"tbl_project\").get (\"name\")\n q = db.select (\"tbl_file t1\").join (subq, \"t2\", \"t1.name = t2.name\")\n q.filter (id__gt = 100)\n q.get (\"score\", \"t2.name\")\n\n >> SELECT score, t2.name FROM tbl_file AS t1\n INNER JOIN (SELECT * FROM tbl_project) AS t2 ON t1.name = t2.name\n WHERE id > 100\n\nYou can use 'from\\_()' for update query,\n\n.. code:: python\n\n q = db.update (\"tbl_file\", \"t1\")\n q.from_ (\"tbl_record t2\", \"t1.id = t2.id\")\n q.set (score = F (\"t2.score\"))\n q.filter (t1__id = 1)\n\n >> UPDATE tbl_file AS t1 SET score = t2.score\n FROM tbl_record AS t2 ON t1.id = t2.id\n WHERE t1.id = 1\n\nAlso available,\n\n- left_join ()\n- right_join ()\n- full_join ()\n\n\nUnion, Intersect, Except\n=====================================\n\n.. code:: python\n\n q1 = db.select (\"tbl_project\").get (\"name\")\n q2 = db.select (\"tbl_file t1\").get (\"name\")\n q1.union (q2)\n\nAlso union_all, intersect and except\\_ are available.\n\n\nINSERT INTO ... SELECT ...\n=====================================\n\n.. code:: python\n\n sql = (\n db.select (\"tbl1\")\n .get (\"name\")\n .into (\"tbl2\", \"name\")\n )\n >> INSERT INTO tbl2 (name) SELECT name from tbl1\n\n\nTransaction\n====================\n\n.. code:: python\n\n q = (db.tran ()\n .update (\"tbl_file\")\n .set (score = 5.0).filter (id = 6)\n .execute (True))\n\t>>> BEGIN TRANSACTION;\n\t UPDATE tbl_file SET score = 5.0 WHERE id = 5;\n\t COMMIT;\n\n\nMultiple Statement\n================================\n\n.. code:: python\n\n sql = db.insert (\"temp\").set (id = 2, comment = 'Comment')\n sql.append (db.update (\"temp2\").set (comment_count = F ('comment_count + 1')).filter (id = 2))\n >>> INSERT INTO temp (id, comment) VALUES (2, 'Comment');\n UPDATE temp2 SET comment_count = comment_count + 1 WHERE id = 2\n\n\nBranching\n================\n\nYou can branch your query branch() method.\n\n.. code:: python\n\n stem = db.select (\"tbl_file\").filter (...)\n q1 = stem.clone ().get (\"id, name, create, modified\").order_by (-id)\n q2 = stem.clone ().get (\"counte (*) as cnt\")\n\n\nUsing Template\n=================\n\nFor simple example,\n\n.. code:: python\n\n with sp.sqlite3 (r\"sqlite3.db3\") as db:\n q = (db.tempate (\"SELECT {columns} FROM tbl_file WHERE {this.filters} {this.order_by}\")\n .feed (columns = \"id, name\")\n .filter (id__eq = 6)\n .order_by (\"-id\"))\n q.as_sql () # OR q.render ()\n >> SELECT id, name FROM tbl_file WHERE id = 6 ORDER BY id DESC\n\nIf you create SQL templates in specific directory,\n\n.. code:: python\n\n with sp.sqlite3 (\"sqlite3.db3\", dir = \"./sqlmaps\", auto_reload = True) as db:\n ...\n\nSQLPhile will load all of your templates in ./sqlmaps.\n\nIf you are under developing phase, set auto_reload True.\n\nAssume there is a template file named 'file.sql':\n\n.. code:: html\n\n <sqlmap version=\"1.0\">\n\n <sql name=\"get_stat\">\n SELECT type, org, count(*) cnt FROM tbl_file\n WHERE {this.filters}\n GROUP BY type\n ORDER BY org, cnt DESC\n {this.limit} {this.offset}\n </sql>\n\nIt 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.\n\nNow you can access each sql temnplate via filename without extension and query name attribute:\n\n.. code:: python\n\n # filename.query name\n q = db.file.get_stat\n q.filter (...).order_by (...)\n\n # or\n q = db.file.get_stat.filter (...).order_by (...)\n\nNote: filename is *default.sql*, you can ommit filename.\n\n.. code:: python\n\n q = db.get_stat\n q.filter (...).order_by (...)\n\nNote 2: SHOULD NOT use starts with \"select\", \"update\", \"insert\", \"delete\" or \"template\" as template filename.\n\n\nFor another example template is like this,\n\n.. code:: html\n\n <sqlmap version=\"1.0\">\n\n <sql name=\"get_stat\">\n SELECT type, org, count(*) cnt FROM tbl_file\n WHERE {this.filters}\n GROUP BY type\n ORDER BY org, cnt DESC\n {this.limit} {this.offset}\n </sql>\n\n <sql name=\"get_file\">\n SELECT * cnt FROM tbl_file\n WHERE {this.filters}\n {this._order_by}\n {this.limit}\n {this.offset}\n </sql>\n\nYou just fill variables your query reqiures,\n\n.. code:: python\n\n q = db.file.get_file.filter (id__gte = 1000)[:20]\n q.order_by (\"-id\")\n\nCurrent reserved variables are,\n\n- this.filters\n- this.group_by\n- this.order_by\n- this.limit\n- this.offset\n- this.having\n- this.returning\n\n\nAdding Data\n--------------\n\ndata () also creates 3 variables automatically for inserting and updating purpose,\n\n- this.pairs\n- this.columns\n- this.values\n\n.. code:: html\n\n <sql name=\"update_profile\">\n UPDATE tbl_profile SET {this.pairs} WHERE {this.filters};\n INSERT INTO tbl_profile ({this.columns}) VALUES ({this.values});\n </sql>\n\n.. code:: python\n\n q = db.update_profile\n q.set (name = \"Hans Roh\", birth_year = 2000)\n q.set (email = None, age = 20)\n\nWill be rendered:\n\n.. code:: python\n\n {this.columns} : name, birth_year, email, age\n {this.values} : 'Hans Roh', 2000, NULL, 20\n {this.pairs} : name='Hans Roh', birth_year=2000, email=NULL, age=20\n\n\nD Object\n```````````\n\nD object convert dictionary into SQL column and value format and can feed them into SQL template.\n\n.. code:: python\n\n from sqlphile import D\n\n d = D (name = \"Hans\", id = 1, email = None)\n d.values\n >> 'Hans', 1, NULL\n\n d.columns\n >> name, id, email\n\n d.pairs\n >> name = 'Hans', id = 1, email = NULL\n\nAnd you can feed to template with prefix.\n\n.. code:: html\n\n <sql name=\"get_file\">\n INSERT ({this.columns}, {additional.columns})\n VALUES ({this.values}, {additional.values})\n {this.returning};\n </sql>\n\nIn app,\n\n.. code:: python\n\n q = db.file.get_file.set (area = \"730\", additional = D (name = 'Hans', id = 1))\n q.returning (\"id\")\n q.execute ()\n\nIn a conclusion, it will be created 3 variables automatically,\n\n- additional.pairs\n- additional.columns\n- additional.values\n\nMore About filter()\n---------------------\n\nIn some cases, filter is tricky.\n\n.. code:: html\n\n <sqlmap version=\"1.0\">\n\n <sql name=\"get_stat\">\n SELECT type, org, count(*) cnt FROM tbl_file\n WHERE isdeleted is false AND {this.filters}\n </sql>\n\nAbove SQL is only valid when {this.filters} exists, but what if filter doesn't be provided all the time? You can write like this:\n\n.. code:: python\n\n q = db.file.get_file.filter (__all = True, id__gte = None)\n >> WHERE isdeleted is false AND 1 = 1\n\n q = db.file.get_file.filter (__all = True, id__gte = 1)\n >> WHERE isdeleted is false AND 1 = 1 AND id >= 1\n\n\nVariablize Your Query\n-----------------------\n\nYou can add variable on your sql by feed() and data() and both can be called multiple times.\n\nFeeding Variable Key-Value Pairs\n``````````````````````````````````````\n\n.. code:: html\n\n <sql name=\"get_file\">\n SELECT {cols} FROM {tbl}\n WHERE {this.filters}\n </sql>\n\nNow feed keywords args with feed ():\n\n.. code:: python\n\n q = db.file.get_file\n q.feed (cols = \"id, name, created\", tbl = \"tbl_file\")\n q.filter (id__gte = 1000)\n\n\nAlso you can feed filter.\n\n.. code:: html\n\n <sql name=\"get_file\">\n SELECT * FROM tbl_file\n WHERE {id} AND {name} AND create BETWEEN {created}\n </sql>\n\n.. code:: python\n\n q.feed (id = Q (id__in = [1,2,3,4,5]))\n >> id IN (1,2,3,4,5)\n\n q.feed (id = Q (id__in = [1,2,3,4,5]), name = \"Hans\")\n >> id IN (1,2,3,4,5) AND name = 'Hans'\n\n q.feed (id = Q (id__in = [1,2,3,4,5]), name = Q (name = None), created = B (1, 4))\n # name is ignored by 1 = 1\n >> id IN (1,2,3,4,5) AND 1 = 1\n\nActually, feed () can be omitable,\n\n.. code:: python\n\n # like instance constructor\n q = db.file.get_file (cols = \"id, name, created\", tbl = \"tbl_file\")\n q.filter (id__gte = 1000)\n\nActually this template formating use python format function,\n\n.. code:: html\n\n <sql name=\"get_file\">\n SELECT * FROM tbl_file\n WHERE id = '{id:010d}' AND name = '{name:10s}'\n </sql>\n\n q.feed (id = 10000, name = 'hansroh')\n >> WHERE id = '0000010000' AND name = 'hansroh '\n\n\nFeeding V Object\n````````````````````\n\nIf V will escape values for fitting SQL. You needn't care about sing quotes, escaping or type casting on date time field.\n\n.. code:: python\n\n V (1)\n >> 1\n\n V (__eq = 1)\n >> 1\n\n V (datetime.date.today ())\n >> TIMESTAMP '20171224 00:00:00'\n\n V (\"Hans\")\n >> 'Hans'\n\n V (None)\n >> NULL\n\n V ()\n >> NULL\n\n V (__eq = \"Hans\")\n >> 'Hans'\n\n V (__contains = \"Hans\")\n >> '%Hans%'\n\n V (__in = [1,2])\n >> (1,2)\n\n V (__between = [1,2])\n >> 1 AND 2\n\nFor example,\n\n.. code:: html\n\n <sql name=\"get_file\">\n UPDATE tbl_profile\n SET {this.pairs}\n WHERE id IN (\n SELECT id FROM tbl_member\n WHERE name = {name}\n );\n UPDATE tbl_stat SET count = count + 1\n WHERE birth_year IN {birth_year};\n </sql>\n\n.. code:: python\n\n q = db.file.get_file.feed (\n email = V (\"hansroh@email.com\"),\n birth_year = V (__in = (2000, 2002, 2004))\n )\n q.set (name = \"Hans Roh\")\n\n\nUsing SQLPhile as SQL Query Generator\n=========================================\n\nIf you need just SQL statement, you can use SQLPhile as template engine.\n\n.. code:: python\n\n import sqlphle as sp\n\n template = sp.Template (\"postgresql\")\n q = template.select (\"tbl_file\").get (\"score\", \"t2.name\")\n q.as_sql () == str (q)\n\n # specify template file\n template = sp.Template (\"postgresql\", \"./sqlmaps/test.sql\")\n q = template.house (tbl = 'tbl_file')\n\n # specify template directory\n template = sp.Template (\"postgresql\", \"./sqlmaps\")\n q = template.test.house (tbl = 'tbl_file')\n\n\nMigrating to version 0.5\n===================================\n\nIn version 0.5 template format string has been changed. most of them are compatable but some aren't.\n\nIf you used D (...), look carefully and SHOUD rewrite.\n\n.. code:: html\n\n # default.sql\n <sql name=\"get_file\">\n UPDATE tbl_profile\n SET {mydata_pairs}\n WHERE {_filters}\n </sql>\n\nAt your code,\n\n.. code:: python\n\n template = sp.Template (\"postgresql\", \"./sqlmaps\")\n q = template.get_file (mydata = D (name = 'Hans Roh'))\n\nIn version 0.5, you should change **{mydata_pairs}** into **{mydata.pairs}**.\n\nAlso _something has been deprecated, I recommend changes.\n\n- {_filters} => {this.filter}\n- {_order_by} => {this.oreder_by}\n- {_group_by} => {this.group_by}\n- {_having} => {this.having}\n- {_returning} => {this.returning}\n- {_columns} => {this.columns}\n- {_values} => {this.values}\n- {_pairs} => {this.pairs}\n\n\nChange Logs\n=============\n\n- 0.8\n - deprecated Q (k, v), use Q (k = v) form\n\n- 0.6\n\n - add x_join_related ()\n - add fetch1 () and fetchn ()\n - add excommit (), exfetch () and exone ()\n - add haskey and haskeyin operators for JSON object query\n - add JSON query\n - add .aggregate ()\n - .branch () => .clone ()\n - .data () => .set ()\n - add upflict (field_name, \\*\\*data)\n - with\\_ can be for initiating\n - add const\n - add multiple statement using .append ()\n - with\\_ for CTE\n\n- 0.5\n\n - add .with\\_ (sql, alias) for common table expression\n - add .intersect (sql) and .except\\_ (sql)\n - change templating format style: this not compatable with version 0.4, see upgrade section\n\n- 0.4.9\n\n - add .union () abd union_all ()\n\n- 0.4\n\n - add .clone ()\n - add __regex\n - fix exclude\n - fix ~Q\n - add fetchxxx to SQL class\n - fetchxxx (as_dict = True) returns AttrDict\n - add sqlphile.Template\n\n- 0.3.5\n\n - add sp.sqlite3 and sp.postgres (== prevous sp.db3.open and qlphile.pg2.open)\n\n- 0.3.4\n\n - extend IN query\n - enalbe multiple keyword argument for Q\n\n- 0.3.3\n\n - add db3 and pg2\n\n- 0.3.1\n\n - fix datetime type\n - add boolean type casting\n\n\n\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "SQL Phile",
"version": "0.9.7",
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "97143e20f7ee40ba88695d866e6cbb3a69dc5e4e1f7c2c7c34fb7b7f105a8169",
"md5": "780d3b30b2c6c43a95224aa4ea02f7a0",
"sha256": "f7a9713a665b16e95d0dfa51d485b2e695cc5885061731a2f49074542e607335"
},
"downloads": -1,
"filename": "sqlphile-0.9.7-py3-none-any.whl",
"has_sig": false,
"md5_digest": "780d3b30b2c6c43a95224aa4ea02f7a0",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 27261,
"upload_time": "2023-01-30T04:21:23",
"upload_time_iso_8601": "2023-01-30T04:21:23.342332Z",
"url": "https://files.pythonhosted.org/packages/97/14/3e20f7ee40ba88695d866e6cbb3a69dc5e4e1f7c2c7c34fb7b7f105a8169/sqlphile-0.9.7-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-01-30 04:21:23",
"github": false,
"gitlab": true,
"bitbucket": false,
"gitlab_user": "skitai",
"gitlab_project": "sqlphile",
"lcname": "sqlphile"
}
JSON