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.
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
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:
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 (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
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:
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/>.
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.