def get_all_employees():
with sqlite3.connect("./kennel.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.id,
e.name,
e.location_id,
l.name location_name,
l.address location_address
FROM Employee e
JOIN Location l
ON l.id = e.location_id""")
employees = []
dataset = db_cursor.fetchall()
for row in dataset:
employee = Employee(row['id'], row['name'], row['location_id'])
location = Location(row['location_id'], row['location_name'], row['location_address'])
employee.location = location.__dict__
employees.append(employee.__dict__)
return json.dumps(employees)
def get_single_employee(id):
with sqlite3.connect("./kennel.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Use a ? parameter to inject a variable's value
# into the SQL statement.
db_cursor.execute(
"""
SELECT
e.id,
e.name,
e.address,
e.location_id,
l.name location_name,
l.address location_address
FROM employee e
JOIN Location l
ON l.id = e.location_id
WHERE e.id = ?
""", (id, ))
# Load the single result into memory
data = db_cursor.fetchone()
# Create an employee instance from the current row
employee = Employee(data['id'], data['name'], data['address'],
data['location_id'])
# Create a Location instance from the current row
location = Location(data['id'], data['location_name'],
data['location_address'])
# Add the dictionary representation of the location to the animal
employee.location = location.__dict__
return json.dumps(employee.__dict__)
def get_single_employee(id):
with sqlite3.connect("./kennel.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
a.id,
a.name,
a.address,
a.location_id,
l.name location_name,
l.address location_address
FROM employee a
JOIN location l
ON l.id = a.location_id
WHERE a.id = ?
""", (id))
data = db_cursor.fetchone()
employee = Employee(data['id'], data['name'], data['address'],
data['location_id'])
location = Location(data['id'], data['location_name'],
data['location_address'])
employee.location = location.__dict__
return json.dumps(employee.__dict__)
def get_employees_by_location(location_id):
with sqlite3.connect("./kennel.db") as conn:
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Write the SQL query to get the information you want
db_cursor.execute(
"""
select
e.id,
e.name,
e.location_id
from Employee e
WHERE e.location_id = ?
""", (location_id, ))
employees = []
dataset = db_cursor.fetchall()
for row in dataset:
employee = Employee(row['id'], row['name'], row['location_id'])
location = Location(row['location_name'], row['location_address'])
employee.location = location.__dict__
employees.append(employee.__dict__)
return json.dumps(employees)
def get_all_employees():
# Open a connection to the database
with sqlite3.connect("../../kennel.db") as conn:
# Just use these. It's a Black Box.
conn.row_factory = sqlite3.Row
db_cursor = conn.cursor()
# Write the SQL query to get the information you want
db_cursor.execute("""
SELECT
e.id,
e.name,
e.address,
e.location_id,
l.name location_name,
l.address location_address
FROM Employee e
JOIN Location l
ON l.id = e.location_id
""")
# Initialize an empty list to hold all employee representations
employees = []
# Convert rows of data into a Python list
dataset = db_cursor.fetchall()
# Iterate list of data returned from database
for row in dataset:
# Create an employee instance from the current row.
# Note that the database fields are specified in
# exact order of the parameters defined in the
# Employees class above.
employee = Employee(row['id'], row['name'], row['address'],
row['location_id'])
location = Location(row['location_id'], row['location_name'],
row['location_address'])
# remove the location id from the results
del location.__dict__['id']
employee.location = location.__dict__
employees.append(employee.__dict__)
# Use `json` package to properly serialize list as JSON
return json.dumps(employees)
