Skip to main content

database abstraction layer for pythoneer

Project description

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

Featues

  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

The Projects use dbpy

Lilac (Distributed Scheduler Task System)

Install

Firstly download or fetch it form github then run the command in shell:

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

Development

Fork or download it, then run:

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

Compatibility

Built and tested under Python 2.7

DB API

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='defalut', 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, defalut is 10.

adapter:

the database driver adapter name, currently supports mysql 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

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

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

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

specific columns

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.

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
db.select('users').condition('uid', 1) # condition('uid', 1, '=')
# > SELECT * FROM `users`
# > WHERE  `uid` = %s
in
db.select('users').condition('uid', (1, 3)) # condition('uid', [1, 3]) 一样
# > SELECT * FROM `users`
# > WHERE  `uid` IN  (%s, %s)
between
db.select('users').condition('uid', (1, 3), 'between')
# > SELECT * FROM `users`
# > WHERE  `uid` BETWEEN %s AND %s
multi condition
db.select('users').condition('uid', 1).condition('name', 'blabla')
# > SELECT * FROM `users`
# > WHERE  `uid` = %s AND `name` = %s
or condition
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

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

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

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

limit and offset

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

null condition

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:

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 :

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

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

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.

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

limit

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

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.

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

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.

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.

# 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()

simple orm

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'

Future

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.

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

Project details


Download files

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

Source Distributions

dbpy-0.1.1.zip (34.5 kB view hashes)

Uploaded Source

dbpy-0.1.1.tar.gz (15.1 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