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',])")