Table of Contents
HTSQL is a Python library which can be used to make HTSQL requests directly from Python applications.
You can install HTSQL with pip or easy_install package manager. For example, to install HTSQL using pip, run:
# pip install HTSQL
Alternatively, you can download HTSQL source package and install it manually.
You also need to install a database driver specific to the database server you are using. HTSQL requires the following driver libraries:
Start with creating an instance of class htsql.HTSQL. Pass the address of the database as the argument of the class constructor:
>>> from htsql import HTSQL
>>> htsql = HTSQL("pgsql:///htsql_demo")
To execute an HTSQL request and get output rows, use method HTSQL.produce():
>>> rows = htsql.produce("/school{name, count(department)}")
HTSQL.produce() returns an iterator emitting output rows. You could access individual fields of an output row either by name (when it is specified) or by position:
>>> for row in rows:
... print "%s: %s" % (row.name, row[1])
...
School of Art and Design: 2
School of Business: 3
College of Education: 2
School of Engineering: 4
School of Arts and Humanities: 5
School of Music & Dance: 4
School of Natural Sciences: 4
Public Honorariums: 0
School of Continuing Studies: 0
It is easy to pass parameters to the query:
>>> for row in htsql.produce("/department{name}?school.code=$school_code",
... school_code='ns'):
... print row
...
department(name=u'Astronomy')
department(name=u'Chemistry')
department(name=u'Mathematics')
department(name=u'Physics')
In this example, the parameter school_code is available in the query as a reference $school_code.
Creates an HTSQL instance.
Parameter db specifies connection parameters to the database and must be either a string or a dictionary. If db is a string, it must have the form of connection URI:
<engine>://<username>:<password>@<host>:<port>/<database>
All parameters except <engine> and <database> are optional.
Examples:
Connect to a local PostgreSQL database htsql_demo with the credentials of the current system user:
>>> htsql = HTSQL('pgsql:htsql_demo')
Connect to a MySQL server running on host 10.0.0.1 with the username root and password admin:
>>> htsql = HTSQL('mysql://root:admin@10.0.0.1/htsql_demo')
Connect to a SQLite database build/regress/sqlite/htsql_demo.sqlite:
>>> htsql = HTSQL('sqlite:///build/regress/sqlite/htsql_demo.sqlite')
Alternatively, the database address could be passed as a dictionary with keys 'engine', 'username', 'password', 'host', 'port', 'database'. For example,
>>> htsql = HTSQL({'engine': 'pgsql', 'database': 'htsql_demo'})
Parameter addons allows you to extend HTSQL with additional functionality provided by plugins. This parameter is a dictionary; the keys are addon names, the value is a dictionary of addon parameters. For example, to use addon tweak.autolimit and set the parameter limit to 1000, run:
>>> htsql = HTSQL('pgsql:htsql_demo',
... {'tweak.autolimit': {'limit': 1000}})
The WSGI entry point.
An HTSQL instance is a complete WSGI application. For example, to start HTSQL as an HTTP server on localhost:8080, run:
>>> htsql = HTSQL('pgsql:htsql_demo')
>>> from wsgiref.simple_server import make_server
>>> httpd = make_server('localhost', 8080, htsql)
>>> httpd.serve_forever()
Executes an HTSQL query; returns output rows.
Use this method to execute an HTSQL query and to get the results back. The method returns an iterator that generates output rows.
Example:
>>> rows = htsql.produce("/program{code,title}?school.code='ns'")
>>> for row in rows:
... print row
...
program(code=u'gmth', title=u'Masters of Science in Mathematics')
program(code=u'pmth', title=u'Doctorate of Science in Mathematics')
program(code=u'uastro', title=u'Bachelor of Science in Astronomy')
program(code=u'uchem', title=u'Bachelor of Science in Chemistry')
program(code=u'umth', title=u'Bachelor of Science in Mathematics')
program(code=u'uphys', title=u'Bachelor of Science in Physics')
Individual row fields could be accessed either by name or by position:
>>> [row[0] for row in rows]
[u'gmth', u'pmth', u'uastro', u'uchem', u'umth', u'uphys']
>>> [row.code for row in rows]
[u'gmth', u'pmth', u'uastro', u'uchem', u'umth', u'uphys']
You can use in-segment assignment to specify the row name when it cannot be automatically inferred from the expression. In this example, the output column count(student) is assigned the name num_std:
>>> rows = htsql.produce("/program.limit(3)"
... "{code,num_std:=count(student)}")
>>> for row in rows:
... print row.code, row.num_std
...
gart 16
uhist 20
ustudio 26
You can pass parameters as keyword arguments. Use reference syntax (with $ prefix) to access the parameters in the query:
>>> rows = htsql.produce("/program?school.code=$school_code",
... school_code='ns')
>>> print [row.code for row in rows]
[u'gmth', u'pmth', u'uastro', u'uchem', u'umth', u'uphys']
Values passed as parameters are converted to HTSQL literals. The domain of the literal is determined from the type of the parameter:
| Python Type | HTSQL Domain |
|---|---|
| None | untyped |
| string, unicode | untyped |
| bool | boolean |
| int, long | integer |
| float | float |
| decimal.Decimal | decimal |
| datetime.date | date |
| datetime.time | time |
| datetime.datetime | datetime |
| list, tuple | record |