def view_the_log() -> 'html':
""" 文字検索処理の有用な内容をHTMLテーブルで表示する """
# contents = []
# with open('vsearch1.log') as log:
# for line in log:
# contents.append([])
# for item in line.split('|'):
# contents[-1].append(escape(item))
try:
with UseDataBase(app.config['dbconfig']) as cursor:
_SQL = """select ts, phrase, letters, ip, browser_string, results from log"""
cursor.execute(_SQL)
contents = cursor.fetchall()
titles = ['検索日時', 'フレーズ', '検索文字', 'リモートアドレス', 'ユーザエージェント', '結果']
return render_template('viewlog.html',
the_title='ログの閲覧',
the_row_titles=titles,
the_data=contents)
except CredentialsError as err:
print('※※※※※ ユーザID/パスワード不正。:', str(err))
except ConnectionError as err:
print('※※※※※ データベースに接続できません。データベースは起動していますか？:', str(err))
except SQLError as err:
print('※※※※※ SQLクエリの実行に失敗しました。', str(err))
except Exception as err:
print('※※※※※ 想定外のエラーが発生しました。:', str(err))
return 'Error'
def view_the_log() -> 'html':
try:
"""Display the contents of the log file as a HTML table."""
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 DBConnectionError as err:
print('Is your database swithed 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))
except Exception as err:
print('Something went wrong:', str(err))
return "Error"
def show_editregion():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """select * from firm_region where id=%(my_id)s"""
cursor.execute(_SQL, {'my_id': request.form['id_edit_del']})
contents = cursor.fetchall()
return render_template('editreg.html',
id_reg=contents[0][0],
reg_name=contents[0][1])
def show_editedregion():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """update firm_region set region_name=%(my_name)s where id=%(my_id)s"""
cursor.execute(_SQL, {
'my_id': request.form['id_reg'],
'my_name': request.form['name_reg']
})
return regions()
def show_delfirmservice():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """delete from firm_service where id_firm=%(my_id_firm)s and id_service=%(my_id_service)s"""
cursor.execute(
_SQL, {
'my_id_firm': request.form['id_edit_del_firm'],
'my_id_service': request.form['id_edit_del_service']
})
return firmservice()
def show_editservice():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """select * from services where id=%(my_id)s"""
cursor.execute(_SQL, {'my_id': request.form['id_edit_del']})
contents = cursor.fetchall()
return render_template(
'editservice.html',
id_service=contents[0][0],
name_service=contents[0][1],
)
def log_request(req: 'flask_request', res: str) -> None:
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 addservices():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """insert into services
(id, service_name)
values
(%s, %s)"""
cursor.execute(_SQL, (
request.form['id_service'],
request.form['service_name'],
))
return services()
def log_request(req: 'flask_request', res: str) -> None:
dbconfig = {
'host': '127.0.0.1',
'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 show_editedservice():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """update services
set service_name=%(my_name)s
where id=%(my_id)s"""
cursor.execute(
_SQL, {
'my_id': request.form['id_service'],
'my_name': request.form['name_service']
})
return services()
def addfirmservice():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """insert into firm_service
(id_firm, id_service)
values
(%s, %s)"""
cursor.execute(_SQL, (
request.form['id_firm'],
request.form['id_service'],
))
return firmservice()
def addregion():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """insert into firm_region
(id, region_name)
values
(%s, %s)"""
cursor.execute(_SQL, (
request.form['id_reg'],
request.form['name_reg'],
))
return regions()
def log_request(req: 'flask_request', res: str) -> None: # recebe request / search4letters
sleep(15) # atrasa o código pra não gerar atrasos
with UseDataBase(app.config['dbconfig']) as cursor: # utiliza o interpretador do MySQL
_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 addfirm():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """insert into firm
(id, id_region, firm_name, firm_info)
values
(%s, %s, %s, %s)"""
cursor.execute(_SQL, (
request.form['id_firm'],
request.form['id_region'],
request.form['firm_name'],
request.form['firm_info'],
))
return firms()
def show_editedfirm():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """update firm
set id_region=%(my_id_reg)s, firm_name=%(my_name)s, firm_info=%(my_info)s
where id=%(my_id)s"""
cursor.execute(
_SQL, {
'my_id': request.form['id'],
'my_id_reg': request.form['id_reg'],
'my_info': request.form['info_firm'],
'my_name': request.form['name_firm']
})
return firms()
def view_the_log():
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_user_name='user_name',
the_title='View Log',
the_row_titles=titles,
the_data=contents,
)
def viewlog() -> 'html':
title = 'View Log'
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=title,
the_row_titles=titles,
the_data=contents,
)
def log_request(req: 'flask_request', res: str) -> None:
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('Database might be off')
except CredentialsError as err:
print('Wrong creds', str(err))
except SQLError as err:
print('no such query', str(err))
except Exception as err:
print('smth went wrong: ', str(err))
def view_the_log() -> 'html':
contents = []
try:
with UseDataBase(app.config['dbconfig']) as cursor:
_SQL = """select phrase, letters, ip, browser_string, results from log"""
cursor.execute(_SQL)
contents = cursor.fetchall()
except Exception as err:
print('Logging failed with this error:', str(err))
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_request2(req: 'flask_request', res: str) -> None:
""" Webリクエストの詳細とレスポンスをロギングする """
sleep(15) # スレッドの効果を確認するためのスリープ
try:
with UseDataBase(app.config['dbconfig']
) as cursor: # 自作のコンテキストマネージャ(UseDataBase)を使用する
_SQL = """insert into log2
(phrase, words, ip, browser_string, results)
values
(%s, %s, %s, %s, %s)"""
cursor.execute(_SQL, (
req.form['phrase'],
req.form['words'],
req.remote_addr,
req.user_agent.browser,
res,
))
except Exception as err:
print('※※※※※ ロギング処理でエラーが発生しました。', str(err))
def viewlog_page() -> '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_title=titles,
the_data=contents, )
except ConnectionErro as err:
print('Sua database mudou? Erro:', str(err))
except CredentialsError as err:
print('User-id/Password estão incorretos, Error:', str(err))
except SQLError as err:
print('Algo deu errado:', str(err))
except Exception as erro:
print('Algo deu errado:', str(erro))
return 'Error'
def view_the_log2() -> 'html':
""" 単語検索＆カウント処理の有用な内容をHTMLテーブルで表示する """
try:
with UseDataBase(app.config['dbconfig']) as cursor:
_SQL = """select ts, phrase, words, ip, browser_string, results from log2"""
cursor.execute(_SQL)
contents = cursor.fetchall()
titles = ['検索日時', 'フレーズ', '検索単語', 'リモートアドレス', 'ユーザエージェント', '結果']
return render_template('viewlog.html',
the_title='ログの閲覧',
the_row_titles=titles,
the_data=contents)
except CredentialsError as err:
print('※※※※※ ユーザID/パスワード不正。:', str(err))
except ConnectionError as err:
print('※※※※※ データベースに接続できません。データベースは起動していますか？:', str(err))
except SQLError as err:
print('※※※※※ SQLクエリの実行に失敗しました。', str(err))
except Exception as err:
print('※※※※※ 想定外のエラーが発生しました。:', str(err))
return 'Error'
def log_request(req: 'flask_request', res: str) -> None:
""" Webリクエストの詳細とレスポンスをロギングする """
# with open('vsearch1.log', 'a') as log:
# print(req.form, req.remote_addr, req.user_agent, res, file=log, sep='|')
sleep(15) # スレッドの効果を確認するためのスリープ
try:
with UseDataBase(app.config['dbconfig']
) as cursor: # 自作のコンテキストマネージャ(UseDataBase)を使用する
_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 Exception as err:
print('※※※※※ ロギング処理でエラーが発生しました。', str(err))
def view_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',
row_titles=titles,
the_data=contents,
)
except ConnectionError as err:
print('Database might be off')
except CredentialsError as err:
print('Wrong creds', str(err))
except SQLError as err:
print('no such query', str(err))
except Exception as err:
print('smth went wrong: ', str(err))
return 'error'
def searched():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL1 = """select id
from firm_region
where region_name=%(reg_name)s"""
cursor.execute(_SQL1, {'reg_name': request.form['reg_name']})
contents = cursor.fetchall()
id_reg = contents[0][0]
_SQL2 = """select id
from services
where service_name=%(serv_name)s"""
cursor.execute(_SQL2, {'serv_name': request.form['service_name']})
contents = cursor.fetchall()
id_serv = contents[0][0]
_SQL = """select *
from firm f, firm_region r, firm_service fs
where f.id_region=%(id_reg)s and fs.id_service=%(id_serv)s
and r.id=f.id_region and fs.id_firm=f.id"""
cursor.execute(_SQL, {'id_serv': str(id_serv), 'id_reg': str(id_reg)})
contents = cursor.fetchall()
edited_conents = []
for row in contents:
add = []
add.append(row[2])
add.append(request.form['reg_name'])
add.append(request.form['service_name'])
add.append(row[3])
edited_conents.append(add)
titles = ('Название фирмы', 'Регион', 'Услуга', 'Информация о фирме')
return render_template(
'searched.html',
the_title='Найденные фирмы',
row_titles=titles,
the_data=edited_conents,
)
def services():
try:
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """select id, service_name
from services"""
cursor.execute(_SQL)
contents = cursor.fetchall()
titles = ('ID услуги', 'Услуга')
return render_template(
'services.html',
the_title='Услуги',
row_titles=titles,
the_data=contents,
)
except ConnectionError as err:
print('Trouble with SQL-server', 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'
def log_request(req: 'flask_request', res: str) -> None:
"""Log details of the web request and the results."""
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, ))
phrase=request.form['phrase']
letters=request.form['letters']
title='Here are your results:'
results=str(search4letters(phrase, letters))
try:
t=Thread(target=log_request,args=(request,results))
t.start()
except Exception as err:
print('***** Logging failed with this error:', str(err))
return render_template('results.html',the_phrase=phrase,the_letters=letters,the_title=title,the_results=results,)
def firms():
try:
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """select id, id_region, firm_name, firm_info
from firm""" # CHANGEEEE for id_reg = name_reg
cursor.execute(_SQL)
contents = cursor.fetchall()
titles = ('ID', 'Регион', 'Название фирмы', 'Информация')
return render_template(
'firm.html',
the_title='Фирмы',
row_titles=titles,
the_data=contents,
)
except ConnectionError as err:
print('Trouble with SQL-server', 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'
def firmservice():
try:
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """select id_firm, id_service
from firm_service""" # CHANGEEEE
# for id_firm(service) = firm_name
cursor.execute(_SQL)
contents = cursor.fetchall()
titles = ('Фирма', 'Услуга')
return render_template(
'firmservice.html',
the_title='Услуги фирм',
row_titles=titles,
the_data=contents,
)
except ConnectionError as err:
print('Trouble with SQL-server', 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'
def show_delservice():
with UseDataBase(app1.config['dbconfig']) as cursor:
_SQL = """delete from services where id=%(my_id)s"""
cursor.execute(_SQL, {'my_id': request.form['id_edit_del']})
return services()
