Skip to main content

Simple MySQL query builder and profiler

Project description

Following the rule simple easy, complex possibe, MySql Simple Query Builder provides API for basic queries, nested transactions, and aid in complex query building and profiling.

DML

SELECT

The following modes are supported:

  • counting:

    db.count('customer', {'storeId' : 1})
    # 326L
  • single row’s field value:

    db.one(('firstName',), 'customer', {'customerId' : 1})
    # u'MARY'
  • single row’s fields value dictionary:

    db.one(('firstName', 'lastName'), 'customer', {'customerId' : 1})
    # {'firstName': u'MARY', 'lastName': u'SMITH'}
  • multiple rows’ single field value projection:

    db.select(('firstName',), 'customer', {'customerId' : (1, 2)})
    # (u'MARY', u'PATRICIA')
  • multiple rows’ fields value tuple:

    db.select(('firstName', 'lastName'), 'customer', {'customerId' : (1, 2)})
    # ({'firstName': u'MARY', 'lastName': u'SMITH'}, {'firstName': u'PATRICIA', 'lastName': u'JOHNSON'})

WHERE condition is a conjunction. Supported arguments are ones support by underlying library, MySQLdb, and its escaping functionality:

  • None,

  • int (long, float, Decimal),

  • datetime (date),

  • unicode (str),

  • tuple of int (list of int),

Besides supported are ORDER BY and LIMIT.

Complex query

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
  {0} AND t.email LIKE {1}
'''
db.query(
  # escape % as % operator is used by MySQLdb
  sql.format('{where}', db.quote('%%@sakilastaff.com')),
  {'c.active' : True},
  limit = 1
).fetchall()
# ({'firstName': u'MARY', 'lastName': u'SMITH'},)

Or the same with cursor:

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 = db.cursor(dict)
cursor.execute(sql, {'active' : True, 'email' : '%@sakilastaff.com'})
cursor.fetchall()
# ({'firstName': u'MARY', 'lastName': u'SMITH'},)

INSERT

Supports single row insert.

UPDATE

Supports one table at a time. Has the same WHERE features as SELECT.

DELETE

Supports one table at a time. Has the same WHERE features as SELECT.

Test

Test suite is built against Sakila test database available at MySQL site. Besides allowing further refacotring, it provides examples of usage.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

MysqlSimpleQueryBuilder-0.2.3.zip (12.9 kB view hashes)

Uploaded Source

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page