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()
