dbpy

dbpy is database abstration layer wrote by python. The design is inspired by webpy db <https://github.com/webpy/webpy> and drupal database <https://www.drupal.org/developing/api/database> . If like the simple db abstration layer like tornado db or webpy db, it is worth to try.

中文|chinese <https://github.com/thomashuang/dbpy/blob/master/README_CN.rst>_

changes

. Add pymysql adapter

Install the extension with the following command::

$ easy_install pymysql

or alternatively if you have pip installed::

$ pip install pymysql

Featues

. silmple and flexible

. graceful and useful sql query builder.

. thread-safe connection pool

. supports read/write master-slave mode

. supports transaction

The Projects use dbpy

Lilac (Distributed Scheduler Task System) <https://github.com/thomashuang/Lilac>_

.. contents:: :depth: 4

Install

Install the extension with the following command::

$ easy_install dbpy

or alternatively if you have pip installed::

$ pip install dbpy

or clone it form github then run the command in shell:

.. code-block:: bash

cd db # the path to the project
python setup.py install

Development

Fork or download it, then run:

.. code-block:: bash

cd db # the path to the project
python setup.py develop

Compatibility

Built and tested under Python 2.7+

DB API

Have a look:

.. code-block:: python

config = {
        'passwd': 'test',
        'user': 'test',
        'host': 'localhost',
        'db': 'test',
        'max_idle' : 5*60
    }

db.setup(config,  minconn=5, maxconn=10,  
    adapter='mysql', key='default', slave=False)

db.execute('show tables')

setup

:config: the connection basic config, the all of arguements of MySQLDB#connect is acceptable。 the max_idle is the connect timeout setting that is used to reconnection when connection is timeout, default is 10 seconds. :minconn: the minimum connections for the connection pool, default is 5. :maxconn: the maximum connections for the connection pool, default is 10. :adapter: the database driver adapter name, currently supports mysql (MySQLdb, pymysql) only. :key: the database idenfify for database, default database is "default" :slave: if set to true, the database will be register as a slave database. make sure you setup a master firstly.

.. code-block:: python

config = {
        'passwd': 'test',
        'user': 'test',
        'host': 'localhost',
        'db': 'test',
        'max_idle' : 5*60
    }

db.setup(config, key='test')
config['host'] = 'test.slave'
# set a slave, and now the master can only to write
db.setup(config, key='test', slave=True) 

config['host'] = 'test.slave2'
# add more slave for 'test'
db.setup(config, key='test', slave=True)

config['host'] = 'host2'
config['db'] = 'social'
# set another database
db.setup(config, key='social', slave=True)

query

query api is used for reading database operation, like select..., show tables, if you wanna update your database please use execute api.

query(sql, args=None, many=None, as_dict=False, key='default'):

:sql: the raw sql :args: the args for sql arguement to prepare execute. :many: when set to a greater zero integer, it will use fetchmany then yield return a generator, otherwise a list. :as_dict: when set to true, query api will return the database result as dict row, otherwise tuple row. :key: the idenfify of database.

.. code-block:: python

print db.query('SELECT 1')
# > ((1L,),)

# use social db
print db.query('SELECT 1', key='social')
# > ((1L,),)

print db.query('SELECT * FROM users WHERE uid=%s and name=%s', (1, 'user_1'))
# > ((1L, u'user_1'),)

# Wanna return dict row
print db.query('SELECT * FROM users WHERE uid=%s and name=%s', 
            (1, 'user_1'), as_dict=True)
# > ({'uid': 1L, 'name': u'user_1'},)

# Use fetchmany(many) then yeild, Return generator
res = db.query('SELECT * FROM users WHERE uid=%s and name=%s', 
                (1, 'user_1'), many=5, as_dict=True)
print res
print res.next()
# > <generator object _yield at 0x7f818f4b6820>
# > {'uid': 1L, 'name': u'user_1'}

execute

the api is used for writing database operation, like insert, update, delete.. if you wanna read query your database please use query api.

execute(sql, args=None, key='default'):

:sql: the raw sql :args: the args for sql arguement to prepare execute. :key: the idenfify of database.

Return::

it returns last_insert_id when sql is insert statement, otherwise rowcount

.. code-block:: python

db.execute('DROP TABLE IF EXISTS `users`')
db.execute("""CREATE TABLE `users` (
         `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `name` varchar(20) NOT NULL,
        PRIMARY KEY (`uid`))""")

# when inset mutil-values，the api will call executemany
db.execute('INSERT INTO users VALUES(%s, %s)', [(10, 'execute_test'), (9, 'execute_test')])
# > 9
db.execute('DELETE FROM users WHERE name=%s', ('execute_test',))
# > 2

# use social db
db.execute('delete from events where created_at<%s', (expired, ), key='social')
# > 10

select

the api is used for select sql database query.

select(table, key='default'):

:table: the table name :key: the idenfify of database

select all


.. code-block:: python

    db.select('users')
    # > SELECT * FROM `users`

specific columns

.. code-block:: python

db.select('users').fields('uid', 'name')
# > SELECT `uid`, `name` FROM `users`

execute


when you already build your sql, try execute api to fetch your database result.

execute(many=None, as_dict=False):

:many: when set to a greater zero integer, it will use fetchmany then yield return a generator, otherwise a list.
:as_dict: when set to true, query api will return the database result as dict row, otherwise tuple row.

.. code-block:: python

    q = db.select('users').fields('uid', 'name')
    res = q.execute()
    print res
    # > ((1L, u'user_1'), (2L, u'user_2'), (3L, u'user_3'), (4L, u'user_4'), (5L, None))

    res = q.execute(many=2, as_dict=True)
    print res
    print res.next()
    # > <generator object _yield at 0x7f835825e820>
    # > {'uid': 1L, 'name': u'user_1'}

Condition

It is time to try more complex select query.

condition(field, value=None, operator=None):

:field: the field of table :value: the value of field, defaul is None ("field is null") :operator: the where operator like BETWEEN, IN, NOT IN, EXISTS, NOT EXISTS, IS NULL, IS NOT NULL, LIKE, NOT LIKE, =, <, >, >=, <=, <> and so on.

simple ^^^^^^^^^^^^^^^^

.. code-block:: python

db.select('users').condition('uid', 1) # condition('uid', 1, '=')
# > SELECT * FROM `users`
# > WHERE  `uid` = %s

in ^^^^^^^^^^^^^^^^

.. code-block:: python

db.select('users').condition('uid', (1, 3)) # condition('uid', [1, 3]) 一样
# > SELECT * FROM `users`
# > WHERE  `uid` IN  (%s, %s)

between ^^^^^^^^^^^^^^^^

.. code-block:: python

db.select('users').condition('uid', (1, 3), 'between')
# > SELECT * FROM `users`
# > WHERE  `uid` BETWEEN %s AND %s

multi condition ^^^^^^^^^^^^^^^^^^^^^^^^

.. code-block:: python

db.select('users').condition('uid', 1).condition('name', 'blabla')
# > SELECT * FROM `users`
# > WHERE  `uid` = %s AND `name` = %s

or condition ^^^^^^^^^^^^^^

.. code-block:: python

or_cond = db.or_().condition('uid', 1).condition('name', 'blabla')
db.select('users').condition(or_cond).condition('uid', 1, '<>')
# > SELECT * FROM `users`
# > WHERE  ( `uid` = %s OR `name` = %s ) AND `uid` <> %s

order by


.. code-block:: python

    db.select('users').order_by('name')
    # > SELECT * FROM `users`
    # > ORDER BY `name`

    db.select('users').order_by('name', 'DESC')
    # > SELECT * FROM `users`
    # > ORDER BY `name` DESC

    db.select('users').order_by('name', 'DESC').order_by('uid')
    # > SELECT * FROM `users`
    # > ORDER BY `name` DESC, `uid`

distinct

.. code-block:: python

db.select('users').distinct().condition('uid', 1)
# > SELECT DISTINCT * FROM `users`
# > WHERE  `uid` = %s 

db.select('users').fields('uid', 'name').distinct().condition('uid', 1)
# > SELECT DISTINCT `uid`, `name` FROM `users`
# > WHERE  `uid` = %s

group by


.. code-block:: python

    db.select('users').group_by('name', 'uid')
    # > SELECT * FROM `users`
    # > GROUP BY `name`, `uid`

limit and offset

.. code-block:: python

db.select('users').limit(2).offset(5)
# > SELECT * FROM `users`
# > LIMIT 2 OFFSET 5

null condition


.. code-block:: python

    db.select('users').is_null('name').condition('uid', 5)
    # > SELECT * FROM `users`
    # > WHERE  `name` IS NULL  AND `uid` = %s 

    db.select('users').is_not_null('name').condition('uid', 5)
    # > SELECT * FROM `users`
    # > WHERE  `name` IS NOT NULL  AND `uid` = %s 

    db.select('users').condition('name', None)
    # > SELECT * FROM `users`
    # > WHERE  `name` IS NULL  

complex conditions
-------------------

using db.and_(), db.or_(), we can build complex where conditions:

.. code-block:: python

    or_cond = db.or_().condition('field1', 1).condition('field2', 'blabla')
    and_cond = db.and_().condition('field3', 'what').condition('field4', 'then?')
    print db.select('table_name').condition(or_cond).condition(and_cond)

    # > SELECT * FROM `table_name`
    # > WHERE  ( `field1` = %s OR `field2` = %s ) AND ( `field3` = %s AND `field4` = %s ) 

expr
------------

if you wanna use the aggregate functions like sum, count, please use ``erpr`` :

.. code-block:: python

    from  db import expr

    db.select('users').fields(expr('count(*)'))
    # > SELECT count(*) FROM `users`

    db.select('users').fields(expr('count(uid)', 'total'))
    # > SELECT count(uid) AS `total` FROM `users`

insert
-----------

The ``insert`` api is used for building insert into sql statement.

insert(table, key='default'):

:table: the table name
:key: the idenfify of database 

.. code-block:: python

    q = db.insert('users').values((10, 'test_insert'))
    # > INSERT INTO `users` VALUES(%s, %s)
    print q._values
    # > [(10, 'test_insert')]

    q = db.insert('users').fields('name').values({'name': 'insert_1'}).values(('insert_2',))
    # > INSERT INTO `users` (`name`) VALUES(%s)
    print q._values
    # > [('insert_1',), ('insert_2',)]

When you use ``execute`` api to get result, it will reutrn the ``last insert id``：

.. code-block:: python

    print q.execute()
    # > 2

update
-----------

The ``update`` api is used for building update sql statement.

update(table, key='default'):

:table: the table name
:key: the idenfify of database 

mset and set：

:mset: the value must be dict tpye, that sets mutil-fileds at once time.
:set(column, value): set one field one time.

the where conditions please see `select`_ for more information.

.. code-block:: python

    db.update('users').mset({'name':None, 'uid' : 12}).condition('name','user_1')
    # > UPDATE `users` SET `name` = %s, `uid` = %s WHERE  `name` = %s 

    q = (db.update('users').set('name', 'update_test').set('uid', 12)
        .condition('name', 'user_2').condition('uid', 2)) # .execute()
    print q.to_sql()
    # > UPDATE `users` SET `name` = %s, `uid` = %s WHERE  `name` = %s AND `uid` = %s 

When you use ``execute`` api to get result, it will reutrn the ``rowcount``：

.. code-block:: python

    print q.execute()
    # > 2

limit

You can use limit api to lim the quantity of update.

.. code-block:: python

db.update('users').mset({'name':None, 'uid' : 12}).condition('name','user_1').limit(5)
# > UPDATE `users` SET `name` = %s, `uid` = %s WHERE  `name` = %s  LIMIT 5

delete

The delete api is used for building DELETE FROM sql statement.

delete(table, key='default'):

:table: the table name :key: the idenfify of database

the where conditions please see select_ for more information.

.. code-block:: python

db.delete('users').condition('name','user_1')
# > DELETE FROM `users` WHERE  `name` = %s

When you use execute api to get result, it will reutrn the rowcount：

.. code-block:: python

print q.execute()
# > 2

to_sql and str

you can use to_sql or str method to the objects of select, insert, update, delete to print the sql you build.

.. code-block:: python

q = (db.update('users').set('name', 'update_test').set('uid', 12)
        .condition('name', 'user_2').condition('uid', 2))
print q.to_sql()
print q
# > UPDATE `users` SET `name` = %s, `uid` = %s WHERE  `name` = %s AND `uid` = %s

transaction

transaction(table, key='default'):

:table: the table name :key: the idenfify of database

The simple transaction done all or do nothing, you cann't set savepoint.

.. code-block:: python

# with context
with db.transaction() as t:
    t.delete('users').condition('uid', 1).execute()
    (t.update('users').mset({'name':None, 'uid' : 12})
        .condition('name','user_1').execute())

# the normal way
t = db.transaction()
t.begin()
t.delete('users').condition('uid', 1).execute()
(t.update('users').mset({'name':None, 'uid' : 12})
    .condition('name','user_1').execute())

#if failed will rollback
t.commit()

.. note:: when uses begin must be combine with commit，otherwise the connection will not return connection pool.suggets to use with context

simple orm

the orm demo samples <https://github.com/thomashuang/dbpy/blob/master/samples>_

.. code-block:: python

import model
from orm import Backend
import db

db.setup({ 'host': 'localhost', 'user': 'test', 'passwd': 'test', 'db': 'blog'})

user = Backend('user').find_by_username('username')
if user and user.check('password'):
    print 'auth'

user = model.User('username', 'email', 'real_name', 'password', 
        'bio', 'status', 'role')
if Backend('user').create(user):
    print 'fine'

user = Backend('user').find(12)
user.real_name = 'blablabla....'
if Backend('user').save(user):
    print 'user saved'

if Backend('user').delete(user):
    print 'delete user failed'

post = model.Post('title', 'slug', 'description', 'html', 'css', 'js', 
        'category', 'status', 'comments', 'author')
if not Backend('post').create(post):
    print 'created failed'

Future

Personal idea:

. add `join` for select api

. add a schema class for creating or changing table.

. add some api for mysql individual sql like `replace` or `duplicate update`

. improve connection pool.

LICENSE

Copyright (C) 2014-2015 Thomas Huang

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, version 2 of the License.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.

whiteclover / dbpy

readme

changes

. Add pymysql adapter

Featues

. silmple and flexible

. graceful and useful sql query builder.

. thread-safe connection pool

. supports read/write master-slave mode

. supports transaction

The Projects use dbpy

Install

Development

Compatibility

DB API

setup

query

execute

select

delete

to_sql and str

transaction

simple orm

Future

. add `join` for select api

. add a schema class for creating or changing table.

. add some api for mysql individual sql like `replace` or `duplicate update`

. improve connection pool.

LICENSE

whiteclover / dbpy

readme

changes

. Add pymysql adapter

Featues

. silmple and flexible

. graceful and useful sql query builder.

. thread-safe connection pool

. supports read/write master-slave mode

. supports transaction

The Projects use dbpy

Install

Development

Compatibility

DB API

setup

query

execute

select

delete

to_sql and str

transaction

simple orm

Future

. add join for select api

. add a schema class for creating or changing table.

. add some api for mysql individual sql like replace or duplicate update

. improve connection pool.

LICENSE

. add `join` for select api

. add some api for mysql individual sql like `replace` or `duplicate update`