利用 Pandas DataFrame 作为 MySQL 查询中的参数

有一个名为 df 的 Pandas DataFrame，包含多列数据。
* 有一个名为 Table-B 的大型 MySQL 表，需要对 df 与 Table-B 进行左连接。
* 不希望将整个 Table-B 加载至 Pandas 中，因为这会占用大量内存且效率低下。
* 希望能够将 df 作为对象或参数传递给 read_sql 函数，以便在 MySQL 中直接进行查询。

• 示例数据:

df = pd.DataFrame({'SEC1':['IBM','CSCO','MSFT','AMZN' ], 'SEC2':['GOOG', 'INTC', 'ABX', 'CREE'], 'HOUR':[10 ,10 ,15, 12], 'Size':[100 ,200 ,50 ,500],'Price':[300 ,25 ,150, 80] })

df = df[['SEC1', 'SEC2', 'HOUR', 'Size', 'Price']]

2、解决方案

• 方式一：使用 df.to_sql() 函数将 df 临时存储到 MySQL 中

  • 可以使用 df.to_sql() 函数将 df 临时存储到 MySQL 中，然后在 MySQL 中执行查询，最后再使用 read_sql() 函数读取查询结果。
  • 在此方案中，需要确保具有相应的 MySQL 访问权限，并确保在执行查询之前已将 df 加载至 MySQL 中。

import pandas as pd
import sqlalchemy as SA
import config

df = pd.DataFrame({'SEC1':['IBM','CSCO','MSFT','AMZN' ], 'SEC2':['GOOG', 'INTC', 'ABX', 'CREE'], 'HOUR':[10 ,10 ,15, 12], 'Size':[100 ,200 ,50 ,500],'Price':[300 ,25 ,150, 80] })
df = df[['SEC1', 'SEC2', 'HOUR', 'Size', 'Price']]

engine = SA.create_engine('mysql+oursql://{u}:{p}@{h}/{d}'.format(
    u=config.USER, p=config.PASS, h=config.HOST, d='test'))
tablename = 'Table-A'
df.to_sql(tablename, engine, if_exists='replace')

sql = '''select * from  Table-A a
         left join Table-B b
         on (a.sec1 = b.sec1)
         where DATE_SUB(CURDATE(),INTERVAL 12 MONTH) <= dt
         group by 1,2,3,4,5,6,7'''

result = pd.read_sql(sql, engine)

• 方式二：使用 read_sql_query() 函数将查询语句参数化

  • 可以使用 read_sql_query() 函数将查询语句参数化，然后在函数中直接传递 df 作为参数。
  • 在此方案中，需要确保具有相应的 MySQL 访问权限，并确保在执行查询之前已将 Table-B 加载至 MySQL 中。

import pandas as pd
import sqlalchemy as SA
import config

df = pd.DataFrame({'SEC1':['IBM','CSCO','MSFT','AMZN' ], 'SEC2':['GOOG', 'INTC', 'ABX', 'CREE'], 'HOUR':[10 ,10 ,15, 12], 'Size':[100 ,200 ,50 ,500],'Price':[300 ,25 ,150, 80] })
df = df[['SEC1', 'SEC2', 'HOUR', 'Size', 'Price']]

engine = SA.create_engine('mysql+oursql://{u}:{p}@{h}/{d}'.format(
    u=config.USER, p=config.PASS, h=config.HOST, d='test'))

sql = '''select * from  Table-A a
         left join Table-B b
         on (a.sec1 = b.sec1)
         where DATE_SUB(CURDATE(),INTERVAL 12 MONTH) <= dt
         group by 1,2,3,4,5,6,7'''

result = pd.read_sql_query(sql, engine, params={'df': df})

• 方式三：使用 Pandas 的 merge() 或 join() 函数进行连接

  • 可以使用 Pandas 的 merge() 或 join() 函数将 df 与 Table-B 进行连接，无需将两者都加载至内存中。
  • 在此方案中，需要确保具有相应的 MySQL 访问权限，并确保在执行查询之前已将 Table-B 加载至 MySQL 中。

import pandas as pd
import sqlalchemy as SA
import config

df = pd.DataFrame({'SEC1':['IBM','CSCO','MSFT','AMZN' ], 'SEC2':['GOOG', 'INTC', 'ABX', 'CREE'], 'HOUR':[10 ,10 ,15, 12], 'Size':[100 ,200 ,50 ,500],'Price':[300 ,25 ,150, 80] })
df = df[['SEC1', 'SEC2', 'HOUR', 'Size', 'Price']]

engine = SA.create_engine('mysql+oursql://{u}:{p}@{h}/{d}'.format(
    u=config.USER, p=config.PASS, h=config.HOST, d='test'))

table_b = pd.read_sql_table('Table-B', engine)

result = pd.merge(df, table_b, on='SEC1', how='left')