pandas sql

.

import pandas as pd
import sqlalchemy
from sqlalchemy import create_engine
import datetime as dt

con_string = 'postgresql+psycopg2://scott:tiger@localhost:5432/mydatabase'
# dialect+driver://username:password@host:port/database

engine = create_engine(con_string)
query = "SELECT * FROM employees e JOIN salaries s ON e.emp_no = s.emp_no WHERE e.hire_date > '1999-01-01';"

df = pd.read_sql(query, engine)
# pd.read_sql_table()
# pd.read_sql_query()

# WRITING TO THE DATABASE
max_salary = df.groupby(['emp_no', 'first_name', 'last_name'])['salary'].max().reset_index()
max_salary['create_date'] = dt.datetime.now()
max_salary.to_sql('max_salary', engine, index=False)  # if_exists ['fail', 'replace', 'append']