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)
