多线程多进程使用sqlite有点麻烦 对速度和性能又没有要求很高
所以就用了一下xmlrpc
服务端
from xmlrpc.server import SimpleXMLRPCServer from xmlrpc.server import SimpleXMLRPCRequestHandler import sqlite3 def dict_factory(cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d class SQL(): def __init__(self): self.__conn = sqlite3.connect("cash.db") def query(self,sql,arg,size=0): self.__conn.row_factory = dict_factory cur= self.__conn.cursor() cur.execute(sql,arg) if size !=0: rs = cur.fetchmany(size) else: rs = cur.fetchall() self.__conn.commit() return rs def update(self,sql,arg): self.__conn.row_factory = dict_factory cur= self.__conn.cursor() cur.execute(sql,arg) result = cur.rowcount self.__conn.commit() return result class RequestHandler(SimpleXMLRPCRequestHandler): rpc_paths = ('/RPC2',) if __name__ == '__main__': with SimpleXMLRPCServer(('localhost', 8887), requestHandler=RequestHandler,logRequests=True) as server: server.register_introspection_functions() server.register_instance(SQL()) server.serve_forever()
客户端
import xmlrpc.client import time import logging class Db(): def __init__(self): self.conn = xmlrpc.client.ServerProxy('http://localhost:8887') def query(self,sql,args,size=0): start = time.clock() rs= self.conn.query(sql,args,size) s = time.clock() - start if s > 0.005: logging.warning(f"SQL:{sql},args:{args},size:{size},run:{s}s") return rs def update(self,sql,args): start = time.clock() rs = self.conn.update(sql,args) s =time.clock() - start if s > 0.005: logging.warning(f"SQL:{sql},args:{args},run:{s}s") return rs s = Db()
orm
import datetime import logging import time import uuid from utils.sqliteClient import s def log(sql, args=()): logging.info('SQL: %s' % sql) class MyDb(): def __init__(self): self.pool = s def select(self,sql, args, size=0): log(sql, args) rs = self.pool.query(sql,args,size) logging.info('rows returned: %s' % len(rs)) return rs def execute(self,sql, args): log(sql) affected = 0 try: affected = self.pool.update(sql,args) except BaseException as e: log(e) return affected db = MyDb() def next_id(): return '%015d%s000' % (int(time.time() * 1000), uuid.uuid4().hex) def create_args_string(num): L = [] for n in range(num): L.append('?') return ', '.join(L) class Field(object): def __init__(self, name, column_type, primary_key, default): self.name = name self.column_type = column_type self.primary_key = primary_key self.default = default def __str__(self): return '<%s, %s:%s>' % (self.__class__.__name__, self.column_type, self.name) class StringField(Field): def __init__(self, name=None, primary_key=False, default=None, ddl='varchar(100)'): super().__init__(name, ddl, primary_key, default) class BooleanField(Field): def __init__(self, name=None, default=False): super().__init__(name, 'boolean', False, default) class IntegerField(Field): def __init__(self, name=None, primary_key=False, default=0): super().__init__(name, 'bigint', primary_key, default) class FloatField(Field): def __init__(self, name=None, primary_key=False, default=0.0): super().__init__(name, 'real', primary_key, default) class TextField(Field): def __init__(self, name=None, default=None): super().__init__(name, 'text', False, default) class DateTimeField(Field): def __init__(self, name=None, default=None): super().__init__(name, 'timestamp', False, default) class ModelMetaclass(type): def __new__(cls, name, bases, attrs): if name=='Model': return type.__new__(cls, name, bases, attrs) tableName = attrs.get('__table__', None) or name logging.info('found model: %s (table: %s)' % (name, tableName)) mappings = dict() fields = [] primaryKey = None for k, v in attrs.items(): if isinstance(v, Field): logging.info(' found mapping: %s ==> %s' % (k, v)) mappings[k] = v if v.primary_key: # 找到主键: if primaryKey: raise AttributeError('Duplicate primary key for field: %s' % k) primaryKey = k else: fields.append(k) if not primaryKey: raise AttributeError('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) class Model(dict, metaclass=ModelMetaclass): def __init__(self, **kw): super(Model, self).__init__(**kw) def __getattr__(self, key): try: return self[key] except KeyError: raise AttributeError(r"'Model' object has no attribute '%s'" % key) def __setattr__(self, key, value): self[key] = value def getValue(self, key): return getattr(self, key, None) def getValueOrDefault(self, key): value = getattr(self, key, None) if value is None: field = self.__mappings__[key] if field.default is not None: value = field.default() if callable(field.default) else field.default logging.debug('using default value for %s: %s' % (key, str(value))) setattr(self, key, value) return value @classmethod def findAll(cls, where=None, args=None, **kw): ' find objects by where clause. ' sql = [cls.__select__] if where: sql.append('where') sql.append(where) if args is None: args = [] orderBy = kw.get('orderBy', None) if orderBy: sql.append('order by') sql.append(orderBy) limit = kw.get('limit', None) if limit is not None: sql.append('limit') if isinstance(limit, int): sql.append('?') args.append(limit) elif isinstance(limit, tuple) and len(limit) == 2: sql.append('?, ?') args.extend(limit) else: raise ValueError('Invalid limit value: %s' % str(limit)) rs = db.select(' '.join(sql), args) return [cls(**r) for r in rs] @classmethod def query(cls, sql=None, args=()): rs = db.select(sql, args) return [cls(**r) for r in rs] @classmethod def findNumber(cls, selectField, where=None, args=None): ' find number by select and where. ' sql = ['select %s _num_ from `%s`' % (selectField, cls.__table__)] if where: sql.append('where') sql.append(where) rs = db.select(' '.join(sql), args, 1) if len(rs) == 0: return None return rs[0]['_num_'] @classmethod def find(cls, pk): ' find object by primary key. ' rs = db.select('%s where `%s`=?' % (cls.__select__, cls.__primary_key__), [pk], 1) if len(rs) == 0: return None return cls(**rs[0]) def save(self): args = list(map(self.getValueOrDefault, self.__fields__)) args.append(self.getValueOrDefault(self.__primary_key__)) rows = db.execute(self.__insert__, args) if rows != 1: logging.error('failed to insert record: affected rows: %s' % rows) def update(self): args = list(map(self.getValue, self.__fields__)) args.append(self.getValue(self.__primary_key__)) rows = db.execute(self.__update__, args) if rows != 1: logging.error('failed to update by primary key: affected rows: %s' % rows) def remove(self): args = [self.getValue(self.__primary_key__)] rows = db.execute(self.__delete__, args) if rows != 1: logging.error('failed to remove by primary key: affected rows: %s' % rows) def timestamp_datetime(ts): if isinstance(ts, (int, float, str)): try: ts = int(ts) except ValueError: raise if len(str(ts)) == 13: ts = int(ts / 1000) if len(str(ts)) != 10: raise ValueError else: raise ValueError() return datetime.datetime.fromtimestamp(ts)
简单的测试
from utils.orm import Model,StringField,TextField,next_id,DateTimeField,IntegerField,timestamp_datetime from datetime import datetime class UserBean(Model): __table__ = 'fish_users' id = StringField(primary_key=True,default=next_id(),ddl="char(50)") username = StringField(ddl="char(200)") name = StringField(ddl="char(200)",default="") passwd = StringField(ddl="char(200)") create_date = DateTimeField(default=datetime.now().timestamp()) token = StringField(default="token") last_login = DateTimeField(default=datetime.now().timestamp()) is_online = IntegerField(default=0)
还存在的问题
时间类型序列化还存在问题
是否语序None存在不稳定因素
但是已经可以凑活用了