Python Program with Data Insertion 💾

Python Program with Data Insertion 💾

This revised script adds the connection and table creation steps, followed by inserting the specified number of records using SQL INSERT commands.

Python

import sqlite3
import os

# --- Configuration ---
DB_FILE = 'company.db'

# --- Sample Data ---
# 5 Department Records
dept_data = [
    (10, 'ACCOUNTING', 'NEW YORK'),
    (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'),
    (40, 'OPERATIONS', 'BOSTON'),
    (50, 'MARKETING', 'SAN FRANCISCO')
]

# 20 Employee Records (linked to the 5 departments above)
emp_data = [
    # (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, None, 20),
    (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
    (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),
    (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, None, 20),
    (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30),
    (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, None, 30),
    (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, None, 10),
    (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, None, 20),
    (7839, 'KING', 'PRESIDENT', None, '1981-11-17', 5000.00, None, 10),
    (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
    (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, None, 20),
    (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, None, 30),
    (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, None, 20),
    (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, None, 10),
    # Additional Records to reach 20 employees, using department 40 and 50
    (8001, 'JAYNE', 'SALESMAN', 7698, '1982-03-01', 1400.00, 200.00, 30),
    (8002, 'KELLY', 'ANALYST', 7902, '1982-03-05', 3200.00, None, 20),
    (8101, 'BROWN', 'ENGINEER', 7566, '1983-01-10', 4000.00, None, 40),
    (8102, 'DAVIS', 'TECHNICIAN', 7788, '1983-02-15', 3500.00, None, 40),
    (8201, 'GARCIA', 'MARKETER', 7839, '1984-06-20', 2000.00, None, 50),
    (8202, 'EVANS', 'SPECIALIST', 7782, '1984-07-01', 2100.00, None, 50)
]
# -----------------------------------------------------------

# Clean up previous file for a fresh start (Optional)
if os.path.exists(DB_FILE):
    os.remove(DB_FILE)

# Connect to the database and get a cursor
conn = sqlite3.connect(DB_FILE)
cursor = conn.cursor()
print(f"Successfully connected to the database: {DB_FILE}")

# --- Table Creation ---
# Create the 'dept' table
cursor.execute('''
    CREATE TABLE dept (
        deptno INTEGER PRIMARY KEY,
        dname TEXT NOT NULL,
        loc TEXT
    );
''')
# Create the 'emp' table
cursor.execute('''
    CREATE TABLE emp (
        empno INTEGER PRIMARY KEY,
        ename TEXT NOT NULL,
        job TEXT,
        mgr INTEGER,
        hiredate TEXT,
        sal REAL,
        comm REAL,
        deptno INTEGER,
        FOREIGN KEY (deptno) REFERENCES dept (deptno)
    );
''')
print("Tables 'dept' and 'emp' created.")

# --- Data Insertion ---

# 1. Insert 5 Dept Records
print(f"Inserting {len(dept_data)} department records...")
cursor.executemany(
    "INSERT INTO dept VALUES (?, ?, ?)",
    dept_data
)
print("5 dept records inserted successfully.")

# 2. Insert 20 Emp Records
print(f"Inserting {len(emp_data)} employee records...")
cursor.executemany(
    "INSERT INTO emp VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
    emp_data
)
print("20 emp records inserted successfully.")

# Commit the changes permanently
conn.commit()
print("All data insertion committed successfully.")

# --- Verification (Optional, but useful) ---
cursor.execute("SELECT COUNT(*) FROM dept")
print(f"Total records in dept table: {cursor.fetchone()[0]}")

cursor.execute("SELECT COUNT(*) FROM emp")
print(f"Total records in emp table: {cursor.fetchone()[0]}")
# ---------------------------------------------

# Close the connection
conn.close()
print("Database connection closed.")