python-3.x - Python，dataframe sql join

Question

我有两个直接查询数据库的 python 函数。
有没有办法在python中加入这两个函数？

我想做几个连接，但不确定如何在 python 中执行此操作。

查询 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)

查询 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)

我想在 identifier 上做一个左外连接表 1 和表 2 , stack , iddate和 businessdate
试:

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)

尝试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)

出现错误 name 'sql' is not defined'

Answer 1

左外连接

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')