def giveFeedback():
if request.method == 'POST':
query = """INSERT INTO FEEDBACK VALUES(%s,%s,%s,%s)"""
cursor.execute(query,(session['username'],session['ISBN'],request.form['rating'],request.form['comments']))
connection.con.commit()
return redirect(url_for('Searchbooks'))
return render_template('feedback.html')
def set_role(self):
read = '''
SELECT role_id FROM `users` WHERE username = %s && password = %s
'''
val = (self.username, self.password)
cursor.execute(read, val)
for data in cursor.fetchall():
self.role = data[0]
def displayImage():
cursor.execute("""SELECT Cover_Image from ITEM where ISBN = 5533282""")
image = cursor.fetchone()[0]
#print image
print type(image)
#image = '/9j/4AAQSkZJRgABAQEALAAsAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAsLDBkSEw8UHRofHh0aHBwgJC4nICIsIxwcKDcpLDAxNDQ0Hyc5PTgyPC4zNDL/2wBDAQkJCQwLDBgNDRgyIRwhMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjL/wAARCABLADIDASIAAhEBAxEB/8QAGgAAAgMBAQAAAAAAAAAAAAAAAgMABAUBBv/EADQQAAIBAwMCBAMGBgMAAAAAAAECAwQRIQASMQUTBiJBURRhcTKBkdHh8BUjM0JioSSi8f/EABoBAAMBAQEBAAAAAAAAAAAAAAECBAADBQf/xAApEQABAwIEBQQDAAAAAAAAAAABAAIREiEDMUFRYXGRofATgbHhFMHR/9oADAMBAAIRAxEAPwD2lX1bradUZaehMlNFUshXssC8YCC+7i5LOwIxZbHTYetdVPR+n1c3TNtRUzhZIEV2MceTciwsQB62+/jS6yXxS9bWrSQRRUwdRTs+xiQA1zzwTs5yLnUiXxE1HXTSl0qZqmJIo02HswhxvZQTY4Lc5Nh8hr5WWYdDZDNNb+/WTyV8niqMXibrbNUU0fS1lqIIkZgwYlWKlrNtFvQgAZBtfnWn0/qvVa/qpjkoJKWkjkk87xkGRVUADP8Ak2COQuqnTo/FENdTpMIVppah5qiRlVmCkA7MH3LKG58o9NNqZfFXxj9iKHsPULsuqHtxhm5ucggKSeRewHs2KzCJLWBgtnPt98FgTxW6J5SL9k+voddLzCNceZgScce2vNhvFoKRxRxKrPFukkCt2wQzSY3XYA7Re/HAxfXpZDP3H7YAT+0m2osXBGGRBaeR837Ls11WiESz2/og/fqa4XqwTaJCPQ31Nc6Rw6poRNFKZNwa3muM/T9dQRymFV7l2v5mv/rVatqKOlSaeprY4kTMl2+yPpf6aYFilJC1KNgE7WBxzfn5j/WjSaQTlyWqG6Z2pyMyZvjPHGulJhG69wb2NwSeB8tLDwVUx7VXE94wwVHB8t+cHg250EU1I6t266BzJaJSsqnJGBzk/LQgxf4QqG6c0Uz3HduCbHPH7xonikaQkNYXBGfl/wC6Qnw0xBiqomDNayODcg2tg/d92nSRCR2PdAJxb8MaxMHPsjI0KAxVV/6v/bU1wxeY/wDLA+Vx+eppquI6Jp8hecrOl+G365VvUV0vxjSIXhL3VWJBFht/HJsCb2GqdDJ0GgeqaDqIqYa2EwQRxLu7caoQxJYW82wDODYWFtWPiug0XV+prVU87TpO7GZiXD7kYsFANlABdbWza+Tw+KLw9B0+CtPT5o4O8KKLcxIIWQhWPmta4PmObY9bH1ai1oa+sggDSMpt5lIKk6JVJT+HqF6hYK2pleal+G3MfspdYSVcqBclVySR5Tb11VFB4RCxunVXjjDNU2Vtqkpc3I2/2kMR682uNGvVPCyxQqKStdGAiVCHYBQSy3BbjzsR7C4xxpvd8MDoNRWw0dS9OjPTCxcPMdpvtJbIKlvMTxfTS9tz6lzGnJC3BC9D0nonXemziaSOmjglqSpjJACiwZ8YA3gAWuTzxrbj6r0x6mWM1ACoO40zuoTlQPMTyScfQ6yJeqeHepwx0tXRTkkRUxjRWsq7haxBF1D4J5OMEW1q9KWj316jpgglhdYphuEiu2XspPtvzgZNvTU+OCWVYodULab/AH8J2Egw1aCJTOislQrKQCGV1II9xqaE1kcbFFgwpsLAW1NQ04h37KiHoJaagaaVm6fTyOZFkZjEt2cDDE25HodEIKQxACip+3HLujXYLByctxg5OdPZ5u55Vuob25GP110STGFW7Y3k8ZwNLU6B/UtI2VOCloE7ccfTqZADZAkSgKL3xj53/HRNBQiEUpoIDAsgZYzGu3dcm4FrXvnVjuzEX7Z5wLH5frou7L2nbt+YGyr+eiXOmf3qtSNlVSGijcslBAHaTuXWNQSxN93HPlBvzcDTiYIZp9lOm5yHkZVA3ta1z7mwGflomlnyFjtfANuNFI8okIRbgEenIt+/w0CSTf5RgbJRq4VJBiNxj7OpqGoqL4h/EHU0aOHdNSNu6xuoHxT8TU/ArD2nkVYd2z+WoOWN8m4GR/kLcHTOmDxF/FY/jWVaELI8gYIWZi7bUBBuAF2WPrm+eNKeWRZJQHIAOM6GSWRUkIcixHH1P5aoDy5lIY2+sX286rmMI5ysR4fFM8aO21JU3svnVQ1jIVBANrYiFs8nPOnQr4rkrds0kENOe2LoqMQCdzkH3UeTixuDrbqJHWQAMQCnofmNBG7MsrFiSIsG/Gj67nNmhvTJAYNplOk7+5tlrcAYxrjfE522z9Pf8tVpJHRn2uwyPX9+2m1ErrM4DkC3AP01MMM2FvIXUNKuDgXydTVEzSX+0dTSfjndD0yv/9k='
image = base64.encodestring(image)
return render_template('image.html',image = image)
def insere_pessoa(dados_pessoa):
cursor.execute(
"INSERT INTO pessoa (nome, endereco, cpf, estado,"
"turma, periodo, modulo)"
"VALUES (%s, %s, %s, %s, %s, %s, %s)",
(dados_pessoa['nome'], dados_pessoa['endereco'], dados_pessoa['cpf'],
dados_pessoa['uf'], dados_pessoa['turma'], dados_pessoa['periodo'],
dados_pessoa['modulo']))
def get_companies_data(self):
read = 'SELECT * FROM companies'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'company name', 'address'])
table.add_rows(a)
return table
def get_chairs_data(self):
read = 'SELECT * FROM chairs'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'number', 'status'])
table.add_rows(a)
return table
def get_roles_data(self):
read = 'SELECT * FROM user_roles'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'nama roles'])
table.add_rows(a)
return table
def get_topping_data(self):
read = 'SELECT * FROM toppings'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'nama topping', 'harga'])
table.add_rows(a)
return table
def get_studio_data(self):
read = 'SELECT * FROM studios'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'studio', 'description'])
table.add_rows(a)
return table
def get_schedules_data(self):
read = 'SELECT * FROM schedules'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'date', 'start', 'ends', 'movie', 'studio'])
table.add_rows(a)
return table
def get_movies_data(self):
read = 'SELECT * FROM movies'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable(['id', 'title', 'genre', 'company'])
table.add_rows(a)
return table
def checkReviews():
query = """SELECT * FROM FEEDBACK WHERE ISBN = %s"""
cursor.execute(query,session['ISBN'])
entries = [dict(
CUSTOMER=row[0],
ISBN=row[1],
RATING= row[2],
COMMENTS=row[3]
) for row in cursor.fetchall()]
return render_template('reviews.html',entries = entries)
def reports():
cursor.execute("""SELECT SUM(((Quantity*Rate) - Discount + Tax + Shipping_Cost)) as Result from SALES_DETAIL""")
total = [dict(TOTAL=row[0]) for row in cursor.fetchall()]
if request.method == 'POST':
query = """SELECT SUM(((Quantity*Rate) - Discount + Tax + Shipping_Cost)) from SALES_DETAIL natural join SALES_HEADER where Order_Date >%s and Order_Date < %s"""
cursor.execute(query,(request.form['startdate'],request.form['enddate']))
boundtotal = [dict(TOTAL=row[0]) for row in cursor.fetchall()]
print request.form['enddate']
return render_template('reports.html',total = total,boundtotal = boundtotal)
return render_template('reports.html',total = total)#,boundtotal = boundtotal)
def atualiza_pessoa(pessoa):
query = "UPDATE pessoa SET nome = %s, endereco = %s, estado = %s, periodo %s, modulo = %s" \
"WHERE id = %s"
params = [
pessoa['nome'], pessoa['endereco'], pessoa['cpf'], pessoa['estado'],
pessoa['estado'], pessoa['turma'], pessoa['periodo'], pessoa['modulo'],
pessoa['id']
]
cursor.execute(query, params)
def get_users_data(self):
read = 'SELECT * FROM users'
cursor.execute(read)
a = cursor.fetchall()
for data in a:
self.temporary_data.append(data)
table = PrettyTable([
'id', 'name', 'username', 'password', ' gender', ' contact',
'address', 'role id'
])
table.add_rows(a)
return table
def get_schedules_today(self):
read = 'SELECT schedules.id_schedule, movies.title, schedules.date_schedule, schedules.start, schedules.end, studios.studio_name FROM schedules INNER JOIN movies ON schedules.film_code = movies.code_film INNER JOIN studios ON schedules.id_studio = studios.id_studio'
cursor.execute(read)
fetch = cursor.fetchall()
for data in fetch:
self.temporary_data.append(data)
table = PrettyTable([
'ID Jadwal', 'Nama Film', 'Tanggal Film', 'Mulai', 'Selesai',
'Studio'
])
table.add_rows(fetch)
return table
def otp_check():
check_otp = input('enter 6 digit otp sent your mail : ')
if check_otp == otp:
encoded_pwd = password_match()
query = "insert into user (user_id,user_name,mail_id,password) values(%s,%s,%s,%s)"
cursor.execute(
query,
(user_id, user_name, user_mail, encoded_pwd))
mydb.commit()
print("User Creation Successful")
else:
print('incorrect OTP \n')
otp_check()
def subsidies_info(url, api_fp_id, fp_id, s_start_id, pages):
# субсидии фeдпроекта
subs_id_f = s_start_id
for i in range(1, pages + 1):
print(url + str(i), '\t', 'время отправки запроса: ',
datetime.datetime.now())
resp_subsidies = requests.get(url + str(i)).text
data_subsidies = json.loads(resp_subsidies)
print(url + str(i), '\t', 'время получения данных: ',
datetime.datetime.now())
for item_s in data_subsidies['items']:
regnum = item_s['regnum'] # уникальный рег. номер субсидии
grbs_name = item_s['grbs_name'] # имя ГРБС
date_agreem = item_s['year'] # дата соглашения
full_amount = item_s['amount'] # сумма субсидии
subs_url = item_s['spending_subs_url'] # ссылка на субсидию
reciever = item_s['receivers'][0]['receiver_name'] # получатель
reciever = reciever.replace(
"'", '') # замена кавычек для корректных sql запросов
if grbs_name == None: # в апи почти всегда не данных по ГРБС /не костыль/
grbs_svr_code = item_s[
'grbs_svr_code'] # код по сводному регистру
resp_grbs = requests.get(
"https://spending.gov.ru/subsidies/grbs/%s/" %
grbs_svr_code)
soup = BeautifulSoup(resp_grbs.text, 'lxml')
data_grbs = soup.find_all(
'p', {'class': "h3 text-center font-weight-semibold mb-0"})
grbs_name = data_grbs[0].text
try:
cursor.execute(
"insert into subsidies(id_subsidy, id_federal_project, subsidy_sum, manager, recipient, release_date, reg_num, subs_url)"
"values (%i, %i, %f, '%s', '%s', %i, '%s', '%s')" %
(subs_id_f, fp_id, full_amount, grbs_name, reciever,
date_agreem, regnum, subs_url))
conn.commit()
except Exception as e:
print('Error!', e)
subs_id_f += 1
print('Успешно записано 50 субсидий, всего: %i' % (subs_id_f - 1))
def UserLogin():
if request.method == 'POST':
if request.form['submitB'] == 'login':
userName = request.form['username']
password = request.form['password']
query = """SELECT * FROM CUSTOMER WHERE Customer_ID = %s and Customer_password = %s"""
cursor.execute(query,(userName,password))
result = cursor.fetchone()
if result is None:
return render_template('failure.html')
else:
session['username'] = request.form['username']
#return render_template('BookSearch.html')
return redirect(url_for('Searchbooks'))
if request.form['submitB'] == 'register':
return redirect(url_for('addCustomer'))
return render_template('Userlogin.html')
def addBook():
form = addItemForm()
session['username']
if request.method == 'POST':
ISBN = request.form['ISBN']
bookname = request.form['bookname']
bookdesc = request.form['bookdesc']
authorname = request.form['authorname']
cost = request.form['cost']
stock = request.form['stock']
query = """INSERT INTO ITEM (ISBN,Book_Name,Book_Description,Author_Name,Cost_Price,Stock) VALUES (%s,%s,%s,%s,%s,%s)"""
cursor.execute(query,(ISBN,bookname,bookdesc,authorname,cost,stock))
connection.con.commit()
return redirect(url_for('addBook'))
#return render_template('addCustomer.html',form = form)
return render_template('addBook.html',form = form)
def user_signup():
"""This module is used for user Signup
"""
try:
# Signup User Input
user_id = valid.userid(input('enter user id : ').strip().lower())
query = "select mail_id from user where user_id =%s;"
if cursor.execute(query, (user_id)):
print('user id already exists')
user_signup()
else:
user_name = valid.user_name_validation(
input('enter user name : ').strip())
user_mail = valid.email_validation(
input('enter user mail : ').strip().lower())
query = "select mail_id from user where mail_id =%s;"
if cursor.execute(query, (user_mail)):
print('mail_id already exists')
user_signup()
else:
otp = otp_generation()
print(otp)
mail_generation(otp, user_mail)
def otp_check():
check_otp = input('enter 6 digit otp sent your mail : ')
if check_otp == otp:
encoded_pwd = password_match()
query = "insert into user (user_id,user_name,mail_id,password) values(%s,%s,%s,%s)"
cursor.execute(
query,
(user_id, user_name, user_mail, encoded_pwd))
mydb.commit()
print("User Creation Successful")
else:
print('incorrect OTP \n')
otp_check()
otp_check()
except Exception as exception_e:
print("Error", exception_e)
def addCustomer():
#form = AddCustomerForm()
if request.method == 'POST':
userName = request.form['username']
firstName = request.form['firstname']
lastName = request.form['lastname']
password = request.form['password']
email = request.form['email']
street = request.form['street']
aptno = request.form['aptno']
city = request.form['city']
state = request.form['state']
phone = request.form['Phone']
query = """INSERT INTO CUSTOMER VALUES (%s,%s,%s,%s,%s)"""
cursor.execute(query,(userName ,firstName,lastName ,email,password))
query = """INSERT INTO CUSTOMER_ADDRESS VALUES(%s,%s,%s,%s,%s)"""
cursor.execute(query,(userName,street,aptno,city,state))
query = """INSERT INTO CUSTOMER_CONTACT VALUES(%s,%s)"""
cursor.execute(query,(userName,phone))
connection.con.commit()
return redirect(url_for('home'))
return render_template('addCustomer.html')#,form = form)
def forgot_password():
"""This custom function updates the new encrypted password to the db
"""
user_mail = valid.email_validation(
input('enter user mail : ').strip().lower())
query = "select mail_id from user where mail_id =%s;"
if cursor.execute(query, (user_mail)):
otp = otp_generation()
print(otp)
mail_generation(otp, user_mail)
check_otp = input('enter 6 digit otp sent your mail : ')
if check_otp == otp:
new_pass = password_match()
cursor.execute('update user set password= %s where mail_id = %s',
(new_pass, user_mail))
mydb.commit()
print('password changed')
else:
print('incorrect OTP \n')
forgot_password()
else:
print("User Not Found")
def add_user():
ulang = True
#memasukan nama
name = input('masukan nama : ')
#memasukan email
username = input('masukan username : '******'masukan password : '******'masukan password sekali lagi : ')
if password == password_confirm:
ulang = False
ulang = True
#memasukan gender
while ulang == True:
gender = input('masukan gender (L/P) : ').upper()
if gender == 'L' or gender == 'P':
ulang = False
else:
print('masukan sesuai format')
#memasukan kontak
contact = input('masukan kontak : ')
#memasukan alamat
alamat = input('masukan alamat : ')
#masukan role
read = 'SELECT * FROM user_roles'
cursor.execute(read)
print('id\tnama roles')
for data in cursor.fetchall():
print('{}\t{}'.format(data[0], data[1]))
role_id = int(input('masukan role id : '))
sql = 'INSERT INTO users (id_user, name, username, password, gender, contact, address, role_id) VALUES (NULL, %s, %s, %s, %s, %s, %s, %s)'
val = (name, username, password, gender, contact, alamat, role_id)
sqlQuery = (sql, val)
return sqlQuery
def updateCart():
ISBN = request.form['product_code']
quantity = request.form['product_qty']
userName = session['username']
session['ISBN'] = ISBN
if request.form['buyitems'] == 'add':
query = """INSERT INTO SALES_HEADER (Order_Date,Customer_ID) VALUE (now(),%s)"""
cursor.execute(query,(userName))
query = """UPDATE SALES_HEADER SET Shipped_Date = DATE_ADD(Order_Date,INTERVAL 2 DAY), Delivary_Date = DATE_ADD(Order_Date,INTERVAL 4 DAY) WHERE Order_ID = (last_insert_id())"""
cursor.execute(query)
query = """UPDATE SALES_DETAIL SET Quantity=%s, ISBN = %s WHERE Order_ID = (last_insert_id())"""
cursor.execute(query,(quantity,ISBN))
query = """UPDATE SALES_DETAIL SET Rate = Quantity * (SELECT Cost_Price FROM ITEM WHERE ISBN = %s),Discount = Rate/10,Tax=Rate *33/100,Shipping_Cost = Rate/4 WHERE Order_ID=(last_insert_id())"""
cursor.execute(query,(ISBN))
query = """UPDATE ITEM SET Stock = Stock-%s WHERE ISBN = %s and Stock>0"""
cursor.execute(query,(quantity,ISBN))
connection.con.commit()
if request.form['buyitems'] == 'feedback':
return render_template('feedback.html',ISBN=ISBN)
if request.form['buyitems'] == 'reviews':
ISBN = request.form['product_code']
session['ISBN'] = ISBN
return redirect(url_for('checkReviews'))
return redirect(url_for('payment'))
def user_login():
"""This custom module returns user id of the validate user
Returns:
string: returns the user id of the validated user
"""
try:
user_mail = input('enter user mail : ').strip()
password = input('enter password : '******'ascii'))
query = "select user_id from user where mail_id =%s and password =%s;"
if cursor.execute(query, (user_mail, encoded_pwd)):
user_id = cursor.fetchone()
print('login')
return user_id[0]
else:
print('Invalid Credentials \n\n--Login Again--\n ')
except Exception as exception_e:
print("Error", exception_e)
def admin_login():
"""This is a function that validates the .
Args:
admin_mail (string): Admin email address
password (string): Admin Password
cursor (DB_class): cursor class
mydb (DB_class): connection class
Returns:
string : returns the user id of the validated admin
"""
try:
admin_mail = valid.email_validation(input('enter admin mail '))
password = input("Enter you password")
encoded_pwd = base64.b64encode(password.encode('ascii'))
query = "select user_id from user \
where mail_id =%s and password =%s and is_admin_flag = %s;"
if cursor.execute(query, (admin_mail, encoded_pwd, '1')):
print('admin login')
admin_uid = cursor.fetchone()
return admin_uid[0]
print('invalid credentials')
except Exception as exception_e:
print('Error', exception_e)
def payment():
query = '''SELECT (((Quantity*Rate) - Discount + Tax + Shipping_Cost)) as Result,Discount,Tax,Shipping_Cost,Rate from SALES_DETAIL WHERE Order_ID = ((SELECT max(Order_ID) FROM SALES_DETAIL))'''
cursor.execute(query)
total = [dict(
TOTAL=row[0],
DISCOUNT=row[1],
TAX=row[2],
SHIPPING = row[3],
RATE = row[4]
) for row in cursor.fetchall()]
#print total
#total = [dict(TOTAL = row[0]) for row in cursor.fetchall()]
if request.method == 'POST':
cardno = request.form['cardno']
expdate = request.form['expDate']
query = '''INSERT INTO BILLING (Billing_Date,ORDER_ID) VALUES (now(),(SELECT max(Order_ID) FROM SALES_DETAIL))'''
cursor.execute(query)
query = '''UPDATE BILLING SET Card_Number=%s,Card_expDate = %s WHERE Order_ID in (SELECT max(Order_ID) FROM SALES_DETAIL)'''
cursor.execute(query,(cardno,expdate))
connection.con.commit()
return redirect(url_for('Searchbooks'))
return render_template('Payment.html',total =total)
# -*- coding: utf-8 -*-
import sys
import json
with open('env.json', 'r') as env:
data=env.read()
# parse file
env_json = json.loads(data)
env_settings = env_json['settings']
from connection import cursor
from connection import connection
cursor.execute("SELECT * FROM status;")
status = cursor.fetchone()
try:
if sys.argv[1] == "catalog":
from catalog import *
parser = Catalog()
url = env_settings['catalog_url']
last_page = status[0] if status else 9999
elif sys.argv[1] == "one":
from item import *
parser = Item()
url = env_settings['item_url']
last_page = status[1] if status else None
if last_page is None:
cursor.execute("SELECT max(id) FROM catalog WHERE status = 0;")
def sql_execute(temporary):
cursor.execute(temporary[0], temporary[1])
connection.db.commit()
print('esekusi sukses')
def get_national_and_federal_projects_info(url):
resp = requests.get(url).text
data = json.loads(resp)
proj_id = 1
subs_id = 1
try:
cursor.execute("truncate table national_projects")
cursor.execute("truncate table federal_projects")
cursor.execute("truncate table subsidies")
conn.commit()
except Exception as e:
print("Error!", e)
# нацпроекты
for index, item in enumerate(data['items']):
national_project_name = item['np_short_name']
np_budget_released = item['total_sum']
np_id = item['np_id'] # id нацпроекта
try:
cursor.execute(
"insert into national_projects("
" id_national_project, project_name, total_np_budget, released_budget, np_api_id)"
" values (%i, '%s', %f, %f, '%s')" %
(index + 1, national_project_name, np_budget_released,
np_budget_released, np_id))
conn.commit()
except Exception as e:
print("Error!", e)
# федеральные проекты по нацпроекту
for item2 in item['fedprojects']:
api_fp_id = item2['fp_id'] # id федпроекта
federal_project_name = item2['fp_fullname'] # название
fp_budget = item2['total_sum'] # сумма контрактов и субсидий
subsidies_count = item2['subsidies_count'] # кол-во субсидий
subsidies_sum = item2['subsidies_sum'] # сумма субсидий
contracts_count = item2['contracts_count'] # кол-во контрактов
contracts_sum = item2['contracts_sum'] # сумма контрактов
print('Феделарьный проект №', proj_id)
try:
cursor.execute(
"insert into federal_projects(id_federal_project, id_national_project, project_name,"
" project_budget, fp_api_id, subsidies_count, contracts_count, contracts_sum, subsidies_sum)"
"values (%i, %i, '%s', %f, '%s', %i, %i, %f, %f)" %
(proj_id, index + 1, federal_project_name, fp_budget,
api_fp_id, subsidies_count, contracts_count,
contracts_sum, subsidies_sum))
conn.commit()
except Exception as e:
print("Error!", e)
if subsidies_count > 0:
pages = subsidies_count // 50 + 1
subsidies_info(
'https://api.spending.gov.ru/v1/natprojects/fpsubsidies?fp_id=%s&page='
% (api_fp_id), api_fp_id, proj_id, subs_id, pages)
subs_id += subsidies_count
proj_id += 1 # инкремент id федерального провекта
print(key + " needs a foreign key table")
params = params + ""
elif isinstance(prevline[key], (float, int)):
params = params + ", " + key + " BIGINT"
finalparms[key] = int()
else:
print(key + " is something I don't know yet")
params = params[2:]
sqlmakeTable.format(params=params)
print(sqlmakeTable.format(params=params))
#Attempt to add the table
try:
cursor.execute(sqlmakeTable.format(params=params))
cursor.commit()
except Exception as ex:
print("Table either already in or unable to add. See error message")
print(ex)
#Put the JSON Objects in the database
sqlInsertToTable = "INSERT INTO mytweets428 VALUES ({vals})"
sqlCheckIfExists = "SELECT COUNT(1) FROM table_name WHERE id = {val};"
for line in of:
myvals = ""
jsonobj = json.loads(line)
for param in finalparms:
insertedFieldValue = jsonobj[param]
def Searchbooks():
form = searchBooksForm()
query = """SELECT ISBN,Book_Description,Author_Name,Cost_Price,Stock,Book_Name,Cover_Image FROM ITEM"""
cursor.execute(query)
entries = [dict(
ISBN=row[0],
BOOKDES=row[1],
AUTHORNAME= row[2],
COSTPRICE=row[3],
STOCK=row[4],
BOOKNAME=row[5],
IMAGE=row[6]
) for row in cursor.fetchall()]
for i in range(len(entries)):
entries[i]['IMAGE'] = base64.encodestring(entries[i]['IMAGE'])
# entries[0]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# entries[1]['IMAGE'] = base64.encodestring(entries[1]['IMAGE'])
# entries[2]['IMAGE'] = base64.encodestring(entries[2]['IMAGE'])
# entries[3]['IMAGE'] = base64.encodestring(entries[3]['IMAGE'])
# entries[4]['IMAGE'] = base64.encodestring(entries[4]['IMAGE'])
# entries[5]['IMAGE'] = base64.encodestring(entries[5]['IMAGE'])
# entries[6]['IMAGE'] = base64.encodestring(entries[6]['IMAGE'])
# entries[7]['IMAGE'] = base64.encodestring(entries[7]['IMAGE'])
# entries[8]['IMAGE'] = base64.encodestring(entries[8]['IMAGE'])
# entries[9]['IMAGE'] = base64.encodestring(entries[9]['IMAGE'])
query = """SELECT ISBN,Book_Description,Author_Name,Cost_Price,Stock,Book_Name,Cover_Image FROM ITEM where ISBN in
(select ISBN from ITEM_MASTER where Category in
(select category from ITEM_MASTER where ISBN in
(Select ISBN from SALES_DETAIL Natural JOIN SALES_HEADER Natural Join ITEM_MASTER WHERE Customer_ID=%s)))"""
cursor.execute(query,session['username'])
suggestedentries = [dict(
ISBN=row[0],
BOOKDES=row[1],
AUTHORNAME= row[2],
COSTPRICE=row[3],
STOCK=row[4],
BOOKNAME=row[5],
IMAGE=row[6]
) for row in cursor.fetchall()]
# print len(suggestedentries)
for j in range(len(suggestedentries)):
suggestedentries[j]['IMAGE'] = base64.encodestring(suggestedentries[j]['IMAGE'])
# suggestedentries[0]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[1]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[2]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[3]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[4]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[5]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
# suggestedentries[6]['IMAGE'] = base64.encodestring(entries[0]['IMAGE'])
if request.method == 'POST':
if request.form['submitB'] == 'search':
book = request.form['book']
query = """SELECT ISBN,Book_Description,Author_Name,Cost_Price,Stock,Book_Name from ITEM where Book_Name like %s"""
cursor.execute(query,"%"+book+"%")
#cursor.execute(query)
entries = [dict(
ISBN=row[0],
BOOKDES=row[1],
AUTHORNAME= row[2],
COSTPRICE=row[3],
STOCK=row[4],
BOOKNAME=row[5]
) for row in cursor.fetchall()]
return render_template('showBooks.html', entries=entries,suggestedentries = suggestedentries)
return render_template('showBooks.html', entries=entries,suggestedentries = suggestedentries)
def retorna_pessoa(id):
cursor.execute("SELECT * FROM pessoa WHERE id = %s", [id])
pessoa = cursor.fetchone()
return pessoa
def retorna_pessoas():
cursor.execute("SELECT * FROM pessoa")
return cursor.fetchall()
