def get_sample_weight():
"""
retrieve sample_weight from DB
"""
global sample_weight
try:
db = MySQLdb.connect(host=DB_HOST,
user=DB_USER,
passwd=DB_PASS,
db=DB_NAME)
cursor = db.cursor()
w_output = ("get_sample_weight() : get_sample_weight Connected to DB")
global_broadcast(w_output)
cursor.execute(
"""SELECT sample_weight FROM calibration WHERE device_id = %s;""",
(DEVICE_ID, ))
row = cursor.fetchone()
sample_weight = int(row[0])
if sample_weight:
return float(sample_weight)
return 0.04
except Exception as err:
w_output = "get_sample_weight() : get_sample_weight error: {}".format(
err)
global_broadcast(w_output)
return 0.04
def cadastrar(nome, idade, telefone):
with closing(MySQLdb.connect(**__dados)) as conn:
cursor = conn.cursor()
cursor.execute(
f"INSERT INTO topskills01.danielilagacione(nome, idade, telefone)VALUES('{nome}',{idade}');"
)
conn.commit()
def db_update_total_sprays_left(sprays_count):
"""
insert total_sprays_left into DB
"""
try:
db = MySQLdb.connect(host=DB_HOST,
user=DB_USER,
passwd=DB_PASS,
db=DB_NAME)
cur = db.cursor()
sql = """
UPDATE sprays SET total_sprays_left = total_sprays_left - %s WHERE device_id = %s;
"""
vals = (sprays_count, DEVICE_ID)
cur.execute(sql, vals)
db.commit()
db.close()
w_output = "db_update_total_sprays_left() : db_insert [total_sprays_left] DB success"
global_broadcast(w_output)
return True
except Exception as err:
w_output = "db_update_total_sprays_left() : db_insert [total_sprays_left] DB error: {}".format(
err)
global_broadcast(w_output)
return None
def withd():
id = request.form['id']
money = request.form['money']
db = MySQLdb.connect(user='******', password='', host='localhost', database='banking')
ob = db.cursor()
res = ob.execute("select bal,mbal from account WHERE accountid=%s", [id])
if res > 0:
data = ob.fetchall()
for row in data:
ball = row[0]
mbal = row[1]
nbal = int(ball) - int(money)
if(nbal >= int(mbal)):
newbal = str(nbal)
query = "insert into transaction ( accountid,amount,balance, description) values ( %s , %s ,%s,'Debited') "
val = (id, money, newbal)
res = ob.execute(query, val)
ob.execute("update account set bal=%s where accountid=%s", (newbal, id))
db.commit()
return render_template('depositresult.html', m=id, n=money)
# return newbal
else:
return "Your Balance is Low"
else:
return "Wrong Account ID"
def buscar_por_id(self, id:int):
conexao = MySQLdb.connect(host='mysql.zuplae.com', user='******', passwd='lendas19', database='zuplae04')
cur = conexao.cursor()
cur.execute(f'SELECT ID, NOME, CPF, DATA_NASCIMENTO from Cliente WHERE ID={id}')
c = cur.fetchone()
cliente = Cliente(c[1], c[2], c[3], c[0])
return cliente
def deposit():
id = request.form['id']
money=request.form['money']
db = MySQLdb.connect(user='******', password='', host='localhost', database='banking')
ob = db.cursor()
res = ob.execute("select bal from account WHERE accountid=%s", [id])
if res > 0:
data = ob.fetchone()
st = (str(data))
nw = (st.replace('(', ''))
nw = (nw.replace(',', ''))
nw = (nw.replace(')', ''))
nw = (nw.replace('\'', ''))
bal = nw
nbal =int(bal)+int(money)
newbal=str(nbal)
query= "insert into transaction ( accountid,amount,balance, description) values ( %s , %s ,%s,'Credited') "
val =(id,money,newbal )
res= ob.execute(query,val)
ob.execute("update account set bal=%s where accountid=%s",(newbal,id))
db.commit()
return render_template('depositresult.html', m=id, n=money)
else:
return "Wrong Account ID"
def review_submit():
# email = request.form['mail']
rating = request.form['rating']
review = request.form['filed']
username = session['name']
mid = words
db = MySQLdb.connect(user='******',
password='',
host='localhost',
database='movie')
ob = db.cursor()
id = ob.execute("SELECT userid FROM user WHERE name = %s", (username, ))
userid = ob.fetchall()
query = "INSERT INTO rating (userid,movieid,rate,reviewlist) VALUES(%s,%s,%s,%s )"
val = (userid, mid, rating, review)
ob.execute(query, val)
db.commit()
rat = str(rating)
ob1 = db.cursor()
ob1.execute(
"SELECT userid,movieid,rate,date,reviewlist FROM rating WHERE userid = %s",
[userid])
res = ob.execute("SELECT title,rating FROM movielist WHERE rating = %s",
[rat])
if res > 0:
data = ob.fetchall()
userdetails = ob1.fetchall()
return render_template('user_rating.html', m=data, l=userdetails)
else:
return "done"
def update_sample_weight(weight):
"""
insert new sample_weight into DB
"""
try:
db = MySQLdb.connect(host=DB_HOST,
user=DB_USER,
passwd=DB_PASS,
db=DB_NAME)
cur = db.cursor()
sql = """
INSERT IGNORE INTO calibration
(device_id, sample_weight)
VALUES
(%s, %s);
"""
vals = (DEVICE_ID, weight)
cur.execute(sql, vals)
db.commit()
db.close()
w_output = "update_sample_weight() : Success, updated to {}".format(
weight)
global_broadcast(w_output)
return True
except Exception as err:
w_output = "update_sample_weight() : DB error: {}".format(err)
global_broadcast(w_output)
return None
def transaction():
id = request.form['id']
db = MySQLdb.connect(user='******', password='', host='localhost', database='banking')
ob = db.cursor()
res = ob.execute("SELECT time, amount, balance, description FROM transaction WHERE accountid=%s GROUP BY time DESC LIMIT 5", [id])
if res >0:
data=ob.fetchall()
# SELECT time, amount, balance, description FROM transaction GROUP BY time DESC LIMIT 5
return render_template('trans5.html', m=data)
def deletar(self, id: int):
conexao = MySQLdb.connect(host='mysql.zuplae.com',
user='******',
passwd='lendas19',
database='zuplae04')
cur = conexao.cursor()
cur.execute(f'DELETE * FROM Cliente WHERE ID ={id}')
cur.commit()
conexao.close()
def listar_endereco():
conexao = __MySQLdb.connect(host='127.0.0.1',
database='aulabd',
user='******')
cursor = conexao.cursor()
cursor.execute('SELECT * FROM ENDERECO')
enderecos = cursor.fetchall()
for ed in enderecos:
print(ed)
def connect(self):
try:
mydb = MySQLdb.connect(config.mysqlhost, config.mysqluser,
config.mysqlpasswd, config.mysqldb)
except MySQLdb.Error as e:
#escribir error en el fichero de LOG
print('error {}: {}'.format(e.args[0], e.args[1]))
sys.exit(1)
return mydb
def lista_todos(self):
conexao = MySQLdb.connect(host='mysql.zuplae.com', user='******', passwd='lendas19', database='zuplae04')
cur = conexao.cursor()
cur.execute('SELECT ID, NOME, CPF, DATA_NASCIMENTO from Cliente')
lista_clientes = []
for c in cur.fetchall():
cliente = Cliente(c[1], c[2], c[3], c[0])
lista_clientes.append(cliente)
return lista_clientes
def rom():
mydb = MySQLdb.connect(user='******',
password='',
host='localhost',
database='movie')
cu = mydb.cursor()
rom = "Romantic Movie List"
cu.execute('select romantic from emotion')
i = cu.fetchall()
return render_template('emotion.html', m=i, r=rom)
def action():
mydb = MySQLdb.connect(user='******',
password='',
host='localhost',
database='movie')
cu = mydb.cursor()
ac = "Action movie List"
cu.execute('select action from emotion')
i = cu.fetchall()
return render_template('emotion.html', m=i, r=ac)
def connection():
try:
conn = MySQLdb.connect(host='dublinbike.clpyaelniude.us-west-2.rds.amazonaws.com',
user='******',
password='******',
database='dublinbike18')
return conn
except Exception as e:
print(e.message, e.args)
def sad():
mydb = MySQLdb.connect(user='******',
password='',
host='localhost',
database='movie')
cu = mydb.cursor()
cc = "Drama Movie List"
cu.execute('select drama from emotion')
i = cu.fetchall()
return render_template('emotion.html', m=i, r=cc)
def happy():
mydb = MySQLdb.connect(user='******',
password='',
host='localhost',
database='movie')
cursor = mydb.cursor()
ha = "Comedy movie List"
cursor.execute('select comedy from emotion')
i = cursor.fetchall()
return render_template('emotion.html', m=i, r=ha)
def movie():
db = MySQLdb.connect(user='******',
password='',
host='localhost',
database='movie')
query = "select * FROM movielist"
ob = db.cursor()
result = ob.execute(query)
if result > 0:
data = ob.fetchall()
return render_template('movie.html', m=data)
def detail():
id=request.form['id']
db = MySQLdb.connect(user='******', password='', host='localhost', database='banking')
ob = db.cursor()
res = ob.execute("select account_type,mbal, bal, nom_name,nom_rel from account WHERE accountid=%s", [id])
if res> 0:
data=ob.fetchone()
return render_template('account_Detail.html', m=data)
else:
return "Wrong Account ID"
def __init__(self):
host = "127.0.0.1"
user = "******"
password = ""
db = "Msomi"
self.con = MySQLdb.connect(host=host,
user=user,
password=password,
db=db,
cursorclass=MySQLdb.cursors.DictCursor)
self.cur = self.con.cursor()
def alterar(self, cliente: Cliente):
conexao = MySQLdb.connect(host='mysql.zuplae.com',
user='******',
passwd='lendas19',
database='zuplae04')
cur = conexao.cursor()
cur.execute(
f'UPDATE Cliente SET (NOME = {cliente.nome}, CPF = {cliente.cpf}, DATA_NASCIMENTO = {cliente.data_nascimento} WHERE = ID = {cliente.id} '
)
cur.commit()
conexao.close()
def salvar(self, cliente: Cliente):
conexao = MySQLdb.connect(host='mysql.zuplae.com',
user='******',
passwd='lendas19',
database='zuplae04')
cur = conexao.cursor()
cur.execute(
f'INSERT INTO Cliente (NOME, CPF, DATA_NASCIMENTO) VALUES("{cliente.nome}", "{cliente.cpf}", "{cliente.data_nascimento}") '
)
cur.commit()
conexao.close()
def detail():
us = session['name']
db = MySQLdb.connect(user='******',
password='',
host='localhost',
database='movie')
ob = db.cursor()
result = ob.execute(
"select userid,name,email,gender,movie FROM user WHERE name =%s", [us])
if result > 0:
data = ob.fetchall()
return render_template('user_detail.html', m=data)
return us
def db_insert(data):
"""
Should not be used with live-updated data
insert data (from kairos API) into DB as one operation
"""
try:
db = MySQLdb.connect(host=DB_HOST,
user=DB_USER,
passwd=DB_PASS,
db=DB_NAME)
cur = db.cursor()
# this is beautiful
sql = """
INSERT INTO retail
( device_id, timestamp_line, spray_count,
gender, age, ethnic_group,
anger, disgust, fear,
joy, sadness, surprise,
positive
)
VALUES
( %s, %s, %s,
%s, %s, %s,
%s, %s, %s,
%s, %s, %s,
%s);
"""
# calulcate positive and duration values
data["positive"] = 1 if (data["surprise"] != 0
or data["joy"] != 0) else 0
vals = (DEVICE_ID, data["timestamp_line"], data["spray_count"],
data["gender"], data["age"], data["ethnic_group"],
data["anger"], data["disgust"], data["fear"], data["joy"],
data["sadness"], data["surprise"], data["positive"])
cur.execute(sql, vals)
db.commit()
db.close()
w_output = "db_insert(): db_insert DB success"
global_broadcast(w_output)
return True
except Exception as err:
w_output = "b_insert(): db_insert DB error: {}".format(err)
global_broadcast(w_output)
return None
def db_upsert(data, timestamp_up):
"""
update data (from kairos API) into DB as one operation using timestamp_up as reference
"""
try:
db = MySQLdb.connect(host=DB_HOST,
user=DB_USER,
passwd=DB_PASS,
db=DB_NAME)
cur = db.cursor()
sql = """
UPDATE retail SET
timestamp_line=%s,
spray_count=%s,
gender=%s,
age=%s,
ethnic_group=%s,
anger=%s,
disgust=%s,
fear=%s,
joy=%s,
sadness=%s,
surprise=%s,
positive=%s
WHERE device_id=%s AND timestamp_up=%s;
"""
# calulcate positive and duration values
data["positive"] = 1 if (data["surprise"] != 0
or data["joy"] != 0) else 0
vals = (data["timestamp_line"], data["spray_count"], data["gender"],
data["age"], data["ethnic_group"], data["anger"],
data["disgust"], data["fear"], data["joy"], data["sadness"],
data["surprise"], data["positive"], DEVICE_ID, timestamp_up)
cur.execute(sql, vals)
db.commit()
db.close()
w_output = "db_upsert() : db_upsert DB success"
global_broadcast(w_output)
return True
except Exception as err:
w_output = "db_upsert() : db_upsert DB error: {}".format(err)
global_broadcast(w_output)
return None
def account():
atype= request.form['atype']
mbal=request.form['mbal']
bal=request.form['bal']
nname=request.form['nname']
nrel=request.form['nrel']
bank_id =bankid
cid=session['customerid']
db = MySQLdb.connect(user='******', password='', host='localhost', database='banking')
ob=db.cursor()
# query = "INSERT INTO account (bankid, customerid, account_type, mbal, bal, nom_name, nom_rel) VALUES (%s,%s,%s,%s,%s,%s, %s )"
# val =(bank_id,cid, atype, mbal, bal, nname, nrel)
# ob.execute(query,val)
# db.commit()
ob1=db.cursor()
result = ob1.execute( "select accountid, account_type, mbal, bal, nom_name, nom_rel FROM account WHERE customerid =%s", [cid])
if result > 0:
data = ob1.fetchone()
session.clear()
return render_template('account_create.html' ,m=data)
def rating():
if request.method == "POST":
global words
id = request.form['smt']
mydb = MySQLdb.connect(user='******',
password='',
host='localhost',
database='movie')
# query =
q = str(id)
words = q
ob = mydb.cursor()
res = ob.execute(
"select movieid, title,genres,pic FROM movielist where movieid = %s",
[q])
if res > 0:
data = ob.fetchone()
return render_template('rating.html', m=data)
else:
return "not in if"
return "not goes"
def reg():
if request.method == 'GET':
return render_template('reg.html')
else:
# userid=request.form['userid']
name = request.form['name']
password = request.form['pass'].encode('utf-8')
# hash_password = bcrypt.hashpw(password, bcrypt.gensalt())
email = request.form['email']
gender = request.form['gender']
movie = request.form['movie']
db = MySQLdb.connect(user='******',
password='',
host='localhost',
database='movie')
query = "INSERT INTO user (name, pass, email, gender,movie) VALUES (%s,%s,%s,%s,%s )"
val = (name, password, email, gender, movie)
ob = db.cursor()
ob.execute(query, val)
db.commit()
session['name'] = request.form['name']
session['email'] = request.form['email']
return redirect(url_for('index'))
def acc():
global word
global bankid
db = MySQLdb.connect(user='******', password='', host='localhost', database='banking')
name = session['name']
ob = db.cursor()
res = ob.execute("select customerid FROM customer WHERE fname = %s", [name])
userid = ob.fetchone()
ob1 =db.cursor()
sol =ob.execute("SELECT `bankid` FROM `bank`")
banki=ob.fetchone()
bank=str(banki)
nn = (bank.replace('(', ''))
nn = (nn.replace(',', ''))
nn = (nn.replace(')', ''))
s = str(userid)
nw =(s.replace('(', ''))
nw = (nw.replace(',', ''))
nw = (nw.replace(')', ''))
word=nw
bankid=nn
session['customerid'] = word
return render_template('account.html' )
