def delete_user(user_id):
mysql = connectToMySQL('users_schema')
query = "DELETE FROM users WHERE id = %(id)s;"
data = {'id': user_id}
connectToMySQL('users_schema').query_db(query, data)
return redirect('/')
def new(cls, info):
query = "INSERT INTO users (first_name, last_name, email, pw_hash) VALUES (%(first_name)s,%(last_name)s,%(email)s,%(pw_hash)s)"
data = {
"first_name": info['first_name'],
"last_name": info['last_name'],
"email": info['email'],
"pw_hash": info['pw_hash'],
}
new_user_id = connectToMySQL(db).query_db(query, data)
query = "SELECT * FROM users WHERE id = %(new_user_id)s"
data = {"new_user_id": new_user_id}
new_user = connectToMySQL(db).query_db(query, data)[0]
return new_user
def get_all(cls):
query = "SELECT * FROM artist"
artist_from_db = connectToMySQL('songinfo').query_db(query)
artist = []
for a in artist_from_db:
artist.append(cls(a))
return artist
def get_recipe_by_account(cls, data):
query = 'SELECT * FROM recipe WHERE account_id=%(id)s;'
recipes_from_db = connectToMySQL('recipe').query_db(query, data)
recipes = []
for r in recipes_from_db:
recipes.append(cls(r))
return recipes
def get_pypie_by_id(cls, data):
query = 'SELECT * FROM pypie WHERE id=%(pypie_id)s;'
pypie_from_db = connectToMySQL('pypie').query_db(query, data)
pypie = []
for p in pypie_from_db:
pypie.append(cls(p))
return pypie
def get_all_pies(cls):
query = 'SELECT p.id, p.name, p.vote,a.first_name, a.last_name FROM pypie p JOIN account a ON p.account_id=a.id ORDER BY p.vote DESC;'
all_pypies = connectToMySQL('pypie').query_db(query)
#pypies = []
#for p in all_pypies:
#pypies.append(cls(p))
return all_pypies
def update_pie_info(cls, data):
query = "UPDATE pypie SET pie_name = %(pie_name)s, filling = %(filling)s, crust = %(crust)s WHERE id = %(id)s;"
updated_pie = connectToMySQL('pypie_derby').query_db(query, data)
return updated_pie
def get_by_email(cls,data):
query = "SELECT * FROM users WHERE email = %(email)s;"
result = connectToMySQL("car_schema").query_db(query,data)
# Didn't find a matching user
if len(result) < 1:
return False
return cls(result[0])
def get_all(cls):
query = "SELECT * FROM emails;"
results = connectToMySQL('email_db').query_db(query)
emails = []
for email in results:
emails.append(cls(email))
return emails
def validate_registration(data):
is_valid = True
if len(data['first_name']) < 2 or len(data['first_name']) > 45:
is_valid = False
flash('Try Again - Name should be between 2 - 45 characters.')
if len(data['last_name']) < 2 or len(data['last_name']) > 45:
is_valid = False
flash('Try Again - Last name should be between 2 - 60 characters.')
if not EMAIL_REGEX.match(data['email']):
is_valid = False
flash("Try Again - Invalid email address!")
query = 'SELECT * FROM registrations WHERE email = %(email)s;' #validate if email is in use
result = connectToMySQL('registrations').query_db(query, data)
if len(result) != 0:
is_valid = False
flash('Error: Please sign in.')
if len(data['password']) < 8:
is_valid = False
flash(
'Invalid Password - Try Again - Password must be 8+ characters.'
)
if data['password'] != data['confirm_password']:
is_valid = False
flash('Passwords do not match. Please Try Again.')
return is_valid
def get_one_by_email(cls, email):
mysql = connectToMySQL("recipes_schema")
query = 'SELECT * FROM users WHERE email = %(users_email)s;'
data = {"users_email": email}
one_user = mysql.query_db(query, data)
print(one_user)
return one_user
def edit_user(user_id):
mysql = connectToMySQL('users_schema')
query2 = "SELECT * FROM users WHERE id = %(id)s;"
data2 = {'id': user_id}
user = mysql.query_db(query2, data2)[0]
return render_template("edit.html", user=user)
def get_all(cls):
query = "SELECT * FROM messages;"
results = connectToMySQL('wall_db').query_db(query)
messages = []
for message in results:
messages.append(cls(message))
return messages
def show_books(cls):
query = 'SELECT * FROM books'
results = connectToMySQL('books').query_db(query)
books = []
for b in results:
books.append(cls(b))
return books
def get_all(cls):
query = "SELECT * from authors;"
results = connectToMySQL('books_schema').query_db(query)
authors = []
for author in results:
authors.append(cls(author))
return authors
def validate_registeration(data):
is_valid = True
EMAIL_REGEX = re.compile(
r'^[a-zA-Z0-9.+_-]+@[a-zA-Z0-9._-]+\.[a-zA-Z]+$')
if len(data['first_name']) < 3 or len(data['first_name']) > 30:
is_valid = False
flash("First name should be between 3 and 30 characters.")
if len(data['last_name']) < 3 or len(data['last_name']) > 30:
is_valid = False
flash("Last name should be between 3 and 30 characters.")
if not EMAIL_REGEX.match(data['email']):
flash("Invalid email address!")
is_valid = False
mysql = connectToMySQL('user_login_registration_schema')
query = "SELECT * FROM users WHERE email = %(email)s;"
results = mysql.query_db(query, data)
if len(results) != 0:
is_valid = False
flash('Email is already in use!')
if len(data['password']) < 6:
is_valid = False
flash("Password should be at least 6 characters.")
if data['password'] != data['confirm_password']:
is_valid = False
flash("Password and Confirm Password does not match.")
return is_valid
def get_all(cls):
dojos = []
query = "SELECT * FROM dojos;"
dojos_from_db = connectToMySQL('dojos_and_ninjas_schema').query_db(query)
for dojo in dojos_from_db:
dojos.append(cls(dojo))
return dojos
def get_one(cls,data):
mysql = connectToMySQL("user_schema")
query = "SELECT * FROM users where id = %(id)s;"
new_user = mysql.query_db(query,data)
print(new_user)
return new_user
def get_one_dojo(cls,data):
query = "SELECT * FROM dojos WHERE id = %(id)s;"
results = connectToMySQL("dojos_and_ninjas").query_db(query, data)
dojo_obj = cls(results[0])
print(dojo_obj, '==============================================')
return dojo_obj
def get_all(cls):
query = "SELECT * from books;"
results = connectToMySQL('books_schema').query_db(query)
books = []
for book in results:
books.append(cls(book))
return books
def allDojos(cls):
query = 'SELECT * FROM dojos;'
result = connectToMySQL('dojosAndNinjas').query_db(query)
dojoList = []
for dojo in result:
dojoList.append(cls(dojo))
return dojoList
def retrieve(cls, **data):
'''
Retrieves everything from the database that matches the given data in the form of a list.
If no parameters are given, everything from that table will be returned.
Example usages:
--------------
``User.retrieve() -> returns a list of all users``
``User.retrieve(id=1) -> returns a single user matching the id``
``User.retrieve(name="John") -> returns a list of all users with the name "John"``
Parameters
----------
data (**str) : Key word arguments for each of the column names and the values to try and match.
Returns
-------
List of class instances created from the matching rows in the database.
'''
cols, vals = cls.format_data(data.keys())
query = f"SELECT * FROM `{cls.table}` {'WHERE'+' AND'.join(f' {col}={val}' for col,val in zip(cols,vals)) if data else ''}"
return [
cls(**item) for item in connectToMySQL(db).query_db(query, data)
]
def create(cls, data):
query = """
INSERT INTO users (first_name, last_name, email, password, created_at, updated_at)
VALUES (%(first_name)s, %(last_name)s, %(email)s, %(password)s, NOW(), NOW());
"""
return connectToMySQL(cls.schema).query_db(query, data)
def get_account_by_id(cls, data):
query = 'SELECT * FROM account WHERE id=%(id)s;'
accounts_from_db = connectToMySQL('recipe').query_db(query, data)
accounts = []
for a in accounts_from_db:
accounts.append(cls(a))
return accounts
def get_pypie_by_account(cls, data):
query = 'SELECT * FROM pypie WHERE account_id=%(id)s;'
pypies_from_db = connectToMySQL('pypie').query_db(query, data)
pypies = []
for p in pypies_from_db:
pypies.append(cls(p))
return pypies
def get_account_exclude_id(cls, data):
query = 'SELECT * FROM account WHERE id <> %(id)s ORDER BY first_name;'
account_list = connectToMySQL('recipe').query_db(query, data)
accounts = []
for a in account_list:
accounts.append(cls(a))
return accounts
def get_user_and_friends(cls):
query = "SELECT * FROM users JOIN friendships ON \
users.id = friendships.user_id JOIN users as user2 \
ON user2.id = friendships.friend_id ORDER BY users.last_name"
results = connectToMySQL('friendship_schema').query_db(query)
return results
def get_account_by_email(cls, data):
query = 'SELECT * FROM account WHERE email=%(email)s;'
account = connectToMySQL('recipe').query_db(query, data)
#accounts = []
#for a in account:
#accounts.append(cls(a))
return account
def get_recipe_by_id(cls, data):
query = 'SELECT * FROM recipe WHERE id=%(recipe_id)s;'
recipe_from_db = connectToMySQL('recipe').query_db(query, data)
recipe = []
for r in recipe_from_db:
recipe.append(cls(r))
return recipe
def validate_registration(data):
is_valid = True
EMAIL_REGEX = re.compile(
r'^[a-zA-Z0-9.+_-]+@[a-zA-Z0-9._-]+\.[a-zA-Z]+$')
if len(data['first_name']) < 3 or len(data['first_name']) > 45:
is_valid = False
flash('First Name should be 3 to 45 characters')
if len(data['last_name']) < 3 or len(data['last_name']) > 45:
is_valid = False
flash('Last Name should be 3 to 45 characters')
if not EMAIL_REGEX.match(data['email']):
is_valid = False
flash('Please provide a valid Email')
connection = connectToMySQL('login_reg')
query = "SELECT * FROM users WHERE email = %(email)s"
results = connection.query_db(query, data)
if len(results) != 0:
is_valid = False
flash('Email is already in use')
if len(data['password']) < 8:
flash('Password should be at least eight characters long')
if data['password'] != data['confirm_password']:
is_valid = False
flash('Password and Confirm Password fields should match')
return is_valid
