def getAll(self):
conn = generic_db_connect()
cursor = conn.cursor()
query = 'select user_name, email, first_name, last_name, phone_number from users'
cursor.execute(query)
result = cursor.fetchall()
conn.close()
return result
def getByUsername(self, username):
conn = generic_db_connect()
cursor = conn.cursor()
query = 'select user_name, email, first_name, last_name, phone_number from users where user_name=?'
cursor.execute(query, (username, ))
result = cursor.fetchall()
conn.close()
return result
def add(json):
conn = generic_db_connect()
cursor = conn.cursor()
query = 'insert into resources (resource_type, resource_name, resource_description, sold) values (?,?,?,?)'
cursor.execute(
query, (json['type'], json['name'], json['description'], False))
resource_id = cursor.lastrowid
query = 'insert into supplies (supply_quantity, supply_date, resource_id, supplier_id, price) values (?,?,?,?,?)'
cursor.execute(query, (json['quantity'], json['date'], resource_id,
json['supplier_id'], json['price']))
supplier_id = cursor.lastrowid
query = 'with supply_address as (select supplier_id, first_name, last_name, phone_number, country, city, street from address join users natural inner join suppliers where users.address = address.address_id) select supply_id, resource_type, resource_name, resource_description, supply_quantity, supply_date, first_name, last_name, phone_number, country, city, street from supplies natural inner join resources natural inner join supply_address where supply_id=?'
cursor.execute(query, (supplier_id, ))
result = cursor.fetchall()
conn.close()
return result
def add(self, json):
conn = generic_db_connect()
cursor = conn.cursor()
query = "insert into address (country, city, street, district, zipcode, longitude, latitude) values (?,?,?,?,?,?,?)"
cursor.execute(
query,
(json['country'], json['city'], json['street'], json['district'],
json['zipcode'], json['longitude'], json['latitude']))
address_id = cursor.lastrowid
query = "insert into users (user_name, email, password, first_name, last_name, dob, phone_number, address) values (?,?,?,?,?,?,?,?)"
cursor.execute(query,
(json['user_name'], json['email'], json['password'],
json['first_name'], json['last_name'], json['dob'],
json['phone_number'], address_id))
conn.close()
return
def __init__(self):
self.conn = generic_db_connect()
def connectDB():
return generic_db_connect()