Python, dataframe sql join

Question

I have two python functions that query a database directly. Is there a way to join these 2 functions within python?

I want to do a couple of joins not really sure how to do that in python.

Query 1:

def query1(businessDate):
    con = pyodbc.connect(r'DSN='+'Stack',autocommit=True)  
    print('working')
    #businessDate = r"'2019-03-13'"
    #remember business date should be entered like "'2019-03-13'"

    sql = f"""

     SELECT 
       iddate, 
       businessdate, 
       stack, identifier
     FROM stackoverflow
     where stack is not null 
     and businessdate = {businessDate}

    """

    df_stack = pd.read_sql(sql,con)

    con.close()

    return(df_stack)

query 2:

    def superuser(businessDate):  
    con = pyodbc.connect(r'DSN='+'super',autocommit=True)  
    print('working')
    #remember business date should be entered like "'2019-03-13'"

    sql = f"""
    SELECT 
      iddate,
      businessdate,
      stack, identifier
    FROM superuser
    WHERE stack is not null 
    and businessdate = {businessDate}


    """

    df_super = pd.read_sql(sql,con)

    con.close()

    return(df_super)

I'd want to do a left outer join table 1 with table 2 on identifier, stack, iddate and businessdate

Trying:

def testjoin():
    con = pyodbc.connect(r'DSN='+'Stack',autocommit=True)  
    print('working') 

    pd.merge(df_stack,df_super, on = ['identifier','stack','iddate'])

    df_test = pd.read_sql(sql,con)

    con.close()

    return(df_test)

trying 2:

def testjoin():
    con = pyodbc.connect(r'DSN='+'Stack',autocommit=True)  
    print('working') 

    df_stack= query1("'2019-03-13'")
    df_super= superuser("'2019-03-13'")

    pd.merge(df_stack,df_super, on = ['identifier','stack','iddate'])

    df_test = pd.read_sql(sql,con)

    con.close()

    return(df_test)

getting error name 'sql' is not defined'

Answer 1

Left Outer Join

SELECT *
FROM df_stack
LEFT OUTER JOIN df_super
    ON df_stack.stack = df_super.stack
    ON df_stack.identifier= df_super.identifier 
    ON df_stack.iddate = df_super.iddate
    ON df_stack.businessdate = df_super.businessdate;

pd.merge(df_stack,df_super,
         on=['iddate','businessdate', 'stack', 'identifier'], 
         how='left')