Spaces:
Sleeping
Sleeping
File size: 2,226 Bytes
3dfac81 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
import sqlite3
import pandas as pd
from PIL import Image
from helper_fns import pil_to_binary
# Function to add a new student to the database
def add_student(db, firstname, lastname, matric_no, image_path):
# Open the image from the given path
image = Image.open(image_path)
# Convert the PIL image to binary
image_binary = pil_to_binary(image)
# Connect to the existing database
conn = sqlite3.connect(db)
c = conn.cursor()
# Insert the new data into the table
c.execute("INSERT INTO students (first_name, last_name, matric_no, image) VALUES (?, ?, ?, ?)",
(firstname, lastname, matric_no, image_binary))
# Save (commit) the changes and close the connection
conn.commit()
conn.close()
def create_db(db_name, df):
conn = sqlite3.connect(db_name)
c = conn.cursor()
# Create the table if it doesn't exist
c.execute('''CREATE TABLE IF NOT EXISTS students
(first_name TEXT, last_name TEXT, matric_no TEXT, image BLOB)''')
for _, row in df.iterrows():
add_student(
db_name,
row['first_name'],
row['last_name'],
row['matric_no'],
row['image_path']
)
def get_student_row(db_path, last_name, matric_no):
"""
Retrieve a row from the database based on the last name and matric number.
Parameters:
db_path (str): Path to the SQLite database file.
last_name (str): The last name of the student.
matric_no (str): The matriculation number of the student.
Returns:
tuple: The row matching the last name and matric number, or None if not found.
"""
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
query = """
SELECT * FROM students
WHERE last_name = ? AND matric_no = ?
"""
cursor.execute(query, (last_name, matric_no))
row = cursor.fetchone()
conn.close()
if row:
columns = [col[0] for col in cursor.description]
return dict(zip(columns, row))
else:
return None
if __name__ == '__main__':
create_db('students_database.db', pd.read_csv('students_df.csv')) |