HTSQL in Python Applications¶
Table of Contents
HTSQL is a Python library which can be used to make HTSQL requests directly from Python applications.
Installation¶
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:
- SQLite
- sqlite3 (built-in Python module)
- PostgreSQL
- psycopg2
- MySQL
- MySQL-python
- Oracle
- cx_Oracle
- Microsoft SQL Server
- pymssql
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.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
.
Reference¶
-
class
htsql.
HTSQL
(db, *addons)¶ Creates an HTSQL instance.
db
(a string, a dictionary orNone
)- 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. Ifdb
is a string, it must have the form of connection URI:<engine>://<username>:<password>@<host>:<port>/<database>
<engine>
- the type of the database server, one of
sqlite
,pgsql
,mysql
,oracle
,mssql
; <username>:<password>
- authentication credentials;
<host>:<port>
- address of the server;
<database>
- the name of the 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 usernameroot
and passwordadmin
:>>> 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 addontweak.autolimit
and set the parameterlimit
to1000
, 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
- 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.
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 namenum_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