def write_csv(): book = xlrd.open_workbook(r"D:\FFDT.xlsx") wb = xlutils.copy.copy(book) # 创建一个可写入的副本 ws = wb.get_sheet(0) ws.write(4, 0, sys.argv[1]) list_comp = ['南航','东上航','国深航','海航','川航','山东航','厦航','天津航','首都航','祥鹏航','吉祥航'] list_comp1 = [] cap = "select sum(座位数) from dss_leg where 航线中文='" + sys.argv[1] + "'" + " and 飞行年='" + '2018年' + "'" if(str(cap[0][0]) == 'None'): print("无"+sys.argv[1]+"航线,请核实!") sys.exit(0) cap = tuple_float(connect_mysql(cap)) cap2 = 1 for i in range(len(list_comp)): cap1 = "select sum(座位数) from dss_leg where 航线中文='"+sys.argv[1]+"'"+" and 合并承运人='"+list_comp[i]+"'"+" and 飞行年='"+'2018年'+"'" list_comp1.append(tuple_float(connect_mysql(cap1))/ cap) cap1 = tuple_precent(tuple_float(connect_mysql(cap1)) / cap) ws.write(4, i+1, cap1) cap2 = cap2 - list_comp1[i] cap2 = round_up(cap2) #四舍五入 ws.write(4,12,tuple_precent(cap2)) wb.save(r"D:\FFDT1.xls") print(sys.argv[1]) work = xlsxwriter.Workbook(r"D:\FFDT1.xls") worksheet = work.get_sheet(0) chart_pie(worksheet, list_comp, list_comp1)
def update_device(DeviceMac, DeviceIp, DeviceWifi, Devicepwd, ExtraInfo, DeveloperId, DeviceName, catId=1, Status=1): # update the info of device """register;deviceID:mdeviceID;userID:muserid;userKey:muserkey;""" conn = mysql.connect_mysql() cur = conn.cursor() sql = 'update devices ' + \ """set deviceName=%s, deviceIp=%s, deviceWifi=%s, devicepwd=%s, extraInfo=%s, developerId=%s, catId=%s, status=%s where deviceMac=%s""" value = [ DeviceName, DeviceIp, DeviceWifi, Devicepwd, ExtraInfo, DeveloperId, catId, Status, DeviceMac ] try: cur.execute(sql, value) except MySQLdb.Error, e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def queryMysql(): url = request.path _, db, db_table = url.split('/') raw_query = request.form['query'] # return render_template('index.html', query=raw_query) q = buildQueryFromInput(raw_query) connection = connect_mysql(host='cs527project1group5.cnpt9dsbfddc.us-east-1.rds.amazonaws.com', user='******', password='******', db=db_table, port=3306) try: col_name, res, query_time = connection.make_query(q) except Exception as e: col_name=[] res = [] query_time = 'query error' connection.disconnect() return render_template('index.html', db=db, db_table=db_table, col_name=col_name, res=res, query_time=query_time, query=raw_query)
def register_device(DeviceMac, DeviceIp, DeviceWifi, Devicepwd, ExtraInfo, DeveloperId, DeviceName, catId=1, Status=1): #insert the info of device into table """register;deviceID:mdeviceID;userID:muserid;userKey:muserkey;""" conn = mysql.connect_mysql() cur = conn.cursor() sql = """insert into devices (deviceName, deviceMac, deviceIp, deviceWifi, devicepwd, extraInfo, developerId, catId, status)""" \ + """values(%s,%s,%s,%s,%s,%s,%s,%s,%s)""" value = [ DeviceName, DeviceMac, DeviceIp, DeviceWifi, Devicepwd, ExtraInfo, DeveloperId, catId, Status ] try: cur.execute(sql, value) except MySQLdb.Error, e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def get_history(deviceId, userId, num): conn = mysql.connect_mysql() cur = conn.cursor() sql = '''select * from commands WHERE (deviceId, userId) in((%s,%s)) ORDER BY commandId DESC LIMIT 1,%s''' value=[deviceId, userId, num] try: cur.execute(sql,value) except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def get_history(deviceId, userId, num): conn = mysql.connect_mysql() cur = conn.cursor() sql = '''select * from commands WHERE (deviceId, userId) in((%s,%s)) ORDER BY commandId DESC LIMIT 1,%s''' value = [deviceId, userId, num] try: cur.execute(sql, value) except MySQLdb.Error, e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def get_account_by_key(key): conn = mysql.connect_mysql() cur = conn.cursor() sql = 'select * from developer_account '+ 'WHERE accountKey=%s' value=[key] try: cur.execute(sql,value) except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def alter_status(deviceId, userId, status): conn = mysql.connect_mysql() cur = conn.cursor() sql = '''update commands set status=%s WHERE (deviceId, userId) in((%s,%s)) order by commandId desc limit 1''' value=[ status, deviceId, userId] try: cur.execute(sql,value) except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1]) return -1
def alter_status(deviceId, userId, status): conn = mysql.connect_mysql() cur = conn.cursor() sql = '''update commands set status=%s WHERE (deviceId, userId) in((%s,%s)) order by commandId desc limit 1''' value = [status, deviceId, userId] try: cur.execute(sql, value) except MySQLdb.Error, e: print "Mysql Error %d: %s" % (e.args[0], e.args[1]) return -1
def occupy(cap,p): print((cap)) if(str(cap[0])[1:5] == 'None'): print("无"+sys.argv[1]+"航线,请核实!") sys.exit(0) else: if(str(p[0])[1:5] == 'None'): print("该航司无此航线,请核实!") return '{:.2%}'.format(0) else: return tuple_precent(tuple_float(connect_mysql(p))/cap)
def get_device_by_developerId(developerId): conn = mysql.connect_mysql() cur = conn.cursor() sql = 'select * from devices '+ 'WHERE developerId=%s' value=[developerId] try: cur.execute(sql,value) except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def get_device_by_developerId(developerId): conn = mysql.connect_mysql() cur = conn.cursor() sql = 'select * from devices ' + 'WHERE developerId=%s' value = [developerId] try: cur.execute(sql, value) except MySQLdb.Error, e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def insert(deviceId, userId, command, status=1): conn = mysql.connect_mysql() cur = conn.cursor() sql = """insert into commands (deviceId, userId, command, status)""" \ + """values(%s,%s,%s,%s)""" value = [deviceId, userId, command, status] try: cur.execute(sql,value) except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def list_devices(accountKey, userId): conn = mysql.connect_mysql() cur = conn.cursor() sql = '''SELECT * FROM developer_user_device WHERE (accountKey, userId) in((%s, %s))''' value = [accountKey, userId] try: cur.execute(sql, value) except MySQLdb.Error, e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def list_devices(accountKey, userId): conn = mysql.connect_mysql() cur = conn.cursor() sql = '''SELECT * FROM developer_user_device WHERE (accountKey, userId) in((%s, %s))''' value=[accountKey, userId] try: cur.execute(sql,value) except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def insert(deviceId, userId, command, status=1): conn = mysql.connect_mysql() cur = conn.cursor() sql = """insert into commands (deviceId, userId, command, status)""" \ + """values(%s,%s,%s,%s)""" value = [deviceId, userId, command, status] try: cur.execute(sql, value) except MySQLdb.Error, e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def bind_device(accountKey, userId, deviceId): """register;deviceID:mdeviceID;userID:muserid;userKey:muserkey;""" conn = mysql.connect_mysql() cur = conn.cursor() sql = """insert into developer_user_device (accountKey, userId, deviceId)""" + """values(%s,%s,%s)""" value = [accountKey, userId, deviceId] try: cur.execute(sql,value) except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def bind_device(accountKey, userId, deviceId): """register;deviceID:mdeviceID;userID:muserid;userKey:muserkey;""" conn = mysql.connect_mysql() cur = conn.cursor() sql = """insert into developer_user_device (accountKey, userId, deviceId)""" + """values(%s,%s,%s)""" value = [accountKey, userId, deviceId] try: cur.execute(sql, value) except MySQLdb.Error, e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def register_device(DeviceMac, DeviceIp, DeviceWifi, Devicepwd, ExtraInfo, DeveloperId, DeviceName, catId=1, Status=1): #insert the info of device into table """register;deviceID:mdeviceID;userID:muserid;userKey:muserkey;""" conn = mysql.connect_mysql() cur = conn.cursor() sql = """insert into devices (deviceName, deviceMac, deviceIp, deviceWifi, devicepwd, extraInfo, developerId, catId, status)""" \ + """values(%s,%s,%s,%s,%s,%s,%s,%s,%s)""" value = [DeviceName, DeviceMac, DeviceIp, DeviceWifi, Devicepwd, ExtraInfo, DeveloperId, catId, Status] try: cur.execute(sql,value) except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
def update_device(DeviceMac, DeviceIp, DeviceWifi, Devicepwd, ExtraInfo, DeveloperId, DeviceName, catId=1, Status=1): # update the info of device """register;deviceID:mdeviceID;userID:muserid;userKey:muserkey;""" conn = mysql.connect_mysql() cur = conn.cursor() sql = 'update devices ' + \ """set deviceName=%s, deviceIp=%s, deviceWifi=%s, devicepwd=%s, extraInfo=%s, developerId=%s, catId=%s, status=%s where deviceMac=%s""" value = [DeviceName, DeviceIp, DeviceWifi, Devicepwd, ExtraInfo, DeveloperId ,catId, Status, DeviceMac] try: cur.execute(sql,value) except MySQLdb.Error,e: print "Mysql Error %d: %s" % (e.args[0], e.args[1])
:param init_date: First day in the list :param until_yesterday: :return: list string days """ if until_yesterday: start = datetime.datetime.strptime(init_date, "%Y-%m-%d").date() yesterday = date.today() - timedelta(1) lst_days = [(start + datetime.timedelta(days=x)). strftime("%Y-%m-%d") for x in range(0, (yesterday - start).days + 1)] else: lst_days = [init_date] return lst_days if __name__ == "__main__": np.seterr(all='raise') args = parse_parameter() # Get mysql and big_query client connections mysql_conn = mysql.connect_mysql() client = big_query.get_client_bigquery() print(args) # Init window window.window_init() try: for day in get_days(args.day, args.until_yesterday): load_day(day, args.online, mysql_conn, client) except KeyboardInterrupt: pass mysql.close_mysql(mysql_conn)