def get(self):
connection = DB_Connection()
username = request.args.get('username', None)
password = request.args.get('password', None)
#company = request.args.get('company', None)
response = connection.account_login(username,password)
return response#json.dumps(response, indent=4, sort_keys=True, default=str)
def uploadDataFromDataFrameToDatabase(credentials,table_name,data):
"""
Given some database credentials, a table_name and some data, write a
function that
• Creates a table in the database and name it from table_name (line_details in this case)
• Takes in the data (in relational/ tabular form) and uploads it onto the line_details table
*Note-
Uploading data to SQLServer is not facilitated well using Pandas(requires SQLAlchemy engine/Sqlite), hence I am using SQLite3 to complete this project
Hence, the credentials will contain the 'db location' instead of 'username','password','host','database' as required for tranditional db systems
"""
if credentials == "" or table_name == "" :
return -1
try:
obj = DB_Connection(credentials)
conn = obj.getConnection()
#Creating table
if table_name == "line_details":
# Since this data would not change that often, we can keep a check using change in length of DataFrame & length of number of records in 'line_details'(Would prevent duplication if data doesn't change)
# Due to shortage of time, I have not implemented it.
query = "CREATE TABLE IF NOT EXISTS '" + table_name +"' (line_name VARCHAR(255),description VARCHAR(255));"
# Dumping the Relational Data into SQL Database
data.to_sql(table_name, conn, if_exists="append", index=False)
else:
query = "CREATE TABLE IF NOT EXISTS '"+table_name+"' (id TEXT,time TEXT,late INTEGER,lat DECIMAL,lon DECIMAL,nextstop TEXT,source TEXT,dest TEXT);"
obj.create_table(query)
except Error as e:
print(e)
def __init__(self, Name, Pass, Age, UserID): self.Name = Name self.Pass = Pass self.Age = Age self.UserID = UserID self.animate = Animations() self.DB = DB_Connection() self.cursor = self.DB.get_Database()
def get(self):
connection = DB_Connection()
username = request.args.get('username', None)
password = request.args.get('password', None)
company = request.args.get('company', None)
account_picture = request.args.get('account_picture', None)
occupation = request.args.get('occupation', None)
email = request.args.get('email', None)
first_name = request.args.get('first_name', None)
last_name = request.args.get('last_name', None)
response = connection.create_account(username,password,company,occupation,email,first_name,last_name)
return response#json.dumps(response, indent=4, sort_keys=True, default=str)
def __init__(self):
self.Trains_List = []
self.Stations_List = []
self.Passenger_List = []
self.Tickets_List = []
print("Trying to connect to Database")
self.animate = Animations()
self.animate.dots(3)
self.DB = DB_Connection()
self.cursor = self.DB.get_Database()
self.Update_Passenger_List()
self.Update_Stations_List()
self.Update_Trains_List()
class Passenger:
def __init__(self, Name, Pass, Age, UserID):
self.Name = Name
self.Pass = Pass
self.Age = Age
self.UserID = UserID
self.animate = Animations()
self.DB = DB_Connection()
self.cursor = self.DB.get_Database()
def get_Name(self):
return self.Name
def get_Pass(self):
return self.Pass
def get_Age(self):
return self.Age
def get_UserID(self):
return self.UserID
def menu(self):
print("hello %s" % self.Name)
class MessageBox(Popup):
dbc = DB_Connection()
def popup_dismiss(self, user, cup):
self.dismiss()
self.dbc._open()
if cup == 1:
query = (
"UPDATE user SET anz = anz+1 WHERE kuerzel = '{0}';").format(
user[10:])
elif cup == 0:
query = (
"UPDATE user SET anz = anz+4 WHERE kuerzel = '{0}';").format(
user[10:])
self.dbc.executeNonQuery(query)
self.dbc._close()
class RV(RecycleView):
rv_layout = ObjectProperty(None)
dbc = DB_Connection()
userList = ListProperty([])
def __init__(self, **kwargs):
super(RV, self).__init__(**kwargs)
self.getUserList()
def getUserList(self):
query = "SELECT kuerzel from user"
table = self.dbc.executeQuery("SELECT kuerzel FROM user")
x = []
i = 0
for i in range(0, 25):
x.append({'text': table[0 + i][0].upper(), 'id': table[0 + i][0]})
x.append({
'text': table[25 + i][0].upper(),
'id': table[25 + i][0]
})
x.append({
'text': table[50 + i][0].upper(),
'id': table[50 + i][0]
})
i = i + 1
self.data = x
def getName(self, user):
query = "SELECT kuerzel, nachname, vorname, anz FROM user WHERE kuerzel = '{0}'".format(
user[10:])
table = self.dbc.executeQuery(query)
self.user = User(table[0][0], table[0][1], table[0][2], table[0][3])
name = self.user.vorname[0].upper() + self.user.vorname[
1:] + ' ' + self.user.nachname[0].upper() + self.user.nachname[1:]
return name
def getTasse(self):
return str(self.user.anz)
def get(self):
connection = DB_Connection()
username = request.args.get('username', None)
response = connection.get_account(username)
return response#json.dumps(response, indent=4, sort_keys=True, default=str)
def get(self):
connection = DB_Connection()
response = connection.get_accounts()
return response#json.dumps(response, indent=4, sort_keys=True, default=str)
def get(self):
connection = DB_Connection()
account_id = request.args.get('account_id', None)
response = connection.delete_account(account_id)
#return treu of false if account was deleted play with deleting
return response
class Database:
global animate
def Update_Trains_List(self):
self.cursor.execute("SELECT * FROM Trains")
Trains_List_Temp = []
for row in self.cursor.fetchall():
T1 = Train(row[2], row[3], row[0], row[4], row[5])
Trains_List_Temp.append(T1)
self.Trains_List = Trains_List_Temp
def Update_Stations_List(self):
Station_List_temp = []
self.cursor.execute("SELECT * FROM Stations")
for row in self.cursor.fetchall():
S1 = Station(row[0], row[1])
Station_List_temp.append(S1)
self.Stations_List = Station_List_temp
def Update_Passenger_List(self):
Passenger_List_temp = []
self.cursor.execute("SELECT * FROM Passenger")
for row in self.cursor.fetchall():
U1 = Passenger(row[0], row[1], row[3], row[2])
Passenger_List_temp.append(U1)
self.Passenger_List = Passenger_List_temp
def Update_Ticket_List(self):
Ticket_List_Temp = []
self.cursor.execute("SELECT * FROM TICKETS")
for row in self.cursor.fetchall():
Tick1 = Ticket(row[0], row[1], row[2], row[3], row[4])
Ticket_List_Temp.append(Tick1)
self.Tickets_List = Update_Ticket_List
def set_Trains_List(self, Train_List_temp):
self.Trains_List = Train_List_temp
def set_Stations_List(self, Stations_List_temp):
self.Stations_List = Stations_List_temp
def set_User_List(self, Passenger_List_Temp):
self.Passenger_List = Passenger_List_Temp
def __init__(self):
self.Trains_List = []
self.Stations_List = []
self.Passenger_List = []
self.Tickets_List = []
print("Trying to connect to Database")
self.animate = Animations()
self.animate.dots(3)
self.DB = DB_Connection()
self.cursor = self.DB.get_Database()
self.Update_Passenger_List()
self.Update_Stations_List()
self.Update_Trains_List()
def showTable_Details(self, Name):
print("Showing details of table %s" % Name)
self.animate.dots(3)
self.cursor.execute("SELECT * FROM %s" % Name)
for row in self.cursor.fetchall():
print(", ".join(map(str, row)))
def showTable_Format(self, Name):
print("Showing format for %s" % Name)
self.animate.dots(3)
self.cursor.execute("DESCRIBE %s" % Name)
for row in self.cursor.fetchall():
print(row[0] + " " + row[1])
def show_Tables(self):
self.cursor.execute("SHOW TABLES")
print("Showing the list of tables")
self.animate.dots(3)
i = 1
for row in self.cursor.fetchall():
print("%d.%s" % (i, row[0]))
i = i + 1
def Add_Train(self, Train_Name, Source, Destination, SeatsSleeper,
SeatsNormal):
T1 = Train(SeatsSleeper, SeatsNormal, Train_Name, Source, Destination)
sql = "INSERT INTO Trains (TrainName, TrainID, SeatsSleeper, SeatsNormal, Source, Destination) VALUES (%s, %s, %s, %s, %s, %s)"
index = len(self.Trains_List) + 1
val = (Train_Name, str(index), str(SeatsSleeper), str(SeatsNormal),
str(Source), str(Destination))
self.cursor.execute(sql, val)
self.DB.db.commit()
print("Record inserted successfully into table")
self.Trains_List.append(T1)
def Add_Stations(self, StationName):
index = len(self.Stations_List) + 1
S1 = Station(StationName, index)
sql = "INSERT INTO Stations (StationName, StationNo) VALUES (%s, %s)"
val = (StationName, str(index))
self.cursor.execute(sql, val)
self.DB.db.commit()
print("Record inserted successfully into table")
self.Stations_List.append(S1)
def Add_Passenger(self, Name, Pass, Age):
index = len(self.Passenger_List) + 1
sql = "INSERT INTO Passenger (UserName, Password, UserID, Age) VALUES (%s,%s,%s,%s)"
val = (Name, Pass, str(index), str(Age))
self.cursor.execute(sql, val)
self.DB.db.commit()
print("Record inserted successfully into table")
U1 = Passenger(Name, Pass, Age, index)
self.Passenger_List.append(U1)
return index
def Book_Ticket(self, Source, Destination, UserID, TrainNo):
PNRNO = "PNR" + str(format(random.randint(int(UserID), 500), '05d'))
sql = "INSERT INTO TICKETS (Source, Destination, PNR, UserID, TrainNo) VALUES (%s, %s, %s, %s, %s)"
val = (str(Source), str(Destination), PNRNO, str(UserID), str(TrainNo))
self.cursor.execute(sql, val)
self.DB.db.commit()
self.Tickets_List.append(
Ticket(Source, Destination, PNRNO, UserID, TrainNo))
sql = "SELECT * FROM Trains WHERE TrainID = %s"
val = (str(TrainNo))
self.cursor.execute(sql, val)
Seats = int(self.cursor.fetchall()[0][3])
sql = "UPDATE Trains SET SeatsNormal = %s WHERE TrainID = %s"
val = (str(Seats - 1), TrainNo)
self.cursor.execute(sql, val)
self.DB.db.commit()
print("TrainBooked successfully")
print("Your PNR NO: " + PNRNO)
return PNRNO
def Cancel_Ticket(self, PNRNO, userID, TrainNo):
sql = "DELETE FROM TICKETS WHERE PNR = %s AND UserID = %s"
val = (PNRNO, str(userID))
self.cursor.execute(sql, val)
self.DB.db.commit()
sql = "INSERT INTO CanceledTickets (PNRNO, UserID, TrainNo) VALUES (%s, %s, %s)"
val = (PNRNO, str(userID), str(TrainNo))
self.cursor.execute(sql, val)
self.DB.db.commit()
sql = "SELECT * FROM Trains WHERE TrainID = %s"
val = (str(TrainNo))
self.cursor.execute(sql, val)
Seats = int(self.cursor.fetchall()[0][3])
sql = "UPDATE Trains SET SeatsNormal = %s WHERE TrainID = %s"
val = (str(Seats + 1), TrainNo)
self.cursor.execute(sql, val)
self.DB.db.commit()
print("successfully Canceled a Ticket")
def Login(self, userID, Pass):
sql = "SELECT * FROM Passenger WHERE UserID = %s AND Password = %s"
val = (str(userID), Pass)
rows = self.cursor.execute(sql, val)
if (rows > 0):
print("LoggedIn")
self.animate.dots(3)
index = int(userID) - 1
return (self.Passenger_List[index].get_Name(), 1)
else:
print("UserID ans Password does nto appear to be correct")
return 0