Dask Dataframe- read_sql_table - where condition

Question

I need to retrieve only rows where record_status is 'New' into a Dask Dataframe.

currently I am using below and creating pandas Dataframe as below

cnx = create_engine('mysql+mysqlconnector://root:password@localhost/sshema',
    echo=False)
df_party = pd.read_sql('select  first_name, last_name from party where record_status = \'New\'', con=cnx)

I want to do the same and create Dask Dataframe. Below is what I tried and is working. How can I add where condition to this to only retrieve rows with record_status 'New'

ddf_party = dd.read_sql_table('party','mysql+mysqlconnector://root:password@localhost/schema',
                            index_col='create_date', columns = ['first_name', 'last_name'])

Thanks in advance.

joebeeson joebeeson · Accepted Answer · 2020-07-08T21:24:31

The table argument of dask.dataframe.read_sql_table supports SQLAlchemy expressions -- it ain't pretty though:

import sqlalchemy as sa

sqluri = "mysql+mysqlconnector://root:password@localhost/schema"
engine = sa.create_engine(sqluri)

sa_meta = sa.MetaData()
sa_table = sa.Table("party", sa_meta, autoload=True, autoload_with=engine)
sa_query = sa.select([sa_table]).where(sa_table.c.record_status == "New")
sa_columns = [sa_table.c.first_name, sa_table.c.last_name]

ddf_party = dd.read_sql_table(sa_query, sqluri, index_col="create_date", columns=sa_columns)

Dask Dataframe- read_sql_table - where condition

1 Answers