def create_organization_table(df, org_chart_en, org_chart_fr, tree_depth=7):
'''
Creates the organization table in the database.
'''
engine = create_engine(assemble_sqlalchemy_url(SQLAlchemyConfig))
# Get a dataframe with unique organizations
org_df = df[["org_id", "org_name_en", "org_name_fr", "dept_id", "department_en", "department_fr", "org_structure_en", "org_structure_fr"]].drop_duplicates()
# Get the paths to each org unit and store them in a table column
# TODO: try normalizing text to avoid things like capital letters preventing
# the search from being successful
org_df = generate_org_paths(org_df, org_chart_en, "en")
# # Write org_df to the database
org_df[["org_id", "org_name_en", "org_name_fr", "dept_id", "org_chart_path"]].to_sql(
"organizations",
engine,
if_exists="replace",
index=False,
chunksize=1000,
dtype={
"org_id": Integer,
"org_name_en": Text,
"org_name_fr": Text,
"dept_id": Integer,
"org_chart_path": Text,
})
return org_df
def create_department_table(df, org_chart_en, org_chart_fr):
'''
Creates the department table in the database.
'''
# Create database connection
engine = create_engine(assemble_sqlalchemy_url(SQLAlchemyConfig))
# Keep unique departments as rows
dept_df = df[["dept_id", "department_en",
"department_fr"]].drop_duplicates()
# Create column to hold serialized org chart
dept_df["org_chart_en"] = dept_df["department_en"].apply(
lambda x: get_department_org_chart(x, org_chart_en))
dept_df["org_chart_fr"] = dept_df["department_fr"].apply(
lambda x: get_department_org_chart(x, org_chart_fr))
# Now write department's dataframe to another table in our database. In the
# current use case, we only need to access each department's org chart from
# the root.
dept_df[[
"dept_id", "department_en", "department_fr", "org_chart_en",
"org_chart_fr"
]].to_sql("departments",
engine,
if_exists="replace",
index=False,
dtype={
"dept_id": Integer,
"department_en": Text,
"department_fr": Text,
"org_chart_en": Text,
"org_chart_fr": Text,
})
return dept_df
def create_organization_table(df, org_chart, tree_depth=7):
'''
Creates the organization table in the database.
'''
engine = create_engine(assemble_sqlalchemy_url(SQLAlchemyConfig))
org_df = df[["org_id", "org_name", "dept_id",
"org_structure"]].drop_duplicates()
# Get the paths to each org unit and store them in a table column
org_df = generate_org_paths(org_df, org_chart)
# Write org_df to the database
org_df[["org_id", "org_name", "dept_id",
"org_chart_path"]].to_sql("organizations",
engine,
if_exists="replace",
chunksize=1000,
dtype={
"org_id": Integer,
"org_name": Text,
"dept_id": Integer,
"org_chart_path": Text,
})
def create_employee_table(df):
'''
Creates the employees table in the database.
'''
engine = create_engine(assemble_sqlalchemy_url(SQLAlchemyConfig))
df[[
"employee_id", "last_name", "first_name", "job_title", "org_id",
"dept_id"
]].to_sql("employees",
engine,
if_exists="replace",
index=False,
chunksize=1000,
dtype={
"id": Integer,
"last_name": Text,
"first_name": Text,
"job_title": Text,
"org_id": Integer,
"dept_id": Integer,
})
def create_employee_table(df):
'''
Creates the employees table in the database.
'''
engine = create_engine(assemble_sqlalchemy_url(SQLAlchemyConfig))
# Keep only a subset of the employee dataframe
df = df[[
"employee_id", "last_name", "first_name", "job_title_en",
"job_title_fr", "phone_number", "email", "address_en", "address_fr",
"province_en", "province_fr", "city_en", "city_fr", "postal_code",
"org_id", "dept_id"
]]
# Create employees table (english)
df.to_sql("employees",
engine,
if_exists="replace",
index=False,
chunksize=1000,
dtype={
"employee_id": Integer,
"last_name": Text,
"first_name": Text,
"job_title_en": Text,
"job_title_fr": Text,
"phone_number": Text,
"email": Text,
"address_en": Text,
"address_fr": Text,
"province_en": Text,
"province_fr": Text,
"city_en": Text,
"city_fr": Text,
"postal_code": Text,
"org_id": Integer,
"dept_id": Integer,
})