python - 用于更新 pandas 数据框的 SQL 值

Question

我正在对 pandas 执行大量 sql，并且遇到了以下挑战。

我有一个数据框，看起来像

UserID, AccountNo, AccountName
123,    12345,     'Some name'
...

我想要做的是对于每个帐号，我想添加一个名为总收入的列，该列是从 mysql 数据库获取的，所以我正在考虑类似的内容，

for accountno in df['AccountNo']:
    df1 = pd.read_sql(('select sum(VBRK_NETWR) as sum from sapdata2016.orders where VBAK_BSARK="ZEDI" and VBRK_KUNAG = %s;') % accountno, conn)

我需要扩展数据框，以便

UserID, AccountNo, AccountName, TotalRevenue
123,    12345,     'Some name', df1
...

到目前为止我拥有的代码(并且无法正常工作会产生 getitem 错误)

sets3 = []
i=0
for accountno in df5['kna1_kunnr']:
    df1 = pd.read_sql(('select sum(VBRK_NETWR) as sum from sapdata2016.orders where VBAK_BSARK="ZEDI" and VBRK_KUNAG = %s;') % accountno, conn)
    df2 = pd.DataFrame([(df5['userid'][i], df5['kna1_kunnr'][i], accountno, df5['kna1_name1'][i], df1['sum'][0])], columns=['User ID', 'AccountNo', 'tjeck', 'AccountName', 'Revenue'])
    sets3.append(df2)
    i += 1

df6 = pd.concat(sets3)

这个想法/代码不太漂亮，我想知道是否有更好/更好的方法来做到这一点，有什么想法吗？

Answer 1

考虑将 pandas 数据作为临时表导出到 MySQL，然后运行连接 pandas 数据的 SQL 查询和 TotalRevenue 的聚合查询。然后，将结果集读入 pandas 数据帧。这种方法避免了任何循环。

from sqlalchemy import create_engine
...

# SQL ALCHEMY CONNECTION (PREFERRED OVER RAW CONNECTION)
engine = create_engine('mysql://user:pwd@localhost/database')
# engine = create_engine("mysql+pymysql://user:pwd@hostname:port/database") # load pymysql

df1.to_sql("mypandastemptable", con=engine, if_exists='replace')

sql = """SELECT t.UserID, t.AccountNo, t.AccountName, agg.TotalRevenue
         FROM mypandastemptable t
         LEFT JOIN 
            (SELECT VBRK_KUNAG as AccountNo
                    SUM(VBRK_NETWR) as TotalRevenue
             FROM sapdata2016.orders 
             WHERE VBAK_BSARK='ZEDI'
             GROUP BY VBRK_KUNAG) agg
         ON t.AccountNo = agg.AccountNo) 
"""

newdf = pd.read_sql(sql, con=engine)

当然反之亦然，合并现有数据帧的两个 pandas 数据帧和分组聚合查询结果集:

sql = """SELECT VBRK_KUNAG as AccountNo
                SUM(VBRK_NETWR) as TotalRevenue
         FROM sapdata2016.orders 
         WHERE VBAK_BSARK='ZEDI'
         GROUP BY VBRK_KUNAG 
"""

df2 = pd.read_sql(sql, con=engine)

newdf = df1.merge(df2, on='AccountNo', how='left')