更新熊猫数据的SQL值

Question

我对熊猫做了很多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

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

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)

当然，反过来也是正确的，将现有的dataframe数据和分组聚合查询结果集合并在一起：

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