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)