Python Program with Data Querying 🔍

Python Program with Data Querying 🔍

This script connects, creates, inserts data, and then executes SELECT * queries on both tables.

Python

import sqlite3
import os

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

# --- Sample Data (for insertion) ---
dept_data = [
    (10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON'),
    (50, 'MARKETING', 'SAN FRANCISCO')
]
emp_data = [
    (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),
    (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 and Data Insertion ---
cursor.execute('CREATE TABLE dept (deptno INTEGER PRIMARY KEY, dname TEXT NOT NULL, loc TEXT);')
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));')
cursor.executemany("INSERT INTO dept VALUES (?, ?, ?)", dept_data)
cursor.executemany("INSERT INTO emp VALUES (?, ?, ?, ?, ?, ?, ?, ?)", emp_data)
conn.commit()
print("Tables created and data inserted successfully.")

# ==========================================================
#                      DATA QUERYING
# ==========================================================

print("\n" + "="*50)
print("             QUERYING DATA RESULTS")
print("="*50)

# 1. Query Data from the 'dept' table
print("\n--- Department (dept) Table Records ---")
cursor.execute("SELECT deptno, dname, loc FROM dept")
# The fetchall() method retrieves all rows of the query result.
dept_records = cursor.fetchall() 

# Print the column headers
print(f"{'DEPTNO':<6} | {'DNAME':<12} | {'LOCATION':<15}")
print("-" * 37)

# Print the department records
for row in dept_records:
    # Use f-strings for formatted output
    print(f"{row[0]:<6} | {row[1]:<12} | {row[2]:<15}")


# 2. Query Data from the 'emp' table
print("\n--- Employee (emp) Table Records (First 10 for brevity) ---")
cursor.execute("SELECT empno, ename, job, sal, deptno FROM emp")
# Fetch the first 10 records for display brevity
emp_records = cursor.fetchmany(10)

# Print the column headers
print(f"{'EMPNO':<6} | {'ENAME':<10} | {'JOB':<12} | {'SALARY':<8} | {'DEPTNO':<6}")
print("-" * 47)

# Print the employee records
for row in emp_records:
    print(f"{row[0]:<6} | {row[1]:<10} | {row[2]:<12} | {row[3]:<8.2f} | {row[4]:<6}")

# -----------------------------------------------------------

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

---

Key Querying Commands Explained

• cursor.execute("SELECT ... FROM table"): Executes the SQL query. SELECT * retrieves all columns. In the example, I specified columns for cleaner output.
• cursor.fetchall(): Retrieves all rows from the result set returned by the last execute() call.
• cursor.fetchmany(10): Retrieves the next set of rows from the result set, allowing you to limit the output (I used it to show only the first 10 employee records).
• for row in records:: Iterates through the list of tuples returned by fetchall() or fetchmany(), where each tuple is a row from the database.