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",