def create_pool(para, cursor_class=''):
"""
生成数据库连接池
:param para: 数据库配置参数
:param cursor_class:
:return:
"""
if cursor_class == "DictCursor":
return pools.Pool(dict(host=para['host'],
port=3306,
user=para['user'],
passwd=para['pwd'],
db=para['db'],
charset=para['charset'],
cursorclass=DictCursor),
max_idle_connections=10,
max_open_connections=100,
max_recycle_sec=3)
else:
return pools.Pool(dict(host=para['host'],
port=3306,
user=para['user'],
passwd=para['pwd'],
db=para['db'],
charset=para['charset']),
max_idle_connections=10,
max_open_connections=100,
max_recycle_sec=3)
def __init__(self, config):
self.POOL = pools.Pool(config,
max_idle_connections=1,
max_recycle_sec=3)
self.LOGIN = "******"
self.REGISTER = "INSERT INTO users(login, password, balance, task) VALUES (%s, %s, 0, 0)"
self.UPDATE_SCORE = "UPDATE users SET balance=%s, task=%s WHERE login=%s"
def __init__(self):
settings = dict(
static_path=os.path.join(os.path.dirname(__file__), "static"),
template_path=os.path.join(os.path.dirname(__file__), "templates"),
cookie_secret="ajkfdlkfkdsofidsofjohsdk;eoport",
autoescape=None,
xsrf_cookies=True,
debug=True,
login_url="/signin",
)
super(App, self).__init__(handlers, **settings)
self.db = MySQLdb.connect(host='127.0.0.1',
port=3306,
user='******',
passwd='1227401054',
db='mrp',
charset="utf8")
self.mysql_pool = pools.Pool(dict(host='127.0.0.1',
port=3306,
user='******',
passwd='1227401054',
db='mrp',
charset="utf8"),
max_idle_connections=5,
max_open_connections=10)
def main():
tornado.options.parse_command_line()
logging.getLogger('tornado.access').disabled = True
app = tornado.web.Application(handlers=[(r"/", IndexHandler)], debug=False)
http_server = tornado.httpserver.HTTPServer(app)
http_server.listen(options.port)
# http_server.bind(options.port)
# http_server.start(0)
# 很遗憾，上述代码不能在windows下运行，所以，tornado的多进程只能在linux底下运行。
# 把POOL 的定义放在这里，是因为POOL本身就会初始化一个eventloop。
POOL = pools.Pool(dict(host='127.0.0.1',
port=3306,
user='******',
passwd='',
db='mysql'),
max_idle_connections=10,
max_open_connections=90,
max_recycle_sec=3)
# asyncio.set_event_loop_policy(asyncio.SelectorEventLoop)
# 设置任何eventloop都报错，被windows搞得没脾气。
print(asyncio.get_event_loop_policy())
print(tornado.version)
tornado.ioloop.IOLoop.instance().start()
def init_mysqldb_conn():
pools.DEBUG = True
return pools.Pool(
settings['mysql_config'],
settings['mysql_max_idle_connections'],
settings['mysql_max_recycle_sec'],
)
def initMySQL(host='127.0.0.1',
port=3306,
username='',
password='',
db='mysql'):
""" 初始化mysql连接池
@param host MySQL数据库ip
@param port MySQL数据库端口
@param username MySQL数据库用户名
@param password MySQL数据库密码
@param db 需要连接的数据库名
"""
mysql_config = {
'host': host,
'port': port,
'user': username,
'passwd': password,
'db': db,
'cursorclass': cursors.DictCursor,
'charset': 'utf8'
}
logger.info('mysql_config:', mysql_config)
global CONN_POOL
CONN_POOL = pools.Pool(mysql_config,
max_idle_connections=1,
max_recycle_sec=3)
logger.info('create mysql connection pool.')
class DBHelper():
POOL = pools.Pool(dict(host=config.ws_mysql_ip,
port=config.ws_mysql_point,
user=config.ws_mysql_user,
passwd=config.ws_mysql_passwd,
db=config.ws_mysql_db,
charset=config.ws_mysql_charset),
max_idle_connections=1,
max_recycle_sec=3)
def __init__(self):
pass
# 数据库操作---插入数据
async def execute(self, sql, params=None):
cur = await self.POOL.execute(sql) # self.cur.execute(sql, params)
return True
# 插入一条语句返回自增id
async def getinsertdata_id(self, sql):
cur = await self.POOL.execute(sql) #self.cur.execute(sql)
last_id = self.cur.lastrowid
return last_id
#查询返回结果
async def Seldata(self, sql):
cur = await self.POOL.execute(sql)
return cur.fetchone()
def __init__(self):
self._pool = pools.Pool(dict(host=MYSQL_DB_HOST,
port=MYSQL_DB_PORT,
user=MYSQL_DB_USER,
passwd=MYSQL_DB_PASSWORD,
db=MYSQL_DB_DATABASE),
max_idle_connections=1,
max_recycle_sec=3)
def get_mysql_instance():
return pools.Pool(
dict(host=Mysql_config['host'],
port=Mysql_config['port'],
user=Mysql_config['user'],
password=Mysql_config['password'],
db=Mysql_config['database'],
charset='utf8'))
def init():
ecloud_db_pool = pools.Pool(
get_conn(CONF.database.db_local),
max_idle_connections=CONF.database.max_idle_connections,
max_recycle_sec=CONF.database.max_recycle_sec)
__DB_POOLS[LOCAL_DB] = ecloud_db_pool
common_db_pool = pools.Pool(
get_conn(CONF.database.db_global),
max_idle_connections=CONF.database.max_idle_connections,
max_recycle_sec=CONF.database.max_recycle_sec)
__DB_POOLS[COMMON_DB] = common_db_pool
nova_db_pool = pools.Pool(
get_conn(CONF.database.db_nova),
max_idle_connections=CONF.database.max_idle_connections,
max_recycle_sec=CONF.database.max_recycle_sec)
__DB_POOLS[NOVA_DB] = nova_db_pool
cinder_db_pool = pools.Pool(
get_conn(CONF.database.db_cinder),
max_idle_connections=CONF.database.max_idle_connections,
max_recycle_sec=CONF.database.max_recycle_sec)
__DB_POOLS[CINDER_DB] = cinder_db_pool
keystone_db_pool = pools.Pool(
get_conn(CONF.database.db_keystone),
max_idle_connections=CONF.database.max_idle_connections,
max_recycle_sec=CONF.database.max_recycle_sec)
__DB_POOLS[KEYSTONE_DB] = keystone_db_pool
glance_db_pool = pools.Pool(
get_conn(CONF.database.db_glance),
max_idle_connections=CONF.database.max_idle_connections,
max_recycle_sec=CONF.database.max_recycle_sec)
__DB_POOLS[GLANCE_DB] = glance_db_pool
neutron_db_pool = pools.Pool(
get_conn(CONF.database.db_neutron),
max_idle_connections=CONF.database.max_idle_connections,
max_recycle_sec=CONF.database.max_recycle_sec)
__DB_POOLS[NEUTRON_DB] = neutron_db_pool
redis_conn = get_conn(CONF.database.db_redis)
redis_db_pool = redis.ConnectionPool(host=redis_conn['host'],
port=redis_conn['port'],
db=redis_conn['db'])
__DB_POOLS[REDIS_DB] = redis_db_pool
if CONF.ldap.enable:
au = "CN=%s,%s" % (CONF.ldap.auth_user, CONF.ldap.base_dn)
url = "ldap://%s:389" % CONF.ldap.auth_domain
e_ldap_pool = ldappool.ConnectionManager(url,
au,
CONF.ldap.auth_pass,
use_pool=True,
size=2)
__DB_POOLS[E_LDAP] = e_ldap_pool
def connect(self, host, dbname):
self.mysql_pool = pools.Pool(dict(host=host,
port=3306,
user="******",
passwd="19931122",
db=dbname),
max_idle_connections=5,
max_open_connections=10,
max_recycle_sec=5)
print(self.mysql_pool._get_conn())
def _create_mysql_pool(cls):
MySQLPOOL._pool = pools.Pool(
dict(host=settings.MYSQL_DB_HOST,
port=settings.MYSQL_DB_PORT,
user=settings.MYSQL_DB_USER,
passwd=settings.MYSQL_DB_PASSWORD,
db=settings.MYSQL_DB_DBNAME,
cursorclass=MyDictCursor,
charset=settings.MYSQL_CHARSET),
max_recycle_sec=settings.MYSQL_MAX_RECYCLE_SEC,
max_idle_connections=settings.MYSQL_IDLE_CONNECTIONS)
def __init__(self, mysql_host, mysql_port, mysql_uid, mysql_pwd, mysql_db):
self.db = pools.Pool(dict(host=mysql_host,
port=mysql_port,
user=mysql_uid,
passwd=mysql_pwd,
db=mysql_db,
charset='utf8mb4'),
max_idle_connections=2,
max_open_connections=10,
max_recycle_sec=3)
def __init__(self):
self.pool = pools.Pool(dict(host=settings['mysql_host'],
port=settings['mysql_port'],
user=settings['mysql_user'],
passwd=settings['mysql_password'],
db=settings['mysql_database'],
cursorclass=cursors.DictCursor,
charset='utf8mb4'),
max_idle_connections=16,
max_recycle_sec=120)
self.logger = logger
def make_pool():
"""
配置了数据库，并且穿进去了路由(默认返回default),通过make_poll 建立各个数据库的连接池，需要放在构造的表模型前面
:return: None
"""
try:
for k, v in _SQL_SETTING.items():
_DB_CONNECTION[k] = pools.Pool(v['connect_kwargs'], **v['other_kwargs'])
return True
except Exception as e:
logging.error("连接失败：{}".format(e))
return False
class GetUserHandler(web.RequestHandler):
POOL = pools.Pool(connParam, max_idle_connections=1, max_recycle_sec=3)
@gen.coroutine
def get(self):
userid = self.get_argument('id')
cursor = yield self.POOL.execute('select name from user where id=%s',
userid)
if cursor.rowcount > 0:
self.write({'status': 1, 'name': cursor.fetchone()[0]})
else:
self.write({'status': 0, 'name': ''})
self.finish()
def db_pool_init():
POOL = pools.Pool(
dict(
host = config['database']['host'],
port = config['database']['port'],
user = config['database']['user'],
passwd = config['database']['passwd'],
db = config['database']['db'],
charset = config['database']['charset'],
cursorclass = config['database']['cursorclass'],
),
max_idle_connections = config['database']['max_idle_connections'],
max_recycle_sec = config['database']['max_recycle_sec'])
return POOL
def __init__(self):
options = {
r'max_idle_connections': config.Static.MySqlMaxIdleConn,
r'max_open_connections': config.Static.MySqlMaxOpenConn,
}
self._m_pool = pools.Pool(
{
r'host': config.Static.MySqlMaster[0],
r'port': config.Static.MySqlMaster[1],
r'user': config.Static.MySqlUser,
r'passwd': config.Static.MySqlPasswd,
r'db': config.Static.MySqlName
}, **options)
self._s_pool = pools.Pool(
{
r'host': config.Static.MySqlSlave[0],
r'port': config.Static.MySqlSlave[1],
r'user': config.Static.MySqlUser,
r'passwd': config.Static.MySqlPasswd,
r'db': config.Static.MySqlName
}, **options)
def __init__(self):
settings = dict(template_path=os.path.join(os.path.dirname(__file__),
"templates"), )
self.db = pools.Pool(
dict(host=options.db_host,
port=options.db_port,
user=options.db_user,
passwd=options.db_password,
db=options.db_name,
cursorclass=DictCursor,
charset='utf8'),
max_idle_connections=1,
max_recycle_sec=3,
max_open_connections=2,
)
tornado.web.Application.__init__(self, handlers, **settings)
def __init__(self):
handlers = urls
redis_pool = redis.ConnectionPool(host=CACHES['HOST'],
password=CACHES['PASSWORD'],
port=CACHES['PORT'])
self.cache = RedisCacheBackend(
redis.Redis(connection_pool=redis_pool, charset="utf-8"))
super(App, self).__init__(handlers=handlers, **settings_dict)
self.pool = pools.Pool(dict(host=host,
user=user,
passwd=password,
db=database,
port=port,
charset="utf8mb4"),
max_open_connections=1000,
max_recycle_sec=50)
def __init__(self):
handlers = [(r"/", HomeHandler),
(r"/propagation/([%a-fA-F0-9]+)", QCodeHandler),
(r".*", BaseHandler)]
template_path = os.path.join(os.path.dirname(__file__), "templates")
static_path = os.path.join(os.path.dirname(__file__), "static")
self.db = pools.Pool(connParam,
max_idle_connections=5,
max_recycle_sec=3)
web.Application.__init__(
self,
handlers=handlers,
autoreload=True,
template_path=template_path,
static_path=static_path,
#debug=True
)
class GetUserHandler(web.RequestHandler):
POOL = pools.Pool(
connParam,
max_idle_connections=1,
max_recycle_sec=3,
)
@gen.coroutine
def get(self):
userid = self.get_argument('id')
cursor = yield self.POOL.execute(
"""select name from user where id = {}""".format(userid))
if cursor.rowcount > 0:
self.write({"status": 1, "name": cursor.fetchone()[0]})
else:
self.write({"status": 0, "name": ""})
self.finish()
def __init__(self):
if self.__connection_pool is None:
self.__connection_pool = pools.Pool(
dict(
host=MYSQL_HOST,
user=MYSQL_USR,
passwd=MYSQL_PWD,
db=MYSQL_DB,
charset=MYSQL_CHARSET,
),
max_idle_connections=POOL_MAX_IDLE_CONNECTIONS,
max_recycle_sec=POOL_MAX_RECYCLE_SEC,
max_open_connections=POOL_MAX_OPEN_CONNECTIONS,
)
logging.info("~~~~~ CONNECTION_POOL INIT: ......")
else:
logging.debug("~~~~~ CONNECTION_POOL: has inited")
def connPool():
global dbConnPool
if dbConnPool == None:
configParser = ConfigParser.RawConfigParser()
configDir = os.path.split(os.path.realpath(__file__))[0]
configFile = configDir + "/db.conf"
configParser.read(configFile)
dbConfig = dict(configParser.items('config'))
dbConnPool = pools.Pool(dict(host=dbConfig['host'],
port=int(dbConfig['port']),
db=dbConfig['name'],
user=dbConfig['user'],
passwd=dbConfig['passwd']),
max_idle_connections=1,
max_recycle_sec=8,
max_open_connections=2)
return dbConnPool
def __getConn(self, dbname):
"""
@return MySQLdb.connection
"""
TornadoMysqlUtils.__con_age[dbname] = datetime.datetime.now()
if dbname not in self.__pool or not self.__pool[dbname]:
TornadoMysqlUtils.__pool[dbname] = pools.Pool(
dict(host=self.db_host,
port=self.db_port,
user=self.db_user,
passwd=self.db_passwd,
db=dbname,
use_unicode=False,
charset='utf8'),
max_idle_connections=3,
)
return self.__pool[dbname]
def _get_connection(self):
setting = _db_settings.get(self.target)
conn = None
if not USE_POOL:
conn = yield tornado_mysql.connect(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset'))
else:
conn = pools.Pool(dict(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset')),
max_idle_connections=1,
max_recycle_sec=3)
return conn
def Execute(target, sql, params):
setting = _db_settings.get(target)
conn = None
if not USE_POOL:
conn = yield tornado_mysql.connect(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset'))
else:
conn = pools.Pool(dict(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset')),
max_idle_connections=1,
max_recycle_sec=3)
result = 0
if USE_POOL:
cur = yield conn.execute(sql, params)
result = cur.rowcount
else:
cur = conn.cursor()
try:
result = yield cur.execute(sql, params)
yield conn.commit()
except:
raise
finally:
cur.close()
conn.close()
return result
def Select(target, sql, params):
setting = _db_settings.get(target)
conn = None
if not USE_POOL:
conn = yield tornado_mysql.connect(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset'))
else:
conn = pools.Pool(dict(host=setting.get('host'),
port=setting.get('port'),
user=setting.get('user'),
passwd=setting.get('password'),
db=setting.get('db'),
charset=setting.get('charset')),
max_idle_connections=1,
max_recycle_sec=3)
if USE_POOL:
cur = yield conn.execute(sql, params)
else:
cur = conn.cursor()
try:
yield cur.execute(sql, params)
except:
raise
finally:
cur.close()
conn.close()
rows = cur.fetchall()
column_names = [d[0] for d in cur.description]
result = [Row(zip(column_names, row)) for row in rows]
return result
import logging
import os
from tornado_mysql import pools
from tornado_mysql.cursors import DictCursor
import tornadoredis
__author__ = 'qingfeng'
pools.DEBUG = False
POOL = pools.Pool(
dict(host=os.getenv("DB_HOST", "localhost"),
port=int(os.getenv("DB_PORT", "3306")),
user=os.getenv("DB_USER", "root"),
passwd=os.getenv("DB_PASS", "toor"),
db=os.getenv("DB_NAME", "eleme"),
cursorclass=DictCursor),
max_idle_connections=2000,
max_recycle_sec=3,
max_open_connections=5000,
)
REDIS_CONNECTION_POOL = tornadoredis.ConnectionPool(max_connections=3000,
wait_for_available=True)
from __future__ import print_function
from tornado import ioloop, gen
from tornado_mysql import pools
pools.DEBUG = True
POOL = pools.Pool(dict(host='127.0.0.1',
port=3306,
user='******',
passwd='',
db='mysql'),
max_idle_connections=1,
max_recycle_sec=3)
@gen.coroutine
def worker(n):
for _ in range(10):
t = 1
print(n, "sleeping", t, "seconds")
cur = yield POOL.execute("SELECT SLEEP(%s)", (t, ))
print(n, cur.fetchall())
@gen.coroutine
def main():
workers = [worker(i) for i in range(10)]
yield workers