HTSQL in Python Applications

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:

sqlite3 (built-in Python module)
Microsoft SQL Server

Quick Start

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[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

In this example, the parameter school_code is available in the query as a reference $school_code.


class htsql.HTSQL(db, *addons)

Creates an HTSQL instance.

db (a string, a dictionary or None)
The address of the database.
addons (a dictionary { addon: { parameter: value } })
Plugins and plugin parameters.

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:

the type of the database server, one of sqlite, pgsql, mysql, oracle, mssql;
authentication credentials;
address of the server;
the name of the database.

All parameters except <engine> and <database> are optional.


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 with the username root and password admin:

>>> htsql = HTSQL('mysql://root:admin@')

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}})
htsql.HTSQL.__call__(environ, start_response)

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()
htsql.HTSQL.produce(query, **parameters)

Executes an HTSQL query; returns output rows.

query (a string)
The query to execute.
Parameters passed as top-level references.

Use this method to execute an HTSQL query and to get the results back. The method returns an iterator that generates output rows.


>>> 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 date
datetime.time time
datetime.datetime datetime
list, tuple record