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)
