class DatabasePool(object): INSTANCE = None def __init__(self, config): if self.INSTANCE is not None: raise ValueError("An instantiation already exists!") else: self.__cnxPool = Pool(host=config.db_host, port=config.db_port, user=config.db_user, password=config.db_password, db=config.db_name) self.__cnxPool.init() @classmethod def get_instance(cls, config): if cls.INSTANCE is None: cls.INSTANCE = DatabasePool(config) return cls.INSTANCE def get_connection(self): return self.__cnxPool.get_conn() @classmethod def pool_close(cls): cls.INSTANCE = None def __enter__(self): self.conn = self.__cnxPool.get_conn() return self.conn def __exit__(self, exc_type, exc_val, exc_tb): self.__cnxPool.release(self.conn)
class DatabasePool(SingleTonInstance): def __init__(self): self.logger = logging.getLogger(__name__) self.__cnxPool = Pool(host='localhost', port=3306, user='******', password='******', db='pyqt_erp_proj') self.__cnxPool.init() def __enter__(self): self.logger.debug('__enter__') self.conn = self.__cnxPool.get_conn() return self.conn def __exit__(self, exc_type, exc_val, exc_tb): self.logger.debug('__enter__') self.__cnxPool.release(self.conn)
return result @classmethod def delete_db(cls, querystring): connection = cls.pool.get_conn() cursor = connection.cursor() result = cursor.execute(querystring) cls.pool.release(connection) return result @classmethod def update_db(cls, querystring): pass pool = Pool(**_db_info) pool.init() connection = pool.get_conn() cur = connection.cursor() #result = cur.execute("insert into user values('a2da','adad','adadad')") #print(pool.get_pool_size()) cur.execute("select * from session;") print(cur.fetchone()) #print(result, type(result)) pool.release(connection)
class Database: def __init__(self, pooling=True, db_host=config.DATABASE_HOST, db_port=config.DATABASE_PORT, db_user=config.DATABASE_USER, db_password=config.DATABASE_PASSWORD, autocommit=False, db_name=config.DATABASE_NAME, cursor_class=pymysql.cursors.Cursor): self.last_n = None self.pool = None self.conn = None self.host = db_host self.port = db_port self.user = db_user self.password = db_password self.dbname = db_name self.pooling = pooling self.autocommit = autocommit self.cursor_class = cursor_class if pooling: self._init_pool() else: self._init_connection() def __del__(self): if not self.pooling and self.conn: self.conn.close() def _init_connection(self): self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.dbname, charset=config.DATABASE_CHARSET, cursorclass=self.cursor_class, autocommit=self.autocommit) def _init_pool(self): self.pool = Pool(host=self.host, port=self.port, user=self.user, password=self.password, db=self.dbname, charset=config.DATABASE_CHARSET, cursorclass=self.cursor_class, autocommit=self.autocommit, max_size=config.DATABASE_CONNECTION_POOL_SIZE) @contextmanager def connection(self, ping=False, do_not_begin=False, begin=False): if not self.pooling: if ping: self.conn.ping(reconnect=True) if begin or not do_not_begin and not self.autocommit: self.conn.begin() yield self.conn if begin or not do_not_begin and not self.autocommit: self.conn.commit() else: connection = self.pool.get_conn() try: self.conn = connection if ping: self.conn.ping(reconnect=True) if begin or not do_not_begin and not self.autocommit: connection.begin() yield connection if begin or not do_not_begin and not self.autocommit: connection.commit() self.conn = None finally: self.pool.release(connection) def _query(self, sql, args, query_func): with self.connection(ping=True) as connection: try: return query_func(connection, sql, args) except (InternalError, OperationalError) as e: code, _ = e.args connection.ping(reconnect=True) if code == 1927 or code == 1046: # Connection was killed or no database selected connection.select_db(self.dbname) return query_func(connection, sql, args) def query(self, sql, args=None): self.last_n, response = self._query(sql, args, query) return response def query_many(self, sql, args): self.last_n, response = self._query(sql, args, query_many) return response def create(self, if_not_exists=True, db_name=None): db_name = db_name or self.dbname if_clause = 'IF NOT EXISTS' if if_not_exists else '' return self.query(f'CREATE DATABASE {if_clause} {db_name};') def use(self, db_name): if not self.pooling: self.conn.select_db(db_name) self.dbname = db_name else: # the following line is there to raise an exception when user is denied access self.pool.get_conn().select_db(db_name) self.pool.destroy() self.dbname = db_name self._init_pool() def drop(self, if_exists=True, db_name=None): drop_db = db_name or self.dbname if_clause = 'IF EXISTS' if if_exists else '' return self.query(f'DROP DATABASE {if_clause} {drop_db};') def reset(self, if_exists=True, db_name=None): """ALL DATA IS LOST: Recreate database {db_name} or {self.dbname} with no tables. """ drop_db = db_name or self.dbname self.drop(if_exists=if_exists, db_name=drop_db) self.create(drop_db) if db_name == self.dbname: self.use(self.dbname) def databases(self): return [record[0] for record in self.query('SHOW DATABASES;')] def tables(self, db_name=None): db_name = db_name if db_name else self.dbname db_name = db_name if db_name else config.DATABASE_NAME if not db_name: raise ValueError( 'No database given (not even dbname was set in the config file).' ) return [ record[0] for record in self.query( f'SHOW TABLES FROM {config.DATABASE_NAME};') ] def setup(self, db_name=None): db_name = db_name if db_name else self.dbname db_name = db_name if db_name else config.DATABASE_NAME if not db_name: raise ValueError( 'No database given (not even dbname was set in the config file).' ) databases = self.databases() if db_name not in databases: self.create(if_not_exists=False, db_name=db_name)
class mySql: def __init__(self, host, port, user, password, database): self.pool = Pool(host=host, port=port, user=user, password=password, db=database) self.pool.init() def fetchone(self, sql, parama): newparama = [] news = [] for each in parama: if isinstance(each, list): newparama.extend(each) news.append(','.join(['%s'] * len(each))) else: newparama.append(each) news.append('%s') # for index,data in enumerate(news): # sql = sql.replace('%s', data, 1) sql = sql % tuple(news) connection = self.pool.get_conn() cur = connection.cursor() cur.execute(sql, args=tuple(newparama)) ret = cur.fetchone() self.pool.release(connection) return def fetchall(self, sql, parama): newparama = [] news = [] for each in parama: if isinstance(each, list): newparama.extend(each) news.append(','.join(['%s'] * len(each))) else: newparama.append(each) news.append('%s') # for index,data in enumerate(news): # sql = sql.replace('%s', data, 1) sql = sql % tuple(news) connection = self.pool.get_conn() cur = connection.cursor() cur.execute(sql, args=tuple(newparama)) ret = cur.fetchall() self.pool.release(connection) return ret def update(self, sql, parama): newparama = [] news = [] for each in parama: if isinstance(each, list): newparama.extend(each) news.append(','.join(['%s'] * len(each))) else: newparama.append(each) news.append('%s') sql = sql % tuple(news) print(sql) connection = self.pool.get_conn() cur = connection.cursor() cur.execute(sql, args=tuple(newparama)) connection.commit() self.pool.release(connection) def hp(): print('修改') print("sql = mySql('127.0.0.1', 3306, 'root', 'juiuijiju', 'mysql')") print("cmd = 'update student set name=%s where id=%s'") print("sql.update(cmd, ['修改', '3'])") print() print("插入") print("cmd = 'insert into student values(%s)'") print("sql.update(cmd, [['3', '三力士']])") print() print("查询") print("cmd = 'select * from student where id in (%s)'") print("sql.fetchall(cmd, [['1', '2']])") print("sql.fetchone(cmd, [['1', '2']])") print() print("删除") print("cmd = 'delete from student where id=%s'") print("sql.update(cmd, ['3',])")