def check_db_valid(): """ db -> local. Make sure all the records in db still exits locally. :return: None """ with UseDatabase(config) as cursor: _SQL = """SELECT Dir, Title FROM VIDEOS""" cursor.execute(_SQL) result = cursor.fetchall() # list of tuple existing_files_gc = ((item[0], item[1]) for item in result) # traverse the dir, delete record if not exist. for parent_path, title in existing_files_gc: if not (Path(parent_path) / title).exists(): _SQL = """DELETE FROM VIDEOS WHERE Dir=(%s) AND Title=(%s)""" cursor.execute(_SQL, (parent_path, title))
def view_the_log() -> 'html': with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """select phrase, letters, ip, browser_string, results from log""" cursor.execute(_SQL) contents = cursor.fetchall() titles = ('Phrase', 'Letters', 'Remote addr', 'User_agent', 'Results') return render_template( 'viewlog.html', the_title='View Log', the_row_titles=titles, the_data=contents, )
def log_request(req: 'flask request', res: str) -> None: """Funkcja logująca do bazy danych mysql""" with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """INSERT INTO log (pharse, letters, ip , browser_string, results) VALUES (%s, %s, %s, %s, %s)""" cursor.execute(_SQL, ( req.form['pharse'], req.form['letters'], req.remote_addr, req.user_agent.browser, res, ))
def payment_schedule() -> 'html': with UseDatabase(app.config['dbconfig']) as cursor: cursor.callproc('test_7_1', (a, b, c, i, n)) # _SQL = """select * from payment_schedule """ # cursor.execute(_SQL) # contents = cursor.fetchall() for result in cursor.stored_results(): contents = result.fetchall() titles = ("a", "b", "c", "i", "n") return render_template( 'viewlog.html', the_title='График платежей', the_row_titles=titles, the_data=contents, )
def log_request(req: "flask_request", results: str) -> None: """Logs details of the web request and the results in a SQL DB""" sleep(15) with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """INSERT INTO log (phrase, letters, ip, browser_string, results) values (%s, %s, %s, %s, %s)""" cursor.execute(_SQL, ( req.form['phrase'], req.form['letters'], req.remote_addr, req.user_agent.browser, results, ))
def log_request(req: 'flask_request', res: str) -> None: """Loguje szczegóły żądania sieciowego oraz wyniki.""" # raise Exception("Właśnie stało się coś okropnego.") with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """insert into log (phrase, letters, ip, browser_string, results) values (%s, %s, %s, %s, %s)""" cursor.execute(_SQL, (req.form['phrase'], req.form['letters'], req.remote_addr, req.user_agent.browser, res, ))
def find(self, element_id: int) -> 'CommonEntity': """Find element by ID""" sql = "select {} from {} where {} = {:d}".format( ', '.join(self._entity.to_dict().keys()), self._entity.get_table_name(), self._entity.get_id_name(), element_id) with UseDatabase(dbconfig) as cursor: cursor.execute(sql) elements = cursor.fetchone() if elements is not None: self._entity.prepare_from_dict(elements) return self._entity
def log_request(req: 'flask_request', res: str) -> None: """log details of the web request and the results""" sleep(15) with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """insert into log (phrase, letters, ip, browser_string, results) values (%s,%s,%s,%s,%s)""" cursor.execute(_SQL, ( req.form['phrase'], req.form['letters'], req.user_agent.browser, res, ))
def log_request(req: 'flask_request', res: str): sleep(15) with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """insert into log (phrase, letters, ip, browser_string, results) values (%s, %s, %s, %s, %s) """ cursor.execute(_SQL, ( req.form['phrase'], req.form['letters'], req.remote_addr, req.user_agent.browser, res, ))
def log_request(req: 'flask_request', res: str) -> None: """Log details of the web request and the results.""" # raise Exception("Something awful just happened.") sleep(15) # This makes log_request really slow... with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """insert into log (phrase, letters, ip, browser_string, results) values (%s, %s, %s, %s, %s)""" cursor.execute(_SQL,(req.form['phrase'], req.form['letters'], req.remote_addr, req.user_agent.browser, res, ))
def log_request(req: 'flask_request', res: str) -> None: # dbconfig = { 'host': '127.0.0.1', # 'user': '******', # 'password': '******', # 'database': 'vsearhlogDB', } # conn = mysql.connector.connect(**dbconfig) # cursor = conn.cursor() # sleep is just for testing Thread sleep(15) #raise ConnectionError() try: with UseDatabase(app.config['dbconfig']) as cursor: _SQL = '''insert into log (phrase, letters, ip, browser_string, results) values (%s, %s, %s, %s, %s)''' cursor.execute(_SQL, (req.form['phrase'], req.form['letters'], req.remote_addr, req.user_agent.browser, res, )) except ConnectionError as err: print('Is your DB switched on?:', str(err)) except CredentialError as err: print('User-id/Password issues. Error:', str(err)) except SQLError as err: print('Is your query correct? Error:', str(err)) except Exception as err: print('Something went wrong: ', str(err)) return 'Error' # conn.commit() # _SQL = '''select * from log''' # cursor.close() # conn.close() #with open('vsearch.log','a') as log: """ this is how to print 'flask request' attributes """
def view_all() -> 'html': with UseDatabase(app.config['mydbconf']) as cursor: _SQL = "select code, " \ "last_name, " \ "first_name, " \ "patronymic, " \ "cathedra.name_of_cathedra " \ "from tutor left join cathedra using (code) order by code;" cursor.execute(_SQL) contents = cursor.fetchall() titles = ('Код кафедры', 'Фамилия преподавателя', 'Имя преподавателя', "Отчество преподавателя", 'Название кафедры') return render_template('result.html', the_title='Все преподаватели', the_row_titles=titles, the_data=contents)
def get_bmi_table(): try: with UseDatabase(app.config['dbconfig']) as cursor: _SQL = "SELECT Feet, Inches, Pounds, Result, Timestamp FROM BmiLog" cursor.execute(_SQL) contents = cursor.fetchall() titles = {'Feet', 'Inches', 'Pounds', 'Result', 'Timestamp'} return render_template( 'log.html', the_title='BMI Log', the_row_titles=titles, the_data=contents, ) except Exception as err: print('Something went wrong: ' + str(err)) return 'Error'
def get_distance_table(): try: with UseDatabase(app.config['dbconfig']) as cursor: _SQL = "SELECT x1, x2, y1, y2, Result, Timestamp FROM DistanceLog" cursor.execute(_SQL) contents = cursor.fetchall() titles = {'X1', 'X2', 'Y1', 'Y2', 'Result', 'Timestamp'} return render_template( 'log.html', the_title='Distance Log', the_row_titles=titles, the_data=contents, ) except Exception as err: print('Something went wrong: ' + str(err)) return 'Error'
def log_request( req: 'flask_request', res: str ) -> None: #log_request nested inside the do_search function sleep(15) #This makes log_request really slow... with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """insert into log (phrase, letters, ip, browser_string, results) values (%s, %s, %s, %s, %s)""" cursor.execute(_SQL, ( req.form['phrase'], req.form['letters'], req.remote_addr, req.user_agent.browser, res, ))
def show_book_reviews() -> 'html': with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """SELECT author, book, rating FROM reviews""" cursor.execute(_SQL) contents = cursor.fetchall() titles = ('Author', 'Book', 'Rating') return render_template( 'bookReviews.html', the_title='View Reviews', the_row_titles=titles, the_data=contents, )
def log_request(req, res): """Log details of the web request and the results.""" time.sleep(15) # 连接数据库 with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """insert into log (phrase, letters, ip, browser_string, results) values (%s, %s, %s, %s, %s)""" cursor.execute(_SQL, ( req.form['phrase'], req.form['letters'], req.remote_addr, req.user_agent.browser, res, ))
def get_stored_job_ids() -> set: dbconfig = { 'host': '127.0.0.1', 'user': '******', 'password': '******', 'database': 'job_board_scraping', } id_set = set() print('Getting job ids from database....', end='') with UseDatabase(dbconfig) as cursor: _SQL = """select indeed_id FROM job_board_scraping.raw_data""" cursor.execute(_SQL) #print(cursor.fetchall()) for id in cursor.fetchall(): id_set.add(str(id).translate({ord(c): None for c in '({}),\''})) return id_set
def user_profile() -> 'html': with UseDatabase(app.config['database']) as cursor: query = """SELECT first_name, last_name, login, password FROM users WHERE login = '******'""".format( app.config['current_user']['login']) cursor.execute(query) response = cursor.fetchall() first_name = response[0][0] last_name = response[0][1] login = response[0][2] password = response[0][3] return render_template('user_profile.html', login=login, first_name=first_name, last_name=last_name, password=password)
def view_the_cathedra() -> 'html': with UseDatabase(app.config['mydbconf']) as cursor: _SQL = """SELECT code, name_of_cathedra, dir_last_name, dir_first_name, dir_patronymic_name, priznak_vypuskayushey FROM cathedra ORDER BY code""" cursor.execute(_SQL) contents = cursor.fetchall() titles = ('Код', 'Название кафедры', 'Фамилия', 'Имя', 'Отчество', 'Признак выпускающей') return render_template('result.html', the_title='Список кафедр отсортированный по коду:', the_row_titles=titles, the_data=contents)
def log_bmi(feet, inches, pounds, result): dbconfig = { 'host': '127.0.0.1', 'user': '******', 'password': '******', 'database': 'PPA2DB', } with UseDatabase(dbconfig) as cursor: _SQL = "INSERT INTO BmiLog (Feet, Inches, Pounds, Result, Timestamp) VALUES (%s, %s, %s, %s, %s);" cursor.execute(_SQL, ( str(feet), str(inches), str(pounds), str(result), str(datetime.datetime.now()), ))
def log_request(req:'flask_request',res : str) ->None: dbconfig = {'host': '127.0.0.1', 'user' : 'vsearch', 'password' : 'KevinLee0!', 'database' : 'vsearchDB',} with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """insert into log (phrase, letters, ip, browser_string, results) values (%s, %s, %s, %s, %s)""" cursor.execute(_SQL, (req.form['phrase'], req.form['letters'], req.remote_addr, req.user_agent.browser, res,))
def view_the_log() -> 'html': """Display the contents of the log table from DB as a HTML table.""" try: with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """select ts, phrase, letters, ip, browser_string, results from log""" cursor.execute(_SQL) result = cursor.fetchall() _SQL = """select count(*) from log""" cursor.execute(_SQL) allreq = cursor.fetchall() _SQL = """select count(letters) as 'count', letters from log group by letters order by count desc limit 1""" cursor.execute(_SQL) freqletters = cursor.fetchall() _SQL = """select browser_string, count(browser_string) as 'count' from log group by browser_string order by count desc limit 1""" cursor.execute(_SQL) freqbrowser = cursor.fetchall() ips = select_SQL(cursor, """select distinct ip from log""") #cursor.execute(_SQL) #ips = cursor.fetchall() titles = ('TimeStamp', 'Phrase', 'Letters', 'Remote Address', 'User Agent', 'Results') return render_template( 'viewlog.html', the_title='View Log', the_row_titles=titles, the_data=result, stat_requests=allreq, stat_freqletters=freqletters, stat_freqbrowser=freqbrowser, stat_ip=ips, ) except ConnectionError as err: print('Is your DB on? Error:', str(err)) except CredentialsError as err: print('User-id/Password issues. Error:', str(err)) except SQLError as err: print('Is your query correct? Error:', str(err)) return 'Error'
def view_the_loq() -> 'html': with UseDatabase(app.config['dbconfig']) as cursor: cursor.callproc('test_7') _SQL = """select * from payment_schedule """ cursor.execute(_SQL) contents = cursor.fetchall() # for result in cursor.stored_results(): # contents = result.fetchall() titles = ("ID", "Дата платежа", "Сумма платежа, руб.", "Платеж по основному долгу, руб.", "Платеж по процентам, руб.", "Остаток долга, руб.") return render_template( 'viewlog.html', the_title='График платежей', the_row_titles=titles, the_data=contents, )
def log_distance(x1, x2, y1, y2, result): dbconfig = { 'host': '127.0.0.1', 'user': '******', 'password': '******', 'database': 'PPA2DB', } with UseDatabase(dbconfig) as cursor: _SQL = "INSERT INTO DistanceLog (X1, X2, Y1, Y2, Result, Timestamp) VALUES (%s, %s, %s, %s, %s);" cursor.execute(_SQL, ( str(x1), str(x2), str(y1), str(y2), str(result), str(datetime.datetime.now()), ))
def log_request(req: 'flask_request', res: str) -> None: """Log details of the Web request and the results """ dbConfig = { 'host': 'localhost', 'user': '******', 'password': '******', 'database': 'vsearchlogDB' } with UseDatabase(dbConfig) as cursor: _SQL = """insert into log (phrase, letters, ip, browser_string, results) values(%s, %s, %s, %s, %s) """ cursor.execute(_SQL, (req.form['phrase'], req.form['letters'], req.remote_addr, req.user_agent.browser, res))
def log(req: 'flask_request', res: str) -> None: app.config['dbconfig'] = { 'host': '127.0.0.1', 'user': '******', 'password': '******', 'database': 'vsearchlogDB' } with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """insert into log (phrase, letters, ip, browser_info, results) values (%s, %s, %s, %s, %s)""" # for *.browser, we're only saving the name of the browser cursor.execute(_SQL, (req.form['phrase'], req.form['letters'], req.remote_addr, req.user_agent.browser, res, ))
def view_log() -> 'html': lines = [] # with open('versearch.log') as logstream: # for line in logstream: # lines.append([]) # for item in line.split('|'): # lines[-1].append(escape(item)) with UseDatabase(app.config['dbconfig']) as cursor: select_stmt = """SELECT phrase,letters, ip,browser_string, results FROM log""" cursor.execute(select_stmt) result = cursor.fetchall() #T titles = ('phrase', 'letters', 'ip', 'browser_string', 'Result') return render_template('viewlog.html', the_title='View Log', the_row_titles=titles, the_data=result)
def view_the_log() -> 'html': try: with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """select phrase, letters, ip, browser_string, results from log""" cursor.execute(_SQL) contents = cursor.fetchall() titles = ('Phrase', 'Letters', 'Remote_addr', 'User_agent', 'Results') return render_template('viewlog.html', the_title='View Log', the_row_titles=titles, the_data=contents) except ConnectionError as err: print('Is your databae switched on? Error:', str(err)) except Exception as err: print('Something went wrong:', str(err))
def view_the_log() -> 'html': with UseDatabase(app.config['dbconfig']) as cursor: _SQL = """select phrase, letters, ip, browser_string, results from log""" cursor.execute(_SQL) contents = cursor.fetchall() titles = ('Fraza', 'Litery', 'Adres klienta', 'Agent użytkownika', 'Wyniki') return render_template('viewlog.html', the_title='Widok logu', the_row_titles=titles, the_data=contents,)