import pymysql
import pandas as pd

def extract(query):
    db = pymysql.connect (host="cldy-hub-db-dev-do-user-1524670-0.a.db.ondigitalocean.com", 
                          user="appuser", 
                          passwd="s7xnwc7aeyq9vbzk",
                          db="defaultdb",
                          port=25060,
                          charset='utf8mb4',
                          cursorclass=pymysql.cursors.DictCursor)
    cursor = db.cursor()
    cursor.execute(query)
    results = cursor.fetchall()
    df = pd.DataFrame(columns = results[0].keys())
    for row in results:
        row = pd.DataFrame(pd.Series(row)).T
        df = df.append(row,ignore_index=True)
    return df

query_resume = "select r.id as resume_id, r.person_id, p.job_profile as title, r.resume_details, p.city, p.state_province, p.country, p.total_experience_years from resumes r, person p where r.person_id = p.id and r.person_id is not null and r.resume_details is not null order by r.id desc"
df_resume = extract(query_resume)

query_jobs = "select j.id, j.title, j.details, j.experience_level, j.city, j.state_province, j.country from jobs j where tenant_id = 6"
df_jobs = extract(query_jobs)