def show_all_entries(db_cursor: MySQLCursor, table_name: str):
print(table_name)
db_cursor.execute("SELECT * FROM " + table_name)
myresult = db_cursor.fetchall()
for x in myresult:
print(x)
def connectDatabase(username, password):
sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username='******',
ssh_password='******',
remote_bind_address=(
'Gengruijie.mysql.pythonanywhere-services.com',
3306)) as tunnel:
connection = mysql.connector.connect(
user='******',
password='******',
host='127.0.0.1',
port=tunnel.local_bind_port,
database='Gengruijie$AutoGrading',
)
# Do stuff
query = "SELECT password, level, score from main where name = \"" + username + "\""
# print(query)
# cur = connection.cursor(buffered=True)
cursor = MySQLCursor(connection)
cursor.execute(query)
data = cursor.fetchall()
# print(data, password)
if data[0][0] != password:
return (False, 1, 1)
return (True, data)
def display():
conn = mysql.connector.connect(user='******', password='******', host='127.0.0.1' ,database='stocklaundry')
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT `Item_name`, `Quantity`FROM ` balance_stock` WHERE Date= "2019-06-19"')
sbothval = mycursor.fetchall()
sbothvall = dict(sbothval)
print(sbothvall)
def show_release_status_keys(cursor: MySQLCursor):
"""
Displays the release_status table
:param cursor: mySQL
"""
cursor.execute('SELECT * FROM release_status_key')
print('\nRelease status table')
print('release_statusID, abbreviation, description')
for row in cursor.fetchall():
print('{}, {}, {}'.format(*row))
def show_products(cursor: MySQLCursor):
"""
Displays a list of all the products included in the database with their productID
:param cursor: mySQL
"""
cursor.execute(r'SELECT productID, product_name FROM product_list;')
results = cursor.fetchall()
print('\nCurrent product list:')
print('productID: product_name')
for productID, product_name in results:
print(f'{productID}: {product_name}')
def ReadAllBusinessUnits(db_cursor: MySQLCursor):
bu_list = []
db_cursor.execute(
"SELECT title, business_code, summary FROM business_units")
bu_tuple = db_cursor.fetchall()
for business_unit in bu_tuple:
title = business_unit[0]
code = business_unit[1]
summary = business_unit[2]
bu = BusinessUnit(specific_code=code, title=title, summary=summary)
bu_list.append(bu)
return bu_list
def ReadAllBusinessSeries(db_cursor: MySQLCursor):
bs_list = []
db_cursor.execute(
"SELECT business_series_index, title FROM business_series")
bs_tuple = db_cursor.fetchall()
sorted_bs_tuple = sorted(bs_tuple, key=lambda tup: tup[0])
for business_series in sorted_bs_tuple:
business_series_index = business_series[0]
title = business_series[1]
bs = BusinessSeries(specific_code=business_series_index, title=title)
bs_list.append(bs)
return bs_list
def ReadBusinessUnitsByCode(db_cursor: MySQLCursor,
business_series_index: int):
bu_list = []
db_cursor.execute(
"SELECT title, business_code, summary FROM business_units WHERE business_series_index="
+ str(business_series_index))
bu_tuple = db_cursor.fetchall()
for business_unit in bu_tuple:
title = business_unit[0]
code = business_unit[1]
summary = business_unit[2]
bu = BusinessUnit(specific_code=code, title=title, summary=summary)
bu_list.append(bu)
return bu_list
def ReadAllRecords(db_cursor: MySQLCursor):
records_list = []
db_cursor.execute("SELECT business_code, document_code, full_serial_number, status, title, custodian, revision, link, sow_no, issue_date, effective_date, \
reaffirmation_date, protection_lvl, ec_technical_data, permit, ecl, eccn, usml, cg, us_exemption, ca_exemption, exp_date, summary FROM records")
myresult = db_cursor.fetchall()
for result in myresult:
record = Record(business_code = result[0], document_code = result[1], full_serial_number = result[2], status = result[3], title = result[4], \
custodian = result[5], revision = result[6], link = result[7], sow_no = result[8], issue_date = result[9], effective_date = result[10], \
reaffirmation_date = result[11], protection_lvl = result[12], ec_technical_data = result[13], permit = result[14], \
ecl = result[15], eccn = result[16], usml = result[17], cg = result[18], us_exemption = result[19], ca_exemption = result[20], \
exp_date = result[21], summary = result[22])
records_list.append(record)
return records_list
def ReadAllRecords(db_cursor: MySQLCursor):
records_list = []
db_cursor.execute(
"SELECT full_serial_number, title, business_code, document_code, summary FROM records"
)
records_tuple = db_cursor.fetchall()
for result in records_tuple:
record = Record(full_serial_number=result[0],
title=result[1],
business_code=result[2],
document_code=result[3],
summary=result[4])
records_list.append(record)
return records_list
def __validateDatabase(self, cursor: TYPE_CURSOR, table: str) -> str:
"""
Checks whether the table you are trying to add a new entry to exists, if not throws a 404 not found
exception.
"""
sqlQuery = f"SELECT `sql_query` FROM testing.redirect WHERE table_name = '{table}'"
self.cursor.execute(sqlQuery)
res = cursor.fetchall()
if len(res) == 0:
abort(
404,
message=
f"Table with the name of {table} does not exist, check your spelling, and try again."
)
return res[0][0]
def ReadAllDocumentTypes(db_cursor: MySQLCursor):
document_type_list = []
db_cursor.execute(
"SELECT document_code, title, summary FROM document_types")
documents_tuple = db_cursor.fetchall()
for document_type_entry in documents_tuple:
document_code = document_type_entry[0]
title = document_type_entry[1]
summary = document_type_entry[2]
doctype = DocumentType(document_code=document_code,
title=title,
summary=summary)
document_type_list.append(doctype)
return document_type_list
def show_product_versions(cursor: MySQLCursor, productID):
"""
Displays all the product versions of a specified product that the user chooses
:param cursor: mySQL cursor
:param productID: The unique ID number for a product
"""
query = '''SELECT product_version_ID, version_number, release_date, description
FROM product_versions,
release_status_key
WHERE productID = {}
AND product_versions.release_statusID = release_status_key.release_statusID;
'''.format(productID)
cursor.execute(query)
results = cursor.fetchall()
print(f'\nProduct versions filtered by productID={productID}:')
print('product_version_ID, version_number, release_date, description')
for row in results:
print('{}, {}, {}, {}'.format(*row))
def ReadRecordsFromType(db_cursor: MySQLCursor, business_code: int, document_code: int):
records_list = []
sql = "SELECT business_code, document_code, full_serial_number, status, title, custodian, revision, link, sow_no, issue_date, effective_date, \
reaffirmation_date, protection_lvl, ec_technical_data, permit, ecl, eccn, usml, cg, us_exemption, ca_exemption, exp_date, summary FROM \
records WHERE business_code=%s AND document_code=%s"
values = (str(business_code), str(document_code))
db_cursor.execute(sql, values)
records_tuple = db_cursor.fetchall()
for result in records_tuple:
record = Record(business_code = result[0], document_code = result[1], full_serial_number = result[2], status = result[3], title = result[4], \
custodian = result[5], revision = result[6], link = result[7], sow_no = result[8], issue_date = result[9], effective_date = result[10], \
reaffirmation_date = result[11], protection_lvl = result[12], ec_technical_data = result[13], permit = result[14], \
ecl = result[15], eccn = result[16], usml = result[17], cg = result[18], us_exemption = result[19], ca_exemption = result[20], \
exp_date = result[21], summary = result[22])
records_list.append(record)
return records_list
def execute(cursor: MySQLCursor, stmt: str, multi: bool = False) -> None:
"""Execute a statement in a cursor, and clean up the cursor so it can be
closed without causing warnings on the server."""
warnings = []
results = []
# cursors may contain results objects (if multi==True) or a MySQLCursor (if
# multi==False)
cursors = []
if multi:
cursors = cursor.execute(stmt, multi=True)
else:
cursor.execute(stmt, multi=False)
cursors = [cursor]
for cursor in cursors:
if cursor.with_rows:
for row in cursor.fetchall():
results.append(row)
if (w := cursor.fetchwarnings()):
warnings.append(w)
def __handle_result(cursor: MySQLCursor):
column_names = cursor.column_names
results = cursor.fetchall()
tmp = {}
# Merge meter id on the same AlertDefinition
for result in results:
i = 0
id_def = result[0]
if id_def not in tmp.keys():
tmp[id_def] = {}
tmp[id_def]["meter_ids"] = []
while i < len(column_names):
if column_names[i] == "meter_id":
tmp[id_def]["meter_ids"].append(result[i])
else:
tmp[id_def][column_names[i]] = result[i]
i += 1
# make array
return [tmp[key] for key in tmp.keys()]
class Database:
'''Database class for working with database'''
def __init__(self, config):
self.config = config
def connect(self):
'''Connecting with database, else ask user for reconnect.'''
while True:
try:
self.cnx = connector.connect(**self.config)
self.cnx.autocommit = True
self.cnx.raise_on_warnings = True
self.cursor = MySQLCursor(self.cnx)
if not self.check():
self.disconnect()
return
break
except connector.Error as ex:
print(ex.msg)
return
def disconnect(self):
'''Disconnect from database.'''
try:
self.cursor.close()
finally:
pass
try:
self.cnx.disconnect()
finally:
pass
def _run_query(self, query: str, *values):
'''Run query and fetch data'''
self.cursor.execute(query, values)
data = {}
data['columns'] = self.cursor.column_names
data['count'] = self.cursor.rowcount
data['id'] = self.cursor.lastrowid
data['rows'] = self.cursor.fetchall()
return data
def check(self):
'''Check table in database'''
data = self._run_query(QUERYES.CHECK_TABLE_EXISTS)
if data['rows'][0][0] == 1:
return True
print('table not found')
create = True
if create:
try:
self._run_query(QUERYES.CREATE_TABLE_ENTRIES)
except connector.Error as msg:
print(msg)
return False
return False
def get(self, query, *values):
'''Get data'''
data = self._run_query(query, *values)
return data['rows']
def insert(self, query, *values):
'''Insert data and returns row id'''
data = self._run_query(query, *values)
return data['id']
def update(self, query, *values):
'''Update data and returns row id'''
data = self._run_query(query, *values)
return data['id']
def delete(self, query, *values):
'''Delete data and returns count rows'''
data = self._run_query(query, *values)
return data['count']
import MySQLdb
conn = MySQLdb.connect(host='localhost', user='', passwd='', db='test')
import mysql.connector
from mysql.connector.cursor import MySQLCursor
cnx = mysql.connector.connect(database='test')
cursor = MySQLCursor(cnx)
cursor.execute('SELECT encryted_phrase FROM encrypted_text')
for row in cursor.fetchall():
import sys
previous_binary_result = ""
salt_number = input("Enter the previous salt number=")
salt_binary = bin(salt_number)
salt_binary = salt_binary[2:].zfill(8)
aa_variable = ""
for character in row[0]:
number = character.decode('utf8')
number_result = bin(ord(number))
previous_binary_result = number_result[2:].zfill(8)
given_character_value = ""
variable = 0
ascii = ""
while variable < len(previous_binary_result):
result = int(salt_binary[variable]) ^ int(
previous_binary_result[variable])
variable = variable + 1
given_character_value += str(result)
answer = chr(int(given_character_value, 2))
sys.stdout.write(answer)
print aa_variable
def Associatedcounts3():
conn = mysql.connector.connect(user=driver.user,
password=driver.password,
host=driver.host,
database=driver.databasename)
# for busServiceId,COUNT(busServiceId )
#part1
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT routeId,COUNT(routeId ) FROM available_trips GROUP BY routeId')
tablename = mycursor.fetchall()
tablename = dict(tablename)
var1 = tablename.keys()
listvarkeys = list(var1)
#print(listvarkeys)
var2 = tablename.values()
listvarvalues = list(var2)
if (listvarkeys[0] == ''):
listvarkeys[0] = 'Blank'
tablename = {}
for key in listvarkeys:
for value in listvarvalues:
tablename[key] = value
listvarvalues.remove(value)
break
#print("Resultant dictionary is : " + str(tablename))
pd.DataFrame(tablename.items())
svaldf = pd.DataFrame(tablename.items(),
columns=['routeId', 'TOTAL COUNT(routeId) IN TABLE'])
#part2
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT routeId,COUNT(routeId ) FROM available_trips GROUP BY routeId ORDER BY count(routeId) DESC '
)
tablename = mycursor.fetchall()
tablename = dict(tablename)
var1 = tablename.keys()
listvarkeys = list(var1)
#print(listvarkeys)
var2 = tablename.values()
listvarvalues = list(var2)
if (listvarkeys[0] == ''):
listvarkeys[0] = 'Blank'
tablename = {}
for key in listvarkeys:
for value in listvarvalues:
tablename[key] = value
listvarvalues.remove(value)
break
# print("Resultant dictionary is : " + str(tablename))
#print("Resultant dictionary is : " + str(tablename))
pd.DataFrame(tablename.items())
svaldff = pd.DataFrame(
tablename.items(),
columns=['routeId', 'TOTAL COUNT(routeId) IN TABLE DESC'])
writer_object = pd.ExcelWriter(driver.desktop +
'Associated_counts\\List_routeId.xlsx')
svaldf.to_excel(writer_object, startcol=0, startrow=1, sheet_name='Sheet1')
svaldff.to_excel(writer_object,
startcol=4,
startrow=1,
sheet_name='Sheet1')
workbook_object = writer_object.book
worksheet_object = writer_object.sheets['Sheet1']
worksheet_object.set_column('B:C', 20)
worksheet_object.set_column('F:G', 30)
worksheet_object.write('B1', "Total rows of routeId ")
worksheet_object.write('C1', len(svaldff.axes[0]))
writer_object.save()
def show_compatibility(cursor: MySQLCursor, product_name, version_number):
"""
Shows a list of compatible product versions of the product and version that the user has inputted
:param cursor: mySQL
:param product_name: The name of the product the user wants to find compatibility for
:param version_number: The version of the specified product the user wants to find compatibility for
"""
query = f'''
SELECT pv2.product_version_ID AS 'Product Version ID',
pl2.product_name AS 'Product Name',
pv2.version_number AS 'Version Number',
pv2.release_date AS 'Release Date',
rskey.description AS 'Release Status'
FROM product_list AS pl2,
product_versions AS pv2,
release_status_key AS rskey
WHERE pl2.productID = pv2.productID
AND rskey.release_statusID = pv2.release_statusID
AND (pv2.product_version_ID IN (
SELECT compatibility.product_version_ID_2
FROM compatibility
WHERE compatibility.product_version_ID_1 = (
SELECT product_versions.product_version_ID
FROM product_list,
product_versions
WHERE product_versions.version_number = '{version_number}'
AND product_list.product_name = '{product_name}'
AND product_list.productID = product_versions.productID
)
)
OR pv2.product_version_ID IN (
SELECT compatibility.product_version_ID_1
FROM compatibility
WHERE compatibility.product_version_ID_2 = (
SELECT product_versions.product_version_ID
FROM product_list,
product_versions
WHERE product_versions.version_number = '{version_number}'
AND product_list.product_name = '{product_name}'
AND product_list.productID = product_versions.productID
)
)
);'''
cursor.execute(query)
print('\nCompatibilities:')
print(
'product_version_ID, Product Name, Version Number, Release Date, Release Status'
)
for row in cursor.fetchall():
print('{}, {}, {}, {}, {}'.format(*row))
query = f'''SELECT product_versions.product_version_ID
FROM product_list,
product_versions
WHERE product_versions.version_number = '{version_number}'
AND product_list.product_name = '{product_name}'
AND product_list.productID = product_versions.productID'''
cursor.execute(query)
print('query product_version_ID:')
for row in cursor.fetchall():
print(row[0])
def Myfunc():
conn = mysql.connector.connect(user=driver.user, password=driver.password, host=driver.host,database=driver.databasename)
book = Workbook()
sheet= book.active
mycursor = conn.cursor()
mycursor.execute('show tables') # to catch table name
myresultw = mycursor.fetchall()
tablename = [item for x in zip_longest(*myresultw) for item in x if item]
mycursor = conn.cursor()
mycursor.execute('SELECT id from available_trips')
myresultq = mycursor.fetchall() # for total rows output
totalrows = [item for x in zip_longest(*myresultq) for item in x if item != -55]
countnew=0
for x in range(len(totalrows)):
countnew = countnew + 1
totalrows[0]= countnew
mycursor = conn.cursor()
mycursor.execute('SELECT DATABASE() FROM DUAL') # to catch db name
myresultdb = mycursor.fetchall()
dbname = [item for x in zip_longest(*myresultdb) for item in x if item]
mycursor = conn.cursor()
mycursor.execute('SELECT doj FROM available_trips') #to get blank values as per doj
myresult51 = mycursor.fetchall()
dojcountwithblank = [item for x in zip_longest(*myresult51) for item in x if item != -55]
#print(dojcountwithblank)
countvarblank=0
countvarnotblank=0
for x in range(len(dojcountwithblank)):
if dojcountwithblank[x] == '':
countvarblank+=1
else:
countvarnotblank+=1
# print(countvarblank)
sheet['C4'] = countvarblank
sheet['C5'] = totalrows[0]-countvarblank
sheet['C1'] = dbname[0]
sheet['C2'] = tablename[0]
sheet['C3'] = totalrows[0]
# print(totalrows)
# attribute1:tripid
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT COUNT(DISTINCT id) FROM available_trips') # count id distinct
myresult1 = mycursor.fetchall()
idcount = list(myresult1)
idcountt = idcount[0][0]
mycursor.execute('SELECT MIN(id) FROM available_trips') # Min id value
myresult1 = mycursor.fetchall()
minid = list(myresult1)
minidd = minid[0][0]
if minidd =='':
minidd='BLANK'
mycursor.execute('SELECT MAX(id) FROM available_trips')
myresult1 = mycursor.fetchall() # max id value
maxid = list(myresult1)
maxidd = maxid[0][0]
mycursor.execute('Desc available_trips')
myresult0 = mycursor.fetchall() # to get all rows
res_list = [item for x in zip_longest(*myresult0) for item in x if item]
finalattrib = res_list[:52]
for x in range(52):
c1 = sheet.cell(row=x + 8, column=2) # attribute name insertion loop
c1.value = finalattrib[x]
sheet['D8'] = minidd
sheet['E8'] = maxidd
#book.save("C:\\Users\\AdminPC\\Desktop\\OUTPUTFOLDER\\ParentMainAnalysis.xlsx")
#return idcountt
# attribute2:source
mycursor.execute('SELECT COUNT(DISTINCT source) FROM available_trips')
myresult2 = mycursor.fetchall()
sourcecount = list(myresult2)
sourcecountt = sourcecount[0][0]
mycursor.execute('SELECT MIN(source) FROM available_trips')
myresult2 = mycursor.fetchall()
minsource = list(myresult2)
minsourcee = minsource[0][0]
if minsourcee =='':
minsourcee='BLANK'
mycursor.execute('SELECT MAX(source) FROM available_trips')
myresult2 = mycursor.fetchall()
maxsource = list(myresult2)
maxsourcee = maxsource[0][0]
sheet['D9'] = minsourcee
sheet['E9'] = maxsourcee
#return sourcecountt
# attribute3:source_name
mycursor.execute('SELECT COUNT(DISTINCT source_name) FROM available_trips')
myresult3 = mycursor.fetchall()
sourcenamecount = list(myresult3)
sourcenamecountt = sourcenamecount[0][0]
mycursor.execute('SELECT MIN(source_name) FROM available_trips') # Min id value
myresult1 = mycursor.fetchall()
minsource_name = list(myresult1)
minsource_namee = minsource_name[0][0]
if minsource_namee =='':
minsource_namee='BLANK'
mycursor.execute('SELECT MAX(source_name) FROM available_trips') # Min id value
myresult1 = mycursor.fetchall()
maxsource_name = list(myresult1)
maxsource_namee = maxsource_name[0][0]
sheet['D10'] = minsource_namee
sheet['E10'] = maxsource_namee
# attribute4:destination
mycursor.execute('SELECT COUNT(DISTINCT destination) FROM available_trips')
myresult4 = mycursor.fetchall()
destinationcount = list(myresult4)
destinationcountt = destinationcount[0][0]
# globalistcount[2] = destinationcountt
mycursor.execute('SELECT MIN(destination) FROM available_trips')
myresult4 = mycursor.fetchall()
mindestination = list(myresult4)
mindestinationn = mindestination[0][0]
if mindestinationn =='':
mindestinationn='BLANK'
mycursor.execute('SELECT MAX(destination) FROM available_trips')
myresult4 = mycursor.fetchall()
maxdestination = list(myresult4)
maxdestinationn = maxdestination[0][0]
sheet['D11'] = mindestinationn
sheet['E11'] = maxdestinationn
# attribute5:destination_name
mycursor.execute('SELECT COUNT(DISTINCT destination_name) FROM available_trips')
myresult5 = mycursor.fetchall()
destinationnamecount = list(myresult5)
destinationnamecountt = destinationnamecount[0][0]
mycursor.execute('SELECT MIN(destination_name) FROM available_trips')
myresult4 = mycursor.fetchall()
mindestinationname = list(myresult4)
mindestinationnamee = mindestinationname[0][0]
if mindestinationnamee == '':
mindestinationnamee = 'BLANK'
mycursor.execute('SELECT MAX(destination_name) FROM available_trips')
myresult4 = mycursor.fetchall()
maxdestinationname = list(myresult4)
maxdestinationnamee = maxdestinationname[0][0]
sheet['D12'] = mindestinationnamee
sheet['E12'] = maxdestinationnamee
# attribute6:travels
mycursor.execute('SELECT COUNT(DISTINCT travels) FROM available_trips')
myresult6 = mycursor.fetchall()
travelscount = list(myresult6)
travelscountt = travelscount[0][0]
mycursor.execute('SELECT MIN(travels) FROM available_trips')
myresult4 = mycursor.fetchall()
mintravels = list(myresult4)
mintravelss = mintravels[0][0]
if mintravelss == '':
mintravelss = 'BLANK'
mycursor.execute('SELECT MAX(travels) FROM available_trips')
myresult4 = mycursor.fetchall()
maxtravels = list(myresult4)
maxtravelss = maxtravels[0][0]
sheet['D13'] = mintravelss
sheet['E13'] = maxtravelss
# attrinute7 AC
mycursor.execute('SELECT COUNT(DISTINCT AC) FROM available_trips')
myresult7 = mycursor.fetchall()
ACcount = list(myresult7)
ACcountt = ACcount[0][0]
mycursor.execute('SELECT MIN(AC) FROM available_trips')
myresult7 = mycursor.fetchall()
minac = list(myresult7)
minacc = minac[0][0]
if minacc == '':
minacc = 'BLANK'
mycursor.execute('SELECT MAX(AC) FROM available_trips')
myresult7 = mycursor.fetchall()
maxac = list(myresult7)
maxacc = maxac[0][0]
sheet['D14'] = minacc
sheet['E14'] = maxacc
# attribute7:arrivaltime
mycursor.execute('SELECT COUNT(DISTINCT arrivalTime) FROM available_trips')
myresult8 = mycursor.fetchall()
arrivalTimecount = list(myresult8)
arrivalTimecountt = arrivalTimecount[0][0]
mycursor.execute('SELECT MIN(arrivalTime) FROM available_trips')
myresult8 = mycursor.fetchall()
minarrivalTime = list(myresult8)
minarrivalTimee = minarrivalTime[0][0]
if minarrivalTimee =='':
minarrivalTimee="BLANK"
mycursor.execute('SELECT MAX(arrivalTime) FROM available_trips')
myresult8 = mycursor.fetchall()
maxarrivalTime = list(myresult8)
maxarrivalTimee = maxarrivalTime[0][0]
sheet['D15'] = minarrivalTimee
sheet['E15'] = maxarrivalTimee
# attribute9:availCatCard
mycursor.execute('SELECT COUNT(DISTINCT availCatCard) FROM available_trips')
myresult9 = mycursor.fetchall()
availCatCardcount = list(myresult9)
availCatCardcountt = availCatCardcount[0][0]
mycursor.execute('SELECT MIN(availCatCard) FROM available_trips')
myresult9 = mycursor.fetchall()
minavailCatCard = list(myresult9)
minavailCatCardd = minavailCatCard[0][0]
if minavailCatCardd == '':
minavailCatCardd = 'BLANK'
mycursor.execute('SELECT MAX(availCatCard) FROM available_trips')
myresult9 = mycursor.fetchall()
maxavailCatCard = list(myresult9)
maxavailCatCardd = maxavailCatCard[0][0]
sheet['D16'] = minavailCatCardd
sheet['E16'] = maxavailCatCardd
# attribute10:availSrCitizen
mycursor.execute('SELECT COUNT(DISTINCT availSrCitizen) FROM available_trips')
myresult10 = mycursor.fetchall()
availSrCitizencount = list(myresult10)
availSrCitizencountt = availSrCitizencount[0][0]
mycursor.execute('SELECT MIN(availSrCitizen) FROM available_trips')
myresult10 = mycursor.fetchall()
minavailSrCitizen = list(myresult10)
minavailSrCitizenn = minavailSrCitizen[0][0]
if minavailSrCitizenn == '':
minavailSrCitizenn = 'BLANK'
mycursor.execute('SELECT MAX(availSrCitizen) FROM available_trips')
myresult10 = mycursor.fetchall()
maxavailSrCitizen = list(myresult10)
maxavailSrCitizenn = maxavailSrCitizen[0][0]
sheet['D17'] = minavailSrCitizenn
sheet['E17'] = maxavailSrCitizenn
# attribute11:availableSeats
mycursor.execute('SELECT COUNT(DISTINCT availableSeats) FROM available_trips')
myresult11 = mycursor.fetchall()
availableSeatscount = list(myresult11)
availableSeatscountt = availableSeatscount[0][0]
mycursor.execute('SELECT MIN(availableSeats) FROM available_trips')
myresult11 = mycursor.fetchall()
minavailableSeats = list(myresult11)
minavailableSeatss = minavailableSeats[0][0]
if minavailableSeatss == '':
minavailableSeatss = 'BLANK'
mycursor.execute('SELECT MAX(availableSeats) FROM available_trips')
myresult11 = mycursor.fetchall()
maxavailableSeats = list(myresult11)
maxavailableSeatss = maxavailableSeats[0][0]
sheet['D18'] = minavailableSeatss
sheet['E18'] = maxavailableSeatss
# attribute12 :avlWindowSeats
mycursor.execute('SELECT COUNT(DISTINCT avlWindowSeats) FROM available_trips')
myresult12 = mycursor.fetchall()
avlWindowSeatscount = list(myresult12)
avlWindowSeatscountt = avlWindowSeatscount[0][0]
mycursor.execute('SELECT MIN(avlWindowSeats) FROM available_trips')
myresult12 = mycursor.fetchall()
minavlWindowSeats = list(myresult12)
minavlWindowSeatss = minavlWindowSeats[0][0]
if minavlWindowSeatss == '':
minavlWindowSeatss = 'BLANK'
mycursor.execute('SELECT MAX(avlWindowSeats) FROM available_trips')
myresult12 = mycursor.fetchall()
maxavlWindowSeats = list(myresult12)
maxavlWindowSeatss = maxavlWindowSeats[0][0]
sheet['D19'] = minavlWindowSeatss
sheet['E19'] = maxavlWindowSeatss
# attribute13: boardingTimes
mycursor.execute('SELECT COUNT(DISTINCT boardingTimes) FROM available_trips')
myresult13 = mycursor.fetchall()
boardingTimescount = list(myresult13)
boardingTimescountt = boardingTimescount[0][0]
mycursor.execute('SELECT MIN(boardingTimes) FROM available_trips')
myresult12 = mycursor.fetchall()
minaboardingTimes= list(myresult12)
minaboardingTimess = minaboardingTimes[0][0]
if minaboardingTimess == '':
minaboardingTimess = 'BLANK'
mycursor.execute('SELECT MAX(boardingTimes) FROM available_trips')
myresult12 = mycursor.fetchall()
maxboardingTimes = list(myresult12)
maxboardingTimess = maxboardingTimes[0][0]
sheet['D20'] = minaboardingTimess
sheet['E20'] = maxboardingTimess
# attribute14:bookable
mycursor.execute('SELECT COUNT(DISTINCT bookable) FROM available_trips')
myresult14 = mycursor.fetchall()
bookablecount = list(myresult14)
bookablecountt = bookablecount[0][0]
mycursor.execute('SELECT MIN(bookable) FROM available_trips')
myresult14 = mycursor.fetchall()
minbookable = list(myresult14)
minbookablee = minbookable[0][0]
if minbookablee == '':
minbookablee = 'BLANK'
mycursor.execute('SELECT MAX(bookable) FROM available_trips')
myresult14 = mycursor.fetchall()
maxbookable = list(myresult14)
maxbookablee = maxbookable[0][0]
sheet['D21'] = minbookablee
sheet['E21'] = maxbookablee
# attribute15:bpDpSeatLayout
mycursor.execute('SELECT COUNT(DISTINCT bpDpSeatLayout) FROM available_trips')
myresult15 = mycursor.fetchall()
bpDpSeatLayoutcount = list(myresult15)
bpDpSeatLayoutcountt = bpDpSeatLayoutcount[0][0]
mycursor.execute('SELECT MIN(bpDpSeatLayout) FROM available_trips')
myresult15 = mycursor.fetchall()
minbpDpSeatLayout = list(myresult15)
minbpDpSeatLayoutt = minbpDpSeatLayout[0][0]
if minbpDpSeatLayoutt == '':
minbpDpSeatLayoutt = 'BLANK'
mycursor.execute('SELECT MAX(bpDpSeatLayout) FROM available_trips')
myresult15 = mycursor.fetchall()
maxbpDpSeatLayout = list(myresult15)
maxbpDpSeatLayoutt = maxbpDpSeatLayout[0][0]
sheet['D22'] = minbpDpSeatLayoutt
sheet['E22'] = maxbpDpSeatLayoutt
# attribute16:busImageCount
mycursor.execute('SELECT COUNT(DISTINCT busImageCount) FROM available_trips')
myresult16 = mycursor.fetchall()
busImageCountcount = list(myresult16)
busImageCountcountt = busImageCountcount[0][0]
mycursor.execute('SELECT MIN(busImageCount) FROM available_trips')
myresult16 = mycursor.fetchall()
minbusImageCount = list(myresult16)
minbusImageCountt = minbusImageCount[0][0]
if minbusImageCountt == '':
minbusImageCountt = 'BLANK'
mycursor.execute('SELECT MAX(busImageCount) FROM available_trips')
myresult16 = mycursor.fetchall()
maxbusImageCount = list(myresult16)
maxbusImageCountt = maxbusImageCount[0][0]
sheet['D23'] = minbusImageCountt
sheet['E23'] = maxbusImageCountt
# attribute17:busServiceId
mycursor.execute('SELECT COUNT(DISTINCT busServiceId) FROM available_trips')
myresult17 = mycursor.fetchall()
busServiceIdcount = list(myresult17)
busServiceIdcountt = busServiceIdcount[0][0]
mycursor.execute('SELECT MIN(busServiceId) FROM available_trips')
myresult17 = mycursor.fetchall()
minbusServiceId = list(myresult17)
minbusServiceIdd = minbusServiceId[0][0]
if minbusServiceIdd=='':
minbusServiceIdd= "BLANK"
mycursor.execute('SELECT MAX(busServiceId) FROM available_trips')
myresult17 = mycursor.fetchall()
maxbusServiceId = list(myresult17)
maxbusServiceIdd = maxbusServiceId[0][0]
sheet['D24'] = minbusServiceIdd
sheet['E24'] = maxbusServiceIdd
# attribute18:busType
mycursor.execute('SELECT COUNT(DISTINCT busType) FROM available_trips')
myresult18 = mycursor.fetchall()
busTypecount = list(myresult18)
busTypecountt = busTypecount[0][0]
mycursor.execute('SELECT MIN(busType) FROM available_trips')
myresult17 = mycursor.fetchall()
minbusType = list(myresult17)
minbusTypee = minbusType[0][0]
if minbusTypee == '':
minbusTypee = 'BLANK'
mycursor.execute('SELECT MAX(busType) FROM available_trips')
myresult17 = mycursor.fetchall()
maxbusType = list(myresult17)
maxbusTypee = maxbusType[0][0]
sheet['D25'] = minbusTypee
sheet['E25'] = maxbusTypee
# attribute19:busTypeId
mycursor.execute('SELECT COUNT(DISTINCT busTypeId) FROM available_trips')
myresult19 = mycursor.fetchall()
busTypeIdcount = list(myresult19)
busTypeIdcountt = busTypeIdcount[0][0]
mycursor.execute('SELECT MIN(busTypeId) FROM available_trips')
myresult17 = mycursor.fetchall()
minbusTypeId = list(myresult17)
minbusTypeIdd = minbusTypeId[0][0]
if minbusTypeIdd == '':
minbusTypeIdd = 'BLANK'
mycursor.execute('SELECT MAX(busTypeId) FROM available_trips')
myresult17 = mycursor.fetchall()
maxbusTypeId = list(myresult17)
maxbusTypeId = maxbusTypeId[0][0]
sheet['D26'] = minbusTypeIdd
sheet['E26'] = maxbusTypeId
# attribute20:cancellationPolicy
mycursor.execute('SELECT COUNT(DISTINCT cancellationPolicy) FROM available_trips')
myresult20 = mycursor.fetchall()
cancellationPolicycount = list(myresult20)
cancellationPolicycountt = cancellationPolicycount[0][0]
mycursor.execute('SELECT MIN(cancellationPolicy) FROM available_trips')
myresult17 = mycursor.fetchall()
mincancellationPolicy = list(myresult17)
mincancellationPolicyy = mincancellationPolicy[0][0]
if mincancellationPolicyy == '':
mincancellationPolicyy = 'BLANK'
mycursor.execute('SELECT MAX(cancellationPolicy) FROM available_trips')
myresult17 = mycursor.fetchall()
maxcancellationPolicy = list(myresult17)
maxcancellationPolicyy = maxcancellationPolicy[0][0]
sheet['D27'] = mincancellationPolicyy
sheet['E27'] = maxcancellationPolicyy
# attribute21:departureTime
mycursor.execute('SELECT COUNT(DISTINCT departureTime) FROM available_trips')
myresult21 = mycursor.fetchall()
departureTimecount = list(myresult21)
departureTimecountt = departureTimecount[0][0]
mycursor.execute('SELECT MIN(departureTime) FROM available_trips')
myresult21 = mycursor.fetchall()
mindepartureTime = list(myresult21)
mindepartureTimee = mindepartureTime[0][0]
if mindepartureTimee == '':
mindepartureTimee = 'BLANK'
mycursor.execute('SELECT MAX(departureTime) FROM available_trips')
myresult21 = mycursor.fetchall()
maxdepartureTime = list(myresult21)
maxdepartureTime = maxdepartureTime[0][0]
sheet['D28'] = mindepartureTimee
sheet['E28'] = maxdepartureTime
# attribute22:doj
mycursor.execute('SELECT COUNT(DISTINCT doj) FROM available_trips')
myresult22 = mycursor.fetchall()
dojcount = list(myresult22)
dojcountt = dojcount[0][0]
mycursor.execute('select distinct doj from available_trips ORDER BY doj ASC')
myresult22 = mycursor.fetchall() # orderby min logic
mindoj = [item for x in zip_longest(*myresult22) for item in x if item != -55 and item != '']
mindojj = mindoj[0]
if mindojj =='':
mindojj = 'BLANK'
mycursor.execute('select distinct doj from available_trips ORDER BY doj DESC')
myresult22 = mycursor.fetchall() ##orderby man logic
maxdoj = [item for x in zip_longest(*myresult22) for item in x if item != -55 and item != '']
maxdojj = maxdoj[0]
sheet['D29'] = mindojj
sheet['E29'] = maxdojj
# attribute23:dropPointMandatory
mycursor.execute('SELECT COUNT(DISTINCT dropPointMandatory) FROM available_trips')
myresult23 = mycursor.fetchall()
dropPointMandatorycount = list(myresult23)
dropPointMandatorycountt = dropPointMandatorycount[0][0]
mycursor.execute('SELECT MIN(dropPointMandatory) FROM available_trips')
myresult21 = mycursor.fetchall()
mindropPointMandatory = list(myresult21)
mindropPointMandatoryy = mindropPointMandatory[0][0]
if mindropPointMandatoryy == '':
mindropPointMandatoryy = 'BLANK'
mycursor.execute('SELECT MAX(dropPointMandatory) FROM available_trips')
myresult21 = mycursor.fetchall()
maxdropPointMandatory = list(myresult21)
maxdropPointMandatoryy = maxdropPointMandatory[0][0]
sheet['D30'] = mindropPointMandatoryy
sheet['E30'] = maxdropPointMandatoryy
# attribute24: droppingTimes
mycursor.execute('SELECT COUNT(DISTINCT droppingTimes) FROM available_trips')
myresult24 = mycursor.fetchall()
droppingTimescount = list(myresult24)
droppingTimescountt = droppingTimescount[0][0]
mycursor.execute('SELECT MIN(droppingTimes) FROM available_trips')
myresult21 = mycursor.fetchall()
mindroppingTimes = list(myresult21)
mindroppingTimess = mindroppingTimes[0][0]
if mindroppingTimes=='':
mindroppingTimes='BLANK'
mycursor.execute('SELECT MAX(droppingTimes) FROM available_trips')
myresult21 = mycursor.fetchall()
mindroppingTimes = list(myresult21)
mindroppingTimess = mindroppingTimes[0][0]
sheet['D31'] = mindroppingTimess
sheet['E31'] = mindroppingTimess
# attribute25: fareDetails
mycursor.execute('SELECT COUNT(DISTINCT fareDetails) FROM available_trips')
myresult25 = mycursor.fetchall()
fareDetailscount = list(myresult25)
fareDetailscountt = fareDetailscount[0][0]
mycursor.execute('select distinct fareDetails from available_trips ORDER BY fareDetails ASC')
myresult25 = mycursor.fetchall() # orderby min logic
minfareDetails = [item for x in zip_longest(*myresult25) for item in x if item != -55 and item != '']
minfareDetailss = minfareDetails[0]
if minfareDetailss == '':
minfareDetailss = 'BLANK'
mycursor.execute('select distinct fareDetails from available_trips ORDER BY fareDetails DESC')
myresult25 = mycursor.fetchall() # orderby min logic
maxfareDetails = [item for x in zip_longest(*myresult25) for item in x if item != -55 and item != '']
maxfareDetailss = maxfareDetails[0]
sheet['D32'] = minfareDetailss
sheet['E32'] = maxfareDetailss
# attribute26: fares #min max logic remain
mycursor.execute('SELECT COUNT(DISTINCT fares) FROM available_trips')
myresult26 = mycursor.fetchall()
farescount = list(myresult26)
farescountt = farescount[0][0]
mycursor.execute('SELECT MIN(fares) FROM available_trips')
myresult21 = mycursor.fetchall()
minfares = list(myresult21)
minfaress = minfares[0][0]
if minfaress =='':
minfaress = 'BLANK'
mycursor.execute('SELECT MAX(fares) FROM available_trips')
myresult21 = mycursor.fetchall()
maxfares = list(myresult21)
maxfaress = maxfares[0][0]
sheet['D33'] = minfaress
sheet['E33'] = maxfaress
# attribute27: flatComApplicable
mycursor.execute('SELECT COUNT(DISTINCT flatComApplicable) FROM available_trips')
myresult27 = mycursor.fetchall()
flatComApplicablecount = list(myresult27)
flatComApplicablecountt = flatComApplicablecount[0][0]
mycursor.execute('SELECT MIN(flatComApplicable) FROM available_trips')
myresult21 = mycursor.fetchall()
minflatComApplicable = list(myresult21)
minflatComApplicablee = minflatComApplicable[0][0]
if minflatComApplicablee == '':
minflatComApplicablee = 'BLANK'
mycursor.execute('SELECT MAX(flatComApplicable) FROM available_trips')
myresult21 = mycursor.fetchall()
maxflatComApplicable = list(myresult21)
maxflatComApplicablee = maxflatComApplicable[0][0]
sheet['D34'] = minflatComApplicablee
sheet['E34'] = maxflatComApplicablee
# attribute28: gdsCommission
mycursor.execute('SELECT COUNT(DISTINCT gdsCommission) FROM available_trips')
myresult28 = mycursor.fetchall()
gdsCommissioncount = list(myresult28)
gdsCommissioncountt = gdsCommissioncount[0][0]
mycursor.execute('SELECT MIN(gdsCommission) FROM available_trips')
myresult21 = mycursor.fetchall()
mingdsCommission = list(myresult21)
mingdsCommissionn = mingdsCommission[0][0]
if mingdsCommissionn == '':
mingdsCommissionn = 'BLANK'
mycursor.execute('SELECT MAX(gdsCommission) FROM available_trips')
myresult21 = mycursor.fetchall()
maxgdsCommission = list(myresult21)
maxgdsCommissionn = maxgdsCommission[0][0]
sheet['D35'] = mingdsCommissionn
sheet['E35'] = maxgdsCommissionn
# attribute29: idProofRequired
mycursor.execute('SELECT COUNT(DISTINCT idProofRequired) FROM available_trips')
myresult29 = mycursor.fetchall()
idProofRequiredcount = list(myresult29)
idProofRequiredcountt = idProofRequiredcount[0][0]
mycursor.execute('SELECT MIN(idProofRequired) FROM available_trips')
myresult21 = mycursor.fetchall()
minidProofRequired = list(myresult21)
minidProofRequiredd = minidProofRequired[0][0]
if minidProofRequiredd == '':
minidProofRequiredd = 'BLANK'
mycursor.execute('SELECT MAX(idProofRequired) FROM available_trips')
myresult21 = mycursor.fetchall()
maxidProofRequired = list(myresult21)
maxidProofRequiredd = maxidProofRequired[0][0]
sheet['D36'] = minidProofRequiredd
sheet['E36'] = maxidProofRequiredd
# attribute30: liveTrackingAvailable
mycursor.execute('SELECT COUNT(DISTINCT liveTrackingAvailable) FROM available_trips')
myresult30 = mycursor.fetchall()
liveTrackingAvailablecount = list(myresult30)
liveTrackingAvailablecountt = liveTrackingAvailablecount[0][0]
mycursor.execute('SELECT MIN(liveTrackingAvailable) FROM available_trips')
myresult21 = mycursor.fetchall()
minliveTrackingAvailable = list(myresult21)
minliveTrackingAvailablee = minliveTrackingAvailable[0][0]
if minliveTrackingAvailablee == '':
minliveTrackingAvailablee = 'BLANK'
mycursor.execute('SELECT MAX(liveTrackingAvailable) FROM available_trips')
myresult21 = mycursor.fetchall()
maxliveTrackingAvailable = list(myresult21)
maxliveTrackingAvailablee = maxliveTrackingAvailable[0][0]
sheet['D37'] = minliveTrackingAvailablee
sheet['E37'] = maxliveTrackingAvailablee
# attribute31: maxSeatsPerTicket
mycursor.execute('SELECT COUNT(DISTINCT maxSeatsPerTicket) FROM available_trips')
myresult31 = mycursor.fetchall()
maxSeatsPerTicketcount = list(myresult31)
maxSeatsPerTicketcountt = maxSeatsPerTicketcount[0][0]
mycursor.execute('SELECT MIN(maxSeatsPerTicket) FROM available_trips')
myresult21 = mycursor.fetchall()
minmaxSeatsPerTicket = list(myresult21)
minmaxSeatsPerTickett = minmaxSeatsPerTicket[0][0]
if minmaxSeatsPerTickett == '':
minmaxSeatsPerTickett = 'BLANK'
mycursor.execute('SELECT MAX(maxSeatsPerTicket) FROM available_trips')
myresult21 = mycursor.fetchall()
maxmaxSeatsPerTicket = list(myresult21)
maxmaxSeatsPerTickett = maxmaxSeatsPerTicket[0][0]
sheet['D38'] = minmaxSeatsPerTickett
sheet['E38'] = maxmaxSeatsPerTickett
# attribute32: nonAC
mycursor.execute('SELECT COUNT(DISTINCT nonAC) FROM available_trips')
myresult32 = mycursor.fetchall()
nonACcount = list(myresult32)
nonACcountt = nonACcount[0][0]
mycursor.execute('SELECT MIN(nonAC) FROM available_trips')
myresult21 = mycursor.fetchall()
minnonAC = list(myresult21)
minnonACC = minnonAC[0][0]
if minnonACC == '':
minnonACC = 'BLANK'
mycursor.execute('SELECT MAX(nonAC) FROM available_trips')
myresult21 = mycursor.fetchall()
maxnonAC = list(myresult21)
maxnonACC = maxnonAC[0][0]
sheet['D39'] = minnonACC
sheet['E39'] = maxnonACC
# attribute33: operator
mycursor.execute('SELECT COUNT(DISTINCT operator) FROM available_trips')
myresult33 = mycursor.fetchall()
operatorcount = list(myresult33)
operatorcountt = operatorcount[0][0]
mycursor.execute('SELECT MIN(operator) FROM available_trips')
myresult21 = mycursor.fetchall()
minoperator = list(myresult21)
minoperatorr = minoperator[0][0]
if minoperatorr == '':
minoperatorr = 'BLANK'
mycursor.execute('SELECT MAX(operator) FROM available_trips')
myresult21 = mycursor.fetchall()
maxoperator = list(myresult21)
maxoperatorr = maxoperator[0][0]
sheet['D40'] = minoperatorr
sheet['E40'] = maxoperatorr
# attribute34: otgEnabled
mycursor.execute('SELECT COUNT(DISTINCT otgEnabled) FROM available_trips')
myresult34 = mycursor.fetchall()
otgEnabledcount = list(myresult34)
otgEnabledcountt = otgEnabledcount[0][0]
mycursor.execute('SELECT MIN(otgEnabled) FROM available_trips')
myresult21 = mycursor.fetchall()
minotgEnabled = list(myresult21)
minotgEnabledd = minotgEnabled[0][0]
if minotgEnabledd == '':
minotgEnabledd = 'BLANK'
mycursor.execute('SELECT MAX(otgEnabled) FROM available_trips')
myresult21 = mycursor.fetchall()
maxotgEnabled = list(myresult21)
maxotgEnabledd = maxotgEnabled[0][0]
sheet['D41'] = minotgEnabledd
sheet['E41'] = maxotgEnabledd
# attribute35: otgPolicy
mycursor.execute('SELECT COUNT(DISTINCT otgPolicy) FROM available_trips')
myresult35 = mycursor.fetchall()
otgPolicycount = list(myresult35)
otgPolicycountt = otgPolicycount[0][0]
mycursor.execute('SELECT MIN(otgPolicy) FROM available_trips')
myresult21 = mycursor.fetchall()
minotgPolicy = list(myresult21)
minotgPolicyy = minotgPolicy[0][0]
if minotgPolicyy == '':
minotgPolicyy = 'BLANK'
mycursor.execute('SELECT MAX(otgPolicy) FROM available_trips')
myresult21 = mycursor.fetchall()
maxotgPolicy = list(myresult21)
maxotgPolicyy = maxotgPolicy[0][0]
sheet['D42'] = minotgPolicyy
sheet['E42'] = maxotgPolicyy
# attribute36: partialCancellationAllowed
mycursor.execute('SELECT COUNT(DISTINCT partialCancellationAllowed) FROM available_trips')
myresult36 = mycursor.fetchall()
partialCancellationAllowedcount = list(myresult36)
partialCancellationAllowedcountt = partialCancellationAllowedcount[0][0]
mycursor.execute('SELECT MIN(partialCancellationAllowed) FROM available_trips')
myresult21 = mycursor.fetchall()
minpartialCancellationAllowed = list(myresult21)
minpartialCancellationAllowedd = minpartialCancellationAllowed[0][0]
if minpartialCancellationAllowedd == '':
minpartialCancellationAllowedd = 'BLANK'
mycursor.execute('SELECT MAX(partialCancellationAllowed) FROM available_trips')
myresult21 = mycursor.fetchall()
maxpartialCancellationAllowed = list(myresult21)
maxpartialCancellationAllowedd = maxpartialCancellationAllowed[0][0]
sheet['D43'] = minpartialCancellationAllowedd
sheet['E43'] = maxpartialCancellationAllowedd
# attribute37: partnerBaseCommission
mycursor.execute('SELECT COUNT(DISTINCT partnerBaseCommission) FROM available_trips')
myresult37 = mycursor.fetchall()
partnerBaseCommissioncount = list(myresult37)
partnerBaseCommissioncountt = partnerBaseCommissioncount[0][0]
mycursor.execute('SELECT MIN(partnerBaseCommission) FROM available_trips')
myresult21 = mycursor.fetchall()
minpartnerBaseCommission= list(myresult21)
minpartnerBaseCommissionn = minpartnerBaseCommission[0][0]
if minpartnerBaseCommissionn == '':
minpartnerBaseCommissionn = 'BLANK'
mycursor.execute('SELECT MAX(partnerBaseCommission) FROM available_trips')
myresult21 = mycursor.fetchall()
maxpartnerBaseCommission= list(myresult21)
maxpartnerBaseCommissionn = maxpartnerBaseCommission[0][0]
sheet['D44'] = minpartnerBaseCommissionn
sheet['E44'] = maxpartnerBaseCommissionn
# attribute38: primaryPaxCancellable
mycursor.execute('SELECT COUNT(DISTINCT primaryPaxCancellable) FROM available_trips')
myresult38 = mycursor.fetchall()
primaryPaxCancellablecount = list(myresult38)
primaryPaxCancellablecountt = primaryPaxCancellablecount[0][0]
mycursor.execute('SELECT MIN(primaryPaxCancellable) FROM available_trips')
myresult21 = mycursor.fetchall()
minprimaryPaxCancellable= list(myresult21)
minprimaryPaxCancellablee = minprimaryPaxCancellable[0][0]
if minprimaryPaxCancellablee == '':
minprimaryPaxCancellablee = 'BLANK'
mycursor.execute('SELECT MAX(primaryPaxCancellable) FROM available_trips')
myresult21 = mycursor.fetchall()
maxprimaryPaxCancellable= list(myresult21)
maxprimaryPaxCancellablee = maxprimaryPaxCancellable[0][0]
sheet['D45'] = minprimaryPaxCancellablee
sheet['E45'] = maxprimaryPaxCancellablee
# attribute39: routeId
mycursor.execute('SELECT COUNT(DISTINCT routeId) FROM available_trips')
myresult39 = mycursor.fetchall()
routeIdcount = list(myresult39)
routeIdcountt = routeIdcount[0][0]
mycursor.execute('SELECT MIN(routeId) FROM available_trips')
myresult21 = mycursor.fetchall()
minrouteId = list(myresult21)
minrouteIdd = minrouteId[0][0]
if minrouteIdd == '':
minrouteIdd = 'BLANK'
mycursor.execute('SELECT MAX(routeId) FROM available_trips')
myresult21 = mycursor.fetchall()
maxrouteId = list(myresult21)
maxrouteIdd = maxrouteId[0][0]
sheet['D46'] = minrouteIdd
sheet['E46'] = maxrouteIdd
# attribute40: rtc
mycursor.execute('SELECT COUNT(DISTINCT rtc) FROM available_trips')
myresult40 = mycursor.fetchall()
rtccount = list(myresult40)
rtccountt = rtccount[0][0]
mycursor.execute('SELECT MIN(rtc) FROM available_trips')
myresult21 = mycursor.fetchall()
minrtc = list(myresult21)
minrtcc = minrtc[0][0]
if minrtcc == '':
minrtcc = 'BLANK'
mycursor.execute('SELECT MAX(rtc) FROM available_trips')
myresult21 = mycursor.fetchall()
maxrtc= list(myresult21)
maxrtcc = maxrtc[0][0]
sheet['D47'] = minrtcc
sheet['E47'] = maxrtcc
# attribute41: seater
mycursor.execute('SELECT COUNT(DISTINCT seater) FROM available_trips')
myresult41 = mycursor.fetchall()
seatercount = list(myresult41)
seatercountt = seatercount[0][0]
mycursor.execute('SELECT MIN(seater) FROM available_trips')
myresult21 = mycursor.fetchall()
minseater = list(myresult21)
minseaterr = minseater[0][0]
mycursor.execute('SELECT MAX(seater) FROM available_trips')
myresult21 = mycursor.fetchall()
maxseater = list(myresult21)
maxseaterr = maxseater[0][0]
sheet['D48'] = minseaterr
sheet['E48'] = maxseaterr
# attribute42: selfInventory
mycursor.execute('SELECT COUNT(DISTINCT selfInventory) FROM available_trips')
myresult42 = mycursor.fetchall()
selfInventorycount = list(myresult42)
selfInventorycountt = selfInventorycount[0][0]
mycursor.execute('SELECT MIN(selfInventory) FROM available_trips')
myresult21 = mycursor.fetchall()
minselfInventory = list(myresult21)
minselfInventoryy = minselfInventory[0][0]
if minselfInventoryy == '':
minselfInventoryy = 'BLANK'
mycursor.execute('SELECT MAX(selfInventory) FROM available_trips')
myresult21 = mycursor.fetchall()
maxselfInventory = list(myresult21)
maxselfInventoryy = maxselfInventory[0][0]
sheet['D49'] = minselfInventoryy
sheet['E49'] = maxselfInventoryy
# attribute43: singleLadies
mycursor.execute('SELECT COUNT(DISTINCT singleLadies) FROM available_trips')
myresult43 = mycursor.fetchall()
singleLadiescount = list(myresult43)
singleLadiescountt = singleLadiescount[0][0]
mycursor.execute('SELECT MIN(singleLadies) FROM available_trips')
myresult21 = mycursor.fetchall()
minsingleLadies = list(myresult21)
minsingleLadiess = minsingleLadies[0][0]
if minsingleLadiess == '':
minsingleLadiess = 'BLANK'
mycursor.execute('SELECT MAX(singleLadies) FROM available_trips')
myresult21 = mycursor.fetchall()
maxsingleLadies = list(myresult21)
maxsingleLadiess = maxsingleLadies[0][0]
sheet['D50'] = minsingleLadiess
sheet['E50'] = maxsingleLadiess
# attribute44: sleeper
mycursor.execute('SELECT COUNT(DISTINCT sleeper) FROM available_trips')
myresult44 = mycursor.fetchall()
sleepercount = list(myresult44)
sleepercountt = sleepercount[0][0]
mycursor.execute('SELECT MIN(sleeper) FROM available_trips')
myresult21 = mycursor.fetchall()
minsleeper= list(myresult21)
minsleeperr = minsleeper[0][0]
if minsleeperr == '':
minsleeperr = 'BLANK'
mycursor.execute('SELECT MAX(sleeper) FROM available_trips')
myresult21 = mycursor.fetchall()
maxsleeper = list(myresult21)
maxsleeperr = maxsleeper[0][0]
sheet['D51'] = minsleeperr
sheet['E51'] = maxsleeperr
# attribute45: tatkalTime
mycursor.execute('SELECT COUNT(DISTINCT tatkalTime) FROM available_trips')
myresult45 = mycursor.fetchall()
tatkalTimecount = list(myresult45)
tatkalTimecountt = tatkalTimecount[0][0]
mycursor.execute('SELECT MIN(tatkalTime) FROM available_trips')
myresult21 = mycursor.fetchall()
mintatkalTime = list(myresult21)
mintatkalTimee = mintatkalTime[0][0]
if mintatkalTimee == '':
mintatkalTimee = 'BLANK'
mycursor.execute('SELECT MAX(tatkalTime) FROM available_trips')
myresult21 = mycursor.fetchall()
maxtatkalTime = list(myresult21)
maxtatkalTimee = maxtatkalTime[0][0]
sheet['D52'] = mintatkalTimee
sheet['E52'] = maxtatkalTimee
# attribute46: vehicleType
mycursor.execute('SELECT COUNT(DISTINCT vehicleType) FROM available_trips')
myresult46 = mycursor.fetchall()
vehicleTypecount = list(myresult46)
vehicleTypecountt = vehicleTypecount[0][0]
mycursor.execute('SELECT MIN(vehicleType) FROM available_trips')
myresult21 = mycursor.fetchall()
minvehicleType = list(myresult21)
minvehicleTypee = minvehicleType[0][0]
if minvehicleTypee == '':
minvehicleTypee = 'BLANK'
mycursor.execute('SELECT MAX(vehicleType) FROM available_trips')
myresult21 = mycursor.fetchall()
maxvehicleType = list(myresult21)
maxvehicleTypee = maxvehicleType[0][0]
sheet['D53'] = minvehicleTypee
sheet['E53'] = maxvehicleTypee
# attribute47: viaRoutes
mycursor.execute('SELECT COUNT(DISTINCT viaRoutes) FROM available_trips')
myresult47 = mycursor.fetchall()
viaRoutescount = list(myresult47)
viaRoutescountt = viaRoutescount[0][0]
mycursor.execute('SELECT MIN(viaRoutes) FROM available_trips')
myresult21 = mycursor.fetchall()
minviaRoutes = list(myresult21)
minviaRoutess = minviaRoutes[0][0]
if minviaRoutess == '':
minviaRoutess = 'BLANK'
mycursor.execute('SELECT MAX(viaRoutes) FROM available_trips')
myresult21 = mycursor.fetchall()
maxviaRoutes = list(myresult21)
maxviaRoutess = maxviaRoutes[0][0]
sheet['D54'] = minviaRoutess
sheet['E54'] = maxviaRoutess
# attribute48: zeroCancellationTime
mycursor.execute('SELECT COUNT(DISTINCT zeroCancellationTime) FROM available_trips')
myresult48 = mycursor.fetchall()
zeroCancellationTimecount = list(myresult48)
zeroCancellationTimecountt = zeroCancellationTimecount[0][0]
mycursor.execute('SELECT MIN(zeroCancellationTime) FROM available_trips')
myresult21 = mycursor.fetchall()
minzeroCancellationTime = list(myresult21)
minzeroCancellationTimee = minzeroCancellationTime[0][0]
if minzeroCancellationTimee == '':
minzeroCancellationTimee = 'BLANK'
mycursor.execute('SELECT MAX(zeroCancellationTime) FROM available_trips')
myresult21 = mycursor.fetchall()
maxminzeroCancellationTime = list(myresult21)
maxminzeroCancellationTimee = maxminzeroCancellationTime[0][0]
sheet['D55'] = minzeroCancellationTimee
sheet['E55'] = maxminzeroCancellationTimee
# attribute49: mTicketEnabled
mycursor.execute('SELECT COUNT(DISTINCT mTicketEnabled) FROM available_trips')
myresult49 = mycursor.fetchall()
mTicketEnabledcount = list(myresult49)
mTicketEnabledcountt = mTicketEnabledcount[0][0]
mycursor.execute('SELECT MIN(mTicketEnabled) FROM available_trips')
myresult21 = mycursor.fetchall()
minmTicketEnabled = list(myresult21)
minmTicketEnabled = minmTicketEnabled[0][0]
if minmTicketEnabled == '':
minmTicketEnabled = 'BLANK'
mycursor.execute('SELECT MAX(mTicketEnabled) FROM available_trips')
myresult21 = mycursor.fetchall()
maxmTicketEnabled = list(myresult21)
maxmTicketEnabledd = maxmTicketEnabled[0][0]
sheet['D56'] = minmTicketEnabled
sheet['E56'] = maxmTicketEnabledd
# attribute50: sd_id
mycursor.execute('SELECT COUNT(DISTINCT sd_id) FROM available_trips')
myresult49 = mycursor.fetchall()
sd_idcount = list(myresult49)
sd_idcountt = sd_idcount[0][0]
mycursor.execute('SELECT MIN(sd_id) FROM available_trips')
myresult21 = mycursor.fetchall()
minsd_id = list(myresult21)
minsd_idd = minsd_id[0][0]
if minsd_id == '':
minsd_id = 'BLANK'
mycursor.execute('SELECT MAX(sd_id) FROM available_trips')
myresult21 = mycursor.fetchall()
maxsd_id = list(myresult21)
maxsd_idd = maxsd_id[0][0]
sheet['D57'] = minsd_idd
sheet['E57'] = maxsd_idd
# attribute51: createDt
mycursor.execute('SELECT COUNT(DISTINCT createDt) FROM available_trips')
myresult50 = mycursor.fetchall()
createDtcount = list(myresult50)
createDtcountt = createDtcount[0][0]
mycursor.execute('select distinct createDt from available_trips ORDER BY createDt ASC')
myresult50 = mycursor.fetchall() # orderby min logic
mincreateDt = [item for x in zip_longest(*myresult50) for item in x if item != -55 and item != '']
mincreateDtt = mincreateDt[0]
if mincreateDtt == '':
mincreateDtt = 'BLANK'
mycursor.execute('select distinct createDt from available_trips ORDER BY createDt DESC')
myresult50 = mycursor.fetchall() # orderby max logic
maxcreateDt = [item for x in zip_longest(*myresult50) for item in x if item != -55 and item != '']
maxcreateDtt = maxcreateDt[0]
sheet['D58'] = mincreateDtt
sheet['E58'] = maxcreateDtt
# attribute52: created_date
mycursor.execute('SELECT COUNT(DISTINCT created_date) FROM available_trips')
myresult50 = mycursor.fetchall()
created_datecount = list(myresult50)
created_datecountt = created_datecount[0][0]
mycursor.execute('select distinct created_date from available_trips ORDER BY created_date ASC')
myresult50 = mycursor.fetchall() # orderby min logic
mincreated_date = [item for x in zip_longest(*myresult50) for item in x if item != -55 and item != '']
mincreated_datee = mincreated_date[0]
if mincreated_datee == '':
mincreated_datee = 'BLANK'
mycursor.execute('select distinct created_date from available_trips ORDER BY created_date DESC')
myresult50 = mycursor.fetchall() # orderby max logic
maxcreated_date = [item for x in zip_longest(*myresult50) for item in x if item != -55 and item != '']
maxcreated_datee = maxcreated_date[0]
sheet['D59'] = mincreated_datee
sheet['E59'] = maxcreated_datee
sheet['C6'] = "Min:"+ str(mincreated_datee)+","+"Max:" + str(maxcreated_datee)
sheet['B1'] = "DB Name"
sheet['B2'] = "TABLE NAME"
sheet['B3'] = "TOTAL RECORDS"
sheet['B4'] = "TOTAL BLANK RECORDS ACC. TO DOJ"
sheet['B5'] = "DIFFRENCE(ACTUAL RECORDS) "
sheet['B6'] = "CREATED DATE"
sheet['A7'] = "Attribute.No"
sheet['B7'] = "Attribute Name"
sheet['D7'] = "Min Value"
sheet['E7'] = "Max Value"
sheet['F7'] = "DIFFERENCE VALUE"
sheet['C7'] = "COUNT (DISTINCT)"
# sheet['B3'] = sourcenamecountt
sheet.cell(row=1, column=2).font = Font(size=15)
sheet.cell(row=2, column=2).font = Font(size=15)
sheet.cell(row=3, column=2).font = Font(size=15)
sheet.cell(row=4, column=2).font = Font(size=15)
sheet.cell(row=5, column=2).font = Font(size=15)
sheet.cell(row=6, column=2).font = Font(size=15)
sheet.cell(row=1, column=3).font = Font(size=12)
sheet.cell(row=2, column=3).font = Font(size=12)
sheet.cell(row=3, column=3).font = Font(size=12)
sheet.cell(row=4, column=3).font = Font(size=12)
sheet.cell(row=5, column=3).font = Font(size=12)
sheet.cell(row=6, column=3).font = Font(size=12)
sheet.cell(row=7, column=1).font = Font(size=15)
sheet.cell(row=7, column=2).font = Font(size=15)
sheet.cell(row=7, column=3).font = Font(size=15)
sheet.cell(row=7, column=4).font = Font(size=15)
sheet.cell(row=7, column=5).font = Font(size=15)
# sheet['A1'].fill = PatternFill(bgColor="Orange", fill_type = "solid")
# for x in range(32):
# sheet.cell(row=7, column=1+x).fill = PatternFill(bgColor="Orange", fill_type = "solid")
# set the height of the row
sheet.row_dimensions[7].height = 25
# set the width of the column
sheet.column_dimensions['A'].width = 25
sheet.column_dimensions['B'].width = 45
sheet.column_dimensions['C'].width = 55
sheet.column_dimensions['D'].width = 45
sheet.column_dimensions['E'].width = 45
sheet.column_dimensions['F'].width = 30
sheet.column_dimensions['G'].width = 30
sheet.column_dimensions['H'].width = 30
sheet.column_dimensions['I'].width = 30
sheet.column_dimensions['J'].width = 30
sheet.column_dimensions['K'].width = 30
sheet.column_dimensions['L'].width = 30
sheet.column_dimensions['M'].width = 30
sheet.column_dimensions['N'].width = 30
sheet.column_dimensions['O'].width = 30
sheet.column_dimensions['P'].width = 30
sheet.column_dimensions['Q'].width = 30
sheet.column_dimensions['R'].width = 30
sheet.column_dimensions['S'].width = 30
sheet.column_dimensions['T'].width = 30
sheet.column_dimensions['U'].width = 30
sheet.column_dimensions['V'].width = 30
sheet.column_dimensions['W'].width = 30
sheet.column_dimensions['X'].width = 30
sheet.column_dimensions['Y'].width = 30
sheet.column_dimensions['Z'].width = 30
sheet.column_dimensions['AA'].width = 30
sheet.column_dimensions['AB'].width = 30
sheet.column_dimensions['AC'].width = 30
sheet.column_dimensions['AD'].width = 30
sheet.column_dimensions['AE'].width = 30
sheet.column_dimensions['AF'].width = 30
sheet.column_dimensions['AG'].width = 30
sheet.column_dimensions['AH'].width = 30
sheet.column_dimensions['AI'].width = 30
sheet.column_dimensions['AJ'].width = 30
numberlist = list(range(1, 53))
for x in range(52): # attribute number loop
c3 = sheet.cell(row=x + 8, column=1)
c3.value = numberlist[x]
# sheet['F9'] = (driver.var9 - driver.var10)
sheet['C8'] = idcountt
sheet['C9'] = sourcecountt
sheet['C10'] = sourcenamecountt
sheet['C11'] = destinationcountt
sheet['C12'] = destinationnamecountt
sheet['C13'] = travelscountt
sheet['C14'] = ACcountt
sheet['C15'] = arrivalTimecountt
sheet['C16'] = availCatCardcountt
sheet['C17'] = availSrCitizencountt
sheet['C18'] = availableSeatscountt
sheet['C19'] = avlWindowSeatscountt
sheet['C20'] = boardingTimescountt
sheet['C21'] = bookablecountt
sheet['C22'] = bpDpSeatLayoutcountt
sheet['C23'] = busImageCountcountt
sheet['C24'] = busServiceIdcountt
sheet['C25'] = busTypecountt
sheet['C26'] = busTypeIdcountt
sheet['C27'] = cancellationPolicycountt
sheet['C28'] = departureTimecountt
sheet['C29'] = dojcountt
sheet['C30'] = dropPointMandatorycountt
sheet['C31'] = droppingTimescountt
sheet['C32'] = fareDetailscountt
sheet['C33'] = farescountt
sheet['C34'] = flatComApplicablecountt
sheet['C35'] = gdsCommissioncountt
sheet['C36'] = idProofRequiredcountt
sheet['C37'] = liveTrackingAvailablecountt
sheet['C38'] = maxSeatsPerTicketcountt
sheet['C39'] = nonACcountt
sheet['C40'] = operatorcountt
sheet['C41'] = otgEnabledcountt
sheet['C42'] = otgPolicycountt
sheet['C43'] = partialCancellationAllowedcountt
sheet['C44'] = partnerBaseCommissioncountt
sheet['C45'] = primaryPaxCancellablecountt
sheet['C46'] = routeIdcountt
sheet['C47'] = rtccountt
sheet['C48'] = seatercountt
sheet['C49'] = selfInventorycountt
sheet['C50'] = singleLadiescountt
sheet['C51'] = sleepercountt
sheet['C52'] = tatkalTimecountt
sheet['C53'] = vehicleTypecountt
sheet['C54'] = viaRoutescountt
sheet['C55'] = zeroCancellationTimecountt
sheet['C56'] = mTicketEnabledcountt
sheet['C57'] = sd_idcountt
sheet['C58'] = createDtcountt
sheet['C59'] = created_datecountt
sheet['F9'] = (sourcecountt - sourcenamecountt)
sheet['F11'] = (destinationcountt - destinationnamecountt)
sheet['F13'] = 'travels - operators:' + str(travelscountt - operatorcountt)
# listmin = [minidd, minsourcee, minsource_namee, mindestinationn, mindestinationnamee, mintravelss, minacc,
# minarrivalTimee, minavailCatCardd, minavailSrCitizenn]
# sheet['F18'] = "max seats 68?:" + '' + mymodule.val
book.save(driver.desktop+'ParentMainAnalysis.xlsx')
# book.save('DistinctCounts.xlsx')
# except:
# print("Something went wrong")
conn.close()
def show_tables(db_cursor: MySQLCursor):
db_cursor.execute("SHOW TABLES")
myresult = db_cursor.fetchall()
for x in myresult:
print(x)
def fetch_all(self, cursor: MySQLCursor) -> Union[object, None]:
return cursor.fetchall()
def describe_tables(db_cursor: MySQLCursor, table_name: str):
db_cursor.execute("DESCRIBE " + table_name)
myresult = db_cursor.fetchall()
for x in myresult:
print(x)
def Sample():
# SAMPLES 30 VALUES COLLECT CAPTURE IN LISTS
conn = mysql.connector.connect(user=driver.user,
password=driver.password,
host=driver.host,
database=driver.databasename)
mycursor = MySQLCursor(conn)
book = Workbook()
sheet = book.active
mycursor.execute('Desc available_trips')
myresult0 = mycursor.fetchall() # to get all rows
res_list = [item for x in zip_longest(*myresult0) for item in x if item]
finalattrib = res_list[:52]
for x in range(52):
c1 = sheet.cell(row=x + 2, column=2) # attribute name insertion loop
c1.value = finalattrib[x]
templist = list(range(1, 31))
for x in range(30):
c2 = sheet.cell(row=1, column=5 + x) # sample string loop
c2.value = 'Sample' + str(templist[x])
# print(distinctidd)
mycursor.execute('SELECT DISTINCT id FROM available_trips ')
myresult54 = mycursor.fetchall() # sample 1)id 30 collect from table
idoutput = [
item for x in zip_longest(*myresult54) for item in x if item != -55
]
distinctidd = idoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT source FROM available_trips ')
myresult55 = mycursor.fetchall() # sample 2)source 30 collect from table
sourceoutput = [
item for x in zip_longest(*myresult55) for item in x if item != -55
]
sourceoutputt = sourceoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT source_name FROM available_trips ')
myresult56 = mycursor.fetchall(
) # sample 3)sourcename 30 collect from table
sourcenameoutput = [
item for x in zip_longest(*myresult56) for item in x if item != -55
]
sourcenameoutputt = sourcenameoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT destination FROM available_trips ')
myresult57 = mycursor.fetchall(
) # sample 4)DESTINATION 30 collect from table
destinationoutput = [
item for x in zip_longest(*myresult57) for item in x if item != -55
]
destinationoutputt = destinationoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT destination_name FROM available_trips ')
myresult58 = mycursor.fetchall(
) # sample 5)destination name 30 collect from table
destination_nameoutput = [
item for x in zip_longest(*myresult58) for item in x if item != -55
]
destination_nameoutputt = destination_nameoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'select distinct travels from available_trips ORDER BY operator ASC ')
myresult59 = mycursor.fetchall()
# print(myresult59) #sample 6)travels 30 collect from table
travelsoutput = [
item for x in zip_longest(*myresult59) for item in x
if item != -55 and item != ''
]
# print(travelsoutput)
travelsoutputt = travelsoutput[:30]
#
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT AC FROM available_trips ')
myresult60 = mycursor.fetchall() # sample 7)AC 30 collect from table
acoutput = [
item for x in zip_longest(*myresult60) for item in x if item != -55
]
acoutputt = acoutput[:30]
# print(acoutputt)
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT distinct arrivalTime FROM available_trips WHERE arrivalTime NOT BETWEEN 0000 AND 2400 order by arrivalTime asc '
)
myresult61 = mycursor.fetchall()
# print(myresult61) #sample 8)arrivalTime 30 collect from table
arrivalTimeoutput = [
item for x in zip_longest(*myresult61) for item in x if item != -55
]
arrivalTimeoutputt = arrivalTimeoutput[:30]
# print(arrivalTimeoutputt)
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT availCatCard FROM available_trips ')
myresult62 = mycursor.fetchall(
) # sample 9)availCatCard 30 collect from table
availCatCardoutput = [
item for x in zip_longest(*myresult62) for item in x if item != -55
]
availCatCardoutputt = availCatCardoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT availSrCitizen FROM available_trips ')
myresult63 = mycursor.fetchall(
) # sample 10)availSrCitizen 30 collect from table
availSrCitizenoutput = [
item for x in zip_longest(*myresult63) for item in x if item != -55
]
availSrCitizenoutputt = availSrCitizenoutput[:30]
# sample 11)availableSeats
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT availableSeats FROM available_trips ')
myresult64 = mycursor.fetchall(
) # sample 11)availableSeats 30 collect from table
availableSeatsoutput = [
item for x in zip_longest(*myresult64) for item in x if item != -55
]
availableSeatsoutputt = availableSeatsoutput[:30]
# print(availableSeatsoutputt)
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT avlWindowSeats FROM available_trips ')
myresult65 = mycursor.fetchall(
) # sample12)avlWindowSeats 30 collect from table
avlWindowSeatsoutput = [
item for x in zip_longest(*myresult65) for item in x if item != -55
]
avlWindowSeatsoutputt = avlWindowSeatsoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT boardingTimes FROM available_trips ')
myresult66 = mycursor.fetchall(
) # sample 13)boardingTimes 30 collect from table
boardingTimesoutput = [
item for x in zip_longest(*myresult66) for item in x if item != -55
]
boardingTimesoutputt = boardingTimesoutput[:30]
# 14)bookable 30 collect from table
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT bookable FROM available_trips ')
myresult67 = mycursor.fetchall(
) # sample 14)bookable 30 collect from table
bookableoutput = [
item for x in zip_longest(*myresult67) for item in x if item != -55
]
bookableoutputt = bookableoutput[:30]
# mycursor = MySQLCursor(conn)
# mycursor.execute('SELECT COUNT(bookable) FROM available_trips GROUP BY bookable order by bookable ASC')
# myresult67 = mycursor.fetchall() # sample 14)bookable 30 collect from table
# bookableoutput = [item for x in zip_longest(*myresult67) for item in x if item != -55]
#bookableoutputt = bookableoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT bpDpSeatLayout FROM available_trips ')
myresult68 = mycursor.fetchall(
) # sample 15 bpDpSeatLayout 30 collect from table
bpDpSeatLayoutoutput = [
item for x in zip_longest(*myresult68) for item in x if item != -55
]
bpDpSeatLayoutoutputt = bpDpSeatLayoutoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT busImageCount FROM available_trips ')
myresult69 = mycursor.fetchall()
# print(myresult69) #sample 16busImageCount 30 collect from table
busImageCountoutput = [
item for x in zip_longest(*myresult69) for item in x if item != -55
]
busImageCountoutputt = busImageCountoutput[:30]
# print(busImageCountoutputt) #sample 16busImageCount 30 collect from table
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT busServiceId FROM available_trips ')
myresult70 = mycursor.fetchall(
) # sample 17 busServiceId 30 collect from table
busServiceIdoutput = [
item for x in zip_longest(*myresult70) for item in x if item != -55
]
busServiceIdoutputt = busServiceIdoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT busType FROM available_trips ')
myresult71 = mycursor.fetchall(
) # sample 18busType 30 collect from table
busTypeoutput = [
item for x in zip_longest(*myresult71) for item in x if item != -55
]
busTypeoutputt = busTypeoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT busTypeId FROM available_trips ')
myresult72 = mycursor.fetchall(
) # sample 19busTypeId 30 collect from table
busTypeIdoutput = [
item for x in zip_longest(*myresult72) for item in x if item != -55
]
busTypeIdoutputt = busTypeIdoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT DISTINCT cancellationPolicy FROM available_trips ')
myresult73 = mycursor.fetchall(
) # sample 20cancellationPolicy 30 collect from table
cancellationPolicyoutput = [
item for x in zip_longest(*myresult73) for item in x if item != -55
]
cancellationPolicyoutputt = cancellationPolicyoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT departureTime FROM available_trips ')
myresult74 = mycursor.fetchall(
) # sample 21departureTime 30 collect from table
departureTimeoutput = [
item for x in zip_longest(*myresult74) for item in x if item != -55
]
departureTimeoutputt = departureTimeoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT doj FROM available_trips ')
myresult75 = mycursor.fetchall() # sample 22doj 30 collect from table
dojoutput = [
item for x in zip_longest(*myresult75) for item in x if item != -55
]
dojoutputt = dojoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT DISTINCT dropPointMandatory FROM available_trips ')
myresult76 = mycursor.fetchall(
) # sample 23dropPointMandatory 30 collect from table
dropPointMandatoryoutput = [
item for x in zip_longest(*myresult76) for item in x if item != -55
]
dropPointMandatoryoutputt = dropPointMandatoryoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT droppingTimes FROM available_trips ')
myresult77 = mycursor.fetchall(
) # sample 24droppingTimes 30 collect from table
droppingTimesoutput = [
item for x in zip_longest(*myresult77) for item in x if item != -55
]
droppingTimesoutputt = droppingTimesoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT fareDetails FROM available_trips ')
myresult78 = mycursor.fetchall(
) # sample 25fareDetails 30 collect from table
fareDetailsoutput = [
item for x in zip_longest(*myresult78) for item in x if item != -55
]
fareDetailsoutputt = fareDetailsoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT fares FROM available_trips ')
myresult79 = mycursor.fetchall() # sample 26fares 30 collect from table
faresoutput = [
item for x in zip_longest(*myresult79) for item in x if item != -55
]
faresoutputt = faresoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT DISTINCT flatComApplicable FROM available_trips ')
myresult80 = mycursor.fetchall(
) # sample 27flatComApplicable 30 collect from table
flatComApplicableoutput = [
item for x in zip_longest(*myresult80) for item in x if item != -55
]
flatComApplicableoutputt = flatComApplicableoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT gdsCommission FROM available_trips ')
myresult81 = mycursor.fetchall(
) # sample 28gdsCommission 30 collect from table
gdsCommissionoutput = [
item for x in zip_longest(*myresult81) for item in x if item != -55
]
gdsCommissionoutputt = gdsCommissionoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT idProofRequired FROM available_trips ')
myresult82 = mycursor.fetchall(
) # sample 29idProofRequired 30 collect from table
idProofRequiredoutput = [
item for x in zip_longest(*myresult82) for item in x if item != -55
]
idProofRequiredoutputt = idProofRequiredoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT DISTINCT liveTrackingAvailable FROM available_trips ')
myresult83 = mycursor.fetchall(
) # sample 30liveTrackingAvailable 30 collect from table
liveTrackingAvailableoutput = [
item for x in zip_longest(*myresult83) for item in x if item != -55
]
liveTrackingAvailableoutputt = liveTrackingAvailableoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT DISTINCT maxSeatsPerTicket FROM available_trips ')
myresult84 = mycursor.fetchall(
) # sample 31maxSeatsPerTicket 30 collect from table
maxSeatsPerTicketoutput = [
item for x in zip_longest(*myresult84) for item in x if item != -55
]
maxSeatsPerTicketoutputt = maxSeatsPerTicketoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT nonAC FROM available_trips ')
myresult85 = mycursor.fetchall() # sample 32nonAC 30 collect from table
nonACoutput = [
item for x in zip_longest(*myresult85) for item in x if item != -55
]
nonACoutputt = nonACoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'select distinct operator from available_trips ORDER BY operator ASC')
myresult86 = mycursor.fetchall(
) # sample 33operator 30 collect from table
operatoroutput = [
item for x in zip_longest(*myresult86) for item in x if item != -55
]
operatoroutputt = operatoroutput[:30]
# print(operatoroutputt)
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT otgEnabled FROM available_trips ')
myresult87 = mycursor.fetchall(
) # sample 34otgEnabled 30 collect from table
otgEnabledoutput = [
item for x in zip_longest(*myresult87) for item in x if item != -55
]
otgEnabledoutputt = otgEnabledoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT otgPolicy FROM available_trips ')
myresult88 = mycursor.fetchall(
) # sample 35otgPolicy 30 collect from table
otgPolicyoutput = [
item for x in zip_longest(*myresult88) for item in x if item != -55
]
otgPolicyoutputt = otgPolicyoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT DISTINCT partialCancellationAllowed FROM available_trips ')
myresult89 = mycursor.fetchall(
) # sample 36partialCancellationAllowed 30 collect from table
partialCancellationAllowedoutput = [
item for x in zip_longest(*myresult89) for item in x if item != -55
]
partialCancellationAllowedoutputt = partialCancellationAllowedoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT DISTINCT partnerBaseCommission FROM available_trips ')
myresult90 = mycursor.fetchall(
) # sample 37partnerBaseCommission 30 collect from table
partnerBaseCommissionoutput = [
item for x in zip_longest(*myresult90) for item in x if item != -55
]
partnerBaseCommissionoutputt = partnerBaseCommissionoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT DISTINCT primaryPaxCancellable FROM available_trips ')
myresult91 = mycursor.fetchall(
) # sample 38primaryPaxCancellable 30 collect from table
primaryPaxCancellableoutput = [
item for x in zip_longest(*myresult91) for item in x if item != -55
]
primaryPaxCancellableoutputt = primaryPaxCancellableoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT routeId FROM available_trips ')
myresult92 = mycursor.fetchall() # sample 39routeId 30 collect from table
routeIdoutput = [
item for x in zip_longest(*myresult92) for item in x if item != -55
]
routeIdoutputt = routeIdoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT rtc FROM available_trips ')
myresult93 = mycursor.fetchall() # sample 40rtc 30 collect from table
rtcoutput = [
item for x in zip_longest(*myresult93) for item in x if item != -55
]
rtcoutputt = rtcoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT seater FROM available_trips ')
myresult94 = mycursor.fetchall() # sample 41seater 30 collect from table
seateroutput = [
item for x in zip_longest(*myresult94) for item in x if item != -55
]
seateroutputt = seateroutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT selfInventory FROM available_trips ')
myresult95 = mycursor.fetchall(
) # sample 42selfInventory 30 collect from table
selfInventoryoutput = [
item for x in zip_longest(*myresult95) for item in x if item != -55
]
selfInventoryoutputt = selfInventoryoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT singleLadies FROM available_trips ')
myresult96 = mycursor.fetchall(
) # sample 43singleLadies 30 collect from table
singleLadiesoutput = [
item for x in zip_longest(*myresult96) for item in x if item != -55
]
singleLadiesoutputt = singleLadiesoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT sleeper FROM available_trips ')
myresult97 = mycursor.fetchall(
) # sample 44sleeper 30 collect from table
sleeperoutput = [
item for x in zip_longest(*myresult97) for item in x if item != -55
]
sleeperoutputt = sleeperoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT tatkalTime FROM available_trips ')
myresult98 = mycursor.fetchall(
) # sample 45tatkalTime 30 collect from table
tatkalTimeoutput = [
item for x in zip_longest(*myresult98) for item in x if item != -55
]
tatkalTimeoutputt = tatkalTimeoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT vehicleType FROM available_trips ')
myresult99 = mycursor.fetchall(
) # sample 46vehicleType 30 collect from table
vehicleTypeoutput = [
item for x in zip_longest(*myresult99) for item in x if item != -55
]
vehicleTypeoutputt = vehicleTypeoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT viaRoutes FROM available_trips ')
myresult100 = mycursor.fetchall(
) # sample 47viaRoutes 30 collect from table
viaRoutesoutput = [
item for x in zip_longest(*myresult100) for item in x if item != -55
]
viaRoutesoutputt = viaRoutesoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT DISTINCT zeroCancellationTime FROM available_trips ')
myresult104 = mycursor.fetchall(
) # sample 48zeroCancellationTime 30 collect from table
zeroCancellationTimeoutput = [
item for x in zip_longest(*myresult104) for item in x if item != -55
]
zeroCancellationTimeoutputt = zeroCancellationTimeoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT mTicketEnabled FROM available_trips ')
myresult105 = mycursor.fetchall(
) # sample 49mTicketEnabled 30 collect from table
mTicketEnabledoutput = [
item for x in zip_longest(*myresult105) for item in x if item != -55
]
mTicketEnabledoutputt = mTicketEnabledoutput[:30]
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT sd_id FROM available_trips ')
myresult105 = mycursor.fetchall() # sample 50sd_id 30 collect from table
sd_idoutput = [
item for x in zip_longest(*myresult105) for item in x if item != -55
]
sd_idoutputt = sd_idoutput[:30]
#51 createDt
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT createDt FROM available_trips ')
myresult107 = mycursor.fetchall(
) # sample 51created_date 30 collect from table
createDtoutput = [
item for x in zip_longest(*myresult107) for item in x if item != -55
]
createDtoutputt = createDtoutput[:30]
#52 created_date
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT DISTINCT created_date FROM available_trips ')
myresult107 = mycursor.fetchall(
) # sample 51created_date 30 collect from table
created_dateoutput = [
item for x in zip_longest(*myresult107) for item in x if item != -55
]
created_dateoutputt = created_dateoutput[:30]
len1 = len(distinctidd)
for x in range(len1):
c4 = sheet.cell(row=2, column=5 + x) # distinct id 30 samples loop
c4.value = distinctidd[x]
len2 = len(sourceoutputt)
for x in range(len2):
c4 = sheet.cell(row=3, column=5 + x) # distinct source 30 samples loop
c4.value = sourceoutputt[x]
len3 = len(sourcenameoutputt)
for x in range(len3):
c4 = sheet.cell(row=4, column=5 + x) # distinct source 30 samples loop
c4.value = sourcenameoutputt[x]
len4 = len(destinationoutputt)
for x in range(len4):
c4 = sheet.cell(row=5,
column=5 + x) # distinct destination 30 samples loop
c4.value = destinationoutputt[x]
len5 = len(destination_nameoutputt)
for x in range(len5):
c4 = sheet.cell(row=6,
column=5 + x) # distinct destination 30 samples loop
c4.value = destination_nameoutputt[x]
len6 = len(travelsoutputt)
for x in range(len6):
c4 = sheet.cell(row=7,
column=5 + x) # distinct destination 30 samples loop
c4.value = travelsoutputt[x]
len7 = len(acoutput)
for x in range(len7):
c4 = sheet.cell(row=8,
column=5 + x) # distinct destination 30 samples loop
c4.value = acoutputt[x]
len8 = len(arrivalTimeoutputt)
for x in range(len8):
c4 = sheet.cell(row=9,
column=5 + x) # distinct destination 30 samples loop
c4.value = arrivalTimeoutputt[x]
len9 = len(availCatCardoutputt)
for x in range(len9):
c4 = sheet.cell(row=10,
column=5 + x) # distinct destination 30 samples loop
c4.value = availCatCardoutputt[x]
len10 = len(availSrCitizenoutputt)
for x in range(len10):
c4 = sheet.cell(row=11,
column=5 + x) # distinct destination 30 samples loop
c4.value = availSrCitizenoutputt[x]
len11 = len(availableSeatsoutputt)
for x in range(len11):
c4 = sheet.cell(row=12,
column=5 + x) # distinct destination 30 samples loop
c4.value = availableSeatsoutputt[x]
len12 = len(avlWindowSeatsoutputt)
for x in range(len12):
c4 = sheet.cell(row=13,
column=5 + x) # distinct destination 30 samples loop
c4.value = avlWindowSeatsoutputt[x]
len13 = len(boardingTimesoutputt)
for x in range(len13):
c4 = sheet.cell(row=14,
column=5 + x) # distinct destination 30 samples loop
c4.value = boardingTimesoutputt[x]
len14 = len(bookableoutputt)
for x in range(len14):
c4 = sheet.cell(row=15,
column=5 + x) # distinct destination 30 samples loop
c4.value = bookableoutputt[x]
len15 = len(bpDpSeatLayoutoutputt)
for x in range(len15):
c4 = sheet.cell(row=16,
column=5 + x) # distinct destination 30 samples loop
c4.value = bpDpSeatLayoutoutputt[x]
len16 = len(busImageCountoutputt)
for x in range(len16):
c4 = sheet.cell(row=17,
column=5 + x) # distinct destination 30 samples loop
c4.value = busImageCountoutputt[x]
len17 = len(busServiceIdoutputt)
for x in range(len17):
c4 = sheet.cell(row=18,
column=5 + x) # distinct destination 30 samples loop
c4.value = busServiceIdoutputt[x]
len18 = len(busTypeoutputt)
for x in range(len18):
c4 = sheet.cell(row=19,
column=5 + x) # distinct destination 30 samples loop
c4.value = busTypeoutputt[x]
# sourceoutputt[x]
len19 = len(busTypeIdoutputt)
for x in range(len19):
c4 = sheet.cell(row=20,
column=5 + x) # distinct destination 30 samples loop
c4.value = busTypeIdoutputt[x]
len20 = len(cancellationPolicyoutputt)
for x in range(len20):
c4 = sheet.cell(row=21,
column=5 + x) # distinct destination 30 samples loop
c4.value = cancellationPolicyoutputt[x]
len21 = len(departureTimeoutputt)
for x in range(len21):
c4 = sheet.cell(row=22,
column=5 + x) # distinct destination 30 samples loop
c4.value = departureTimeoutputt[x]
len22 = len(dojoutputt)
for x in range(len22):
c4 = sheet.cell(row=23,
column=5 + x) # distinct destination 30 samples loop
c4.value = dojoutputt[x]
len23 = len(dropPointMandatoryoutputt)
for x in range(len23):
c4 = sheet.cell(row=24,
column=5 + x) # distinct destination 30 samples loop
c4.value = dropPointMandatoryoutputt[x]
len24 = len(droppingTimesoutputt)
for x in range(len24):
c4 = sheet.cell(row=25,
column=5 + x) # distinct destination 30 samples loop
c4.value = droppingTimesoutput[x]
len25 = len(fareDetailsoutputt)
for x in range(len25):
c4 = sheet.cell(row=26,
column=5 + x) # distinct destination 30 samples loop
c4.value = fareDetailsoutputt[x]
len26 = len(faresoutputt)
for x in range(len26):
c4 = sheet.cell(row=27,
column=5 + x) # distinct destination 30 samples loop
c4.value = faresoutputt[x]
len27 = len(flatComApplicableoutputt)
for x in range(len27):
c4 = sheet.cell(row=28,
column=5 + x) # distinct destination 30 samples loop
c4.value = flatComApplicableoutputt[x]
len28 = len(gdsCommissionoutputt)
for x in range(len28):
c4 = sheet.cell(row=29,
column=5 + x) # distinct destination 30 samples loop
c4.value = gdsCommissionoutputt[x]
len29 = len(idProofRequiredoutputt)
for x in range(len29):
c4 = sheet.cell(row=30,
column=5 + x) # distinct destination 30 samples loop
c4.value = idProofRequiredoutputt[x]
len30 = len(liveTrackingAvailableoutputt)
for x in range(len30):
c4 = sheet.cell(row=31,
column=5 + x) # distinct destination 30 samples loop
c4.value = liveTrackingAvailableoutputt[x]
len31 = len(maxSeatsPerTicketoutputt)
for x in range(len31):
c4 = sheet.cell(row=32,
column=5 + x) # distinct destination 30 samples loop
c4.value = maxSeatsPerTicketoutputt[x]
len32 = len(nonACoutputt)
for x in range(len32):
c4 = sheet.cell(row=33,
column=5 + x) # distinct destination 30 samples loop
c4.value = nonACoutputt[x]
len33 = len(operatoroutputt)
for x in range(len33):
c4 = sheet.cell(row=34,
column=5 + x) # distinct destination 30 samples loop
c4.value = operatoroutputt[x]
len34 = len(otgEnabledoutputt)
for x in range(len34):
c4 = sheet.cell(row=35,
column=5 + x) # distinct destination 30 samples loop
c4.value = otgEnabledoutputt[x]
len35 = len(otgPolicyoutputt)
for x in range(len35):
c4 = sheet.cell(row=36,
column=5 + x) # distinct destination 30 samples loop
c4.value = otgPolicyoutputt[x]
len36 = len(partialCancellationAllowedoutputt)
for x in range(len36):
c4 = sheet.cell(row=37,
column=5 + x) # distinct destination 30 samples loop
c4.value = partialCancellationAllowedoutputt[x]
len37 = len(partnerBaseCommissionoutputt)
for x in range(len37):
c4 = sheet.cell(row=38,
column=5 + x) # distinct destination 30 samples loop
c4.value = partnerBaseCommissionoutputt[x]
len38 = len(primaryPaxCancellableoutputt)
for x in range(len38):
c4 = sheet.cell(row=39,
column=5 + x) # distinct destination 30 samples loop
c4.value = primaryPaxCancellableoutputt[x]
len39 = len(routeIdoutputt)
for x in range(len39):
c4 = sheet.cell(row=30,
column=5 + x) # distinct destination 30 samples loop
c4.value = routeIdoutputt[x]
len40 = len(rtcoutputt)
for x in range(len40):
c4 = sheet.cell(row=41,
column=5 + x) # distinct destination 30 samples loop
c4.value = rtcoutputt[x]
len41 = len(seateroutputt)
for x in range(len41):
c4 = sheet.cell(row=42,
column=5 + x) # distinct destination 30 samples loop
c4.value = seateroutputt[x]
len42 = len(selfInventoryoutputt)
for x in range(len42):
c4 = sheet.cell(row=43,
column=5 + x) # distinct destination 30 samples loop
c4.value = selfInventoryoutputt[x]
len43 = len(singleLadiesoutputt)
for x in range(len43):
c4 = sheet.cell(row=44,
column=5 + x) # distinct destination 30 samples loop
c4.value = singleLadiesoutputt[x]
len44 = len(sleeperoutputt)
for x in range(len44):
c4 = sheet.cell(row=45,
column=5 + x) # distinct destination 30 samples loop
c4.value = sleeperoutputt[x]
len45 = len(tatkalTimeoutputt)
for x in range(len45):
c4 = sheet.cell(row=46,
column=5 + x) # distinct destination 30 samples loop
c4.value = tatkalTimeoutputt[x]
len46 = len(vehicleTypeoutputt)
for x in range(len46):
c4 = sheet.cell(row=47,
column=5 + x) # distinct destination 30 samples loop
c4.value = vehicleTypeoutputt[x]
len47 = len(viaRoutesoutputt)
for x in range(len47):
c4 = sheet.cell(row=48,
column=5 + x) # distinct destination 30 samples loop
c4.value = viaRoutesoutputt[x]
len48 = len(zeroCancellationTimeoutputt)
for x in range(len48):
c4 = sheet.cell(row=49,
column=5 + x) # distinct destination 30 samples loop
c4.value = zeroCancellationTimeoutputt[x]
len49 = len(mTicketEnabledoutputt)
for x in range(len49):
c4 = sheet.cell(row=50,
column=5 + x) # distinct destination 30 samples loop
c4.value = mTicketEnabledoutputt[x]
len50 = len(sd_idoutputt)
for x in range(len50):
c4 = sheet.cell(row=51,
column=5 + x) # distinct destination 30 samples loop
c4.value = sd_idoutputt[x]
len51 = len(createDtoutputt)
for x in range(len51):
c4 = sheet.cell(row=52,
column=5 + x) # distinct destination 30 samples loop
c4.value = createDtoutputt[x]
len52 = len(created_dateoutputt)
for x in range(len52):
c4 = sheet.cell(row=53,
column=5 + x) # distinct destination 30 samples loop
c4.value = created_dateoutputt[x]
for x in range(34):
sheet.cell(row=1, column=1 + x).font = Font(size=15)
for x in range(52):
sheet.cell(row=2 + x, column=2).font = Font(size=15)
sheet.column_dimensions['A'].width = 25
sheet.column_dimensions['B'].width = 30
sheet.column_dimensions['C'].width = 25
sheet.column_dimensions['D'].width = 25
sheet.column_dimensions['E'].width = 45
sheet.column_dimensions['F'].width = 30
sheet.column_dimensions['G'].width = 30
sheet.column_dimensions['H'].width = 30
sheet.column_dimensions['I'].width = 30
sheet.column_dimensions['J'].width = 30
sheet.column_dimensions['K'].width = 30
sheet.column_dimensions['L'].width = 30
sheet.column_dimensions['M'].width = 30
sheet.column_dimensions['N'].width = 30
sheet.column_dimensions['O'].width = 30
sheet.column_dimensions['P'].width = 30
sheet.column_dimensions['Q'].width = 30
sheet.column_dimensions['R'].width = 30
sheet.column_dimensions['S'].width = 30
sheet.column_dimensions['T'].width = 30
sheet.column_dimensions['U'].width = 30
sheet.column_dimensions['V'].width = 30
sheet.column_dimensions['W'].width = 30
sheet.column_dimensions['X'].width = 30
sheet.column_dimensions['Y'].width = 30
sheet.column_dimensions['Z'].width = 30
sheet.column_dimensions['AA'].width = 30
sheet.column_dimensions['AB'].width = 30
sheet.column_dimensions['AC'].width = 30
sheet.column_dimensions['AD'].width = 30
sheet.column_dimensions['AE'].width = 30
sheet.column_dimensions['AF'].width = 30
sheet.column_dimensions['AG'].width = 30
sheet.column_dimensions['AH'].width = 30
sheet.column_dimensions['AI'].width = 30
sheet.column_dimensions['AJ'].width = 30
sheet.column_dimensions['AK'].width = 30
numberlist = list(range(1, 53))
for x in range(52): # attribute number insertion loop
c3 = sheet.cell(row=2 + x, column=1)
c3.value = numberlist[x]
sheet['A1'] = "Attribute Number"
sheet['B1'] = "Attribute Name"
sheet['C1'] = "Min"
sheet['D1'] = "Max"
#min max insertion logic
path = driver.desktop + 'ParentMainAnalysis.xlsx'
wb_obj = openpyxl.load_workbook(path)
sheet_obj = wb_obj.active
minlist = []
maxlist = []
count = 0
for x in range(52):
cell_obj = sheet_obj.cell(row=8 + x, column=4)
count = cell_obj.value
if count == '' or count == None:
count = 'BLANK'
minlist.insert(x, count)
count = 0
for x in range(52):
cell_obj = sheet_obj.cell(row=8 + x, column=5)
count = cell_obj.value
maxlist.insert(x, count)
for x in range(52): # min values insertion loop
c3 = sheet.cell(row=2 + x, column=3)
c3.value = minlist[x]
for x in range(52): # max values insertion loop
c3 = sheet.cell(row=2 + x, column=4)
c3.value = maxlist[x]
# print(minlist)
# print(maxlist)
book.save(driver.desktop + 'Sample30.xlsx')
conn.close()
def refbustypeIDcount():
conn = mysql.connector.connect(user=driver.user,
password=driver.password,
host=driver.host,
database=driver.databasename)
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT DISTINCT(busTypeId),busServiceId,routeId,travels,operator FROM available_trips ORDER BY busTypeId ASC'
)
refbustypeID = mycursor.fetchall()
# dfrefbusserviceid
# A column
dfbustypeID = pd.DataFrame(refbustypeID) # main dataframe
dfbustypeID.columns = [
'bustypeid',
'busservicid',
'routeid',
'travels',
'operator',
]
dfbustypeID = dfbustypeID.drop_duplicates(subset='bustypeid',
keep='first',
inplace=False)
dfbustypeID.replace('', 'BLANK', inplace=True)
writer_object = pd.ExcelWriter(driver.desktop + '5_refbustypeID.xlsx')
dfbustypeID['bustypeid'].to_excel(writer_object,
startcol=0,
startrow=1,
sheet_name='Sheet1',
index=False)
# B column
tempdf = driver.dataframe1[(driver.dataframe1.type.isin(['busTypeId']))]
tempdf = tempdf.drop(['type'], axis=1)
tempdfdict = tempdf.set_index('id')['count'].to_dict()
# print(tempdfdict)
finaldf = pd.DataFrame()
listbcol = dfbustypeID['bustypeid'].tolist()
finaldf = pd.DataFrame({
'bustypeid': listbcol,
'Unique_count_bustypeid': listbcol
})
finaldf['Unique_count_bustypeid'].replace(tempdfdict)
finaldf.to_excel(writer_object,
startcol=0,
startrow=2,
sheet_name='Sheet1',
index=False,
header=None)
# C and D column
busServiceIdlist = dfbustypeID['busservicid'].tolist()
# print(travelslist.count('Blank')) #LIST1
# print(len(travelslist))
tempdf = driver.dataframe1[(driver.dataframe1.type.isin(['Busserviceid']))]
tempdf = tempdf.drop(['type'], axis=1)
tempdfdict = tempdf.set_index('id')['count'].to_dict()
# print(tempdfdict)
finaldfdcol = pd.DataFrame(busServiceIdlist, columns=['busservicid '])
finaldfdcol['busservicid_count'] = busServiceIdlist
finaldfdcol = finaldfdcol.replace({"busservicid_count": tempdfdict})
# finaldfdcol=pd.DataFrame(travelslist, columns=['travels_count '])
# print(finaldfdcol)
finaldfdcol.to_excel(writer_object,
startcol=2,
startrow=2,
sheet_name='Sheet1',
index=False,
header=None)
# print(finaldf)
# E & F column routeid
routeidlist = dfbustypeID['routeid'].tolist()
# print(travelslist.count('Blank')) #LIST1
# print(len(travelslist))
tempdf1 = driver.dataframe1[(driver.dataframe1.type.isin(['routeId']))]
tempdf1 = tempdf1.drop(['type'], axis=1)
tempdf1dict = tempdf1.set_index('id')['count'].to_dict()
# print(tempdf1dict)
routeidlist = list(map(str, routeidlist)) # casting list to str
finaldfedcol = pd.DataFrame(routeidlist, columns=['routeId '])
# print(finaldfdcol)
finaldfedcol['routeId_count'] = routeidlist
# print(finaldfedcol)
finaldfedcol = finaldfedcol.replace({"routeId_count": tempdf1dict})
# finaldfdcol=pd.DataFrame(travelslist, columns=['travels_count '])
# print(finaldfdcol)
finaldfedcol.to_excel(writer_object,
startcol=4,
startrow=2,
sheet_name='Sheet1',
index=False,
header=None)
# G and H column
travellist = dfbustypeID['travels'].tolist()
# print(travelslist.count('Blank')) #LIST1
# print(len(travelslist))
tempdf2 = driver.dataframe1[(driver.dataframe1.type.isin(['travels']))]
tempdf2 = tempdf2.drop(['type'], axis=1)
tempdf2dict = tempdf2.set_index('id')['count'].to_dict()
# print(tempdfdict)
finaldf2dcol = pd.DataFrame(travellist, columns=['travels '])
finaldf2dcol['travels_count'] = travellist
finaldf2dcol = finaldf2dcol.replace({"travels_count": tempdf2dict})
# finaldfdcol=pd.DataFrame(travelslist, columns=['travels_count '])
# print(finaldfdcol)
finaldf2dcol.to_excel(writer_object,
startcol=6,
startrow=2,
sheet_name='Sheet1',
index=False,
header=None)
# I and J column
operatorlist = dfbustypeID['operator'].tolist()
# print(travelslist.count('Blank')) #LIST1
# print(len(travelslist))
tempdf3 = driver.dataframe1[(driver.dataframe1.type.isin(['Operator']))]
tempdf3 = tempdf3.drop(['type'], axis=1)
tempdf3dict = tempdf3.set_index('id')['count'].to_dict()
# print(tempdfdict)
finaldf3dcol = pd.DataFrame(operatorlist, columns=['operator '])
finaldf3dcol['operator_count'] = operatorlist
finaldf3dcol['operator_count'].replace(tempdf3dict)
# finaldfdcol=pd.DataFrame(travelslist, columns=['travels_count '])
# print(finaldfdcol)
finaldf3dcol.to_excel(writer_object,
startcol=8,
startrow=2,
sheet_name='Sheet1',
index=False,
header=None)
workbook_object = writer_object.book
worksheet_object = writer_object.sheets['Sheet1']
worksheet_object.set_column('A:B', 30)
worksheet_object.set_column('C:D', 30)
worksheet_object.set_column('E:F', 30)
worksheet_object.set_column('G:H', 30)
worksheet_object.set_column('I:J', 30)
worksheet_object.write('A2', "bustypeID ")
worksheet_object.write('B2', "Unique_bustypeID ")
worksheet_object.write('C2', "busserviceid ")
worksheet_object.write('D2', "count_busserviceid ")
worksheet_object.write('E2', " routeId")
worksheet_object.write('F2', "count_routeId ")
worksheet_object.write('G2', " travels")
worksheet_object.write('H2', "count_travels ")
worksheet_object.write('I2', " operator")
worksheet_object.write('J2', "count_operator ")
worksheet_object.write('A1',
"Total_rows_all _col:" + str(dfbustypeID.shape[0]))
writer_object.save()
def Sourceerror(): # this function exports source,sourcename error file
conn = mysql.connector.connect(user=driver.user,
password=driver.password,
host=driver.host,
database=driver.databasename)
book = Workbook()
sheet1 = book.active
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT distinct source,source_name FROM available_trips ')
sbothval = mycursor.fetchall()
# dictionary conversion & dataframe assignment
sbothvall = dict(sbothval)
pd.DataFrame(sbothvall.items())
svaldf = pd.DataFrame(sbothvall.items(), columns=['source', 'source_name'])
# case1
# captures repeat value according to 'source name' column but print both columns
duplicate_bool = svaldf.duplicated(subset=['source_name'], keep=False)
sduplicate = svaldf.loc[duplicate_bool == True]
sduplicate1 = sduplicate
#print(sduplicate1.shape[0]) # shape returns number of rows
# case1
# captures repeat value according to 'source' column but print both columns
duplicate_bool = svaldf.duplicated(subset=['source'], keep=False)
sduplicate = svaldf.loc[duplicate_bool == True]
sduplicate2 = sduplicate
# print(sduplicate2.shape[0])
if sduplicate1.shape[0]:
sduplicate = sduplicate1
else:
sduplicate = sduplicate2
# insertion loop of values to excel file
s = sduplicate.get(["source"])
slist = [] # empty list
for row in s.itertuples():
mylist = [row.source]
slist.append(mylist)
sfval = [item for x in zip_longest(*slist) for item in x if item != -55]
sname = sduplicate.get(["source_name"])
snamelist = []
for row in sname.itertuples():
mylist = [row.source_name]
snamelist.append(mylist)
snamefval = [
item for x in zip_longest(*snamelist) for item in x if item != -55
]
# writeback code to excel file
sheet1.row_dimensions[1].height = 25
sheet1['C1'] = "ERROR CASE:THIS SOURCENAMES DOESNOT HAVE UNIQUE 'SOURCEid'"
sheet1.column_dimensions['C'].width = 85
sheet1.column_dimensions['A'].width = 25
sheet1.column_dimensions['B'].width = 30
sheet1.cell(row=2, column=1).font = Font(size=15)
sheet1.cell(row=2, column=2).font = Font(size=15)
sheet1.cell(row=1, column=3).font = Font(size=15)
sheet1['A2'] = "source"
sheet1['B2'] = "source_name"
for x in range(len(sfval)):
c1 = sheet1.cell(row=x + 3, column=1) # source insertion loop
c1.value = sfval[x]
for x in range(len(snamefval)):
c1 = sheet1.cell(row=x + 3, column=2) # source name insertion loop
c1.value = snamefval[x]
book.save(driver.desktop + 'error\\ERRORsourceandsourcename.xlsx')
conn.close()
def Combinedssociatedcounts2():
conn = mysql.connector.connect(user=driver.user,
password=driver.password,
host=driver.host,
database=driver.databasename)
dict1 = {}
dict2 = {}
dict3 = {}
dict4 = {}
#for busservicid counts occurence
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT busServiceId FROM available_trips ORDER by busServiceId ')
myresult65 = mycursor.fetchall(
) # sample12)avlWindowSeats 30 collect from table
busserviceidoutput = [
item for x in zip_longest(*myresult65) for item in x if item != -55
]
#print(busserviceidoutput1)
for x in range(len(busserviceidoutput)):
if busserviceidoutput[x] == '':
busserviceidoutput[x] = "BLANK"
#busserviceidoutput=list(unique_everseen(busserviceidoutput)) #removing duplicates
#print(busserviceidoutput)
c = Counter(busserviceidoutput)
# print(c) #counting ocurrence
finaldict = dict(c)
dict1 = finaldict
#print(finaldict)
pd.DataFrame(finaldict.items())
busservicedf = pd.DataFrame(
finaldict.items(),
columns=['busServiceId', 'busServiceId_Unique_Count'])
writer_object = pd.ExcelWriter(driver.desktop +
'Combinedssociatedcounts2.xlsx')
busservicedf.to_excel(writer_object,
startcol=0,
startrow=1,
sheet_name='Sheet1')
#worksheet_object.set_column('F:G', 30)
# for travels counts occurence
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT travels FROM available_trips ORDER by travels ')
myresult65 = mycursor.fetchall(
) # sample12)avlWindowSeats 30 collect from table
travelsoutput = [
item for x in zip_longest(*myresult65) for item in x if item != -55
]
# print(busserviceidoutput1)
for x in range(len(travelsoutput)):
if travelsoutput[x] == '':
travelsoutput[x] = "BLANK"
# busserviceidoutput=list(unique_everseen(busserviceidoutput)) #removing duplicates
c = Counter(travelsoutput)
#print(c) # counting ocurrence
finaldict = dict(c)
dict2 = finaldict
pd.DataFrame(finaldict.items())
travelsdf = pd.DataFrame(finaldict.items(),
columns=['travels', 'travels_Unique_Count'])
travelsdf.to_excel(writer_object,
startcol=4,
startrow=1,
sheet_name='Sheet1')
# for routeId counts occurence
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT routeId FROM available_trips ORDER by routeId ')
myresult65 = mycursor.fetchall(
) # sample12)avlWindowSeats 30 collect from table
routeIdoutput = [
item for x in zip_longest(*myresult65) for item in x if item != -55
]
# print(busserviceidoutput1)
for x in range(len(routeIdoutput)):
if routeIdoutput[x] == '':
routeIdoutput[x] = "BLANK"
# busserviceidoutput=list(unique_everseen(busserviceidoutput)) #removing duplicates
c = Counter(routeIdoutput)
#print(c) # counting ocurrence
finaldict = dict(c)
dict3 = finaldict
pd.DataFrame(finaldict.items())
routeIddf = pd.DataFrame(finaldict.items(),
columns=[' routeId ', 'routeId _Unique_Count'])
routeIddf.to_excel(writer_object,
startcol=8,
startrow=1,
sheet_name='Sheet1')
# for operator counts occurence
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT operator FROM available_trips ORDER by operator ')
myresult65 = mycursor.fetchall()
operatoroutput = [
item for x in zip_longest(*myresult65) for item in x if item != -55
]
# print(busserviceidoutput1)
for x in range(len(operatoroutput)):
if operatoroutput[x] == '':
operatoroutput[x] = "BLANK"
# busserviceidoutput=list(unique_eveBlankrseen(busserviceidoutput)) #removing duplicates
c = Counter(operatoroutput)
#print(c) # counting ocurrence
finaldict = dict(c)
dict4 = finaldict
pd.DataFrame(finaldict.items())
operatordf = pd.DataFrame(finaldict.items(),
columns=[' operator ', 'operator _Unique_Count'])
operatordf.to_excel(writer_object,
startcol=12,
startrow=1,
sheet_name='Sheet1')
# for bustypeID counts occurence
mycursor = MySQLCursor(conn)
mycursor.execute(
'SELECT busTypeId FROM available_trips ORDER by busTypeId ')
myresult65 = mycursor.fetchall()
busTypeIdoutput = [
item for x in zip_longest(*myresult65) for item in x if item != -55
]
# print(busserviceidoutput1)
for x in range(len(busTypeIdoutput)):
if busTypeIdoutput[x] == '':
busTypeIdoutput[x] = "BLANK"
# busserviceidoutput=list(unique_everseen(busserviceidoutput)) #removing duplicates
c = Counter(busTypeIdoutput)
#print(c) # counting ocurrence
finaldict = dict(c)
dict5 = finaldict
pd.DataFrame(finaldict.items())
busTypeIddf = pd.DataFrame(
finaldict.items(),
columns=[' busTypeId ', ' busTypeId _Unique_Count'])
busTypeIddf.to_excel(writer_object,
startcol=16,
startrow=1,
sheet_name='Sheet1')
worksheet_object = writer_object.sheets['Sheet1']
worksheet_object.set_column('A:C', 30)
worksheet_object.set_column('E:G', 30)
worksheet_object.set_column('I:K', 30)
worksheet_object.set_column('M:O', 30)
worksheet_object.set_column('Q:S', 30)
worksheet_object.write('B1', "Total rows of Busserviceid ")
worksheet_object.write('C1', len(busservicedf.axes[0]))
worksheet_object.write('F1', "Total rows of travels")
worksheet_object.write('G1', len(travelsdf.axes[0]))
worksheet_object.write('J1', "Total rows of routeid ")
worksheet_object.write('K1', len(routeIddf.axes[0]))
worksheet_object.write('N1', "Total rows of operator ")
worksheet_object.write('O1', len(operatordf.axes[0]))
worksheet_object.write('R1', "Total rows of busTypeId ")
worksheet_object.write('S1', len(busTypeIddf.axes[0]))
writer_object.save()
# print 4 attribs as per types sending dataframe to another function
###################for busserviceid
s1 = list(dict1.keys())
if (s1[0] == ''):
s1[0] = 'BLANK'
s2 = list(dict1.values())
data = {'type': 'Busserviceid', 'id': s1, 'count': s2}
dff1 = pd.DataFrame(data)
###############################for travels
s1 = list(dict2.keys())
if (s1[0] == ''):
s1[0] = 'BLANK'
s2 = list(dict2.values())
data = {'type': 'travels', 'id': s1, 'count': s2}
dff2 = pd.DataFrame(data)
######################## for routeId
s1 = list(dict3.keys())
# print(s1)
for x in range(len(s1)):
s1[x] = str(s1[x])
# print(s1)
if (s1[0] == ''):
s1[0] = 'BLANK'
s2 = list(dict3.values())
data = {'type': 'routeId', 'id': s1, 'count': s2}
dff3 = pd.DataFrame(data)
################for operator
s1 = list(dict4.keys())
if (s1[0] == ''):
s1[0] = 'BLANK'
s2 = list(dict4.values())
data = {'type': 'Operator', 'id': s1, 'count': s2}
dff4 = pd.DataFrame(data)
################for bustypeid
s1 = list(dict5.keys())
if (s1[0] == ''):
s1[0] = 'BLANK'
s2 = list(dict5.values())
data = {'type': 'busTypeId', 'id': s1, 'count': s2}
dff5 = pd.DataFrame(data)
dff1 = dff1.append(dff2, ignore_index=True)
dff1 = dff1.append(dff3, ignore_index=True)
dff1 = dff1.append(dff4, ignore_index=True)
dff1 = dff1.append(dff5, ignore_index=True)
conn.close()
# print(dff1)
return dff1
def Scores():
global loginUsers
username = loginUsers[0][0]
sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username='******',
ssh_password='******',
remote_bind_address=(
'Gengruijie.mysql.pythonanywhere-services.com',
3306)) as tunnel:
connection = mysql.connector.connect(
user='******',
password='******',
host='127.0.0.1',
port=tunnel.local_bind_port,
database='Gengruijie$AutoGrading',
)
# Do stuff
query = "SELECT level, score from main where name = \"" + username + "\""
# print(query)
# cur = connection.cursor(buffered=True)
cursor = MySQLCursor(connection)
cursor.execute(query)
data1 = cursor.fetchall()
# print(username)
if data1[0][0] == "student":
return render_template('Scores.html', name=username, score=data1[0][1])
else:
sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username='******',
ssh_password='******',
remote_bind_address=(
'Gengruijie.mysql.pythonanywhere-services.com',
3306)) as tunnel:
connection = mysql.connector.connect(
user='******',
password='******',
host='127.0.0.1',
port=tunnel.local_bind_port,
database='Gengruijie$AutoGrading',
)
# Do stuff
query = "SELECT name, score from main"
# print(query)
# cur = connection.cursor(buffered=True)
cursor = MySQLCursor(connection)
cursor.execute(query)
data = cursor.fetchall()
# print(data)
result = []
for element in data:
if element[1] != "":
if element[0] == "Turner":
continue
result.append((element[0], element[1]))
return render_template('ScoresProfessor.html',
name=username,
items=result)