Example #1
0
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
Example #2
0
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()
Example #3
0
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
Example #4
0
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
Example #6
0
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"
Example #7
0
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"
Example #8
0
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
Example #9
0
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()
Example #11
0
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)
Example #12
0
 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
Example #14
0
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)
Example #15
0
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)
Example #16
0
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)
Example #17
0
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)
Example #18
0
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)
Example #19
0
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)
Example #20
0
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"
Example #21
0
 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()
Example #24
0
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
Example #25
0
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
Example #26
0
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
Example #27
0
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)
Example #28
0
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"
Example #29
0
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'))
Example #30
0
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' )