Pyton 实现SQLHelper

看了廖老师的教程实现了这个模块，按照自己的思路实现了一个，代码附下。

需要说名的几点：

1. dbcontext继承自threading.local，确保每个线程中都有独立的一个dbcontext对象，保证个用户数据独立。

2. connection对象是对dbcontext对象的一个封装，实现了getcursor方法，和关闭方法。

3. 现在的代码不够DRY，select方法和update方法调用了connection对象后都要手动关闭，可以实现一个装饰器，把要调用connection对象的代码块儿包裹起来，初始化和关闭connection对象的代码提取到装饰器中。下次补上。

代码附下：

import MySQLdb,logging,threading,time,uuid
logging.basicConfig(level=logging.INFO,format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',datefmt='%a, %d %b %Y %H:%M:%S')

def next_id(t=None):
    '''
    Return next id as 50-char string.

    Args:
        t: unix timestamp, default to None and using time.time().
    '''
    if t is None:
        t = time.time()
    return '%015d%s000' % (int(t * 1000), uuid.uuid4().hex)

class Dict(dict):
    def __init__(self,names=(),values=(),**args):
        super(Dict,self).__init__(**args)
        for k,v in zip(names,values):
            self[k]=v
    def __getattr__(self, item):
        try:
            return self[item]
        except KeyError:
            raise AttributeError('it has no attribute named %s'%(str(item),))
    def __setattr__(self, key, value):
        self[key]=value

class dbcontext(threading.local):
    def __init__(self):
        self.db= MySQLdb.connect(host="localhost",port=3306,passwd='toor',user='root',db='xdyweb')
    def getdb(self):
        return self.db

class conntection(object):
    def __init__(self):
        self.dbctx=dbcontext()
        self.db=self.dbctx.getdb()
        self.cursor=self.db.cursor()
    def getcursor(self):
        return self.cursor
    def close(self):
        self.db.close()
        self.cursor=None
def _select(sql,first,*args):
    conn=conntection()
    csr=conn.getcursor()
    sql=sql.replace('?','%s')
    values=csr.execute(sql,*args)
    try:
        if csr.description:
            names=[x[0] for x in csr.description]
        if first:
            values=csr.fetchone()
            if values is None:
                return None
            return Dict(names,values)
        return  [Dict(names,x) for x in csr.fetchall()]
    finally:
        conn.close()

def select(sql,first,*args):
    return _select(sql,first,*args)
def select_one(sql,pk):
    return _select(sql,True,pk)

def update(sql,*args):
    r'''
    Execute update SQL.

    >>> u1 = dict(id=1000, name='Michael', email='michael@test.org', passwd='123456', last_modified=time.time())
    >>> insert('user', **u1)
    1
    >>> u2 = select_one('select * from user where id=?', 1000)
    >>> u2.email
    u'michael@test.org'
    >>> u2.passwd
    u'123456'
    >>> update('update user set email=?, passwd=? where id=?', 'michael@example.org', '654321', 1000)
    1
    >>> u3 = select_one('select * from user where id=?', 1000)
    >>> u3.email
    u'michael@example.org'
    >>> u3.passwd
    u'654321'
    >>> update('update user set passwd=? where id=?', '***', '123\' or id=\'456')
    0
    '''
    conn=conntection()
    csr=conn.getcursor()
    sql=sql.replace('?','%s')

    try:
        csr.execute(sql,args)
        return csr.rowcount
    finally:
        conn.close()
def insert(table,**kw):
    '''
    Execute insert SQL.

    >>> u1 = dict(id=2000, name='Bob', email='bob@test.org', passwd='bobobob', last_modified=time.time())
    >>> insert('user', **u1)
    1
    >>> u2 = select_one('select * from user where id=?', 2000)
    >>> u2.name
    u'Bob'
    >>> insert('user', **u2)
    Traceback (most recent call last):
      ...
    IntegrityError: 1062 (23000): Duplicate entry '2000' for key 'PRIMARY'
    '''
    cols, args = zip(*kw.iteritems())
    sql = 'insert into `%s` (%s) values (%s)' % (table, ','.join(['`%s`' % col for col in cols]), ','.join(['?' for i in range(len(cols))]))
    logging.info(sql)
    return update(sql,*args)

def main():
    # db=MySQLdb.connect(host="localhost",port=3306,passwd='toor',user='root',db='xdyweb')

    # conn=conntection()
    # c=conn.getcursor()
    # r=c.execute("select * from users where email=%s",('sss@sss.sss',))
    # logging.warning(r)

    # f=_select('select * from users where email =?',False,('sss@sss.sss',))
    # logging.info(f)
    # pass

    u1 = dict(id=2000, name='Bob', email='bob@test.org', passwd='bobobob', last_modified=time.time())
    r=insert('user', **u1)
    logging.info(r)
if __name__=="__main__":
    main()