File size: 2,489 Bytes
3a32bd4 | 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 | import sqlite3
import os
def init_database():
"""Initialize the SQLite database with sample certificate records."""
db_path = "certs.db"
# Remove existing database if it exists
if os.path.exists(db_path):
os.remove(db_path)
print(f"Removed existing database: {db_path}")
# Create connection and table
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Create table
cursor.execute('''
CREATE TABLE IF NOT EXISTS certificates(
reg_no TEXT PRIMARY KEY,
name TEXT,
institution TEXT,
degree TEXT,
year INTEGER,
notes TEXT
)
''')
# Sample data
sample_data = [
('ABC2023001', 'Saksham Sharma', 'DevLabs Institute', 'B.Tech Computer Engg', 2023, 'Sample student'),
('ABC2022007', 'Prisha Verma', 'Global Tech University', 'M.Tech AI', 2022, 'Sample student'),
('UNI10009', 'Rajeev Kumar', 'Northfield University', 'B.Sc Physics', 2019, 'Sample student'),
('INSTX-555', 'Anita Desai', 'Sunrise Polytechnic', 'Diploma Civil', 2021, 'Sample student'),
('COLL-7788', 'John Doe', 'WestEnd College', 'BBA', 2020, 'Sample placeholder'),
('CERT-9001', 'Maya Iyer', 'Metro University', 'MSc Maths', 2018, 'Sample student'),
('REG-2021-345', 'Ram Singh', 'City College', 'BCom', 2021, 'Sample student'),
('EDU-3333', 'Nina Gupta', 'Coastal Institute', 'BCA', 2025, 'Sample student'),
('COL-1212', 'Alex Wong', 'Global Tech University', 'B.Tech ECE', 2022, 'Sample student'),
('STU-0007', 'Liu Chen', 'International Academy', 'PhD Chemistry', 2020, 'Sample student')
]
# Insert sample data
cursor.executemany('''
INSERT OR IGNORE INTO certificates
(reg_no, name, institution, degree, year, notes)
VALUES (?, ?, ?, ?, ?, ?)
''', sample_data)
conn.commit()
print(f"Database initialized with {cursor.rowcount} records")
# Verify data
cursor.execute("SELECT COUNT(*) FROM certificates")
count = cursor.fetchone()[0]
print(f"Total records in database: {count}")
# Show sample records
cursor.execute("SELECT * FROM certificates LIMIT 3")
sample_records = cursor.fetchall()
print("\nSample records:")
for record in sample_records:
print(f" {record}")
conn.close()
print(f"\nDatabase '{db_path}' created successfully!")
if __name__ == "__main__":
init_database()
|