Skip to content

whiteclover/dbpy

Repository files navigation

dbpy

dbpy is database abstration layer wrote by python. The design is inspired by webpy db and drupal database . If like the simple db abstration layer like tornado db or webpy db, it is worth to try.

中文|chinese

  1. Add pymysql adapter

Install the extension with the following command:

$ easy_install pymysql

or alternatively if you have pip installed:

$ pip install pymysql
  1. silmple and flexible
  2. graceful and useful sql query builder.
  3. thread-safe connection pool
  4. supports read/write master-slave mode
  5. supports transaction

Lilac (Distributed Scheduler Task System)

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:

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

Fork or download it, then run:

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

Built and tested under Python 2.7+

Have a look:

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')
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.
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 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.
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'}

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
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

the api is used for select sql database query.

select(table, key='default'):

table:the table name
key:the idenfify of database
db.select('users')
# > SELECT * FROM `users`
db.select('users').fields('uid', 'name')
# > SELECT `uid`, `name` FROM `users`

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.
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'}

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.
db.select('users').condition('uid', 1) # condition('uid', 1, '=')
# > SELECT * FROM `users`
# > WHERE  `uid` = %s
db.select('users').condition('uid', (1, 3)) # condition('uid', [1, 3]) 一样
# > SELECT * FROM `users`
# > WHERE  `uid` IN  (%s, %s)
db.select('users').condition('uid', (1, 3), 'between')
# > SELECT * FROM `users`
# > WHERE  `uid` BETWEEN %s AND %s
db.select('users').condition('uid', 1).condition('name', 'blabla')
# > SELECT * FROM `users`
# > WHERE  `uid` = %s AND `name` = %s
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
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`
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
db.select('users').group_by('name', 'uid')
# > SELECT * FROM `users`
# > GROUP BY `name`, `uid`
db.select('users').limit(2).offset(5)
# > SELECT * FROM `users`
# > LIMIT 2 OFFSET 5
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

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

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 )

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

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`

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

insert(table, key='default'):

table:the table name
key:the idenfify of database
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

print q.execute()
# > 2

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.

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

print q.execute()
# > 2

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

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

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.

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

print q.execute()
# > 2

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

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(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.

# 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

the orm demo samples

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'

Personal idea:

  1. add join for select api
  2. add a schema class for creating or changing table.
  3. add some api for mysql individual sql like replace or duplicate update
  4. improve connection pool.

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/>.