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)
def connect_mysql():
# Connecting MySQL with connection pool
pool = Pool(host=env_config.db_host,
port=int(env_config.db_port),
user=env_config.db_username,
password=env_config.db_password,
db=env_config.db_name)
pool.init()
global connection
connection = pool.get_conn()
class MySQLDB:
def __init__(self):
with open("password.txt", "r") as f:
password = f.read().strip("\n")
self.pool = Pool(host="localhost",
user="******",
password=password,
db="discordb",
port=3306,
autocommit=True)
self.pool.init()
class MySQLTESTDB(MySQLDB):
def __init__(self):
self.pool = Pool(host="localhost",
user="******",
password="******",
db="discordb",
port=3307,
autocommit=True)
self.pool.init()
def rollback_transaction(self):
self.db.rollback()
def __init__(self, config):
mysql_section = config['mysql']
database = mysql_section['database']
host = mysql_section['host']
password = mysql_section['password']
user = mysql_section['user']
_pool = Pool(
cursorclass=pymysql.cursors.DictCursor,
database=database,
host=host,
password=password,
user=user,
)
_pool.init()
self._pool = _pool
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)
def create_conn():
try:
##https://github.com/egmkang/PyMySQLPool
##https://pypi.org/project/pymysql-pooling/
# cursor = pymysql.cursors.DictCursor
pool = Pool(host=mysql_host, \
port=int(mysql_port), \
user=mysql_user, \
password=mysql_password, \
database=mysql_db, \
charset='utf8')
pool.init()
connection = pool.get_conn()
cursor = connection.cursor()
cursor.execute(fn_sql)
result = cursor.fetchall()
return result
except pymysql.err.OperationalError as err:
print('create_conn ', err)
os._exit(1)
except:
print('MySQL Error create_conn')
os._exit(1)
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)
from pymysqlpool.pool import Pool # Для работы с сервером и БД
import pymysql
import datetime
bot = vk_botting.Bot(vk_botting.when_mentioned_or_pm(), case_insensitive=True)
config = {
'host': cred.host,
'user': cred.user,
'password': cred.password,
'db': cred.db,
'autocommit': True,
'charset': cred.charset,
'cursorclass': pymysql.cursors.DictCursor
}
try:
sqlpool = Pool(**config)
sqlpool.init()
except Exception as exc:
print(exc)
def sex_transform(sex):
if int(sex) == 1:
return 'м'
else:
return 'ж'
def mainmenu():
keyboard = vk_botting.Keyboard()
keyboard.add_button('Искать', vk_botting.KeyboardColor.PRIMARY)
keyboard.add_line()
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',])")
