def __init__(self):
super(MySQLHelper, self).__init__()
self.pool = tormysql.ConnectionPool(max_connections=150,
idle_seconds=7200,
wait_connection_timeout=3,
charset="utf8",
**config.database_options)
def _conn():
# 创建异步连接
pool = tormysql.ConnectionPool(
max_connections=20, # max open connections
idle_seconds=7200, # connection idle timeout time, 0 is not timeout
wait_connection_timeout=3, # wait connection timeout
host="127.0.0.1",
user="******",
passwd="TEST",
db="test",
charset="utf8"
)
with (yield pool.Connection()) as conn:
try:
with conn.cursor() as cursor:
yield cursor.execute("INSERT INTO test(id) VALUES(1)")
except Exception as e:
print(e)
yield conn.rollback()
else:
yield conn.commit()
with conn.cursor() as cursor:
yield cursor.execute("SELECT * FROM test")
data = cursor.fetchall()
print(data)
yield pool.close()
def _connect(self):
conn_kwargs = {
'charset': 'utf8',
'use_unicode': True,
}
conn_kwargs.update(self.connect_params)
if 'password' in conn_kwargs:
conn_kwargs['passwd'] = conn_kwargs.pop('password')
return tormysql.ConnectionPool(db=self.database, **conn_kwargs)
def __gen_db_conn(self):
pool = tormysql.ConnectionPool(
max_connections=self.config_instance['maxsize'],
debug_connection_used=self.config_instance['debug'],
host=self.config_instance['host'],
database=self.config_instance['db'],
user=self.config_instance['user'],
password=self.config_instance['password'],
charset=self.config_instance['charset'])
return pool
def init(self):
self.pool = tormysql.ConnectionPool(
max_connections=20, # max open connections
idle_seconds=7200, # conntion idle timeout time, 0 is not timeout
wait_connection_timeout=3, # wait connection timeout
host=self.host,
user=self.db_user,
passwd=self.db_password,
db=self.db,
charset="utf8",
cursorclass=tormysql.cursor.OriginDictCursor)
def __init__(self, host=None, user=None, password=None, db=None,
charset='utf8', size=None):
"""初始化连接池
"""
self._pool = tormysql.ConnectionPool(
max_connections=size,
idle_seconds=7200,
wait_connection_timeout=3,
host=host, user=user, passwd=password, db=db, charset=charset,
cursorclass=pymysql.cursors.DictCursor)
self.ioloop = IOLoop.instance()
def __init__(self):
"insert into coupon_info values(" "," "," "," "," "," "," ");"
self.pool = tormysql.ConnectionPool(
max_connections=500,
idle_seconds=7500,
wait_connection_timeout=600,
host="127.0.0.1",
user="******",
passwd="",
db="eesy",
charset="utf8",
cursorclass=pymysql.cursors.DictCursor)
def _reset_db_connection(self, db_type):
if not db_type in self.db_dict:
raise Exception("Unknown db_type: %s" % db_type)
if self.db_dict[db_type]: self.db_dict[db_type].close()
#针对每个db_type,创建一个连接池，连接池最大数目由max_connections指定，连接按需建立，长时间闲置则会自动断开（由idle_seconds指定）
self.db_dict[db_type] = tormysql.ConnectionPool(
max_connections=100, #max open connections
idle_seconds=720, #conntion idle timeout time, 0 is not timeout
wait_connection_timeout=10, #wait connection timeout
host=self.config.get(db_type, 'host'),
user=self.config.get(db_type, 'user'),
passwd=self.config.get(db_type, 'passwd'),
db=self.config.get(db_type, 'db'),
charset="utf8")
def __init__(self):
self.__dict__ = self.__shared_state
try:
self.mariadbConnectionPool = tormysql.ConnectionPool(
max_connections=20, #max open connections
idle_seconds=7200, #conntion idle timeout time, 0 is not timeout
wait_connection_timeout=3, #wait connection timeout
host="127.0.0.1",
user="******",
passwd="manishs",
db="akila",
charset="utf8")
except mariadb.Error as error:
#TOFO LOG error handle error
print("Error: {}".format(error))
def __init__(self, sql):
try:
dbpool = tormysql.ConnectionPool(
max_connections=20, # max open connections
idle_seconds=
7200, # conntion idle timeout time, 0 is not timeout
wait_connection_timeout=3, # wait connection timeout
host=dbhost,
user=dbuser,
passwd=dbpass,
db=dbname,
charset="utf8mb4")
self.sql = sql
self.dbpool = dbpool
except Exception as e:
print('数据库连接失败!,$s' % e)
def __init__(self, db=None):
if db:
_db = db
else:
_db = options.mysql_database
self.pool = tormysql.ConnectionPool(max_connections=20,
host=options.mysql_host,
port=options.mysql_port,
user=options.mysql_user,
password=options.mysql_password,
db=_db,
autocommit=True,
charset='utf8',
idle_seconds=1800)
super(AsyncMysqlClient, self).__init__()
def _DoConnect(self):
if self.pConnectPool != None:
return True
try:
self.pConnectPool = tormysql.ConnectionPool(
max_connections=self.iMaxConnections, # max open connections
idle_seconds=0, # conntion idle timeout time, 0 is not timeout
wait_connection_timeout=self.iConnectTimeOut, # wait connection timeout
host=self.szHost,
user=self.szUser,
passwd=self.szPassword,
db=self.szDbName,
charset="utf8",
autocommit=True
)
except:
return False
return True
def connect_database(self):
"""数据库连接方法.
return:
pool: 返回连接池
"""
logger.info("Connect to MySQL server")
pool = tormysql.ConnectionPool(
host=self.host,
port=int(self.port),
user=self.user,
passwd=self.passwd,
db=self.db,
charset=self.charset,
max_connections=int(self.max_connections),
idle_seconds=int(self.idle_seconds),
wait_connection_timeout=int(self.wait_connection_timeout))
return pool
def __init__(self):
super(AsyncMysqlClient, self).__init__()
self._db_name = ccstv4new['db']
self._host = ccstv4new['host']
self._port = ccstv4new['port']
self._user_name = ccstv4new['user']
self._password = ccstv4new['passwd']
self._max_conn = ccstv4new['max_conn']
self._conn_time_out = ccstv4new['connect_timeout']
self._db = None
self._client = None
self.pool = tormysql.ConnectionPool(
max_connections=self._max_conn, # max open connections
idle_seconds=7200, # conntion idle timeout time, 0 is not timeout
wait_connection_timeout=self.
_conn_time_out, # wait connection timeout
host=self._host,
port=self._port,
user=self._user_name,
passwd=self._password,
db=self._db_name,
charset="utf8")
def __init__(self, **settings):
self._db_name = settings['database']
self._host = settings['ip']
self._port = settings['port']
self._user_name = settings['user']
self._password = settings['password']
self._max_conn = settings['conn_max']
self._conn_time_out = settings['conn_time_out']
self._charset = settings['charset']
self._db = None
self._client = None
self.pool = tormysql.ConnectionPool(
max_connections=self._max_conn, # max open connections
idle_seconds=7200, # connection idle timeout time, 0 is not timeout
wait_connection_timeout=self._conn_time_out, # wait connection timeout
host=self._host,
port=self._port,
user=self._user_name,
passwd=self._password,
db=self._db_name,
charset=self._charset
)
def __init__(self, config):
# """
# :param app: Tornado Application
# """
# if not app:
# raise ValueError('Tornado Application should not be None')
# if not hasattr(app, 'config'):
# raise ValueError('Tornado Application should has "config" attr')
# self._app = app
# self._config = app.config
self._config = config
self._logger = utils.get_logger(self._config.DEBUG,
self._config.MYSQL_LOGGER,
self._config.TORMYSQL_LOG)
self._pool = tormysql.ConnectionPool(
max_connections=self._config.MAX_CONNECTION,
idle_seconds=self._config.IDLE_SECONDS,
wait_connection_timeout=self._config.WAIT_TIMEOUT_SECONDS,
host=self._config.TORMYSQL_HOST,
user=self._config.TORMYSQL_USER,
passwd=self._config.TORMYSQL_PASSWORD,
db=self._config.TORMYSQL_DB,
charset=self._config.DEFAULTCHARSET,
)
# -*- coding: utf-8 -*-
#!/usr/bin/env python
from tornado.ioloop import IOLoop
from tornado import gen
from tornado.concurrent import Future
import tormysql
import uuid
pool = tormysql.ConnectionPool(
max_connections = 20, #max open connections
idle_seconds = 7200, #conntion idle timeout time, 0 is not timeout
wait_connection_timeout = 3, #wait connection timeout
host = "127.0.0.1",
user = "******",
passwd = "000000",
db = "zi",
charset = "utf8"
)
@gen.coroutine
def test():
with (yield pool.Connection()) as conn:
try:
with conn.cursor() as cursor:
yield cursor.execute("INSERT INTO ds_group VALUES('"+str(uuid.uuid1())+"','123','1')")
except:
yield conn.rollback()
else:
yield conn.commit()
# coding=utf-8
import pymysql
import tormysql
app_pool = tormysql.ConnectionPool(
max_connections=80, # max open connections
idle_seconds=180, # conntion idle timeout time, 0 is not timeout 连接空闲超过idle_seconds 后会自动关闭回收连接的
wait_connection_timeout=3, # wait connection timeout
host="127.0.0.1",
user="******",
passwd="app_pass",
db="myapp",
charset="utf8",
cursorclass=pymysql.cursors.DictCursor,
)
class ORM:
"""数据库接口类"""
host = config.mydb["host"]
user = config.mydb["user"]
password = config.mydb["password"]
dbbase = config.mydb["dbname"]
pool = tormysql.ConnectionPool(
# max_connections=100, # max open connections
max_connections=500, # max open connections
idle_seconds=7500, # conntion idle timeout time, 0 is not timeout
wait_connection_timeout=3, # wait connection timeout
host=host,
user=user,
passwd=password,
db=dbbase,
charset="utf8",
cursorclass=pymysql.cursors.DictCursor # 获取的是字典形式， 没有这句获取的是元组
)
def __init__(self, column):
self.column = column
@classmethod
@coroutine
def get(cls, sets=list(), where=list()):
with (yield ORM.pool.Connection()) as conn:
fields = ''
for field in sets:
fields += field
fields += ","
fields = fields[0:-1]
value = where[1]
if isinstance(value, str):
value = "'" + value + "'"
sql = f"select {fields} from {cls().column} where {where[0]}={value}"
try:
with conn.cursor() as cursor:
yield cursor.execute(sql)
datas = cursor.fetchall()
except:
yield conn.rollback()
else:
yield conn.commit()
raise Return(datas)
yield ORM.pool.close()
@classmethod
@coroutine
def delete(cls, *args):
"""删除函数"""
with (yield ORM.pool.Connection()) as conn:
s = ''
for d in args:
for field, value in d.items():
if isinstance(value, str):
s += f'{field}="{value}"'
else:
s += f'{field}={value}'
s += ' or '
s = s[0:-4]
sql = f"delete from {cls().column} where " + s
try:
with conn.cursor() as cursor:
yield cursor.execute(sql)
except:
yield conn.rollback()
raise Return(False)
else:
yield conn.commit()
raise Return(True)
yield ORM.pool.close()
@classmethod
@coroutine
def update(cls, sets=list(), where=list()):
"""更新数据,传入列表[field, values]"""
with (yield ORM.pool.Connection()) as conn:
set_field = sets[0]
where_field = where[0]
if isinstance(where[1], str):
s = f'"{where[1]}"'
else:
s = where[1]
if isinstance(where[1], str):
k = f'"{sets[1]}"'
else:
k = sets[1]
sql = f"update {cls().column} set {set_field}=" + k + f" where {where_field}=" + s
try:
with conn.cursor() as cursor:
yield cursor.execute(sql)
except:
yield conn.rollback()
raise Return(False)
else:
yield conn.commit()
raise Return(True)
yield ORM.pool.close()
@classmethod
@coroutine
def insert(cls, **kwargs):
"""更新数据,传入{field1:values1,field2:values2....}"""
with (yield ORM.pool.Connection()) as conn:
fileds = ''
values = list()
for key, value in kwargs.items():
fileds += key
fileds += ','
values.append(value)
fileds = fileds[0:-1]
values = tuple(values)
sql = f"insert into {cls().column} ({fileds}) values {values}"
try:
with conn.cursor() as cursor:
yield cursor.execute(sql)
except:
yield conn.rollback()
raise Return(False)
else:
yield conn.commit()
raise Return(True)
yield ORM.pool.close()
#coding=utf-8
import tormysql
import pymysql
from config import MYSQL_INFO
pool = tormysql.ConnectionPool(
max_connections=20, # max open connections
idle_seconds=7200, # conntion idle timeout time, 0 is not timeout
wait_connection_timeout=3, # wait connection timeout
host=MYSQL_INFO["host"],
user=MYSQL_INFO["user"],
passwd=MYSQL_INFO["password"],
db=MYSQL_INFO["db"],
charset="utf8",
port=MYSQL_INFO["port"],
autocommit=True,
cursorclass=pymysql.cursors.DictCursor
)
def fx_escape_string(str_text):
return pymysql.escape_string(str_text)
# from tornado.ioloop import IOLoop
# from tornado import gen
# @gen.coroutine
# def test():
# with (yield pool.Connection()) as conn:
# try:
# with conn.cursor() as cursor:
# yield cursor.execute("INSERT INTO test(id) VALUES(1)")
# except:
# yield conn.rollback()
# else:
def init_db_pool():
db_pools = {}
db_confs = options.config["db_confs"]
for name in db_confs:
db_pools[name] = tormysql.ConnectionPool(**db_confs[name])
options.config['db_pools'] = db_pools
# Constants
# Change these to the details you chose
SQL_USER = "******"
SQL_PASSWD = "admin"
SQL_DB = "db"
SQL_HOST = "db"
# Creating the DB connection pool
pool = tormysql.ConnectionPool(
max_connections=20,
idle_seconds=7200,
wait_connection_timeout=3,
host=SQL_HOST,
user=SQL_USER,
passwd=SQL_PASSWD,
db=SQL_DB,
charset="utf8",
# To get SQL results as dicts
cursorclass=pymysql.cursors.DictCursor,
)
async def execute(query, params):
"""
Execute an sql command with the params given
Args:
query: the SQL query to execute
params: params for the SQL query
Returns:
import tormysql
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool
db_user = "******"
db_host = "127.0.0.1"
db_pass = "******"
db_name = "myapp"
# tormysql
app_pool = tormysql.ConnectionPool(
max_connections=80, # max open connections
idle_seconds=
180, # conntion idle timeout time, 0 is not timeout 连接空闲超过idle_seconds 后会自动关闭回收连接的
wait_connection_timeout=3, # wait connection timeout
host=db_host,
user=db_user,
passwd=db_pass,
db=db_name,
charset="utf8",
cursorclass=pymysql.cursors.DictCursor,
)
# sqlalchemy
app_engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(db_user, db_pass, db_host, db_name),
encoding='utf-8',
echo=False,
poolclass=QueuePool,
max_overflow=100,
pool_size=1,
pool_recycle=10,
def __init__(self, dbtable, ConfigID=0, dbConfig=None):
if not isinstance(ConfigID, (int, str)):
self.throw_exception("第二个参数只能是数字或字符串", True)
# 将类变量中的可变元素初始化
self.columns = [] # 记录表中字段名
self.whereStringArray = []
self.whereValueArray = []
self.SQLerror = {}
if ConfigID in Ormer.configs:
if dbConfig != None:
self.throw_exception(
'数据库配置编号' +
(str(ConfigID) if isinstance(ConfigID, int) else "'" +
str(ConfigID) + "'") + '已被占用', True)
if dbConfig == None:
if not isset('CONFIG'):
self.throw_exception("配置文件未定义CONFIG", True)
# 检查配置文件中是否有对应的配置信息
if ConfigID not in CONFIG:
self.throw_exception(
"配置文件中无" +
(str(ConfigID) if isinstance(ConfigID, int) else "'" +
str(ConfigID) + "'") + "的配置信息", True)
# 使用配置文件中对应的配置
if ConfigID == 0:
dbConfig = CONFIG[0]
else:
dbConfig = dict(CONFIG[ConfigID])
# dbConfig.update(CONFIG[ConfigID])
if 'DB_DEBUG' in dbConfig:
if dbConfig['DB_DEBUG'] == True:
self.dbdebug = True
del dbConfig['DB_DEBUG']
if 'password' not in dbConfig:
if 'password' in CONFIG[0]:
dbConfig['password'] = CONFIG[0]['password']
else:
self.throw_exception('数据库未设置密码')
if 'host' not in dbConfig:
dbConfig['host'] = '127.0.0.1'
if 'user' not in dbConfig:
dbConfig['user'] = '******'
if 'port' not in dbConfig:
dbConfig['port'] = '3306'
if 'autocommit' not in dbConfig:
dbConfig['autocommit'] = True
if 'dbms' not in dbConfig:
dbConfig['dbms'] = 'mysql'
Ormer.configs[ConfigID] = dbConfig
self.current = ConfigID
self.config = dbConfig
self.database = dbConfig['database']
del dbConfig['dbms']
self.table_name = dbtable
self.dbpool = tormysql.ConnectionPool(
max_connections=dbConfig['max_connections'],
idle_seconds=dbConfig['idle_seconds'],
wait_connection_timeout=dbConfig['wait_connection_timeout'],
host=dbConfig['host'],
user=dbConfig['user'],
passwd=dbConfig['password'],
db=dbConfig['database'],
autocommit=dbConfig['autocommit'],
charset="utf8mb4")
import tornado
import asyncio
import datetime
import tornado.ioloop
import tornado.web
import tormysql
pool = tormysql.ConnectionPool(
max_connections=50, # max open connections
idle_seconds=7200, # conntion idle timeout time, 0 is not timeout
wait_connection_timeout=60, # wait connection timeout
host="104.155.227.39",
user="******",
passwd="Lhj13950822156",
db="IDO",
charset="utf8"
)
from WFSvc import WfSvc
async def test():
with (await pool.Connection()) as conn:
with conn.cursor() as cursor:
await cursor.execute('call test();')
results = cursor.fetchall()
col_name_list = [tuple[0] for tuple in cursor.description]
print(col_name_list)
b = cursor.nextset()
tmp = cursor.fetchall()
ii = 3
class Component:
"""
该模块参数均来源与madtornado配置文件，并且不支持同时连接多个不同的数据库
异步mysql快速请求模块(基于tormysql)，示例::
db = asyncMysql().Component()
await db.on()
result = await db.select_tcw("mt") # 读取mt表下的所有内容并返回
self.write(result) # 返回给前台页面显示
安全建议::
永远不要相信用户输入内容，sql模块虽然对于value进行了预检测
但是对于key部分还是无能为力，所以一定不要让用户对key的部分有
任何操作，只基于用户提供value值的权限，这样做是安全的。
"""
sql_pool = tormysql.ConnectionPool(
max_connections=int(option["max_connections"]),
idle_seconds=int(option["idle_seconds"]),
wait_connection_timeout=int(option["wait_connection_timeout"]),
host=option["host"],
port=int(option["port"]),
user=option["user"],
passwd=option["password"],
db=option["db"],
charset=option["charset"])
def __init__(self):
self.conn = None
self.cur = None
self.is_return_dict = False
self.switch = False
async def __aenter__(self):
await self.on()
return self
async def __aexit__(self, exc_type, exc_val, exc_tb):
await self.off()
def set_return_dict(self, is_return_dict: bool = True) -> None:
"""
设置是否返回字典格式而非数组格式的数据对象
:param is_return_dict: 是否返回字典格式数据
:return: None
"""
self.is_return_dict = is_return_dict
self.cur = self.conn.cursor(
tormysql.DictCursor if is_return_dict else None)
async def on(self) -> None:
"""
开启一个数据库实例从sql_pool当中
举例::
db=Component()
db.on()
result = await db.select_tw("table")
db.off()
# 与上述使用方法一致
async with Component() as com:
result = await com.select_tcw("table")
快捷使用方法::
with Component() as db:
db.select_tw("table")
:return: None
"""
self.conn = await Component.sql_pool.Connection()
self.set_return_dict()
self.switch = True
async def off(self) -> None:
"""
关闭一个数据库连接实例
:return: None
"""
if self.switch:
self.switch = False
await self.cur.close()
await self.conn.close()
async def output_sql(self,
sql: str,
arg_list: List[SQL_CONTENT] = None) -> Tuple:
"""
输出查询内容，直接传入select的sql语句，用于查询
:param sql: select开头的sql语句
:param arg_list: 预处理参数，如果需要预处理查询，需要在sql语句中使用%s作为占位符
:return: 查询到的结果内容
"""
await self.cur.execute(sql, arg_list)
result = self.cur.fetchall()
await self.conn.commit()
return result
@overload
async def input_sql(self,
sql: str,
arg_list: List[SQL_CONTENT] = None,
many: bool = False) -> genSQL.InputResult:
...
@overload
async def input_sql(self,
sql: str,
arg_list: List[List[SQL_CONTENT]] = None,
many: bool = True) -> genSQL.InputResult:
...
async def input_sql(self,
sql,
arg_list=None,
many=False) -> genSQL.InputResult:
"""
执行插入，更新，删除等输入系列的sql语句
:param sql: 输入系列的sql语句
:param arg_list: 预处理参数列表，可以是二维数组代表多行插入前提需要设置many参数
:param many: 是否启用多行输入
:return: genSQL.InputResult 输入语句查询信息
"""
ir = genSQL.InputResult()
try:
if many:
affect = await self.cur.executemany(sql, arg_list)
else:
affect = await self.cur.execute(sql, arg_list)
except Exception as e:
await self.conn.rollback()
ir.status = False
ir.err_info = e
else:
await self.conn.commit()
ir.affect = affect
ir.last_rowid = self.cur.lastrowid
return ir
async def select_tcw(self,
table: str,
field: Tuple[str, ...] = ("*", ),
where: str = None,
where_arg: List[SQL_CONTENT] = None) -> Tuple:
"""
简化版的查询，用于快捷查询表格内容，不支持连表等高端操作
:param table: 查询的表格名称
:param field: 一个元组，代表要查询的字段名称
:param where: 一个字符串，代表where条件筛选部分，如id=1 and name=a，可以在其中使用占位符，同时要提供where_arg
:param where_arg: 如果where传入的字符串包括占位符，那么传入参数列表，让sql可以预查询
:return: Tuple 查询到的结果内容
"""
return await self.output_sql(genSQL.select_tcw(table, field, where),
where_arg)
@overload
async def insert_tc(self,
table: str,
content: List[SQL_CONTENT],
many: bool = False) -> genSQL.InputResult:
...
@overload
async def insert_tc(self,
table: str,
content: List[List[SQL_CONTENT]],
many: bool = True) -> genSQL.InputResult:
...
@overload
async def insert_tc(self,
table: str,
content: Dict[str, SQL_CONTENT],
many: bool = False) -> genSQL.InputResult:
...
@overload
async def insert_tc(self,
table: str,
content: Dict[str, List],
many: bool = True) -> genSQL.InputResult:
...
async def insert_tc(self,
table,
content,
many=False) -> genSQL.InputResult:
"""
简化版的插入数据，只能简单的插入数据
示例内容::
insert_tc("table", [1, 2, 3, 4, 5])
转换内容 ： ('insert into table values(%s,%s,%s,%s,%s)', [1, 2, 3, 4, 5])
insert_tc("table", [[1, 2, 3, 4, 5], [6, 7, 8, 9, 10]], many=True, ph="?")
转换内容 ： ('insert into table values(?,?,?,?,?)', [[1, 2, 3, 4, 5], [6, 7, 8, 9, 10]])
insert_tc("table", {"id": 12, "name": "SystemLight"}, many=False, ph="%s")
转换内容 ： ('insert into table(name,id) values(%s,%s)', ['SystemLight', 12])
insert_tc("table", {"key": ["id", "name"], "value": [["1", "lisys"], ["2", "sl"]]}, many=True, ph="%s")
转换内容 ： ('insert into table(id,name) values(%s,%s)', [['1', 'lisys'], ['2', 'sl']])
:param table: 表格的名称
:param content: 支持四中格式传参，参考示例内容
:param many: 是否启用多行插入
:return: genSQL.InputResult结果信息对象
"""
sql, content = genSQL.insert_tc(table, content, many)
ir = await self.input_sql(sql, content, many)
return ir
@overload
async def insert_update_tc(self,
table: str,
content: Dict[str, SQL_CONTENT],
many: bool = False) -> genSQL.InputResult:
...
@overload
async def insert_update_tc(self,
table: str,
content: Dict[str, List],
many: bool = False) -> genSQL.InputResult:
...
async def insert_update_tc(self,
table,
content,
many=False) -> genSQL.InputResult:
"""
简化版的插入即更新，这是mysql的特性方法，存在则更新数据，不存在则插入一条新数据
:param table: 数据表名称
:param content: 支持dict多行模式{"key":[],"value":[[]]}，和dict单行模式传参
:param many: 是否启用多行模式
:return: genSQL.InputResult结果信息对象
"""
sql, content = genSQL.insert_update_tc(table, content, many)
ir = await self.input_sql(sql, content, many)
return ir
async def update_tcw(
self,
table: str,
content: Dict[str, Any],
where: str = None,
where_arg: List[SQL_CONTENT] = None) -> genSQL.InputResult:
"""
简化版的更新数据
:param table: 数据表名称
:param content: 字典对象，键值对相对应
:param where: 一个字符串，代表where条件筛选部分，如id=1 and name=a，可以在其中使用占位符，同时要提供where_arg
:param where_arg: 如果where传入的字符串包括占位符，那么传入参数列表，让sql可以预查询
:return: genSQL.InputResult结果信息对象
"""
sql, arg_list = genSQL.update_tcw(table, content, where, where_arg)
ir = await self.input_sql(sql, arg_list)
return ir
async def delete_tw(
self,
table: str,
where: str = None,
where_arg: List[SQL_CONTENT] = None) -> genSQL.InputResult:
"""
简化版的删除数据
:param table: 数据表名称
:param where: 如果该项不填，则删除整个表格，一个字符串，代表where条件筛选部分，如id=1 and name=a
:param where_arg: 如果where传入的字符串包括占位符，那么传入参数列表，让sql可以预查询
:return: genSQL.InputResult结果信息对象
"""
sql = genSQL.delete_tw(table, where)
ir = await self.input_sql(sql, where_arg)
return ir
async def truncate_t(self, table: str) -> genSQL.InputResult:
"""
快速清空表格数据
:param table: 数据表名称
:return: genSQL.InputResult结果信息对象
"""
code1, code2, code3 = genSQL.truncate_t(table)
await self.input_sql(code1)
ir = await self.input_sql(code2)
await self.input_sql(code3)
return ir
def connect(config):
global pool
pool = tormysql.ConnectionPool(**config)
# Python Authentication using JsonWebTokens
from tornado.ioloop import IOLoop
from tornado import gen
import tormysql
import jwt
import json
with open('cred.json', 'r') as f:
cred = json.load(f)
pool = tormysql.ConnectionPool(
max_connections=20, #max open connections
idle_seconds=7200, #conntion idle timeout time, 0 is not timeout
wait_connection_timeout=3, #wait connection timeout
host=cred['host'],
user=cred['user'],
passwd=cred['passwd'],
db=cred['db'],
charset="utf8")
@gen.coroutine
def test():
with (yield pool.Connection()) as conn:
try:
with conn.cursor() as cursor:
yield cursor.execute("INSERT INTO test(id) VALUES(1)")
except:
yield conn.rollback()
else:
"""create table If Not Exists `haircut_carousel` (
id smallint auto_increment comment '轮播图ID',
product_id int not null,
image varchar(128) comment '图片',
`index` tinyint comment '轮播顺序',
primary key (`id`)
) engine=InnoDB default charset=UTF8MB4
"""
]
pool = tormysql.ConnectionPool(
max_connections=1, # max open connections
idle_seconds=7200, # conntion idle timeout time, 0 is not timeout
wait_connection_timeout=3, # wait connection timeout
host="127.0.0.1",
user="******",
passwd="123456",
db="haircut",
charset="utf8",
cursorclass=tormysql.DictCursor)
async def create_table(delete=False):
"""
创建表
:param delete:
:return:
"""
async with await pool.Connection() as conn:
async with conn.cursor() as cursor:
if delete:
import tormysql
import requests
from tornado.web import *
from tornado.ioloop import *
from tornado.template import *
from dotenv import load_dotenv
load_dotenv('.env')
MAX_WORKERS = 16
POOL = tormysql.ConnectionPool(max_connections=64,
idle_seconds=7200,
wait_connection_timeout=3,
host=os.environ.get('MYSQL_HOST'),
port=int(os.environ.get('MYSQL_PORT')),
user=os.environ.get('MYSQL_USER'),
passwd=os.environ.get('MYSQL_PASSWORD'),
db='db',
charset='utf8')
USERS_FIELDS = ['id', 'name', 'email', 'gender', 'location']
class Index(RequestHandler):
@staticmethod
@gen.coroutine
def generate_page():
return Loader('template')\
.load('index.html')\
.generate()
@gen.coroutine
def get(self):
