def testLoginFailsWithInvalidPassword(self):
try:
conn = Connection(dbhost, dbname, dbuser, 'xxx', 3306, 60.0)
self.fail('expected ServerException')
except ServerException, ex:
log.debug('Exception = %s:%s' % (type(ex), ex))
self.assertTrue(str(ex).find('password'))
def testLoginFailsWhenMySQLNotListeningOnPort(self):
try:
conn = Connection(dbhost, dbname, dbuser, dbpassword, 9999, 60.0)
fail('expected failure')
except socket.error, ex:
log.debug('Exception = %s:%s' % (type(ex), ex))
self.assertTrue(str(ex).find('refused'), 'Error msg didnt match')
def __init__(self):
handlers = [(r'/', MainHandler), (r'/users/([^/]*)', UserHandler),
(r'/gacha/([^/]*)', GachaHandler),
(r'/avatar/([^/]*)', AvatarHandler),
(r'/ad/([^/]*)', AdHandler),
(r'/finance/([^/]*)', FinanceHandler),
(r'/guestmsg/([^/]*)', GuestmsgHandler),
(r'/activity/([^/]*)', ActivityHandler),
(r'/apl/([^/]*)', AplHandler),
(r'/activity_notice/([^/]*)', ActivityNoticeHandler),
(r'/pic/([^/]*)', PicHandler),
(r'/event/([^/]*)', EventHandler),
(r'/level/([^/]*)', LevelHandler),
(r'/mainten/([^/]*)', MaintenHandler),
(r'/playerids/([^/]*)', PlayerIDsHandler),
(r'/playerdatas/([^/]*)', PlayerDatasHandler),
(r'/playercommands/([^/]*)', PlayerCommandsHandler),
(r'/logout', LogoutHandler), (r'/login', LoginHandler)]
settings = dict(template_path=os.path.join(os.path.dirname(__file__),
"templates"),
static_path=os.path.join(os.path.dirname(__file__),
"static"),
xsrf_cookies=False,
cookie_secret="dsfc333cdddW!0de",
login_url="/login")
super(Application, self).__init__(handlers, **settings)
# Have one global connection to the blog DB across all handlers
self.db = Connection(host=options.mysql_host,
database=options.mysql_database,
user=options.mysql_user,
password=options.mysql_password)
self.maybe_create_tables()
def testLoginFailsWhenConnectingToNonExistantHostname(self):
try:
conn = Connection('1.1.1.1', dbname, dbuser, dbpassword, 3306, 1)
fail('expected ServerException')
except Exception, e:
#except socket.timeout, e:
errorMessage = str(e)
log.debug('Exception = %s:%s' % (type(e), errorMessage))
def testLoginFailsWithInvalidUser(self):
try:
conn = Connection(dbhost, dbname, 'xxx', dbpassword, 3306, 60.0)
fail('expected ServerException')
except ServerException, e:
errorMessage = str(e)
log.debug('Exception = %s:%s' % (type(e), errorMessage))
self.assertTrue(errorMessage.find('denied'))
self.assertTrue(errorMessage.find('user'))
def check_conn(self, transaction=False):
# if self._pool is not None:
# __conn = self._pool.getConn()
# else:
# __conn = self._db
# self._conn = __conn
# self._db = __conn.getConnect()
try:
if not self._db:
# 无连接,需要获取连接
# print('没有发现连接,尝试获取连接')
if self._poolFlag:
# 连接池
self._conn = self._pool.getConn()
self._db = self._conn.getConnect()
else:
# 单个连接理论上只执行一次,过后直接关闭
self._db = Connection()
except Exception as e:
print(e)
raise Exception('检查连接失败')
def getConn(self):
__pool = self._pool
if __pool:
currConn = __pool.pop(0)
if currConn.testConn():
# 连接有效
# print('连接有效')
# 不作处理
pass
else:
print('连接无效')
currConn.reConn()
return currConn
else:
# 连接数不足则新增连接
conn = Connection.Connection()
self._pool.append(conn)
def ocr():
if request.method == 'POST':
HoldStatus("").writeFile("", "_serial")
HoldStatus("").writeFile("", "_lastScan")
HoldStatus("").writeFile("0", "_lastScanCount")
HoldStatus("").writeFile("2", "_scan")
HoldStatus("").writeFile("0", "_serialrowcount")
HoldStatus("").writeFile("0", "_serialpostCount")
HoldStatus("").writeFile("", "_goodData")
HoldStatus("").writeFile("0", "_processing")
dict = {}
for value in Connection().getModels(request.form['customer'],
request.form['model']):
mdict1 = {value[1]: value[2]}
dict.update(mdict1)
HoldStatus("").writeFile(json.dumps(dict), "_validation")
return App.render(render_template("ocr.html", user=session['user']))
def serial(m_id, s):
return jsonify(Connection().insertModel(m_id, s)), 200
def models(customer_id):
responseBody = {"results": Connection().getModels(customer_id)}
return jsonify(responseBody), 200
def testLoginPasses(self):
conn = Connection(dbhost, dbname, dbuser, dbpassword, 3306, 60.0)
self.assertTrue(conn, "connection null")
conn.close()
def testCloseSucceeds(self):
conn = Connection(dbhost, dbname, dbuser, dbpassword, 3306, 60.0)
conn.close()
self.assertFalse(conn.isConnected)
def testLoginPasses(self):
conn = Connection(dbhost, dbname, dbuser, dbpassword, 3306, 60.0)
self.assertTrue(conn, 'connection null')
conn.close()
def testGetErrorCodeIsNoneWhenNothingDone(self):
conn = Connection(dbhost, dbname, dbuser, dbpassword, 3306, 60.0)
errorCode = conn.getErrorCode()
self.assertTrue(errorCode is None)
def testGetErrorCodeIsNoneWhenNothingDone(self):
conn = Connection(dbhost, dbname, dbuser, dbpassword, 3306, 60.0)
errorCode = conn.getErrorCode()
self.assertTrue(errorCode is None)
def model(m_id):
return jsonify(Connection().getModel(m_id)), 200
class SelectQueryTestCase(unittest.TestCase):
def setUp(self):
self.host = dbhost
self.dbName = dbname
self.username = dbuser
self.password = dbpassword
self.conn = Connection(self.host, self.dbName, self.username, self.password, 3306, 10)
def tearDown(self):
if self.conn:
self.conn.close()
def testSelectStatementWithNoRowsReturned(self):
result = self.conn.executeSQL('select * from channel where chanid = 999')
if result:
log.debug(self.conn.getFormattedSQLResultsFromIterator())
else:
log.debug(str(result))
log.debug('Result = %s' % result)
self.assertEquals(1, result, 'successfull sql select should return 1')
self.assertTrue(self.conn.getErrorCode() is None, 'expected None error code')
self.assertEquals('', self.conn.getErrorMsg(), 'expected empty error msg')
itr = self.conn.dictRowIterator()
self.assertTrue(itr is None, 'expected null iterator')
def testSelectStatementWithOneRowReturned(self):
if self.conn.executeSQL( "select chanid, visible, xmltvid, freqid, sourceid, callsign, name, channum from channel where chanid=1021" ):
log.debug('\n' + self.conn.getFormattedSQLResultsFromIterator())
else:
log.debug('ERROR ' + str(self.conn.getErrorCode()) + ': ' + self.conn.getErrorMsg())
def testSelectStatementWithManyRowsReturned(self):
if self.conn.executeSQL( "select chanid, visible, xmltvid, freqid, sourceid, callsign, name, channum from channel order by chanid" ):
log.debug('\n' + self.conn.getFormattedSQLResultsFromIterator())
else:
log.debug("ERROR " + str(self.conn.getErrorCode()) + ": " + self.conn.getErrorMsg())
class curObj:
_page = False
_db = None
_cursor = None
_conn = None
_debug = False
_cursor = None
sql = reader.Mapper()
def __init__(self, db, path, poolFlag, debug):
self._debug = debug
self._poolFlag = poolFlag
if poolFlag:
# 连接池实例化
self._pool = db
else:
self._db = db
self.sql.openDom(path)
self._path = path
self._sqls = self.sql.getTree()
def refreash_sqls(self):
global sql
print(self._sqls)
self.sql.openDom(self._path)
self._sqls = self.sql.getTree()
print(self._sqls)
def check_conn(self, transaction=False):
# if self._pool is not None:
# __conn = self._pool.getConn()
# else:
# __conn = self._db
# self._conn = __conn
# self._db = __conn.getConnect()
try:
if not self._db:
# 无连接,需要获取连接
# print('没有发现连接,尝试获取连接')
if self._poolFlag:
# 连接池
self._conn = self._pool.getConn()
self._db = self._conn.getConnect()
else:
# 单个连接理论上只执行一次,过后直接关闭
self._db = Connection()
except Exception as e:
print(e)
raise Exception('检查连接失败')
# def setConn(self):
# if self._poolFlag:
# # 连接池实例化
# db =self._pool
# else:
# db = self._db
# __conn = db.getConn()
# self._conn = __conn
# self._db = __conn.getConnect()
#
# # 单独连接实例化
# # 初始化指针
# self._cursor = self._db.cursor()
def set_cursor(self):
# 初始化指针(如果不存在指针)
if not self._cursor:
self._cursor = self._db.cursor()
# 获取sql语句(包含处理)
def get_sql(self, methodName, pageInfo, args=()):
# 单独连接实例化
# 判断是否存在子节点
if methodName not in self._sqls:
raise Exception('没有该方法!method:' + str(methodName))
_sql = self._sqls[methodName]
# 判断是否分页(总开关)
# 开启之后该实例所有语句都认为是 需要分页
# 慎用!!!!
if self._page:
# 分页
_sql = _sql + 'limit ' + str(
(self._pageNum - 1) * self._pageSize) + ',' + str(
self._pageSize)
if pageInfo:
# print(pageInfo)
# print(pageInfo['pageNum'])
# print(pageInfo['pageSize'])
# 分页
_sql = _sql + PageHelper.depkg_page_info(pageInfo)
# print(_sql)
# 判断是否骨架拼接
if args:
# print(type(args))
# 检查骨架实参传入类型,并作不同处理
if type(args) is type(()):
if re.match(r'(\#|\$)\{.*\}', _sql):
raise Exception('骨架与参数不匹配')
_sql = _sql % args[:]
elif type(args) is type({}):
for key in args.keys():
reg_str = r'(\#|\$)\{' + str(key) + '\}'
if not re.search(reg_str, _sql):
'''
此处有几种情况:
1.语句骨架不存在该key的空位(多余参数)
2.骨架参数与骨架不对应(多余空位)
'''
pass
else:
_sql = re.sub(
'\#\{' + str(key) + '\}',
'\'' + str(args[key]) + '\'',
re.sub('\$\{' + str(key) + '\}', str(args[key]),
_sql))
# 多余空位检查
if re.search('(\#|\$)\{' + str(key) + '\}', _sql):
raise Exception('存在无法配对的骨架参数')
else:
try:
_sql = _sql % args[:]
except Exception as e:
print(e)
# 去除注释与空格,换行等
__sql = re.sub('\\s+', ' ', re.sub('<!--.*-->', ' ', _sql))
# print(_sql)
return __sql
# 设定分页信息
def set_page(self, pageNum, pageSize):
self._pageNum = pageNum
self._pageSize = pageSize
self._page = True
def initial_page(self):
self._page = False
# 批量执行语句(整体版)
"""
queue_obj中key为方法名,value为参数
注意!!!!
对于一个业务来说,一个sql方法只使用一次(因为有内部数据缓存)
若其中有重复方法,建议用分割版
"""
def exe_sql_obj_queue(self, queue_obj={}):
if queue_obj:
methods = list(queue_obj.keys())
args = list(queue_obj.values())
self.exe_sql_queue(method_queue=methods, args_queue=args)
else:
raise Exception('queue_obj参数不正确')
# 批量执行语句(拆分版)
"""
method_queue中存放顺序执行的sql方法名[str]
args_queue中存放对应下标方法的参数元组[()]
若其中包含select无条件参数语句,请用空元组()占位
"""
def exe_sql_queue(self, method_queue=[], args_queue=[]):
# 参数检查
if not method_queue:
raise Exception('语句方法为空')
return
if not args_queue:
raise Exception('语句参数列表为空')
return
self.check_conn()
self.set_cursor()
try:
# 开启事务
# self._db.begin()
# 批量取语句(以方法名为准,多于参数队列元素将丢弃)
while method_queue:
method = method_queue.pop(0)
args = args_queue.pop(0)
"""
对于增改查来说,并不需要分页,参数列表是必须的
"""
_sql = self.get_sql(methodName=method,
args=args,
pageInfo=None)
# print(self._cursor)
# 执行sql语句
self._cursor.execute(_sql)
# 调试模式打印语句
if self._debug:
print_debug(methodName=method,
args=args,
sql=_sql,
result=self._cursor.rowcount)
# 事务提交(pymysql要求除查询外所有语句必须手动提交)
except Exception as e:
print(e)
self._db.rollback()
print('事务回滚' + str(method_queue))
else:
self._db.commit()
print('事务提交' + str(method_queue))
# 关闭连接
self.close()
# 执行单条语句
# 防报错参数设定默认值
def exe_sql(self, methodName='', pageInfo=None, args=()):
lock = threading.Lock()
lock.acquire(blocking=True)
# 参数检查
if not re.sub('\s+', '', methodName):
raise Exception('语句方法为空')
return
self.check_conn()
self.set_cursor()
# 定义返回结果集
result = []
try:
# print(pageInfo)
if pageInfo and type(pageInfo) is type({}):
_sql = self.get_sql(methodName=methodName,
pageInfo=pageInfo,
args=args)
else:
_sql = self.get_sql(methodName=methodName,
args=args,
pageInfo=None)
# print(_sql)
except Exception as ex:
print(ex)
return result
try:
# 试执行语句
self._cursor.execute(_sql)
self._db.commit()
self.initial_page()
'''
下列代码弃用是因为查询事务不提交会导致查询数据为历史数据,
同时查询事务会导致重复读（REPEATABLE READ）表锁生效,需要提交事务消除表行锁
'''
# 为了保障新增以及修改的操作可以生效而提交事务(暂弃)
# if 'select' not in _sql and 'SELECT' not in _sql:
# # print('不是查询!!')
# self._db.commit()
# else:
# # 回复分页状态
# self.initial_page()
except Exception as e:
self._db.rollback()
self.initial_page()
# 为了保障新增以及修改的操作可以生效而提交事务(暂弃)
# if 'select' not in _sql and 'SELECT' not in _sql:
# # print('不是查询!!')
# self._db.rollback()
# else:
# # 回复分页状态
# self.initial_page()
print("执行出错,错误信息为:", e)
return result
# cursor.execute('select * from mw_system_member_level')
if 'select' not in _sql and 'SELECT' not in _sql:
data = [[self._cursor.rowcount]]
description = [['row_count']]
else:
data = self._cursor.fetchall()
# print(data)
description = self._cursor.description
# print(description)
result = sort_result(data, description, result)
# print(data)
# print(result)
# 关闭连接
self.close()
# 调试模式语句执行信息打印
if self._debug:
print_debug(methodName=methodName,
args=args,
sql=_sql,
result=result)
lock.release()
# 非查询语句返回影响行数
if 'select' not in _sql and 'SELECT' not in _sql:
return data[0][0]
return result
def close(self):
# print('关闭连接')
self._cursor.close()
if self._poolFlag:
# 为连接池定义
pool.closeConn(self._conn)
# 归还连接后清除指针
self._cursor = None
self._db = None
self._conn = None
else:
# 为单独连接定义
self._db.close()
# 定义插入更新器方法
def insert_to_update_dispacther(self, millionSecond):
if isinstance(millionSecond, int):
w_time = millionSecond
pass
else:
try:
w_time = int(millionSecond)
except Exception as e:
print(e)
# 此处为增量更新代码
'''
临时思路
1.设定定时间隔
2.传入当前语句对象
3.内部压缩保存binlog
4.定时完毕重新获取语句,获取新语句对象binlog
5.对比binlog
5.1若更新后binog无差异则不作处理
5.2若存在差异,替换语句对象
'''
self._bin_cache = mysql.binlog.BinCache(self._path)
# 添加变更处理
self._bin_cache.set_false_fun(self.refreash_sqls)
# 调度器添加任务
Schued.sech_obj(fun=self._bin_cache.chk_diff, delay=w_time).enter()
def setUp(self):
self.host = dbhost
self.dbName = dbname
self.username = dbuser
self.password = dbpassword
self.conn = Connection(self.host, self.dbName, self.username, self.password, 3306, 10)
class SelectQueryTestCase(unittest.TestCase):
def setUp(self):
self.host = dbhost
self.dbName = dbname
self.username = dbuser
self.password = dbpassword
self.conn = Connection(self.host, self.dbName, self.username, self.password, 3306, 10)
def tearDown(self):
if self.conn:
self.conn.close()
def testSelectStatementWithNoRowsReturned(self):
result = self.conn.executeSQL("select * from channel where chanid = 999")
if result:
log.debug(self.conn.getFormattedSQLResultsFromIterator())
else:
log.debug(str(result))
log.debug("Result = %s" % result)
self.assertEquals(1, result, "successfull sql select should return 1")
self.assertTrue(self.conn.getErrorCode() is None, "expected None error code")
self.assertEquals("", self.conn.getErrorMsg(), "expected empty error msg")
itr = self.conn.dictRowIterator()
self.assertTrue(itr is None, "expected null iterator")
def testSelectStatementWithOneRowReturned(self):
if self.conn.executeSQL(
"select chanid, visible, xmltvid, freqid, sourceid, callsign, name, channum from channel where chanid=1021"
):
log.debug("\n" + self.conn.getFormattedSQLResultsFromIterator())
else:
log.debug("ERROR " + str(self.conn.getErrorCode()) + ": " + self.conn.getErrorMsg())
def testSelectStatementWithManyRowsReturned(self):
if self.conn.executeSQL(
"select chanid, visible, xmltvid, freqid, sourceid, callsign, name, channum from channel order by chanid"
):
log.debug("\n" + self.conn.getFormattedSQLResultsFromIterator())
else:
log.debug("ERROR " + str(self.conn.getErrorCode()) + ": " + self.conn.getErrorMsg())
def testGetErrorMessageIsEmptyWhenNothingDone(self):
conn = Connection(dbhost, dbname, dbuser, dbpassword, 3306, 60.0)
errorMessage = conn.getErrorMsg()
self.assertEquals(0, len(errorMessage))
def testCloseSucceeds(self):
conn = Connection(dbhost, dbname, dbuser, dbpassword, 3306, 60.0)
conn.close()
self.assertFalse(conn.isConnected)
def testSuccessiveCallsToCloseHaveNoAdverseEffects(self):
conn = Connection(dbhost, dbname, dbuser, dbpassword, 3306, 60.0)
conn.close()
conn.close()
conn.close()
self.assertFalse(conn.isConnected)
#!/usr/bin/env python
import sys
import datetime
from mysql import Connection
db = Connection(host="172.31.15.206:3306",
database="admin",
user="******",
password="******")
values = {}
datestr = sys.argv[1]
sql = "delete from Level where datestr='%s'" % (datestr)
db.execute(sql)
for l in sys.stdin:
try:
version, level, stats, count = l.strip().split('\t')
if stats == "ad":
sql = "INSERT INTO Ad SET ad_id='%s',ad_ui='%s', datestr='%s',count=%s ON DUPLICATE KEY UPDATE count=%s" % (
version, level, datestr, count, count)
db.execute(sql)
continue
if stats == "gacha":
sql = "INSERT INTO Gacha SET item_id='%s', datestr='%s',count=%s ON DUPLICATE KEY UPDATE count=%s" % (
version, datestr, count, count)
db.execute(sql)
continue
if stats == "fight_avatar":
sql = "INSERT INTO FightAvatar SET level=%s, avatar='%s', datestr='%s',count=%s ON DUPLICATE KEY UPDATE count=%s" % (
def testGetErrorMessageIsEmptyWhenNothingDone(self):
conn = Connection(dbhost, dbname, dbuser, dbpassword, 3306, 60.0)
errorMessage = conn.getErrorMsg()
self.assertEquals(0, len(errorMessage))
def testSuccessiveCallsToCloseHaveNoAdverseEffects(self):
conn = Connection(dbhost, dbname, dbuser, dbpassword, 3306, 60.0)
conn.close()
conn.close()
conn.close()
self.assertFalse(conn.isConnected)
def setUp(self):
self.host = dbhost
self.dbName = dbname
self.username = dbuser
self.password = dbpassword
self.conn = Connection(self.host, self.dbName, self.username, self.password, 3306, 10)
def customers():
responseBody = {"results": Connection().getCustomer()}
return jsonify(responseBody), 200
