The Modules Deprecated after v0.6

After v0.6, the following modules are deprecated and they will be obsoleted in a future release:

  1. mosql.build
  2. mosql.result
  3. mosql.json
  4. mosql.psycopg2_escape
  5. mosql.MySQLdb_escape

Warning

The modules here are no longer maintained, and will be removed in version 0.11.

The Common SQL Builders — mosql.build

Deprecated since version 0.6: Use mosql.query instead.

Warning

This module does not work on Python 3, and will be removed in version 0.11.

It contains the common SQL builders.

Changed in version 0.2: It is renamed from common.

Changed in version 0.1.6: It is rewritten for using new mosql.util, but it is compatible with old version.

select(table[, where, select]) It generates the SQL statement, select ... .
insert(table[, set, values]) It generates the SQL statement, insert into ....
update(table[, where, set]) It generates the SQL statement, update ... .
delete(table[, where]) It generates the SQL statement, delete from ... .
join(table[, using, on, type]) It generates the SQL statement, ``...
or_(*conditions) It concats the conditions by OR.

It is designed for building the standard SQL statement and tested in PostgreSQL.

Note

If you use MySQL, here is a patch for MySQL — mosql.mysql.

mosql.build.select(table, where=None, select=None, **clauses_args)[source]

It generates the SQL statement, select ... .

Changed in version 0.1.6: The clause argument, join, is renamed to joins.

The following usages generate the same SQL statement.

>>> print select('person', {'person_id': 'mosky'})
SELECT * FROM "person" WHERE "person_id" = 'mosky'
>>> print select('person', (('person_id', 'mosky'), ))
SELECT * FROM "person" WHERE "person_id" = 'mosky'

It detects the dot in an identifier:

>>> print select('person', select=('person.person_id', 'person.name'))
SELECT "person"."person_id", "person"."name" FROM "person"

Building prepare statement with mosql.util.param:

>>> print select('table', {'custom_param': param('my_param'), 'auto_param': param, 'using_alias': ___})
SELECT * FROM "table" WHERE "auto_param" = %(auto_param)s AND "using_alias" = %(using_alias)s AND "custom_param" = %(my_param)s

You can also specify the group_by, having, order_by, limit and offset in the keyword arguments. Here are some examples:

>>> print select('person', {'name like': 'Mosky%'}, group_by=('age', ))
SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' GROUP BY "age"
>>> print select('person', {'name like': 'Mosky%'}, order_by=('age', ))
SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' ORDER BY "age"
>>> print select('person', {'name like': 'Mosky%'}, order_by=('age desc', ))
SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' ORDER BY "age" DESC
>>> print select('person', {'name like': 'Mosky%'}, order_by=('age ; DROP person; --', ))
Traceback (most recent call last):
    ...
DirectionError: this direction is not allowed: '; DROP PERSON; --'
Traceback (most recent call last):
    ...
DirectionError: this direction is not allowed: '; DROP PERSON; --'

See also

The options allowed — mosql.util.allowed_options.

>>> print select('person', {'name like': 'Mosky%'}, limit=3, offset=1)
SELECT * FROM "person" WHERE "name" LIKE 'Mosky%' LIMIT 3 OFFSET 1

The operators are also supported:

>>> print select('person', {'person_id': ('andy', 'bob')})
SELECT * FROM "person" WHERE "person_id" IN ('andy', 'bob')
>>> print select('person', {'name': None})
SELECT * FROM "person" WHERE "name" IS NULL
>>> print select('person', {'name like': 'Mosky%', 'age >': 20})
SELECT * FROM "person" WHERE "age" > 20 AND "name" LIKE 'Mosky%'
>>> print select('person', {"person_id = '' OR true; --": 'mosky'})
Traceback (most recent call last):
    ...
OperatorError: this operator is not allowed: "= '' OR TRUE; --"
Traceback (most recent call last):
    ...
OperatorError: this operator is not allowed: "= '' OR TRUE; --"

See also

The operators allowed — mosql.util.allowed_operators.

If you want to use functions, wrap it with mosql.util.raw:

>>> print select('person', select=raw('count(*)'), group_by=('age', ))
SELECT count(*) FROM "person" GROUP BY "age"

Warning

It is your responsibility to ensure that your SQL queries are properly escaped if you use mosql.util.raw.

See also

How it builds the where clause — mosql.util.build_where()

mosql.build.insert(table, set=None, values=None, **clauses_args)[source]

It generates the SQL statement, insert into ....

The following usages generate the same SQL statement:

>>> print insert('person', {'person_id': 'mosky', 'name': 'Mosky Liu'})
INSERT INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu')
>>> print insert('person', (('person_id', 'mosky'), ('name', 'Mosky Liu')))
INSERT INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu')
>>> print insert('person', ('person_id', 'name'), ('mosky', 'Mosky Liu'))
INSERT INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu')

The columns is ignorable:

>>> print insert('person', values=('mosky', 'Mosky Liu'))
INSERT INTO "person" VALUES ('mosky', 'Mosky Liu')

The insert(), update() and delete() support returning.

>>> print insert('person', {'person_id': 'mosky', 'name': 'Mosky Liu'}, returning=raw('*'))
INSERT INTO "person" ("person_id", "name") VALUES ('mosky', 'Mosky Liu') RETURNING *

The MySQL-specific “on duplicate key update” is also supported:

>>> print insert('person', values=('mosky', 'Mosky Liu'), on_duplicate_key_update={'name': 'Mosky Liu'})
INSERT INTO "person" VALUES ('mosky', 'Mosky Liu') ON DUPLICATE KEY UPDATE "name"='Mosky Liu'
mosql.build.delete(table, where=None, **clauses_args)[source]

It generates the SQL statement, delete from ... .

The following usages generate the same SQL statement.

>>> print delete('person', {'person_id': 'mosky'})
DELETE FROM "person" WHERE "person_id" = 'mosky'
>>> print delete('person', (('person_id', 'mosky'), ))
DELETE FROM "person" WHERE "person_id" = 'mosky'
mosql.build.update(table, where=None, set=None, **clauses_args)[source]

It generates the SQL statement, update ... .

The following usages generate the same SQL statement.

>>> print update('person', {'person_id': 'mosky'}, {'name': 'Mosky Liu'})
UPDATE "person" SET "name"='Mosky Liu' WHERE "person_id" = 'mosky'
>>> print update('person', (('person_id', 'mosky'), ), (('name', 'Mosky Liu'),) )
UPDATE "person" SET "name"='Mosky Liu' WHERE "person_id" = 'mosky'

See also

How it builds the where clause — mosql.util.build_set()

mosql.build.join(table, using=None, on=None, type=None, **clauses_args)[source]

It generates the SQL statement, ... join ... .

New in version 0.1.6.

>>> print select('person', joins=join('detail'))
SELECT * FROM "person" NATURAL JOIN "detail"
>>> print select('person', joins=join('detail', using=('person_id', )))
SELECT * FROM "person" INNER JOIN "detail" USING ("person_id")
>>> print select('person', joins=join('detail', on={'person.person_id': 'detail.person_id'}))
SELECT * FROM "person" INNER JOIN "detail" ON "person"."person_id" = "detail"."person_id"
>>> print select('person', joins=join('detail', type='cross'))
SELECT * FROM "person" CROSS JOIN "detail"

See also

How it builds the where clause — mosql.util.build_on()

mosql.build.or_(*conditions)[source]

It concats the conditions by OR.

New in version 0.1.6.

>>> print or_({'person_id': 'andy'}, {'person_id': 'bob'})
"person_id" = 'andy' OR "person_id" = 'bob'

The Model of Result Set — moqsl.result

Deprecated since version 0.6: It will be removed because it is which MoSQL shouldn’t do. If you need a model, just write a class with mosql.query instead.

Warning

This module does not work on Python 3, and will be removed in version 0.11.

It provides useful Model which let you commuicate with database smoothly.

Changed in version 0.2: It is totally rewritten, and it does not provide the backward-compatibility.

class mosql.result.Model(defaults=None)[source]

The base model of result set.

First, for creating connection, you need to override the two methods below:

Model.getconn() It should return a connection.
Model.putconn(conn) It should accept a connection.

See also

Here are examples which show how to use MoSQL with MySQL or PostgreSQL.

Second, you may want to adjust the attributes table, clauses, arrange_by, squashed or ident_by.

  1. The Model.table is the name of table.
  2. The Model.clauses lets you customize the default clauses of this model, ex. order by, join statement, ... .
  3. The Model.arrange_by is need for arrange() which arranges result set into models.
  4. The Model.squashed defines the columns you want to squash.
  5. The last one, Model.ident_by, makes the save() more efficiently.

Then, make some queries to database:

Model.select(*args, **kargs) It performs a select query and load result set into a model.
Model.insert(*args, **kargs) It performs an insert query and load result set into a model (if any).
Model.update(*args, **kargs) It performs an update query and load result set into a model (if any).
Model.delete(*args, **kargs) It performs a delete query and load result set into a model (if any).
Model.arrange(*args, **kargs) It performs a select query and arrange the result set into models.

The arrange() is like select(), but it uses the arrange_by to arrange the result set.

The following two methods treat all of the keyword arguments as where. It makes statements simpler.

Model.where(**where) It uses keyword arguments as where and passes to select().
Model.find(**where) It uses keyword arguments as where and passes to arrange().

If you want to know what arguments you can use, see mosql.build.

After select, there is a model instance. You can access the data in a model instance by the below statements:

m['col_name'][row_idx]
m.col_name[row_idx]

m[row_idx]['col_name']
m[row_idx].col_name

m['col_name']
m.col_name

m['col_name'][row_idx] = val
m.col_name[row_idx] = val

m[row_idx]['col_name'] = val
m[row_idx].col_name = val

# if this column is squashed
m['col_name'] = val
m.col_name = val

Changed in version 0.4: Added this format, m[row_idx]['col_name'].

The Model.rows() also works well:

for row in m.rows():
    print row.col_name
    print row['col_name']
    row.col_name = val
    row['col_name'] = val

New in version 0.4.

When you finish your editing, use save() to save the changes.

You also have pop() and append() (or add()) to maintain the rows in your model instance, or create a empty model by new().

add(**row_map)[source]

It is a shortcut for Model.append().

New in version v0.5.

append(row_map)[source]

It appends a row into model.

The row_map should be a mapping which includes full or part values of a row. If you provide a part of row, the row will be filled with 1. defaults (by __init__(), new() or squashed columns) 2. default in order.

See the new() for the typical usage.

classmethod arrange(*args, **kargs)[source]

It performs a select query and arrange the result set into models.

arrange_by = ()

It defines how Model.arrange() arrange result set. It should be column names in a tuple.

clauses = {}

The additional clauses arguments for mosql.build. For example:

class Order(Model):
    ...
    table = 'order'
    clauses = dict(order_by=('created',))
    ...
clear()[source]

It pops all of the row in this model.

Changed in version v0.5.1: It doesn’t call pop() anymore. It clears this model directly.

New in version v0.5.

classmethod delete(*args, **kargs)[source]

It performs a delete query and load result set into a model (if any).

dry_run = False

Set it True to make Model.perform() rollback the changes after it performs them.

dump_sql = False

Set it True to make Model.perform() dump the SQLs before it performs them.

classmethod find(**where)[source]

It uses keyword arguments as where and passes to arrange().

classmethod getconn()[source]

It should return a connection.

classmethod getcur(conn)[source]

It lets you customize your cursor. By default, it return a cursor by the following code:

return conn.cursor()

New in version 0.4.

ident_by = None

It defines how to identify a row. It should be column names in a tuple.

classmethod insert(*args, **kargs)[source]

It performs an insert query and load result set into a model (if any).

classmethod new(**defaults)[source]

Create a empty model instance with the key arguments as the default values. It is a shortcut for initialization method.

A typical usage:

>>> m = Model.new(id='mosky')
>>> m.add(email='mosky.tw@gmail.com')
>>> m.add(email='mosky.liu@pinkoi.com')
>>> print m
{'email': ['mosky.tw@gmail.com', 'mosky.liu@pinkoi.com'],
 'id': ['mosky', 'mosky']}

New in version v0.5.

classmethod perform(sql=None, param=None, params=None, proc=None, sqls=None)[source]

It performs SQL, SQLs or/and procedure with parameter(s).

Changed in version v0.5: It supports to use parameter and call procedure.

pop(row_idx=-1)[source]

It pops the row you specified in this model.

Changed in version v0.4: It returns the row poped in a dict.

classmethod putconn(conn)[source]

It should accept a connection.

rows()[source]

It generates the proxy for each row.

New in version 0.4.

save()[source]

It saves the changes.

When it encounters an update, it uses ident_by to build where. If the column updated is squashed, it will use the arrange_by instead. But if the ident_by or arrange_by is not set, it will use all of the columns

For efficiency, it will merge the updates which have same condition into a single update.

Changed in version v0.5.1: It uses arrange_by for the column squashed.

classmethod select(*args, **kargs)[source]

It performs a select query and load result set into a model.

squash_all = False

If you want to squash all of columns, set it True.

New in version 0.4.

squashed = set([])

It defines which columns should be squashed. It is better to use a set to enumerate the names of columns.

table = ''

It is used as the first argument of SQL builder.

classmethod update(*args, **kargs)[source]

It performs an update query and load result set into a model (if any).

classmethod where(**where)[source]

It uses keyword arguments as where and passes to select().

The Compatible JSON Encoder and Decoder — mosql.json

Deprecated since version 0.6: The mosql.result will be removed in a future release, so this module will not be needed once result is removed.

Warning

This module does not work on Python 3, and will be removed in version 0.11.

An alternative of built-in json.

It is compatible with mosql.result and built-in datetime.

New in version 0.2: It supports the new mosql.result.

New in version 0.1.1.

mosql.json.dump = <functools.partial object at 0x7f9e4d741af8>

It uses the ModelJSONEncoder.

mosql.json.dumps = <functools.partial object at 0x7f9e4d741b50>

It uses the ModelJSONEncoder.

mosql.json.load(fp, encoding=None, cls=None, object_hook=None, parse_float=None, parse_int=None, parse_constant=None, object_pairs_hook=None, **kw)[source]

It is same as json.load.

mosql.json.loads(s, encoding=None, cls=None, object_hook=None, parse_float=None, parse_int=None, parse_constant=None, object_pairs_hook=None, **kw)[source]

It is same as json.loads.

class mosql.json.ModelJSONEncoder(skipkeys=False, ensure_ascii=True, check_circular=True, allow_nan=True, sort_keys=False, indent=None, separators=None, encoding='utf-8', default=None)[source]

It is compatible with mosql.result and built-in datetime.

The Native Escape Functions of Psycopg2 — moqsl.psycopg2_escape

Deprecated since version 0.6: You should use safe connection encoding, such as utf-8. This module will be removed in a future release.

Warning

This module does not work on Python 3, and will be removed in version 0.11.

It applies the escape function in psycopg2 to mosql.util.

Usage:

import mosql.psycopg2_escape
mosql.psycopg2_escape.conn = CONNECTION

It will replace the escape functions in mosql.util.

New in version 0.3.

The Native Escape Function of MySQLdb — moqsl.MySQLdb_escape

Deprecated since version 0.6: You should use safe connection encoding, such as utf-8. This module will be removed in a future release.

Warning

This module does not work on Python 3, and will be removed in version 0.11.

It applies the escape function in MySQLdb to mosql.util.

Usage:

import mosql.MySQLdb_escape
mosql.MySQLdb_escape.conn = CONNECTION

It will replace the escape functions in mosql.util.

New in version 0.3.