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 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 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 customers(): responseBody = {"results": Connection().getCustomer()} return jsonify(responseBody), 200