File size: 3,970 Bytes
a1e6f72 d7d98fc a1e6f72 d7d98fc a1e6f72 d7d98fc a1e6f72 | 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 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | import sqlite3
from faker import Faker
import random
def generate_svnr():
"""
Generates a valid Austrian social security number (SVNR).
"""
weights = [3, 7, 9, 0, 5, 8, 4, 2, 1, 6]
while True:
# Generate a 9-digit random number for the main part, ensuring the first digit is not 0.
main_part = str(random.randint(1, 9)) + "".join([str(random.randint(0, 9)) for _ in range(8)])
# Calculate the checksum from the main part
partial_sum = (int(main_part[0]) * weights[0] +
int(main_part[1]) * weights[1] +
int(main_part[2]) * weights[2] +
int(main_part[3]) * weights[4] +
int(main_part[4]) * weights[5] +
int(main_part[5]) * weights[6] +
int(main_part[6]) * weights[7] +
int(main_part[7]) * weights[8] +
int(main_part[8]) * weights[9])
checksum = partial_sum % 11
# The checksum must be a single digit. If it's 10, the number is invalid, so we regenerate.
if checksum < 10:
svnr = main_part[:3] + str(checksum) + main_part[3:]
return svnr
def setup_database():
"""
Initializes and populates the SQLite database with synthetic data.
"""
conn = sqlite3.connect('database/university.db')
cursor = conn.cursor()
# Create tables
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
svnr TEXT NOT NULL UNIQUE
)
''')
cursor.execute('''
CREATE TABLE IF NOT EXISTS faculty (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
department TEXT NOT NULL
)
''')
cursor.execute('''
CREATE TABLE IF NOT EXISTS courses (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
faculty_id INTEGER,
FOREIGN KEY (faculty_id) REFERENCES faculty (id)
)
''')
cursor.execute('''
CREATE TABLE IF NOT EXISTS enrollments (
student_id INTEGER,
course_id INTEGER,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students (id),
FOREIGN KEY (course_id) REFERENCES courses (id)
)
''')
# Populate with synthetic data
fake = Faker()
# Add students
for _ in range(500):
try:
cursor.execute("INSERT INTO students (name, email, svnr) VALUES (?, ?, ?)",
(fake.name(), fake.email(), generate_svnr()))
except sqlite3.IntegrityError:
pass
# Add faculty
for _ in range(100):
try:
cursor.execute("INSERT INTO faculty (name, email, department) VALUES (?, ?, ?)",
(fake.name(), fake.email(), fake.job()))
except sqlite3.IntegrityError:
pass
# Add courses
faculty_ids = [row[0] for row in cursor.execute("SELECT id FROM faculty").fetchall()]
for _ in range(200):
cursor.execute("INSERT INTO courses (name, faculty_id) VALUES (?, ?)",
(fake.bs(), fake.random_element(elements=faculty_ids)))
# Add enrollments
student_ids = [row[0] for row in cursor.execute("SELECT id FROM students").fetchall()]
course_ids = [row[0] for row in cursor.execute("SELECT id FROM courses").fetchall()]
for _ in range(1500):
try:
cursor.execute("INSERT INTO enrollments (student_id, course_id) VALUES (?, ?)",
(fake.random_element(elements=student_ids), fake.random_element(elements=course_ids)))
except sqlite3.IntegrityError:
pass
conn.commit()
conn.close()
print("Database setup complete. 'university.db' created and populated.")
if __name__ == "__main__":
setup_database()
|