SQL and Python: applying programming languages in machine learning, software development, and science careers

# Create database dictionary from ERD

database_dict = {
    # Create the students table
    'students': {
        'fields': [
            ('student_id', 'INTEGER PRIMARY KEY'),
            ('first_name', 'TEXT NOT NULL'),
            ('last_name', 'TEXT NOT NULL')
        ],
        'entries': [
            (1, 'John', 'Doe'),
            (2, 'Jane', 'Doe'),
            (3, 'Bob', 'Smith')
        ]
    },
    # Create the student_contact table
    'student_contact': {
        'fields': [
            ('student_id', 'INTEGER NOT NULL'),
            ('email_address', 'TEXT NOT NULL'),
        ],
        'entries': [
            (1, '[email protected]'),
            (2, '[email protected]'),
            (3, '[email protected]')
        ],
        "foreign_keys": [
            ("student_id", "students(student_id)")
        ]
    },
    # Create the staff table
    'staff': {
        'fields': [
            ('staff_id', 'INTEGER PRIMARY KEY'),
            ('staff_first_name', 'TEXT NOT NULL'),
            ('staff_last_name', 'TEXT NOT NULL')
        ],
        'entries': [
            (1, 'Mark', 'Johnson'),
            (2, 'Amy', 'Lee'),
            (3, 'David', 'Wong')
        ]
    },
    # Create the staff_contact table
    'staff_contact': {
        'fields': [
            ('staff_id', 'INTEGER NOT NULL'),
            ('staff_email_address', 'TEXT NOT NULL'),
        ],
        'entries': [
            (1, '[email protected]'),
            (2, '[email protected]'),
            (3, '[email protected]')
        ],
        "foreign_keys": [
            ("staff_id", "staff(staff_id)")
        ]
    },
    # Create the courses table
    'courses': {
        'fields': [
            ('course_id', 'INTEGER PRIMARY KEY'),
            ('course_name', 'TEXT NOT NULL'),
            ('staff_id', 'INTEGER NOT NULL')
        ],
        'entries': [
            (1, 'Mathematics', 1),
            (2, 'Science', 3),
            (3, 'English', 2)
        ]
    },
    # Create the enrollment table
    'enrollment': {
        'fields': [
            ('enrollment_id', 'INTEGER PRIMARY KEY'),
            ('student_id', 'INTEGER NOT NULL'),
            ('course_id', 'INTEGER NOT NULL'),
            ('grade', 'INTEGER NOT NULL')
        ],
        'entries': [
            (1, 1, 1, 80),
            (2, 1, 2, 85),
            (3, 2, 1, 92),
            (4, 2, 3, 77),
            (5, 3, 1, 65),
            (6, 3, 2, 95),
            (7, 3, 3, 70),
            (8, 1, 3, 81),
            (9, 2, 2, 62)
        ],
        "foreign_keys": [
            ("student_id", "students(student_id)"),
            ("course_id", "courses(course_id)")
        ]
    }
}

%reload_ext sql
import sqlite3

# Connect to/or create the database
conn = sqlite3.connect('school.db')

# Create a cursor object
cur = conn.cursor()

# Iterate through each table in the dictionary and insert the values into the database
for table_name, table_dict in database_dict.items():
    # Create the table by CREATE TABLE SQL command
    fields = ', '.join([f'{field[0]} {field[1]}' for field in table_dict['fields']])
    cur.execute(f'CREATE TABLE IF NOT EXISTS {table_name} ({fields})')

    # Insert the entries into the table by executing the INSERT SQL command
    entries = ', '.join([str(entry) for entry in table_dict['entries']])
    cur.execute(f'INSERT OR IGNORE INTO {table_name} VALUES {entries}')

# Commit the changes and close the connection
conn.commit()
conn.close()

from sqlalchemy import create_engine

# Create a connection to the school.db file
engine = create_engine('sqlite:///school.db')

# Execute an SQL
query = """
SELECT * FROM students;
"""
result = engine.execute(query)

print(*result.fetchall(), sep='\n')

# Close the session
engine.dispose()

# Connect to the school database
conn = sqlite3.connect('school.db')

# Create a cursor object
cur = conn.cursor()

# Execute SQL query
query = """
UPDATE students
SET first_name = 'Kate'
WHERE first_name = 'Jane';
"""
cur.execute(query)

query = """
UPDATE student_contact
SET email_address = '[email protected]'
WHERE student_id = 2;
"""
cur.execute(query)

# Commit and close connection
conn.commit()
conn.close()

# Connect to the school database
conn = sqlite3.connect('school.db')

# Create a cursor object
cur = conn.cursor()

# Execute SQL query
result = cur.execute("SELECT * FROM students;")

print(*result.fetchall(), sep='\n')
print()

result = cur.execute("SELECT * FROM student_contact;")

print(*result.fetchall(), sep='\n')

# Close connection
conn.close()




(1, 'John', 'Doe')
(2, 'Kate', 'Doe')
(3, 'Bob', 'Smith')

(1, '[email protected]')
(2, '[email protected]')
(3, '[email protected]')