import pymysql.cursors
from flask import current_app


def get_db():
    db = pymysql.connect(
        host='localhost',
        user='figo',
        password='scolari',
        database='db_figo',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)
    return db

def show():
    db = get_db().cursor()

    query = 'SELECT * FROM stud'
    db.execute(query)         
    return db.fetchall()

def get(roll_no):
    db = get_db().cursor()

    query = f"SELECT * FROM stud WHERE roll_no='{roll_no}'"
    db.execute(query)    
    return db.fetchone()

def insert(data):
    error = None
    try:
        db = get_db()
        db.cursor().execute(
            'INSERT INTO stud (Name, roll_no)'
            ' VALUES (%s, %s)',
            (data['name'], data['roll_no'])
        )
        db.commit()
    except db.IntegrityError:
        error = f"User number {data['roll_no']} is already registered."
    return error

def delete(roll_no):
    db = get_db()
    db.cursor().execute('DELETE FROM stud WHERE roll_no=%s', (roll_no))
    db.commit()
    return

def update(data,old_roll_no):
    error= None
    try:
        db = get_db()
        db.cursor().execute('UPDATE stud SET roll_no=%s, Name=%s WHERE roll_no=%s', (data['roll_no'],data['name'], old_roll_no))
        db.commit()
    except db.IntegrityError:
        error = f"User number {data['roll_no']} is already registered."    
    return error