def __new__(cls, name, bases, attrs): if name=='Model': return type.__new__(cls, name, bases, attrs) tableName = attrs.get('__table__', None) or name sqllog.info('found model: %s (table: %s)' % (name, tableName)) mappings = dict() fields = [] primaryKey = None for k, v in attrs.items(): if isinstance(v, Field): sqllog.info(' found mapping: %s ==> %s' % (k, v)) mappings[k] = v if v.primary_key: # 找到主键: if primaryKey: raise Exception('Duplicate primary key for field: %s' % k) primaryKey = k else: fields.append(k) if not primaryKey: raise Exception('Primary key not found.') for k in mappings.keys(): attrs.pop(k) escaped_fields = list(map(lambda f: '`%s`' % f, fields)) attrs['__mappings__'] = mappings # 保存属性和列的映射关系 attrs['__table__'] = tableName attrs['__primary_key__'] = primaryKey # 主键属性名 attrs['__fields__'] = fields # 除主键外的属性名 attrs['__select__'] = 'select `%s`, %s from `%s`' % (primaryKey, ', '.join(escaped_fields), tableName) attrs['__insert__'] = 'insert into `%s` (%s, `%s`) values (%s)' % (tableName, ', '.join(escaped_fields), primaryKey, create_args_string(len(escaped_fields) + 1)) attrs['__update__'] = 'update `%s` set %s where `%s`=?' % (tableName, ', '.join(map(lambda f: '`%s`=?' % (mappings.get(f).name or f), fields)), primaryKey) attrs['__delete__'] = 'delete from `%s` where `%s`=?' % (tableName, primaryKey) return type.__new__(cls, name, bases, attrs)
def create_dbconnect(**kw): sqllog.info('create database connection ...') global conn conn = pymysql.connect(host=kw.get('host', 'localhost'), user=kw['user'], password=kw['pasword'], database=kw['db'], charset=kw.get('charset', 'utf8'))
def select(sql, args, size=None): log(sql, args) global conn cursor = conn.cursor() cursor.execute(sql.replace('?', '%s'), args or ()) if size: rs = cursor.fetchmany(size) else: rs = cursor.fetchall() sqllog.info('rows returned: %s' % len(rs)) return rs
async def select(sql, args, size=None): log(sql, args) global __pool async with __pool.get() as conn: async with conn.cursor(aiomysql.DictCursor) as cur: await cur.execute(sql.replace('?', '%s'), args or ()) if size: rs = await cur.fetchmany(size) else: rs = await cur.fetchall() sqllog.info('rows returned: %s' % len(rs)) return rs
def select(self): cursor = self.conn.cursor() sqllog.info('selcet from db') sql = """select * from xxx ;""" try: cursor.execute(sql) req = cursor.fetchall() table_key = cursor.description cursor.close() self.conn.close() return req, table_key except: self.conn.close() sqllog.error("unable to fecth data, please check the sql :" + sql) return None, None
async def create_pool(loop, **kw): sqllog.info('create database connection pool...') global __pool __pool = await aiomysql.create_pool( host=kw.get('host', 'localhost'), port=kw.get('port', 3306), user=kw['user'], password=kw['password'], db=kw['db'], charset=kw.get('charset', 'utf8'), autocommit=kw.get('autocommit', True), maxsize=kw.get('maxsize', 10), minsize=kw.get('minsize', 1), loop=loop )
def log(sql, args=()): sqllog.info('SQL: %s' % sql)
def delete(self): cursor = self.conn.cursor() sqllog.info('delete from db') sql = """delete * from xxx ;""" self.execute(cursor, sql)
def insert(self): cursor = self.conn.cursor() sqllog.info('insert to db') sql = """insert into tablename (colsName) values(values)""" self.execute(cursor, sql)
def update(self, **kwargs): sqllog.info('update data') cursor = self.conn.cursor() args = (kwargs['result'], kwargs['id']) sql = """update mc_conbin_testreq_detail set result=%s, where id=%s; """ self.execute(cursor, sql, args)