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,)
