def query_db(query, args=(), one=False): mycursor.execute(query, args) r = [ dict((mycursor.description[i][0], value) for i, value in enumerate(row)) for row in mycursor.fetchall() ] return (r[0] if r else None) if one else r
def show_users(): mycursor.execute("SELECT * FROM users") myresult = mycursor.fetchall() listt = [] for x in myresult: listt.append(x) dictt = {"Users": listt} return dictt
def test_e3_1(): sql = 'INSERT INTO users (Username, phone) VALUES (%s, %s)' val = (name, num) mycursor.execute(sql, val) mydb.commit() response = client.delete("/del/?phone=" + num) assert response.status_code == 200 assert response.json() == {"User is deleted": [name, num]} mycursor.execute("DELETE FROM users WHERE phone = %s", (num, )) mydb.commit()
def test_e2_1(): sql = 'INSERT INTO users (Username, phone) VALUES (%s, %s)' val = (name, num) mycursor.execute(sql, val) mydb.commit() response = client.get("/read/") assert response.status_code == 200 assert response.json() == {"Users": [[name, num]]} mycursor.execute("DELETE FROM users WHERE phone = %s", (num, )) mydb.commit()
def test_e1_3(): sql = 'INSERT INTO users (Username, phone) VALUES (%s, %s)' val = (name, num) mycursor.execute(sql, val) mydb.commit() response = client.post("/add/?name=" + name + "&phone=" + num) assert response.status_code == 406 assert response.json() == {"detail": "Phone Number is registered"} mycursor.execute("DELETE FROM users WHERE phone = %s", (num, )) mydb.commit()
def del_user(phone: str = Query(..., min_length=11, max_length=11, regex="^09")): mycursor.execute("SELECT * FROM users where phone = %s", (phone, )) check_user = mycursor.fetchone() if check_user is None: raise HTTPException(status_code=406, detail="User is not existed") else: mycursor.execute("DELETE FROM users WHERE phone = %s", (phone, )) mydb.commit() return {"User is deleted": check_user}
def test_e1_1(): response = client.post("/add/?name=" + name + "&phone=" + num) assert response.status_code == 200 assert response.json() == { "User added": { "Username": name, "PhoneNumber": num } } mycursor.execute("DELETE FROM users WHERE phone = %s", (num, )) mydb.commit()
def test_e4_2(): sql = 'INSERT INTO users (Username, phone) VALUES (%s, %s)' val = (name, num) mycursor.execute(sql, val) mydb.commit() response = client.put("/change_username/?name=asghar&phone=" + num) assert response.status_code == 200 assert response.json() == { "User": [name, num], "Username updated to ": "asghar" } mycursor.execute("DELETE FROM users WHERE phone = %s", (num, )) mydb.commit()
def update_username(name: str = Query(..., min_length=1, regex="^[a-z/A-Z]"), phone: str = Query(..., min_length=11, max_length=11, regex="^09")): mycursor.execute("SELECT * FROM users where phone = %s", (phone, )) check_user = mycursor.fetchone() if check_user is None: raise HTTPException(status_code=406, detail="User is not existed") else: mycursor.execute("UPDATE users SET Username = %s WHERE phone = %s", (name, phone)) mydb.commit() return {"User": check_user, "Username updated to ": name}
def add_user(name: str = Query(..., min_length=1, regex="^[a-z/A-Z]"), phone: str = Query(..., min_length=11, max_length=11, regex="^09")): mycursor.execute("SELECT * FROM users where phone = %s", (phone, )) check_phone = mycursor.fetchone() if check_phone: raise HTTPException(status_code=406, detail="Phone Number is registered") else: sql = 'INSERT INTO users (Username, phone) VALUES (%s, %s)' val = (name, phone) mycursor.execute(sql, val) mydb.commit() return {"User added": {"Username": name, "PhoneNumber": phone}}
def login(): username = request.form.get('username') password = request.form.get('password') mycursor.execute('SELECT * FROM User WHERE username = %s and password = %s', (username, password)) account = mycursor.fetchone() database.db.close() if account: session['username'] = session.form('username') return 'Logged in successfully!' else: flash('Incorrect username/password') return redirect(url_for('login'))
def create_user(name: str = typer.Argument( ..., help="Name of user you want to add"), phone: int = typer.Argument( None, help="The phone number of the user [required]")): if phone: if phone >= 9000000000 and phone <= 9999999999: num = "0" + str(phone) mycursor.execute("SELECT * FROM users where phone = %s", (num, )) check_user = mycursor.fetchone() if check_user is None: sql = 'INSERT INTO users (Username, phone) VALUES (%s, %s)' val = (name, num) mycursor.execute(sql, val) mydb.commit() ending = typer.style("User --((" + name + " " + str(phone) + "))-- Added", fg=typer.colors.GREEN, bold=True) typer.echo(ending) else: raise ValueError( typer.style("Phone number existed", fg=typer.colors.RED, bold=True)) else: raise ValueError( typer.style("Invalid phone number", fg=typer.colors.RED, bold=True)) else: num = typer.prompt("Enter your phone number [09xxxxxxxxx]?") if int(num): if re.search("^09.........", num): mycursor.execute("SELECT * FROM users where phone = %s", (num, )) check_user = mycursor.fetchone() if check_user is None: sql = 'INSERT INTO users (Username, phone) VALUES (%s, %s)' val = (name, num) mycursor.execute(sql, val) mydb.commit() ending = typer.style("User --((" + name + " " + str(num) + "))-- Added", fg=typer.colors.GREEN, bold=True) typer.echo(ending) else: raise ValueError( typer.style("Phone number existed", fg=typer.colors.RED, bold=True)) else: raise ValueError( typer.style("Invalid phone number", fg=typer.colors.RED, bold=True))
def db_backup( db_name: str = typer.Argument( ..., help="Name of db you want to backup "+ " # NOTE: if you want to make cron for this script ,"+ " you sould comment typer confirm lines" ) ): mycursor.execute( "SHOW DATABASES LIKE %s", (db_name,) ) db_check = mycursor.fetchone() # print(db_check) if db_check: backup = typer.confirm( "Are you sure you want to backup "+db_name+" ?", abort=True ) # print(backup) db_cont = config("DB_HOST") if backup == True: backup_file = './db_backup/'+db_name+date.strftime("%x").replace("/", "_") # print(backup_file) typer.echo("backup file : "+ backup_file) cmd = 'docker exec -it '+db_cont+' "mysqldump -u {0} -p {1} > {2}"'.format(config("DB_USER"),\ config("DB_NAME"),backup_file,) make_file = "touch "+backup_file os.system(make_file) os.system(cmd) ending = typer.style( "Getting backup from "+db_name, fg=typer.colors.BRIGHT_YELLOW , bold=True ) typer.echo(ending) else: raise ValueError( typer.style( "Database "+db_name+" is not existed! check you database name", fg=typer.colors.RED, bold=True ) )
def register(): #request User registration info Username = request.args.get("username") Password = request.args.get("password") Email = request.args.get("email") Role = request.args.get("role") # if this returns a user, then the email already exists in the database mycursor.execute('SELECT email from User WHERE email = %(email)s', {'email' : Email }) checkUser = mycursor.fetchall() # if a user is returned, the user is rerouted to the register page, where they can try a different email. if checkUser: flash('This email has already been registered.') return redirect(url_for('register')) #add user info to the table User add_user = "******" user_info = (Username, Password, Email, Role) database.mycursor.execute(add_user, user_info) database.db.commit() #database.db.close() return "Registration Complete"
from fastapi import FastAPI, Query, HTTPException from database import mycursor, mydb app = FastAPI() # check if table exists table = [] mycursor.execute("SHOW TABLES LIKE 'users'") for x in mycursor: table.append(x) if table == []: mycursor.execute("CREATE TABLE users (Username VARCHAR(255) NOT NULL," + "phone VARCHAR(255) NOT NULL UNIQUE)") # add user - endpoint 1 @app.post("/add/") def add_user(name: str = Query(..., min_length=1, regex="^[a-z/A-Z]"), phone: str = Query(..., min_length=11, max_length=11, regex="^09")): mycursor.execute("SELECT * FROM users where phone = %s", (phone, )) check_phone = mycursor.fetchone() if check_phone: raise HTTPException(status_code=406, detail="Phone Number is registered") else: sql = 'INSERT INTO users (Username, phone) VALUES (%s, %s)' val = (name, phone) mycursor.execute(sql, val)
from database import mycursor from surprise import Dataset from surprise import Reader from recommender import algo import pandas as pd sql = """SELECT USER_ID, FOOD_ID, RATING FROM RATINGS""" mycursor.execute(sql) all_ratings = mycursor.fetchall() user_ids = [i[0] for i in all_ratings] food_ids = [i[1] for i in all_ratings] ratings = [i[2] for i in all_ratings] ratings_dict = { "user" : user_ids, "item" : food_ids, "rating": ratings, } df = pd.DataFrame(ratings_dict) reader = Reader(rating_scale=(1, 5)) # Loads Pandas dataframe training_data = Dataset.load_from_df(df[["user", "item", "rating"]], reader) trainingSet = training_data.build_full_trainset() algo.fit(trainingSet)