# -*-coding:utf-8 -*-
import pymssql
import numpy as np
import pandas as pd
# 创建数据库连接
def createConnection():
conn = pymssql.connect(server=r'DESKTOP-4MGKDSV\SQLEXPRESS',
user='sa',
password='li199630',
database='Airticket')
if conn:
print('连接成功')
return conn
else:
print('连接失败')
return False
#关闭数据库连接。
def closeConnection(conn):
print('已关闭连接')
conn.close()
#得到查询数据的列表
def get_list(conn,sql):
cursor = conn.cursor()
cursor.execute(sql)
rs = cursor.fetchall()
return rs
#开始
def chahangban(conn):
cursor = conn.cursor()
print('选择查询方式：\n 1. 输入航班号\n 2. 输入地点')
hangbannum = pd.DataFrame()
columns =['航班号','出发时间','起始地','目的地','商务舱','头等舱','经济舱','价格']
choice = input()
if choice == '1':
print("输入航班号:")
num = input()
sql = "select Ano,Fno,Ftime,Ac1,ac2,ac3,Fare from Flight where Fno = '{0}'".format(num)
rs = get_list(conn,sql)
sql1 = "select Spoint as 起始地,Destination from Airline where Ano = '{0}'".format(rs[0][0])
rs1 = get_list(conn,sql1)
# print(rs1)
# print('航班号\t出发时间\t起始地\t目的地\t商务舱\t头等舱\t经济舱')
# print(rs[0][1], rs[0][2], rs1[0][0], rs1[0][1], rs[0][3], rs[0][4],rs[0][5])
ll = [rs[0][1], rs[0][2], rs1[0][0], rs1[0][1], rs[0][3], rs[0][4],rs[0][5],rs[0][6]]
ll = np.array([ll])
data = pd.DataFrame(ll,columns=columns)
hangbannum = data
print(data)
else:
print("输入起始地和目的地，以空格隔开")
num = input().split(' ')
sql = "select Ano,Spoint,Destination from Airline where Spoint = '{0}' and Destination = '{1}'".format(num[0],num[1])
rs = get_list(conn,sql)
sql1 = "select Fno,Ftime,Ac1,ac2,ac3,Fare from Flight where Ano = '{0}'".format(rs[0][0])
rs1 = get_list(conn, sql1)
ll = [rs1[0][0], rs1[0][1], rs[0][1], rs[0][2], rs1[0][2], rs1[0][3], rs1[0][4],rs1[0][5]]
ll = np.array([ll])
data = pd.DataFrame(ll, columns=columns)
hangbannum = data
print(data)
print('是否预定机票\nY: 是\nN: 否')
answer = input()
if answer == 'Y' or answer == 'y':
print("请输入顾客的姓名，性别，身份证号，电话")
print("以空格隔开")
info = input().split(' ')
print(info)
sql = "insert into Passenger values('{0}','{1}','{2}','{3}')".format(info[0], info[1],info[2],info[3])
# print(sql)
cursor.execute(sql)
print(hangbannum)
print('输入你选择的航班号和客舱等级：\n1: 商务舱\n2: 头等舱\n3: 经济舱')
print("以空格隔开")
an1 = input().split()
fno = hangbannum['航班号'][0]
price = int(hangbannum['价格'][0])
ac1 = int(hangbannum['商务舱'][0])
ac2 = int(hangbannum['头等舱'][0])
ac3 = int(hangbannum['经济舱'][0])
ftime = hangbannum['出发时间'][0]
ac = 0
dengji = ''
if an1[-1] == '1':
ac1 = ac1 - 1
sql = "update Flight set ac1 = {0} where fno = '{1}'".format(ac1,fno)
cursor.execute(sql)
dengji = '商务舱'
# print(sql)
sql1 = "insert into Ticket values('{0}','{1}','{2}','{3}','{4}',{5})".format(100-ac1,info[2],fno,ftime,dengji, price * 3)
cursor.execute(sql1)
# print(sql1)
elif an1[-1] == '2':
price = price * 2
ac2 = ac2 -1
sql = "update Flight set ac2 = {0} where fno = '{1}'".format(ac2, fno)
# print(sql)
cursor.execute(sql)
dengji = '头等舱'
sql1 = "insert into Ticket values('{0}','{1}','{2}','{3}','{4}',{5})".format(200 - ac2+100, info[2], fno, ftime,
dengji, price * 2)
cursor.execute(sql1)
# print(sql1)
else:
ac3 = ac3-1
sql = "update Flight set ac3 = {0} where fno = '{1}'".format(ac3, fno)
dengji = '经济舱'
# print(sql)
cursor.execute(sql)
sql1 = "insert into Ticket values('{0}','{1}','{2}','{3}','{4}',{5})".format(300 - ac3+300, info[2], fno, ftime,
dengji, price )
# print(sql1)
cursor.execute(sql1)
# print(ac1,ac2,ac3)
# print(price)
conn.commit()
print('预定成功')
sql2 = "select * from Ticket where id = '{0}'".format(info[2])
rs = get_list(conn, sql2)
if rs:
columns = ['座位号', '身份证号', '航班号', '起飞时间', '机舱等级', '价格']
data = pd.DataFrame(rs, columns=columns)
print(data)
else:
return
def back(conn):
cursor = conn.cursor()
print("输入身份证号：")
sid = input()
sql = "select * from Ticket where id = '{0}'".format(sid)
rs = get_list(conn, sql)
if rs :
columns = ['座位号', '身份证号', '航班号', '起飞时间', '机舱等级', '价格']
data = pd.DataFrame(rs,columns=columns)
print(data)
print('是否选择退票\nY: 是\nN: 否')
answer = input()
if answer == 'Y' or answer == 'y':
sql = "delete from Ticket where id = '{0}'".format(sid)
cursor.execute(sql)
# print(sql)
sql1 = "delete from Passenger WHERE id = '{0}'".format(sid)
# print(sql1)
cursor.execute(sql1)
dengji = data['机舱等级'][0]
ac = ''
fno = data['航班号'][0]
if dengji == '商务舱' :
ac = 'ac1'
elif dengji == '头等舱':
ac = 'ac2'
else:
ac = 'ac3'
sql = "update Flight set {0} = {0} + 1 where fno = {1}".format(ac,fno)
cursor.execute(sql)
conn.commit()
print('退票成功')
else:
print("你还没有机票")
# Company Airline Flight Steward Food
def sql_start():
while 1:
conn = createConnection()
print('输入你的选择:\n1.查询航班和订票\n2.个人机票信息查询与退票\n0.退出')
choice = input()
if choice == '1':
chahangban(conn)
elif choice == '2':
back(conn)
elif choice == '0':
break
closeConnection(conn)
closeConnection(conn)
if __name__ == '__main__':
sql_start()
