def delete_user(user_id):
query = """DELETE FROM user_data WHERE id = :user_id"""
with Database() as conn:
conn.query(query, user_id=user_id)
return True
def get_users():
query = """
SELECT id, user_name, fname, lname, email, user_role, city, state
FROM user_data"""
with Database() as conn:
return conn.query(query).all()
def get_user(user_id):
query = """
SELECT user_name, fname, lname, email, user_role, city, state
FROM user_data
WHERE id = :user_id"""
with Database() as conn:
return conn.query(query, user_id=user_id).first()
def validate_username(self, username):
with Database() as conn:
query = ('SELECT user_name '
'FROM user_data '
'WHERE user_name = :username')
user = conn.query(query, username=username.data).first()
if user is not None:
raise ValidationError('Oops! Someone already has that username.')
def get_user_roles():
with Database() as conn:
query = """
SELECT row_number() over() row_number,
e.enumlabel user_role
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE n.nspname = 'public' AND t.typname = 'role'
ORDER BY 1"""
return conn.query(query).all()
def update_user(user_id, username, fname, lname, email, pw_hash, user_role,
city, state):
query = """
UPDATE user_data
SET user_name = :username, fname = :fname, lname = :lname,
email = :email, pswd = :pw_hash, user_role = :user_role,
city = :city, state = :state
WHERE id = :user_id"""
with Database() as conn:
conn.query(query,
username=username,
fname=fname,
lname=lname,
email=email,
pw_hash=pw_hash,
user_role=user_role,
city=city,
state=state,
user_id=user_id)
return True
def create_user(username, fname, lname, email, pw_hash, user_role, city,
state):
query = """
INSERT INTO user_data (user_name, fname, lname, email, pswd,
user_role, city, state) VALUES
(:username, :fname, :lname, :email, :pw_hash,
:user_role, :city, :state)"""
user_role = next(i.user_role for i in get_user_roles()
if i.row_number == user_role)
with Database() as conn:
conn.query(query,
username=username,
fname=fname,
lname=lname,
email=email,
pw_hash=pw_hash,
user_role=user_role,
city=city,
state=state)
return True
def get(cls, user_id):
with Database() as conn:
query = 'SELECT * FROM user_data WHERE id = :user_id'
return conn.query(query, user_id=user_id).first()