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)