def __init__(self):
"""
Establish the database connection and if it fails to create the connection then log the error in log file
"""
conn_string = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + constant.SQL_SERVER + ';DATABASE=' + constant.SQL_DB_NAME + ';UID=' + constant.SQL_USERNAME + ';PWD=' + constant.SQL_PASSWORD
try:
self.conn = pyodbc.connect(conn_string)
except Exception as err:
logfile.Log().log_error(err)
def get_details_mongo(self):
# self.insert_mongo()
conn = mongodbConnection.MongoDBConn()
attraction_collection = conn.get_collection(
constant.MG_ATTRACTIONS_TABLE)
try:
return attraction_collection.find({})
except Exception as err:
logfile.Log().log_error(err)
finally:
conn.close_conn()
def __init__(self):
"""
Establish the mongo db connection and if it fails to create the connection then log the error in log file
"""
self.url = 'mongodb+srv://' + constant.MONGO_USERNAME + ':' + urllib.parse.quote_plus(
constant.MONGO_PASSWORD
) + '@' + constant.MG_DB_NAME + '.wurzf.mongodb.net/test'
try:
self.conn = pymongo.MongoClient(self.url)
self.conn.server_info()
except Exception as err:
logfile.Log().log_error(err)
def get_mongo_flight_details(self, collection_name):
"""
Get details from collection of mongo database
:param collection_name: Name of collection
:return:
"""
mongo_conn = mongodbConnection.MongoDBConn()
try:
mongo_collection = mongo_conn.get_collection(collection_name)
return mongo_collection.find({})
except Exception as err:
logfile.Log().log_error(err)
finally:
mongo_conn.close_conn()
def data_cleaning(self):
data = self.get_details_mongo()
df = pd.DataFrame(list(data))
df.drop(['_id'], axis='columns', inplace=True)
df.head(520)
df[df[['Places']].duplicated() == True]
# obj.close_conn()
#
# client = mo
# db = client['tourism']
# col = db['attractions']
sql_conn = sqldb.SqlDBConn().conn
cursor = sql_conn.cursor()
try:
cursor.execute('''DROP TABLE dbo.Attractions''')
cursor.execute(
'''CREATE TABLE Attractions ( City nvarchar(200) , Places nvarchar(200) , Address nvarchar(200) , Website nvarchar(200),Phone nvarchar(200) , Rating nvarchar(200) , Review nvarchar(200))'''
)
for index, row in df.iterrows():
cursor.execute(
"INSERT INTO Attractions (City,Places,Address,Website,Phone,Rating,Review) values(?,?,?,?,?,?,?)",
row.City, row.Places, row.Address, row.Website, row.Phone,
row.Rating, row.Review)
sql_conn.commit()
except Exception as err:
logfile.Log().log_error(err)
finally:
cursor.close()
sql_conn = sqldb.SqlDBConn().conn
output_df = pd.DataFrame(
pd.read_sql(
"SELECT City,Avg(Review) as Avg_Review from dbo.attractions GROUP By City",
sql_conn))
fig = plt.bar(output_df,
y='Avg_Review',
x='City',
labels={
'Avg_Review': 'Avg Review',
'City': 'city'
})
fig.show()
def common_insight(self):
sql_conn = sqldb.SqlDBConn().conn
try:
result_df = pd.read_sql(
"select Review,Places,City from attractions where City in (select Top 1 cli.City from flights_detail fd inner join cities_countries cc on fd.arrival_city_id=cc.city_id INNER JOIN city_living_index cli on cc.city=cli.City INNER JOIN attractions aa on aa.City=cc.city where flight_status='scheduled' GROUP by cli.city, flight_status order by AVG([Rent Index]) ASC) and Ratings is not null order by Review Desc",
con=sql_conn)
city = result_df['City'].iloc[0]
fig = px.pie(result_df,
values='Review',
names='Places',
labels={
'Places': 'Places',
'Review': 'Review'
},
title='Top 10 Places and reviews in ' + city)
fig.show()
except Exception as err:
logfile.Log().log_error(err)
return True
def schedule_graph(self):
"""
Create the map graph which shows the count of scheduled flights
:return: boolean
"""
sql_conn = sqldb.SqlDBConn().conn
try:
flight_status_df = pd.read_sql(
"SELECT country,count(country) as c_count FROM flights_detail fd INNER JOIN cities_countries cc on fd.departure_city_id=cc.city_id where flight_status='scheduled' GROUP by cc.country ORDER BY count(country) DESC",
con=sql_conn)
fig = px.choropleth(
flight_status_df,
locations=flight_status_df['country'],
locationmode='country names',
color='c_count',
labels={'c_count': 'Number of Scheduled Flights'})
fig.show(renderer="browser")
except Exception as err:
logfile.Log().log_error(err)
return True
def arrival_delay(self):
"""
Create the pie chart which shows the average arrival delay of all flights
:return: boolean
"""
sql_conn = sqldb.SqlDBConn().conn
try:
flight_status_df = pd.read_sql(
"SELECT AVG(arrival_delay) as mean, arrival_airport from flights_detail fd inner join cities_countries cc on fd.arrival_city_id=cc.city_id where arrival_delay<>0 GROUP by arrival_airport,city",
con=sql_conn)
fig = px.pie(flight_status_df,
values='mean',
names='arrival_airport',
labels={
'arrival_airport': 'Arrival Airport name',
'mean': 'Average delay'
})
fig.show()
except Exception as err:
logfile.Log().log_error(err)
return True
def flight_status(self):
"""
Create the bar graph which shows the count of all flight statuses
:return: boolean
"""
sql_conn = sqldb.SqlDBConn().conn
try:
flight_status_df = pd.read_sql(
"SELECT flight_status,count(flight_status) as s_count from flights_detail GROUP by flight_status",
con=sql_conn)
fig = px.bar(flight_status_df,
y='s_count',
x='flight_status',
labels={
'flight_status': 'Flight Status',
's_count': 'Count'
})
fig.show()
except Exception as err:
logfile.Log().log_error(err)
return True
def mongo_insert_details(self, file_name, collection_name):
"""
Read data from JSON file and insert it to mongo db
:param file_name: Name of File
:return: details inserted in mongo db and close connection
"""
mongo_conn = mongodbConnection.MongoDBConn()
mongo_collection = mongo_conn.get_collection(collection_name)
try:
if mongo_collection.count() > 0:
mongo_collection.drop()
mongo_collection = mongo_conn.get_collection(collection_name)
json_file_path = os.path.join(
os.path.abspath(os.path.dirname(__file__)), '..',
'jsonFiles/' + file_name + '.json')
with open(json_file_path) as jsonFile:
json_obj_list = json.load(jsonFile)
for json_obj in json_obj_list:
mongo_collection.insert_many(json_obj)
except Exception as err:
logfile.Log().log_error(err)
finally:
mongo_conn.close_conn()
def insert_cities_countries(self):
"""
Insert cities and countries in MSSQL database
:return:
"""
df = self.merge_cities_countries()
df = df.drop('country_iso2', axis=1)
df = df.drop('iata_code', axis=1)
cities_list = [tuple(rows) for rows in df.values]
sql_conn = sqldb.SqlDBConn().conn
cursor = sql_conn.cursor()
try:
drop_city_table = 'drop table if exists ' + constant.CITIES_TABLE
create_city_table = 'Create table ' + constant.CITIES_TABLE + ' (city_id int NOT NULL IDENTITY(1,1), city varchar(255) NOT NULL, country varchar(255) NOT NULL, PRIMARY KEY(city_id))'
insert_city_sql = 'insert into ' + constant.CITIES_TABLE + ' (city,country) values (?, ?)'
cursor.execute(drop_city_table)
cursor.execute(create_city_table)
cursor.executemany(insert_city_sql, cities_list)
sql_conn.commit()
except Exception as err:
sql_conn.rollback()
logfile.Log().log_error(err)
finally:
sql_conn.close()
def data_cleansing(self, data):
"""
Clean flights dataset and store it in MSSQL
:param data: dataframe
:return:
"""
pd.set_option('display.max_columns', None)
new_df = pd.DataFrame()
primary_df = pd.DataFrame(data)
# airline
airline_df = pandas.io.json.json_normalize(primary_df['airline'])
new_df['airline_name'] = airline_df['name']
# flight_status
new_df['flight_status'] = primary_df['flight_status']
# Flight
flight_df = pandas.io.json.json_normalize(primary_df['flight'])
new_df[['flight_number',
'flight_iata']] = flight_df[['number', 'iata']]
# Departure
departure_df = pandas.io.json.json_normalize(primary_df['departure'])
new_df[[
'departure_delay', 'departure_airport', 'departure_scheduled',
'departure_iata'
]] = departure_df[['delay', 'airport', 'scheduled', 'iata']]
# Arrival
arrival_df = pandas.io.json.json_normalize(primary_df['arrival'])
new_df[[
'arrival_delay', 'arrival_airport', 'arrival_scheduled',
'arrival_iata'
]] = arrival_df[['delay', 'airport', 'scheduled', 'iata']]
cities_df = self.merge_cities_countries()
new_df = pd.merge(new_df,
cities_df,
left_on='departure_iata',
right_on='iata_code')
new_df = new_df.rename({'city_name': 'departure_city'}, axis=1)
new_df = pd.merge(new_df,
cities_df,
left_on='arrival_iata',
right_on='iata_code')
new_df = new_df.rename({'city_name': 'arrival_city'}, axis=1)
sql_conn = sqldb.SqlDBConn().conn
cursor = sql_conn.cursor()
city_table_df = pd.read_sql("select * from " + constant.CITIES_TABLE,
con=sql_conn)
new_df = pd.merge(new_df,
city_table_df,
left_on='departure_city',
right_on='city')
new_df = new_df.rename({'city_id': 'departure_city_id'}, axis=1)
new_df = pd.merge(new_df,
city_table_df,
left_on='arrival_city',
right_on='city')
new_df = new_df.rename({'city_id': 'arrival_city_id'}, axis=1)
new_df = new_df.drop([
'departure_iata', 'arrival_iata', 'country_iso2_x', 'iata_code_x',
'country_name_x', 'country_iso2_y', 'iata_code_y',
'country_name_y', 'departure_city', 'arrival_city', 'city_x',
'country_x', 'city_y', 'country_y'
],
axis=1)
new_df = new_df.replace({np.NaN: None})
new_df['departure_delay'] = new_df['departure_delay'].replace(
{None: 0})
new_df['arrival_delay'] = new_df['arrival_delay'].replace({None: 0})
drop_flight_table = 'drop table if exists ' + constant.MG_FLIGHT_TABLE
flight_table_sql = 'create table ' + constant.MG_FLIGHT_TABLE + ' (id int not null IDENTITY(1,1), airline_name varchar(255), ' \
'flight_status varchar(255), flight_number varchar(255), flight_iata varchar(255), ' \
'departure_delay decimal, departure_airport varchar(255), departure_scheduled varchar(255), ' \
'arrival_delay decimal, arrival_airport varchar(255), arrival_scheduled varchar(255),' \
' departure_city_id int, arrival_city_id int, PRIMARY KEY (id), FOREIGN KEY (arrival_city_id) REFERENCES ' + constant.CITIES_TABLE + ' (city_id), FOREIGN KEY (departure_city_id) REFERENCES ' + constant.CITIES_TABLE + ' (city_id))'
column_name_list = []
for col in new_df.columns:
column_name_list.append(col)
data_list = [tuple(rows) for rows in new_df.values]
insert_sql = "INSERT INTO " + constant.MG_FLIGHT_TABLE + "(" + ', '.join(
column_name_list) + ") VALUES (" + "?," * (len(column_name_list) -
1) + "?)"
try:
cursor.execute(drop_flight_table)
cursor.execute(flight_table_sql)
cursor.executemany(insert_sql, data_list)
sql_conn.commit()
except Exception as err:
logfile.Log().log_error(err)
sql_conn.rollback()
finally:
sql_conn.close()