def scheduledEvents(): conn = cymysql.connect(host='%s' % dbhost, user='******' % dbuser, passwd='%s' % dbpass, db='%s' % dbname, charset='utf8') cur = conn.cursor() cur.execute('select * from %s' % eventstable) for r in cur.fetchall(): ## Determine if we should take action on event based off of schedule eventinfo = '%s %s %s %s %s %s' % (r[7], r[6], r[5], r[4], r[3], r[1]) print(eventinfo) if checkIfRunIsNeeded(eventinfo): print('Get Sensor Connection information and change GPIO status if needed') print('Sensor: %s' % r[8]) print('Sensor Value for Event: %s' % r[9]) ## Get Sensor PIN and Sensor ID connSensorObject = cymysql.connect(host='%s' % dbhost, user='******' % dbuser, passwd='%s' % dbpass, db='%s' % dbname, charset='utf8') curSensorObject = connSensorObject.cursor() curSensorObject.execute('select * from %s where id = %s' % (sensorobjectstable,r[8])) sensorObject = curSensorObject.fetchone() print('Sensor Object PIN: %s' % sensorObject[2]) print('Sensor ID: %s' % sensorObject[1]) ## Get Sensor IP Address connSensor = cymysql.connect(host='%s' % dbhost, user='******' % dbuser, passwd='%s' % dbpass, db='%s' % dbname, charset='utf8') curSensor = connSensor.cursor() curSensor.execute('select * from %s where id = %s' % (sensorstable,sensorObject[1])) sensor = curSensor.fetchone() print('Sensor IP: %s' % sensor[4]) ## Get Object GPIO url = 'http://%s:8000/GPIO/%s/value' % (sensor[4],sensorObject[2]) print(url) res = urllib.request.urlopen(url).read() content = res.decode("utf8") print('Current Object Value: %s' % content) if (content != r[9]): url = 'http://%s:8000/GPIO/%s/value/%s' % (sensor[4],sensorObject[2],r[9]) os.system('curl -X POST %s' % url)
def push_user_onlinelog(self): keys = self.active_time.keys() now = int(time.time()) online_user = 0 online_user_set = set(); for k in keys: if now - self.active_time[k] < 20: online_user += 1 online_user_set.add(k) query_sql = "INSERT INTO ss_node_online_log_noid (node_id,server,online_user,log_time) VALUES(%s,'%s',%s,now()) ON DUPLICATE KEY UPDATE online_user=%s,log_time=now()" % (self.node_id,self.ip,online_user,online_user) conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute(query_sql) cur.close() conn.commit() conn.close() # push to ss_user_node_online_log_noid query_sql = 'INSERT INTO ss_user_node_online_log_noid (port,node_id,server,online_status,log_time) VALUES ' keys = self.port2userid.keys() for key in keys: if key not in online_user_set: query_sql += "(%s,%s,'%s',%s,now())," % (key,self.node_id,self.ip,0) else: query_sql += "(%s,%s,'%s',%s,now())," % (key,self.node_id,self.ip,1) query_sql = query_sql[:-1] query_sql += ' ON DUPLICATE KEY UPDATE online_status=values(online_status),log_time=now()' online_user_set.clear() conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute(query_sql) cur.close() conn.commit() conn.close()
def test_issue_34(self): try: cymysql.connect(host="localhost", port=1237, user="******") self.fail() except cymysql.OperationalError as e: self.assertEqual(2003, e.args[0]) except: self.fail()
def connect(): conf = readConfig() con = None if conf["DB_SYSTEM"] == "mysql": try: import cymysql as mdb if conf["DB_PORT"] != "": con = mdb.connect( host=conf["DB_HOST"], user=conf["DB_USER"], passwd=conf["DB_PASSWORD"], db=conf["DB_NAME"], port=int(conf["DB_PORT"]), unix_socket=conf["DB_SOCKET"], charset="utf8", ) else: con = mdb.connect( host=conf["DB_HOST"], user=conf["DB_USER"], passwd=conf["DB_PASSWORD"], db=conf["DB_NAME"], unix_socket=conf["DB_SOCKET"], charset="utf8", ) except ImportError: print( bcolors.ERROR + "\nPlease install cymysql for python 3, \ninformation can be found in INSTALL.txt\n" + bcolors.ENDC ) sys.exit() elif conf["DB_SYSTEM"] == "pgsql": try: import psycopg2 as mdb con = mdb.connect( host=conf["DB_HOST"], user=conf["DB_USER"], password=conf["DB_PASSWORD"], dbname=conf["DB_NAME"], port=int(conf["DB_PORT"]), ) except ImportError: print( bcolors.HEADER + "\nPlease install psycopg for python 3, \ninformation can be found in INSTALL.txt\n" + bcolors.ENDC ) sys.exit() cur = con.cursor() return cur, con
def scheduledEvents(): try: conn = cymysql.connect(host='%s' % dbhost, user='******' % dbuser, passwd='%s' % dbpass, db='%s' % dbname, charset='utf8') cur = conn.cursor() cur.execute('select * from %s' % tasktable) for r in cur.fetchall(): ## Determine if we should take action on event based off of schedule eventinfo = '%s %s %s %s %s %s' % (r[6], r[5], r[4], r[3], r[2], r[1]) if checkIfRunIsNeeded(eventinfo): #print('Device: %s' % r[7]) #print('Device Value for Task: %s' % r[8]) connDevice = cymysql.connect(host='%s' % dbhost, user='******' % dbuser, passwd='%s' % dbpass, db='%s' % dbname, charset='utf8') curDevice = connDevice.cursor() curDevice.execute('select * from %s where id = %s' % (devicetable,r[7])) Device = curDevice.fetchone() #print('Device Object Address: %s' % Device[4]) #print('Hub ID: %s' % Device[2]) ## Get Sensor IP Address connHub = cymysql.connect(host='%s' % dbhost, user='******' % dbuser, passwd='%s' % dbpass, db='%s' % dbname, charset='utf8') curHub = connHub.cursor() curHub.execute('select * from %s where id = %s' % (hubtable,Device[2])) hub = curHub.fetchone() #print('Hub IP: %s' % hub[2]) ## Get Object PIN status if (Device[3] == 1): url = 'http://%s:8000/GPIO/%s/value' % (hub[2],Device[4]) #print(url) res = urllib.request.urlopen(url).read() content = res.decode("utf8") #print('Current Object Value: %s' % content) if (content != r[8]): url = 'http://%s:8000/GPIO/%s/value/%s' % (hub[2],Device[4],r[8]) os.system('curl -X POST %s' % url) elif (Device[3] == 2): command = '/home/pi/RF24RaspberryCommunicator/remote -m %s%s%s' % (Device[2],Device[4],r[8]) #print(command) os.system(command) elif (Device[3] == 3): address = Device[4] ipaddress = address.split(",")[0] outletaddress = address.split(",")[1] devval = r[8] if ( devval == "0" ): devval = 3 script = 'run0%s%s=run' % (outletaddress,devval) url = 'http://%s/script?%s' % (ipaddress,script) print(url) os.system('curl %s' % url) except : pass
def push_trafficlog_onlinelog(self): self.active_time.update(self.update_ports_active_time()) keys = self.active_time.keys() now = int(time.time()) online_user = 0 online_user_set = set(); # I am sure that active_time and statics are written at the same time always for k in keys: if now - self.active_time[k] > 1800: # a user has only one port. user_id = self.port2userid[k] # u and d is equal; what the f**k traffic is? query_sql = "INSERT INTO user_traffic_log (user_id,u,d,node_id,rate,traffic,log_time) VALUES(%s,%s,%s,%s,%s,'%s',%s)" % (user_id,self.traffic_logs[k],self.traffic_logs[k],self.node_id,1.0,'',now) conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute(query_sql) cur.close() conn.commit() conn.close() self.traffic_logs[k] = 0 del self.active_time[k] else: online_user += 1 online_user_set.add(k) # push to ss_node_online_log_noid query_sql = "INSERT INTO ss_node_online_log_noid (node_id,server,online_user,log_time) VALUES(%s,'%s',%s,now()) ON DUPLICATE KEY UPDATE online_user=%s,log_time=now()" % (self.node_id,self.ip,online_user,online_user) conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute(query_sql) cur.close() conn.commit() conn.close() # push to ss_user_node_online_log_noid query_sql = 'INSERT INTO ss_user_node_online_log_noid (port,node_id,server,online_status,log_time) VALUES ' keys = self.port2userid.keys() for key in keys: if key not in online_user_set: query_sql += "(%s,%s,'%s',%s,now())," % (key,self.node_id,self.ip,0) else: query_sql += "(%s,%s,'%s',%s,now())," % (key,self.node_id,self.ip,1) query_sql = query_sql[:-1] query_sql += ' ON DUPLICATE KEY UPDATE online_status=values(online_status),log_time=now()' online_user_set.clear() conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute(query_sql) cur.close() conn.commit() conn.close()
def update_all_user(self, dt_transfer): import cymysql update_transfer = {} query_head = 'UPDATE user' query_sub_when = '' query_sub_when2 = '' query_sub_in = None last_time = time.time() for id in dt_transfer.keys(): transfer = dt_transfer[id] #小于最低更新流量的先不更新 update_trs = 1024 * max(2048 - self.user_pass.get(id, 0) * 64, 16) if transfer[0] + transfer[1] < update_trs: continue if id in self.user_pass: del self.user_pass[id] query_sub_when += ' WHEN %s THEN u+%s' % (id, int(transfer[0] * self.cfg["transfer_mul"])) query_sub_when2 += ' WHEN %s THEN d+%s' % (id, int(transfer[1] * self.cfg["transfer_mul"])) update_transfer[id] = transfer if query_sub_in is not None: query_sub_in += ',%s' % id else: query_sub_in = '%s' % id if query_sub_when == '': return update_transfer query_sql = query_head + ' SET u = CASE port' + query_sub_when + \ ' END, d = CASE port' + query_sub_when2 + \ ' END, t = ' + str(int(last_time)) + \ ' WHERE port IN (%s)' % query_sub_in if self.cfg["ssl_enable"] == 1: conn = cymysql.connect(host=self.cfg["host"], port=self.cfg["port"], user=self.cfg["user"], passwd=self.cfg["password"], db=self.cfg["db"], charset='utf8', ssl={'ca':self.cfg["ssl_enable"],'cert':self.cfg["ssl_enable"],'key':self.cfg["ssl_enable"]}) else: conn = cymysql.connect(host=self.cfg["host"], port=self.cfg["port"], user=self.cfg["user"], passwd=self.cfg["password"], db=self.cfg["db"], charset='utf8') cur = conn.cursor() cur.execute(query_sql) cur.close() conn.commit() conn.close() return update_transfer
def open_database_connection(): conn = cymysql.connect(servername, username, password, dbname) curs = conn.cursor() curs.execute("SET sql_notes = 0; ") # Hide Warnings return conn, curs
def push_db_all_user(self): dt_transfer = self.get_servers_transfer() query_head = 'UPDATE user' query_sub_when = '' query_sub_when2 = '' query_sub_in = None last_time = time.time() for id in dt_transfer.keys(): query_sub_when += ' WHEN %s THEN u+%s' % (id, 0) # all in d query_sub_when2 += ' WHEN %s THEN d+%s' % (id, dt_transfer[id]) if query_sub_in is not None: query_sub_in += ',%s' % id else: query_sub_in = '%s' % id if query_sub_when == '': return query_sql = query_head + ' SET u = CASE port' + query_sub_when + \ ' END, d = CASE port' + query_sub_when2 + \ ' END, t = ' + str(int(last_time)) + \ ' WHERE port IN (%s)' % query_sub_in # print query_sql conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute(query_sql) cur.close() conn.commit() conn.close()
def pull_db_all_user(): #数据库所有用户信息 conn = cymysql.connect(host=Config.MYSQL_HOST, port=Config.MYSQL_PORT, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASS, db=Config.MYSQL_DB, charset='utf8') #根据服务器标识获取该节点支持的计划 mealNode = conn.cursor() mealNode.execute("SELECT mid FROM meal_node WHERE nodeMark = '%s'" % (Config.SERVER_MARK)) mealNodeRows = [] mealNodes = None mids = [] for a in mealNode.fetchall(): b = list(a) mids.append(b[0]) if mealNodes is not None: mealNodes += ',%s' % b[0] else: mealNodes = '%s' % b[0] #print mealNodes mealNode.close() #获取计划内用户信息 cur = conn.cursor() sql = "SELECT port, u, d, transfer_enable, passwd, enable, uid, mid FROM user" cur.execute(sql) rows = [] #将每条记录转换成python中的列表 for r in cur.fetchall(): rows.append(list(r)) #printlist(rows) cur.close() conn.close() ret = [] ret.append(rows) ret.append(mids) return ret
def index(): """ Function that serves the home route. It loads the current month and year, queries the database and renders a Jinja2 template as output """ # Checking if the IP comes from a valid subnetwork if not (ip_validate(request.remote_addr)): return abort(403) month = datetime.now().month # Loading the current month year = datetime.now().year # Loading the current year query = QUERY.format(m=month, y=year) # Formating the query string try: # Tries to connect to the database database = connect( host='localhost', user=USER, passwd=PASSWORD, db=DATABASE, use_unicode=True ) except: return abort(500) cursor = database.cursor() cursor.execute(query) result = cursor.fetchall() result = [format_row(list(row)) for row in result] return TEMPLATE.render(records=result)
def __init__(self, host, user, passwd, db, port): try: conn = cymysql.connect(host, user, passwd, db, port, charset = 'utf8') self.cursor = conn.cursor() except cymysql.err.OperationalError: print "Error connecting to database" sys.exit(1)
def index(): dbc = config['db'] conn = cymysql.connect(host=dbc['host'], user=dbc['user'], passwd=dbc['passwd'], db=dbc['db'], charset=dbc['charset']) cur = conn.cursor() cur.execute('select id, first_name, last_name, gender, school, grade, degree, state from users') users = [] for r in cur.fetchall(): users.append(r) content = '' content += '<table>\n' content += '<tr>' + ''.join(['<th>' + cgi.escape(x) + '</th>' for x in ['#', 'Имя', 'Пол', 'Школа', 'Класс', 'Степень', 'Состояние']]) + '</tr>\n' for e in users: user_id = e[0] first_name = e[1] last_name = e[2] gender = e[3] school = e[4] grade = e[5] degree = e[6] state = e[7] state_str = get_state_str(user_id, state) user_link = '<a href="/mf/user' + str(user_id) + '">' + first_name + ' ' + last_name + '</a>' content += '<tr><td>' + str(user_id) + '</td><td>' + user_link + '</td><td>' + gender + '</td><td>' + school + \ '</td><td>' + grade + '</td><td>' + degree + '</td><td>' + state_str + '</td></tr>\n' content += '</table>\n' cur.close() conn.close() return render_template('template.html', title = 'Матпраздник', content = content)
def __init__(self,env="normal"): # server instance management for shadowsocks server instances. # @param `env` specify the running environment # for tests, `env` = "test", # and for actual development, `env` = "normal" self.db_dir = "" self.env = env try: # create new connection conn = cymysql.connect( host = config["MYSQL_CONNECTION_IP"], user = config["MYSQL_USER"], passwd = config["MYSQL_PASSWORD"], charset= "utf8" ) self.connection = conn self.cursor = conn.cursor() if env == "normal": self.db_name = "ss_subnode" elif env == "test": self.db_name = "ss_subnode_test" # create database self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+self.db_name) self.cursor.execute("USE "+self.db_name) # reset timezone to UTC self.cursor.execute("SET @@session.time_zone='+00:00'") except Exception as e: traceback.print_exc()
def checkfor5(id, seperates = 10): conn = cymysql.connect(user = '******', passwd = '1', db = 'movie') cur = conn.cursor() cur.execute('SELECT * FROM m' + str(id)) fetchall = cur.fetchall() size = len(fetchall) dic1 = dict() ratings = [] n = 0 step = size // seperates if (size <= 0): print("Empty") else: for i in range(size): info = fetchall[i] n += 1 if (n % step == 0): nString = 0 nInt = 0 for key, val in dic1.items(): if (val == 5): try: int(key) nInt += 1 except: nString += 1 print("Int: %d, String: %d" % (nInt, nString)) dic1.clear() userId = info[0] rating = int(info[1]) ratings.append(rating) dic1[userId] = rating '''
def connect(): con = None if conf['DB_SYSTEM'] == "mysql": try: import cymysql as mdb con = mdb.connect(host=conf['DB_HOST'], user=conf['DB_USER'], passwd=conf['DB_PASSWORD'], db=conf['DB_NAME'], port=int(conf['DB_PORT']), unix_socket=conf['DB_SOCKET']) except ImportError: sys.exit("\nPlease install cymysql for python 3, \ninformation can be found in INSTALL.txt\n") elif conf['DB_SYSTEM'] == "pgsql": try: import psycopg2 as mdb con = mdb.connect(host=conf['DB_HOST'], user=conf['DB_USER'], password=conf['DB_PASSWORD'], dbname=conf['DB_NAME'], port=int(conf['DB_PORT'])) except ImportError: sys.exit("\nPlease install psycopg for python 3, \ninformation can be found in INSTALL.txt\n") cur = con.cursor() return cur, con
def confirm (): try: hashcode = request.args.get ('hash', '') except: hashcode = '' conn = cymysql.connect (host = '127.0.0.1', user = '******', passwd = 'password', db = 'reg', charset = 'utf8') cur = conn.cursor () cur.execute ('select ' + ', '.join (['id'] + user_fields) + ' from users where hashcode=%s', [hashcode]) u = False for r in cur.fetchall (): u = r if u == False: cur.close () conn.close () return render_template ('template.html', title = 'Error', content = 'No such hashcode') user = {} i = 1 for f in user_fields: user[f] = u[i] i = i + 1 title = lang['event_confirmation_success'] content = '<div>' + lang['event_confirmation_success'] + '</div>\n' content += '<table>\n' for f in user_fields: content += '<tr><td>' + lang['event_form_' + f] + '</td><td>' + str (user[f]) + '</td></tr>\n' content += '</table>\n' content += '<div><a href="/reg/useredit?hash=' + cgi.escape (hashcode) + '">Редактировать информацию</a></div>\n' cur.execute ('update users set state=1 where id=%s', [str (u[0])]) conn.commit () cur.close () conn.close () return render_template ('template.html', title = title, content = content)
def __init__(self, wrapper): self.logger = wrapper.logger self.database = cymysql.connect(host='127.0.0.1', port=3306, user='******', passwd='lilbitch', db='fishing') self.messageHooks = { Command(re.compile('!lastfish', flags=re.IGNORECASE), LastFish(self)) }
def mysql(*args): import cymysql con=cymysql.connect(host='localhost',passwd='root',db='yx',user='******') cur=con.cursor() # call procedure if args[0]=='signin1': procedure_= "(%d)" % (args[1],) if args[0]=='signin2': #### '%s' must add the '' around %s procedure_= "(%d,'%s','%s')" % (args[1],args[2],args[3],) # commit SQL procedure="call " + args[0] + procedure_ cur.execute(procedure) con.commit() # return res if cur.fetchall()!=None: res=cur.fetchall() else: res='' # close connection cur.close() con.close() return res
def main(): # initialize access token access_token = 'CAACEdEose0cBABUIeMZBrColKlU1vVuPhhHWGv8IEwxOf2AXQZC2yzbT1cZCD3WktKiMmADuZCPGuzV1WCYsYk0au19VS6e29iVns33Bgr3D7sA4JNPLmX7jIvanErOHSLMlxWFCPGwNCjb0FiSheVi0LTzPTpn9WOBt9WOPzKfOry6iywasJjIJUo61xZBXDQ8ZCySZAP0pQZDZD' # create request object urlrequest = urllib.request urlopener = urlrequest.build_opener() # open db connection dbconn = cymysql.connect(host='localhost', user='******', db='crawler') dbcur = dbconn.cursor() dbconn.autocommit(True) # insert pages list and crawl pages # inspiration pages # category = "inspiration" # page_ids = [296123726784, 10150142097160646, 108963072463387] # food pages: 140144812679554, 495565190456168, 103178903165747 # category = "food" # page_ids = [495565190456168] # entertainment pages: 175675604415, 242273545889267, 383923618322771 # category = "entertainment" # page_ids = [175675604415, 242273545889267, 383923618322771] # horoscope pages: 233107060077304, 284413701662077, 330871753694883 category = "horoscope" page_ids = [233107060077304, 284413701662077, 330871753694883] for id in page_ids: page.crawl(access_token, urlrequest, urlopener, dbcur, id, category) # close database connection dbcur.close() dbconn.close()
def __init__(self): self.connection = mysql.connect(host='localhost', user='******', passwd='', db='money', charset='utf8') self.db = self.connection.cursor() self.active = self.getActiveWindowTitle() self.activeStartTime = time.time() self.main()
def main(): global time_of_last_run time_of_last_run = time.time() def signal_handler(signal, frame): sys.exit(0) signal.signal(signal.SIGINT, signal_handler) if sys.argv[1] == "additional": print("Fetch for: b = binary, s = sample, m = mediainfo, a = audio, j = jpeg") print("^ added file content, o added previous, z = doing zip, r = doing rar, n = found nfo - %s." %(time.strftime("%H:%M:%S"))) elif sys.argv[1] == "nfo": print("* = hidden NFO, + = NFO, - = no NFO, f = download failed - %s." %(time.strftime("%H:%M:%S"))) if True: #spawn a pool of place worker threads for i in range(run_threads): p = queue_runner(my_queue) p.setDaemon(False) p.start() print("\nPostProcess Threaded Started at %s" %(datetime.datetime.now().strftime("%H:%M:%S"))) #now load some arbitrary jobs into the queue if sys.argv[1] == "additional": for release in datas: my_queue.put("%s %s %s %s %s %s %s" %(release[0], release[1], release[2], release[3], release[4], release[5], release[6])) elif sys.argv[1] == "nfo": for release in datas: my_queue.put("%s %s %s %s" %(release[0], release[1], release[2], release[3])) elif sys.argv[1] == "movie": for release in datas: my_queue.put("%s %s %s" %(release[0], release[1], release[2])) elif sys.argv[1] == "tv": for release in datas: my_queue.put("%s %s" %(release[0], release[1])) my_queue.join() #create the connection to mysql con = None con = mdb.connect(host=conf['DB_HOST'], user=conf['DB_USER'], passwd=conf['DB_PASSWORD'], db=conf['DB_NAME'], port=int(conf['DB_PORT']), unix_socket=conf['DB_SOCKET']) cur = con.cursor() cur.execute("Select ID from releases where nfostatus <= -6") final = cur.fetchall() for item in final: cur.execute("DELETE FROM releasenfo WHERE nfo IS NULL and releaseID = %d" %(item)) final = cur.fetchall() #close connection to mysql cur.close() con.close() print("\nPostProcess Threaded Completed at %s" %(datetime.datetime.now().strftime("%H:%M:%S"))) print("Running time: %s" %(str(datetime.timedelta(seconds=time.time() - start_time))))
def changeStatus(status,port): query_sql = "UPDATE go_ss SET enable='{0}' where sign = '{1}' and port= '{2}'".format(status,Config.ServerSign,port) conn = cymysql.connect(host=Config.MYSQL_HOST, port=Config.MYSQL_PORT, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASS, db=Config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute(query_sql) cur.close() conn.commit() conn.close()
def pull_db_all_user(self): import cymysql #数据库所有用户信息 if self.cfg["ssl_enable"] == 1: conn = cymysql.connect(host=self.cfg["host"], port=self.cfg["port"], user=self.cfg["user"], passwd=self.cfg["password"], db=self.cfg["db"], charset='utf8', ssl={'ca':self.cfg["ssl_enable"],'cert':self.cfg["ssl_enable"],'key':self.cfg["ssl_enable"]}) else: conn = cymysql.connect(host=self.cfg["host"], port=self.cfg["port"], user=self.cfg["user"], passwd=self.cfg["password"], db=self.cfg["db"], charset='utf8') rows = self.pull_db_users(conn) conn.close() if not rows: logging.warn('no user in db') return rows
def __init__(self, host, username, pw, db): self._host = host self._username = username self._pw = pw self._db = db #self._con = None #self._cur = None self._con = mdb.connect(self._host, self._username, self._pw, self._db, charset='utf8') self._cur = self._con.cursor()
def test_charset(self): conn = cymysql.connect( host="localhost", user="******", passwd=self.test_passwd, db="mysql", charset="utf8mb4" ) c = conn.cursor() c.execute("select user from user where user='******'") self.assertEqual(c.fetchone()[0], 'root') conn.close()
def useredit (): try: hashcode = request.args.get ('hash', '') except: hashcode = '' conn = cymysql.connect (host = '127.0.0.1', user = '******', passwd = 'password', db = 'reg', charset = 'utf8') cur = conn.cursor () cur.execute ('select ' + ', '.join (['id'] + user_fields) + ' from users where hashcode=%s', [hashcode]) u = False for r in cur.fetchall (): u = r if u == False: cur.close () conn.close () return render_template ('template.html', title = 'Error', content = 'No such hashcode') user = {} i = 1 for f in user_fields: user[f] = u[i] i = i + 1 need_form = 0 form_errors = '' title = lang['event_edit_user'] content = '' if request.method == 'POST': need_form = 1 for f in user_fields: try: user[f] = request.form[f] except: pass if user[f] == '' or len (user[f]) > 50: form_errors = lang['form_error_user'] need_form = 0 if need_form == 0: content += '<form action="/reg/useredit?hash=' + cgi.escape (hashcode) + '" method="POST">\n' content += '<table>\n' for f in user_fields: content += '<tr><td>' + lang['event_form_' + f] + \ '</td><td><input type="text" name="' + f + '" value="' + cgi.escape (str (user[f])) + '" list="' + f + '" /></td></tr>\n' content += '</table>\n' for f in default_values: content += '<datalist id="' + f + '">\n' for v in default_values[f]: content += '<option value="' + cgi.escape (v) + '">' + cgi.escape (v) + '</option>\n' content += '</datalist>\n' content += '<tr><td colspan="2"><input type="submit" value="' + lang['event_form_edit'] + '" /></td></tr>\n' else: cur.execute ('update users set ' + \ ', '.join ([x + ' = %s' for x in user_fields]) + ' where hashcode=%s', \ [str (user[x]) for x in user_fields] + [hashcode]) content += '<h2>' + lang['event_edit_user_success'] + '</h2>' conn.commit () cur.close () conn.close () return render_template ('template.html', title = title, content = content)
def mysql(sql): import cymysql con=cymysql.connect(host='localhost',passwd='3306',db='yx',user='******') cur=con.cursor() cur.execute(sql) res=cur.fetchall() con.commit() con.close() return res
def __init__(self, dsn): ############## Connect to AqWiki db_config = dsnparse.parse(dsn) self.dbconnection = mysql.connect( user=db_config.username, passwd=db_config.password, db=db_config.paths[0], charset='utf8')
def __init__(self): self.service4 = config.PAY_STATUS.split(':') self.last_get_transfer = {} self.traffic_logs = collections.defaultdict(long) self.active_time = {} #if there was many user, this dic is too large in memeory self.port2userid = {} # pull port2userid from db, and take it to memeory conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute("SELECT id,port FROM user") rows = [] for r in cur.fetchall(): rows.append(list(r)) cur.close() conn.close() for row in rows: self.port2userid[unicode(row[1])] = row[0] # get local ip #localhost = socket.getfqdn(socket.gethostname()) #self.ip = socket.gethostbyname(localhost) #some machine, we have to specify selfip . self.ip = config.HOST_IP # ip2nodeid self.ip2nodeid = collections.defaultdict(int) #pull nodeid2ip from db, and take it to memeory conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute("SELECT id,server FROM ss_node") rows = [] for r in cur.fetchall(): rows.append(list(r)) cur.close() conn.close() self.node_id = -1 for row in rows: if row[1] == self.ip: self.node_id = row[0] if self.node_id < 0: logging.error('this machine is not added into ss-panel.') sys.exit(0)
def run_command(command, id): value = commands.getoutput(command) conn = cymysql.connect(host=configloader.get_config().MYSQL_HOST, port=configloader.get_config().MYSQL_PORT, user=configloader.get_config().MYSQL_USER, passwd=configloader.get_config().MYSQL_PASS, db=configloader.get_config().MYSQL_DB, charset='utf8') conn.autocommit(True) cur = conn.cursor() cur.execute( "INSERT INTO `auto` (`id`, `value`, `sign`, `datetime`,`type`) VALUES (NULL, 'NodeID:" + str(configloader.get_config().NODE_ID) + " Result:\n" + str(value) + "', 'NOT', unix_timestamp(),'2')") rows = cur.fetchall() cur.close() conn.close()
def push_db_all_user(): dt_transfer = DbTransfer.get_instance().get_servers_transfer() conn = cymysql.connect(**DbTransfer.get_instance().get_mysql_config()) cursor = conn.cursor() # 获取用户和端口的关系 sql = 'SELECT userId user_id, port from user' cursor.execute(sql) port_to_user = {} for item in cursor.fetchall(): port_to_user[str(item[1])] = item[0] insert_rows = [] insert_sql = 'INSERT INTO transfer (nodeId, userId, flowUp, flowDown, activeAt) VALUES (%s, %s, %s, %s, %s)' update_head = 'UPDATE user' update_sub_when = '' update_sub_when2 = '' update_sub_in = None last_time = time.strftime('%Y-%m-%d %H:%M:%S') for id in dt_transfer.keys(): # 防止受端口扫描等小流量影响 if (dt_transfer[id]) < 1024: continue user_id = port_to_user[str(id)] insert_rows.append( [config.NODE_ID, user_id, 0, dt_transfer[id], last_time]) update_sub_when += ' WHEN %s THEN flowUp+%s' % (user_id, 0 ) # all in d update_sub_when2 += ' WHEN %s THEN flowDown+%s' % (user_id, dt_transfer[id]) if update_sub_in is not None: update_sub_in += ',%s' % user_id else: update_sub_in = '%s' % user_id cursor.executemany(insert_sql, insert_rows) conn.commit() if update_sub_in is None: return update_sql = update_head + ' SET flowUp = CASE userId' + update_sub_when + \ ' END, flowDown = CASE userId' + update_sub_when2 + \ ' END, activeAt = "%s"' % (last_time) + \ ' WHERE userId IN (%s)' % update_sub_in cursor.execute(update_sql) cursor.close() conn.commit()
def pull_db_all_user(): conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute( "SELECT port, u, d, transfer_enable, passwd, switch, enable, id, pay_status FROM user" ) rows = [] for r in cur.fetchall(): rows.append(list(r)) cur.close() conn.close() return rows
def __init__(self): self.SENDGRID_API_KEY = 'SG.www--www.www' while 1: self.conn = cymysql.connect(host=Config.MYSQL_HOST, port=Config.MYSQL_PORT, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASS, db=Config.MYSQL_DB, charset='utf8') self.cur = self.conn.cursor() self.scan_users() self.conn.commit() self.cur.close() self.conn.close() pause.minutes(10)
def test_issue_33(self): conn = cymysql.connect(host="localhost", user="******", db=self.databases[0]["db"], charset="utf8") c = conn.cursor() try: c.execute( _uni("create table hei\xc3\x9fe (name varchar(32))", "utf8")) c.execute( _uni( "insert into hei\xc3\x9fe (name) values ('Pi\xc3\xb1ata')", "utf8")) c.execute(_uni("select name from hei\xc3\x9fe", "utf8")) self.assertEqual(_uni("Pi\xc3\xb1ata", "utf8"), c.fetchone()[0]) finally: c.execute(_uni("drop table hei\xc3\x9fe", "utf8"))
def query(sql): try: conn = cymysql.connect(host=config.DB_HOST, port=config.DB_PORT, user=config.DB_USER, passwd=config.DB_PASSWORD, db=config.DB_NAME, charset='utf8') cur = conn.cursor() cur.execute(sql) result = cur.fetchall() cur.close() conn.commit() conn.close() return result except Exception as e: logger.error(e)
def begin(self): """ Begins the persistence connection session Raises: PersistenceException: There is DB error """ try: self.connection = dbdriver.connect( host=self.connectionConfig["host"], user=self.connectionConfig["user"], passwd=self.connectionConfig["passwd"], db=self.connectionConfig["db"], port=self.connectionConfig["port"], connect_timeout=self.connectionConfig["connect_timeout"]) self.cursor = self.connection.cursor() except DatabaseError as e: raise PersistenceException(*e.args)
def get_settings_table_values(): # Get the current alert limit settings from the database conn = cymysql.connect(servername, username, password, dbname, cursorclass=cymysql.cursors.DictCursor) curs = conn.cursor() curs.execute("SET sql_notes = 0; ") curs.execute("SELECT * FROM settings WHERE pk = 1") setting_values = curs.fetchone() # divide offset percent by 100 to convert to decimal setting_values["offset_percent"] = (setting_values["offset_percent"] / 100) close_database_connection(conn, curs) return setting_values
def email_user(): global email_list # 将需要发送邮件的用户ID写入数据库 email_list = list(set(email_list)) conn = cymysql.connect(host=Config.MYSQL_HOST, port=Config.MYSQL_PORT, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASS, db=Config.MYSQL_DB, charset='utf8') cur = conn.cursor() for uid in email_list: email_sql = 'Insert ignore into email (uid) values (%s);' % uid cur.execute(email_sql) cur.close() conn.commit() conn.close() email_list = []
def getall(self): conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() main_ip = self.getouterip() cur.execute('SELECT * FROM user where main_ip= "%s"' % main_ip) rows = [] for r in cur.fetchall(): n_time = datetime.datetime.now() if n_time < r[6]: rows.append(r) cur.close() conn.close() return rows
def edit_user(): dbc = config['db'] conn = cymysql.connect(host=dbc['host'], user=dbc['user'], passwd=dbc['passwd'], db=dbc['db'], charset=dbc['charset']) cur = conn.cursor() user_id = request.form['id'] first_name = request.form['first_name'] last_name = request.form['last_name'] gender = request.form['gender'] school = request.form['school'] grade = request.form['grade'] degree = request.form['degree'] state = 100 cur.execute('update users set first_name=%s, last_name=%s, gender=%s, school=%s, grade=%s, degree=%s, state=%s where id=%s', \ [first_name, last_name, gender, school, grade, degree, str(state), str(user_id)]) conn.commit() cur.close() conn.close() return redirect('/user' + str(user_id), code = 302)
def pull_db_all_user(): #数据库所有VIP用户信息 conn = cymysql.connect(host=Config.MYSQL_HOST, port=Config.MYSQL_PORT, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASS, db=Config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute( "SELECT port, u, d, transfer_enable, passwd, plan, enable, vip_end_time FROM user WHERE port != 0 AND plan != \'C\'" ) rows = [] for r in cur.fetchall(): rows.append(list(r)) cur.close() conn.close() return rows
def pull_db_all_user(): #数据库所有用户信息 conn = cymysql.connect(host=Config.MYSQL_HOST, port=Config.MYSQL_PORT, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASS, db=Config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute( "SELECT port, u, d, transfer_enable, sspassword, switch, enable,type,overdue FROM go_ss where sign ='{0}' and switch=1 and enable=1" .format(Config.ServerSign)) rows = [] for r in cur.fetchall(): rows.append(list(r)) cur.close() conn.close() return rows
def fetchAll(self): result = [] try: conn = cymysql.connect( host = config.DB_HOST, user = config.DB_USER, passwd = config.DB_PASS, db = config.DB_NAME, port = config.DB_PORT, ) cur = conn.cursor() cur.execute('SELECT %s FROM %s' % (', '.join(DBconnect.alias), DBconnect.table)) result = cur.fetchall() cur.close() conn.close() except Exception as e: logging.error('Database error: %s' % str(e)) return result
def writeToDB(dateTime=None, loudness=None, geo_lat=None, geo_long=None): if dateTime is None: raise Exception('No date/time provided!') if loudness is None: raise Exception('No loudness provided!') if geo_lat is None: raise Exception('No lat provided!') if geo_long is None: raise Exception('No long provided!') # Accept lat as float or int if not isinstance(geo_lat, float): if not isinstance(geo_lat, int): raise Exception('Latitude should be a float or int') # Accept long as float or int if not isinstance(geo_long, float): if not isinstance(geo_long, int): raise Exception('Longitude should be a float or int') try: conn = cymysql.connect(host='108.167.140.23', user='******', passwd='5LO$3c_73=]B', db='nicolas_CeroBuks', charset='utf8') cur = conn.cursor() except: print("Connection to DB failed.") return query = "INSERT INTO GUNSHOT VALUES ( NULL, \'" +\ str(dateTime) + "\', " + str(loudness) + ", " +\ str(geo_lat) + ", " + str(geo_long) + ");" try: cur.execute(query) except: print("Insert query execution failed.")
def pull_db_all_user(): conn = cymysql.connect(**DbTransfer.get_instance().get_mysql_config()) cursor = conn.cursor() active_at = time.strftime('%Y-%m-%d %H:%M:%S') update_sql = 'UPDATE node SET activeAt = "%s" WHERE nodeId = %d' % ( active_at, config.NODE_ID) cursor.execute(update_sql) conn.commit() cursor.execute( "SELECT port, flowUp flow_up, flowDown flow_down, transferEnable transfer_enable, password, isLocked is_locked FROM user" ) rows = [] for r in cursor.fetchall(): rows.append(list(r)) cursor.close() conn.close() return rows
def connect_to_umls_db(host, user, password, db_name="umls", encoding="latin1"): """Connects to an UMLS MySQL database. This function **must** be called before using UMLS.""" global db, db_cursor, _encoding _encoding = encoding try: import cymysql as sql_module except: try: import pymysql as sql_module except: import MySQLdb as sql_module db = sql_module.connect(host=host, user=user, passwd=password, db=db_name) db_cursor = db.cursor()
def test_issue_17(self): """ could not connect mysql use passwod """ conn = self.connections[0] host = self.databases[0]["host"] db = self.databases[0]["db"] c = conn.cursor() # grant access to a table to a user with a password try: c.execute("create table issue17 (x varchar(32) primary key)") c.execute("insert into issue17 (x) values ('hello, world!')") c.execute("grant all privileges on %s.issue17 to 'issue17user'@'%%' identified by '1234'" % db) conn.commit() conn2 = cymysql.connect(host=host, user="******", passwd="1234", db=db) c2 = conn2.cursor() c2.execute("select x from issue17") self.assertEqual("hello, world!", c2.fetchone()[0]) finally: c.execute("drop table issue17")
def runSql(self, sql): result = [] try: conn = cymysql.connect( host = config.DB_HOST, user = config.DB_USER, passwd = config.DB_PASS, db = config.DB_NAME, port = config.DB_PORT, ) cur = conn.cursor() cur.execute(sql) result = cur.fetchall() cur.close() conn.commit() conn.close() except Exception as e: logging.error('Database error: %s' % str(e)) return result
def getConnection(self): dbconfig = DbConfig(self.dbname,self.configfile) conf = dbconfig.getConfig() self.conf= conf dbtype = conf['type'] host = conf['host'] username = conf['username'] password = conf['password'] database = conf['database'] port = int(conf['port']) encoding = conf['encoding'] if dbtype=='mysql': try: import cymysql c = cymysql.connect(host,username,password,database,charset=encoding,port=port) except Exception, e: print e raise Exception
def run_command(self, command, id): value = subprocess.check_output(command.split(" ")).decode("utf-8") if configloader.get_config().API_INTERFACE == "modwebapi": global webapi webapi.postApi( "func/autoexec", {"node_id": configloader.get_config().NODE_ID}, { "data": [{ "value": "NodeID:" + str(configloader.get_config().NODE_ID) + " Exec Command ID:" + str(configloader.get_config().NODE_ID) + " Result:\n" + str(value), "sign": str(value), "type": 2, }] }, ) else: import cymysql conn = cymysql.connect( host=configloader.get_config().MYSQL_HOST, port=configloader.get_config().MYSQL_PORT, user=configloader.get_config().MYSQL_USER, passwd=configloader.get_config().MYSQL_PASS, db=configloader.get_config().MYSQL_DB, charset="utf8", ) conn.autocommit(True) cur = conn.cursor() cur.execute( "INSERT INTO `auto` (`id`, `value`, `sign`, `datetime`,`type`) VALUES (NULL, 'NodeID:" + str(configloader.get_config().NODE_ID) + " Result:\n" + str(value) + "', 'NOT', unix_timestamp(),'2')") rows = cur.fetchall() cur.close() conn.close()
def active(): config = get_dbconfig() conn = cymysql.connect(host=config['host'], user=config['user'], passwd=config['password'], db=config['db']) cur = conn.cursor() cur.execute('select d, active, pid from user') for r in cur.fetchall(): if r[1] == 0 and r[0] >= 100000000: cur.execute('select billingcycle from tblhosting where id='+r[2]) for t in cur.fetchall(): if t[0] == 'Monthly': end_date = Set_date(1) reg_date = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") cur.execute('update user SET reg_date=\''+reg_date+'\', end_date=\''+end_date+'\', active=1 where pid='+r[2]) conn.commit() elif t[0] == 'Quarterly': end_date = Set_date(3) reg_date = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") cur.execute('update user SET reg_date=\''+reg_date+'\', end_date=\''+end_date+'\', active=1 where pid='+r[2]) conn.commit() elif t[0] == 'Semi-Annually': end_date = Set_date(6) reg_date = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") cur.execute('update user SET reg_date=\''+reg_date+'\', end_date=\''+end_date+'\', active=1 where pid='+r[2]) conn.commit() elif t[0] == 'Annually': end_date = Set_date(12) reg_date = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") cur.execute('update user SET reg_date=\''+reg_date+'\', end_date=\''+end_date+'\', active=1 where pid='+r[2]) conn.commit() elif t[0] == 'Biennially': end_date = Set_date(24) reg_date = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") cur.execute('update user SET reg_date=\''+reg_date+'\', end_date=\''+end_date+'\', active=1 where pid='+r[2]) conn.commit() elif t[0] == 'Triennially': end_date = Set_date(36) reg_date = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") cur.execute('update user SET reg_date=\''+reg_date+'\', end_date=\''+end_date+'\', active=1 where pid='+r[2]) conn.commit() else: print('one time')
def push_traffic_usage(dt_transfer): case1 = '' case2 = '' query_set = None last_time = datetime.utcnow() for port in dt_transfer.keys(): # case1 += ' WHEN %s THEN `traffic_up`+%s' % (port, 0) # all in traffic_down case2 += ' WHEN %s THEN `traffic_down`+%s' % (port, dt_transfer[port]) if query_set is not None: query_set += ',%s' % port else: query_set = '%s' % port if case2 == '': # since case1 is never changed return sql_dict = { 'table': config.MYSQL_USER_TABLE, 'case1': case1, 'case2': case2, 'last_time': str(last_time), 'query_set': query_set } # query_sql = "UPDATE {table} SET `traffic_up`=CASE ss_port {case1} END," \ query_sql = "UPDATE {table} SET " \ "`traffic_down`=CASE ss_port {case2} END," \ "`last_use_time`='{last_time}'" \ " WHERE ss_port IN ({query_set})".format(**sql_dict) conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute(query_sql) cur.close() conn.commit() conn.close() if config.SS_VERBOSE: logging.info('db uploaded')
def pull_db_all_user(): if config.API_ENABLED: rows = DbTransfer.pull_api_user() if config.SS_VERBOSE: logging.info('api downloaded') return rows else: string = '' for index in range(len(config.SS_SKIP_PORTS)): port = config.SS_SKIP_PORTS[index] if config.SS_VERBOSE: logging.info('db skipped port %d' % port) if index == 0: string = ' WHERE `port`<>%d' % port else: string = '%s AND `port`<>%d' % (string, port) if hasattr(config, 'GROUP'): string += ' AND `group` > %d' % config.GROUP conn = cymysql.connect(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER, passwd=config.MYSQL_PASS, db=config.MYSQL_DB, charset='utf8') cur = conn.cursor() cur.execute( 'SELECT port, u, d, transfer_enable, passwd, switch, enable, method, email FROM %s%s ORDER BY `port` ASC' % (config.MYSQL_USER_TABLE, string)) rows = [] for r in cur.fetchall(): rows.append(list(r)) # Release resources cur.close() conn.close() if config.SS_VERBOSE: logging.info('db downloaded, count %d' % len(rows)) return rows
def put_get_all(serverip, last_get_time, allflow): #数据库所有用户信息 conn = cymysql.connect(host=Config.MYSQL_HOST, port=Config.MYSQL_PORT, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASS, db=Config.MYSQL_DB, charset='utf8') cur = conn.cursor() allquery = "call p_put_get_all('%s','%s','%s')" % ( serverip, last_get_time, allflow) logging.info('dbquery:%s' % (allquery)) cur.execute(allquery) #SELECT port,passwd FROM user rows = [] for r in cur.fetchall(): rows.append(list(r)) cur.close() if len(rows) > 0: conn.commit() conn.close() return rows
def __save_to_mysql(self, data): db = cymysql.connect(user='******', port=3306, passwd='woailxn', host='localhost', db='search', charset='utf8') cursor = db.cursor() try: sql = """INSERT INTO SAVE_DATA(TITLE,PRICE,LINK,STORE,DATA_FROM,UP_TIME) VALUES ('%s','%s','%s','%s','%s','%s');"""\ %(data['title'],data['price'],data['link'],data['store'],data['data_from'],data['up_time']) cursor.execute(sql) cursor.close() db.commit() except Exception as err: db.rollback() raise err finally: db.close()
def test_cyclic_reference_leak(self): """ Ensure that a cylic reference between Connection and Cursor isn't leaking objects. """ import gc gc.collect() for i in range(0, 5): conn = cymysql.connect(**self.databases[0]) c = conn.cursor() c.execute('show tables in %s' % (self.databases[0]['db'])) c.close() conn.close() gc.collect() conns = 0 for obj in gc.garbage: if 'Connection' in repr(obj): conns += 1 if conns > 0: raise Exception('%d connections were leaked.' % (conns))
def update_user(): # 更新用户套餐 while True: pause.minutes(1) conn = cymysql.connect(host=Config.MYSQL_HOST, port=Config.MYSQL_PORT, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASS, db=Config.MYSQL_DB, charset='utf8') cur = conn.cursor() # 体验套餐流量用完后变成免费套餐 net_query_sql = 'UPDATE user set plan=\'C\',u=0, money = 0, d=0, transfer_enable=\'3221225472\' where transfer_enable-u-d < 10240 and plan = \'D\';' cur.execute(net_query_sql) conn.commit() # A/B/D/G 套餐到期后变成免费套餐 net_query_sql = 'UPDATE user set plan=\'C\', money=0, u=0, d=0, transfer_enable=\'3221225472\' where vip_end_time < ' + str( int(time.time()) ) + ' and ( plan = \'A\' or plan = \'B\' or plan = \'D\' or plan = \'G\' );' cur.execute(net_query_sql) conn.commit() # B 套餐每月减少money和更新流量 net_query_sql = 'UPDATE user set money=money-10, vip_month=vip_month-1, u=0, d=0, transfer_enable=\'32212254720\' where vip_end_time-(3600*24*31*( vip_month - 1 )) < ' + str( int(time.time())) + ' and plan = \'B\';' cur.execute(net_query_sql) conn.commit() # 更新用户在线信息 net_query_sql = 'UPDATE user set w_n = 0 where t + 20 < ' + str( int(time.time())) + ';' cur.execute(net_query_sql) conn.commit() cur.close() conn.close()
def update_node_speed(): # 更新服务器网速 while True: conn = cymysql.connect(host=Config.MYSQL_HOST, port=Config.MYSQL_PORT, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASS, db=Config.MYSQL_DB, charset='utf8') cur = conn.cursor() # interface iface = Config.iface # before net_rx_before = os.popen( 'ifconfig ' + iface + '|grep bytes|cut -d \":\" -f 2|cut -d \" \" -f 1').read() time.sleep(1) # after net_rx_after = os.popen( 'ifconfig ' + iface + '|grep bytes|cut -d \":\" -f 2|cut -d \" \" -f 1').read() # calculate speed = (int(net_rx_after) - int(net_rx_before)) / 1024 # unit unit = " K" if speed > 1024: unit = " M" speed = speed / 1024 node_speed = str(speed) + unit + 'B/s' # update net_query_sql = 'UPDATE ss_node set node_speed=\'' + node_speed + \ '\' where id=\'' + str(node_number) + '\';' cur.execute(net_query_sql) conn.commit() cur.close() conn.close()
def run_command(self, command, id): value = subprocess.check_output(command.split(' ')).decode('utf-8') if configloader.get_config().API_INTERFACE == 'modwebapi': global webapi webapi.postApi( 'func/autoexec', {'node_id': configloader.get_config().NODE_ID}, { 'data': [{ 'value': 'NodeID:' + str(configloader.get_config().NODE_ID) + ' Exec Command ID:' + str(configloader.get_config().NODE_ID) + " Result:\n" + str(value), 'sign': str(value), 'type': 2 }] }) else: import cymysql conn = cymysql.connect(host=configloader.get_config().MYSQL_HOST, port=configloader.get_config().MYSQL_PORT, user=configloader.get_config().MYSQL_USER, passwd=configloader.get_config().MYSQL_PASS, db=configloader.get_config().MYSQL_DB, charset='utf8') conn.autocommit(True) cur = conn.cursor() cur.execute( "INSERT INTO `auto` (`id`, `value`, `sign`, `datetime`,`type`) VALUES (NULL, 'NodeID:" + str(configloader.get_config().NODE_ID) + " Result:\n" + str(value) + "', 'NOT', unix_timestamp(),'2')") rows = cur.fetchall() cur.close() conn.close()