Spaces:
Runtime error
Runtime error
File size: 3,375 Bytes
1914b78 | 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 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | import sqlite3
import random
def create_sample_db(db_path="sample_project.db"):
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Drop tables if exist
cursor.executescript("""
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;
""")
# Create tables
cursor.executescript("""
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
marks INTEGER
);
CREATE TABLE courses (
course_id INTEGER PRIMARY KEY,
course_name TEXT
);
CREATE TABLE enrollments (
id INTEGER PRIMARY KEY,
student_id INTEGER,
course_id INTEGER,
FOREIGN KEY(student_id) REFERENCES students(id),
FOREIGN KEY(course_id) REFERENCES courses(course_id)
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
student_id INTEGER,
amount INTEGER,
FOREIGN KEY(student_id) REFERENCES students(id)
);
""")
# Insert students (20 records)
names = ["Rohit", "Amit", "Neha", "Priya", "Karan", "Simran", "Raj", "Anjali", "Vikram", "Sneha"]
students = []
for i in range(1, 21):
students.append((
i,
random.choice(names) + str(i),
random.randint(18, 25),
random.randint(50, 100)
))
cursor.executemany("INSERT INTO students VALUES (?, ?, ?, ?)", students)
# Insert courses
courses = [
(1, "Math"),
(2, "Science"),
(3, "History"),
(4, "Computer Science")
]
cursor.executemany("INSERT INTO courses VALUES (?, ?)", courses)
# Insert enrollments (30 records)
enrollments = []
for i in range(1, 31):
enrollments.append((
i,
random.randint(1, 20),
random.randint(1, 4)
))
cursor.executemany("INSERT INTO enrollments VALUES (?, ?, ?)", enrollments)
# Insert orders (25 records)
orders = []
for i in range(1, 26):
orders.append((
i,
random.randint(1, 20),
random.randint(100, 2000)
))
cursor.executemany("INSERT INTO orders VALUES (?, ?, ?)", orders)
conn.commit()
conn.close()
print(f"✅ Database created: {db_path}")
# Run this
# create_sample_db()
def export_to_sql(db_path="sample_project.db", sql_path="sample_project.sql"):
conn = sqlite3.connect(db_path)
with open(sql_path, "w") as f:
for line in conn.iterdump():
f.write(f"{line}\n")
conn.close()
print(f"✅ Exported to {sql_path}")
return
# Run this
# export_to_sql()
import sqlite3
def extract_schema(db_path):
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
cursor.execute("""
SELECT name FROM sqlite_master
WHERE type='table' AND name NOT LIKE 'sqlite_%';
""")
tables = cursor.fetchall()
schema_chunks = []
for (table_name,) in tables:
cursor.execute(f"PRAGMA table_info({table_name});")
columns = cursor.fetchall()
col_names = [col[1] for col in columns]
chunk = f"Table: {table_name} ({', '.join(col_names)})"
schema_chunks.append(chunk)
conn.close()
return schema_chunks
print(extract_schema("sample_project.sql"))
|