Reference¶

This page is reference manual for the builder functionality. For more examples take a look at the project’s test suite [1]. As well as the following snippets, the test suite is built against Sakila test database [2].

Facade¶

class myquerybuilder.builder.QueryBuilder(clauseBuilder=<class 'myquerybuilder.builder.ClauseBuilder'>, **kwargs)¶

The package’s facade. The initializer sets autocommit to server’s default and establishes connection.

from myquerybuilder import QueryBuilder

qb = QueryBuilder(user = 'guest', passwd = 'pass', db = 'sakila')

cursorType = {<type 'dict'>: <class 'myquerybuilder.builder.NamedDictCursor'>, <type 'tuple'>: <class 'myquerybuilder.builder.NamedCursor'>}¶: The alias mapping that cursor() will look up for actual cursor class.

select(fields, table, where=None, order=None, limit=None)¶

Executes a SELECT query with the specified clauses.

param fields:
str sequence of fields to fetch. When it is an one-element sequence, return value is tuple of scalars, otherwise return value is tuple of dict values. If no row is matched, empty tuple is returned.

param table:
str of table name.

param where:
dict of conditions which is applied as a conjunction and whose values can be scalar or vector. These values are values that can be escaped by the underlying MySQL diver. Note that set has correspondence to MySQL ENUM type.

None

number: int, long, float, Decimal

date: datetime, date

string: str, unicode, bytes

number or string sequence

E.g. {'a': 'foo'', 'b': (21, 9), 'c': None} results in WHERE (`a` = %(a)s AND `b` IN %(b)s) AND `c` IS %(c)s which in turn is interpolated by the driver library.

param order:
tuple sequence of field and sort order, e.g. [('a', 'asc'), ('b', 'desc')].

param limit:
int of row limit or tuple with offset and row limit, e.g. 10 or (100, 10).

fields = 'film_id', 'title'
table  = 'film' 
where  = {'rating': ('R', 'NC-17'), 'release_year': 2006}
order  = [('release_year', 'asc'), ('length', 'desc')]
limit  = 2

rows = qb.select(fields, table, where, order, limit)
print(rows)
# (
#   {'film_id': 872, 'title': 'SWEET BROTHERHOOD'}, 
#   {'film_id': 426, 'title': 'HOME PITY'}, 
# )

fields = 'film_id',
rows   = qb.select(fields, table, where, order, limit)
print(rows)
# (872, 426)

one(fields, table, where=None, order=None)¶

Returns first matched row’s field values. When fields is one-element sequence, it returns the field’s value, otherwise returns value is a dict. If no row is matched, None is returned.

fields = 'username', 'email'
table  = 'staff' 
where  = {'active': True}
order  = [('last_name', 'asc')]

row = qb.one(fields, table, where, order)
print(row)
# {'username': 'Mike', 'email': 'Mike.Hillyer@sakilastaff.com'}

fields = 'username',
value  = qb.one(fields, table, where, order)
print(value)
# Mike

count(table, where=None)¶

Returns matched row count.

count = qb.count('payment', {'customer_id': (1, 2, 3), 'staff_id': 1}
print(count)
# 46

insert(table, values)¶

Inserts a row with the values into the table. Last inserted id is returned.

actor = {
  'first_name' : 'John',
  'last_name'  : 'Doe'
}
id = self.testee.insert('actor', actor)

update(table, values, where)¶

Updates the matched rows in the table. Affected row count is returned. If where is None it updates every row in the table.

values = {'title': 'My New Title', 'length': 99}
where  = {'film_id': 10}

affected = qb.update('film', values, where)

delete(table, where)¶

Deletes the matched rows from the table. Affected row count is returned. If where is None it deletes every row in the table.

where = {'release_year': 2000}

affected = qb.delete('film', where)

cursor(type=<type 'tuple'>)¶: Return a cursor instance that corresponds to the provided type. Type can be either an actual cursor class, or an alias that is looked up in cursorType.

quote(value)¶: Returns literal representation comprised of UTF-8 bytes, str for Python 2 and bytes with surrogateescape encoding for Python3, for the value. It doesn’t necessarily quotes the value, when it’s an int, or, specifically in case of pymysql decimal.Decimal.

query(sql, where=None, order=None, limit=None)¶

Executes the SQL query and returns its cursor. Returned cursor is the cursor aliased by dict. The method is an aid for complex query construction when its WHERE, ORDER, LIMIT are yet simple. If there is no clause placeholder in the query, but clause values are provided, its representation is appended to the query. If there is a placeholder, but no values, it is replaced with empty string.

sql = '''
  SELECT address, district
  FROM (
    SELECT ad.*
    FROM country cn
    JOIN city    ct USING(country_id)
    JOIN address ad USING(city_id)
    {where}
    {order}
 ) AS `derived`
 {limit}
'''
where = {
  'ad.address2'   : None,
  'ct.city_id'    : 300,
  'ad.address_id' : (1, 2, 3) 
}
order = [('ad.address_id', 'desc')]
limit = 10

cursor = qb.query(sql, where, order, limit)

ping(reconnect=True)¶

Checks connection to the server.

param reconnect:
	Controls whether reconnection should be performed in case of lost connection.
raises OperationalError:
	Is raised when ping has failed.

Warning

When reconnection occurs, implicit rollback, lock release and other resets are performed! In worst case the server may not yet know that the client has lost connection, thus the client may find itself in a separate to its old transaction. For more details read Persistent connection.

begin()¶: Starts a transaction on the first call (in stack order), or creates a save point on a consecutive call. Increments the transaction level. Read Nested transaction.

commit()¶: Commits a transaction if it’s the only pending one. Otherwise releases the savepoint. Decrements the transaction level. Read Nested transaction.

rollback()¶: Rolls back a transaction if it’s the only pending one. Otherwise rolls back the savepoint. Decrements the transaction level. Read Nested transaction.

Cursor¶

class myquerybuilder.builder.NamedCursor(connection)¶

Default cursor type of QueryBuilder. It converts named paramstyle into pyformat, so it can work with MySQLdb-family as it uses client-side query parametrisation with string interpolation via % operator. Named paramstyle is easy to read and write. Though if you don’t need it you can opt-put with setting desired cursorType mapping as QueryBuilder internally doesn’t rely on named paramstyle.

sql = '''
  SELECT c.first_name `firstName`, c.last_name `lastName`
  FROM customer c
  JOIN store    s USING(store_id)
  JOIN staff    t ON s.manager_staff_id = t.staff_id
  WHERE c.active = :active AND t.email LIKE :email
  LIMIT 0, 1
'''
cursor = self.testee.cursor(dict)
cursor.execute(sql, {'active': True, 'email': '%@sakilastaff.com'})

print(cursor.fetchall())
# ({'firstName': u'MARY', 'lastName': u'SMITH'},)

execute(query, args=None)¶

Executes a query.

Parameters:	query – Query to execute on server. args – Optional sequence or mapping, parameters to use with query. If `args` is a sequence, then format paramstyle, `%s`, must be used in the query. If a mapping is used, then it should be either named or pyformat, `:foo` and `%(bar)s` respectively.
Returns:	Number of affected rows.

class myquerybuilder.builder.NamedDictCursor(connection)¶: The same as NamedCursor but with records represented by a dict.

Internal¶

class myquerybuilder.builder.ClauseBuilder¶

The class is responsible for handling SQL clause strings.

where = '{where}'¶: SQL WHERE clause placeholder to be used with query().

order = '{order}'¶: SQL ORDER clause placeholder to be used with query().

limit = '{limit}'¶: SQL LIMIT clause placeholder to be used with query().

getReference(name, alias=None)¶: Returns a valid reference for a field, with optional alias, or a table, e.g.:

name →`name`

a.name → a.`name`

getPlaceholder(name, nameOnly=False, postfix='')¶: Returns pyformat paramstyle placeholder, which can optionally be postfixed.

getExpressions(fields, postfix='', op='=')¶: Returns a tuple of boolean binary expressions for provided field names, where the entry looks like field operator placeholder. Default operator is equality, =. Placeholder can be postfixed.

getSelectClause(fields)¶: Returns SELECT clause string with provided fields.

getFromClause(table)¶: Returns FROM clause string with provided table name.

getWhereClause(where)¶: Returns WHERE clause string with conjunction of provided conditions. Empty string when no condition is provided.

getOrderClause(order)¶: Returns ORDER clause string with provided order sequence. Empty string when no order is provided.

getLimitClause(limit)¶: Returns LIMIT clause string with provided limit. Empty string when no limit is provided. The parameter can be either entry limiting int or two-element sequence (start, offset).

replaceClause(sql, name, values)¶

Handles the clause in the SQL query. If the query has a placeholder, {where} for instance, and corresponding values are provided, the placeholder is replaced with return value of the clause callable. If there’s no placeholder, return value is appended to the query. If a placeholder presents, but no values are provided, it’s replaced with empty string.

Parameters:	sql – SQL query to process. name – Clause name: where, order, limit. values – Values to pass to the clause callable.
Returns:	SQL query with processed clause.

class myquerybuilder.builder.ClauseBuilderCamelCase¶

The subclass that makes it possible to reference fields and tables with camelCase (also called mixed case when it starts from lowercase letter), when MySQL side, which is dominating convention, uses underscore convention and Python side uses camelCase. QueryBuilder can be instantiated with custom clause builder like this:

qb = QueryBuilder(clauseBuilder = ClauseBuilderCamelCase, **config)

getReference(name, alias=None)¶: Returns a valid reference for a field, with optional alias, or a table, e.g.:

nameToName →`name_to_name`

a.nameToName → a.`name_to_name`

[1]	https://bitbucket.org/saaj/mysql-simple-query-builder/src/tip/myquerybuilder/test/

[2]	http://dev.mysql.com/doc/sakila/en/index.html