def add_states_district(self, state_id, district_id):
add_states_qs = '''insert into state_district (state_id, district_id) values ('{state_id}', '{district_id}') RETURNING id'''
cursor.execute(
add_states_qs.format(state_id=state_id, district_id=district_id))
row = cursor.fetchone()
if row:
conn.commit()
return row['id']
def check_user_exists(self, username):
query = "SELECT count(*) FROM auth WHERE username='******';"
cursor.execute(query.format(username))
row = cursor.fetchone()
if row['count'] > 0:
return True
return False
def add_states(self, name, code):
# values = ', '.join(map(str, address_data))
try:
add_states_qs = '''insert into state (name, code) values ('{name}', '{code}') on conflict (code) DO UPDATE SET name='{name}' RETURNING id'''
cursor.execute(add_states_qs.format(name=name, code=code))
row = cursor.fetchone()
if row:
conn.commit()
return row['id']
except Exception as e:
print(e)
def get_user_info_from_user_id(self, user_id: int, cursor=cursor):
try:
user_profile_qs = '''select a.username, up.email, up.mobile_number, a.group_id
from auth as a join userprofile as up
on a.id = up.user_id
where a.id={user_id};'''
cursor.execute(user_profile_qs.format(user_id=user_id))
row = cursor.fetchone()
return row
except Exception:
raise DaoExceptionError(message="error in user profile info dao",
status_code=400)
def group_user_permission(self, group_id: int, permisstion_name: str):
try:
group_permission_query = f"select * from group_permission as gp join permission as p on p.id=gp.permission_id where group_id={group_id} and p.code='{permisstion_name}'"
cursor.execute(group_permission_query)
group_permission_data = cursor.fetchone()
return group_permission_data
except Exception as e:
print(e)
raise DaoExceptionError(
message="Error in featch data from user permissions",
status_code=403)
def create_user_and_user_profile(self, user_data, user_profile_data, cursor=None):
try:
# create or get user ID
query = """WITH ins as (Insert into auth (username, password, group_id) values ('{username}', '{password}', (SELECT id from "group" WHERE code='{group}')) on conflict (username) do nothing RETURNING *)
select id from ins union select id from auth where username='******'"""
cursor.execute(query.format(**user_data))
user_profile_data['user_id'] = cursor.fetchone()['id']
user_profile_query = "Insert into userprofile (user_id, email, mobile_number, is_active) values('{user_id}', '{email}', \
'{mobile_number}', {is_active}) ON CONFLICT (user_id) DO NOTHING;".format(**user_profile_data)
cursor.execute(user_profile_query)
return True
except Exception as e:
print(e)
raise RegisterUserDaoException(message="error in user register dao", status_code=404)
# https://www.psycopg.org/docs/cursor.html