# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# +
import sqlalchemy
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, inspect, func
from sqlalchemy import Column, Float, Integer, String, Date
from sqlalchemy.ext.declarative import declarative_base
Base2 = declarative_base()

import pandas as pd
import numpy as np
import getpass
import sqlalchemy as db

from matplotlib import style
import matplotlib.pyplot as plt

# -

# connect to local database
from sqlalchemy import create_engine

pa = getpass.getpass("postgres user password: ")

engine = create_engine(f'postgresql://postgres:{pa}@localhost:5432/SQL_Challenge_db')
connection = engine.connect()
metadata = db.MetaData()

departments = pd.read_sql('select * from departments', connection)
departments

### Import the Employees table"
employees = pd.read_sql('select * from employees', connection)
employees.head()

#Convert salaries table to dataFrame
salary = pd.read_sql_query('select * from salaries',connection)
salary.head()

#Convert title table to dataFrame
title = pd.read_sql_query('select * from titles',connection)
title.head()

#by looking at the emp_no values it would appear that they do.
salary.describe()

# Create the inspector and connect it to the engine
inspector = inspect(engine)

# Collect the names of tables within the database
inspector.get_table_names()

# Using the inspector to print the column names within the 'employees' table and its types
columns = inspector.get_columns('employees')
for column in columns:
    print(column["name"], column["type"])


#create employees class
class employees(Base2):
    __tablename__ = 'employees'
    
    emp_no = Column(Integer, primary_key=True)
    birth_date = Column(Date)
    first_name = Column(String)
    last_name = Column(String)
    sex = Column(String)
    hire_date = Column(Date)



session = Session(engine)


# +
# print sums by gender
male = session.query(employees).filter_by(sex = 'M').count()
female = session.query(employees).filter_by(sex = 'F').count()

print(male)
print(female)
# -

# Using the inspector to print the column names within the 'Salaries' table and its types
columns = inspector.get_columns('salaries')
for column in columns:
    print(column["name"], column["type"])


#create salaries class
class salaries(Base2):
    __tablename__ = 'salaries'
    emp_no = Column(Integer, primary_key=True)
    salary = Column(Integer)
    


# +
#query the salaries table
x = session.query(salaries.salary)

# Plot the Results in a Matplotlib bar chart
df = pd.DataFrame(x, columns=['salary'])
a = np.array(df)
x = a[0:,0]

# +
fig, ax = plt.subplots(figsize=(12, 8))

# the histogram of the data
n, bins, patches = plt.hist(x, 12, facecolor='red', alpha=0.75)

plt.xlabel('Salary')
plt.ylabel('Frequency')
plt.title('Histogram of Employee Salaries')

plt.grid(True)
plt.show()
fig.savefig('Employee_Salary_Histogram.png')
# -

# Using the inspector to print the column names within the 'titles' table and its types
columns = inspector.get_columns('titles')
for column in columns:
    print(column["name"], column["type"])


#create titles class
class titles(Base2):
    __tablename__ = 'titles'
    
    title_id = Column(Integer, primary_key=True)
    title = Column(String)
    


session.query(titles.title).distinct().all()






StarcoderdataJupyterScriptsDedupFiltered