def with_email_address():
"""
returns DataFrame of PEOPLE_CODE_ID's with non-NULL HOME email_addresses
"""
connection = local_db.connection()
sql_str = ("SELECT PEOPLE_ORG_CODE_ID FROM ADDRESS WHERE " +
"ADDRESS_TYPE = 'HOME' " + "AND EMAIL_ADDRESS IS NOT NULL " +
"AND EMAIL_ADDRESS LIKE '%@%' ")
with_address = pd.read_sql_query(sql_str, connection)
with_address = with_address.drop_duplicates(["PEOPLE_ORG_CODE_ID"])
with_address = with_address.rename(
columns={"PEOPLE_ORG_CODE_ID": "PEOPLE_CODE_ID"})
return with_address
def active_students():
"""
returns DataFrame of active student IDs
Active Students are those that have been enrolled in last two years.
"""
connection = local_db.connection()
today = date.today()
two_years_ago = today.year - 2
sql_str = ("SELECT PEOPLE_CODE_ID FROM ACADEMIC WHERE " +
f"ACADEMIC_YEAR > '{two_years_ago}' " +
"AND PRIMARY_FLAG = 'Y' " + "AND CURRICULUM NOT IN ('ADVST') " +
"AND GRADUATED NOT IN ('G') ")
active = pd.read_sql_query(sql_str, connection)
active = active.drop_duplicates(["PEOPLE_CODE_ID"])
return active
import numpy as np
import pandas as pd
from datetime import date, datetime
from pathlib import Path
output_path = Path(
r"\\psc-data\E\Applications\Starfish\Files\workingfiles\sections")
sfn_output = output_path / "sections.txt"
tfn_output = output_path / "teaching.txt"
catalog_path = Path(
r"\\psc-data\E\Applications\Starfish\Files\workingfiles\course_catalog")
catalog_fn = catalog_path / "course_catalog.txt"
# local connection information
import local_db
connection = local_db.connection()
sections_begin_year = "2011"
sql_str = ("SELECT * FROM SECTIONS WHERE " +
f"ACADEMIC_YEAR >= '{sections_begin_year}' " +
"AND ACADEMIC_TERM IN ('FALL', 'SPRING', 'SUMMER') " +
"AND ACADEMIC_SESSION IN ('MAIN', 'CULN', 'EXT', 'FNRR', 'HEOP'," +
" 'SLAB', 'BLOCK A', 'BLOCK AB', 'BLOCK B') ")
df_sections = pd.read_sql_query(sql_str, connection)
df = df_sections[[
"EVENT_ID",
"EVENT_SUB_TYPE",
"EVENT_MED_NAME",
"SECTION",
