CODEWITHSUNDEEP
pythonsqlalchemy
Warren
Warren

Reputation: 1001

How to filtering a list of names from Python when querying through SQLAlchemy

My original query is like

select table1.id, table1.value
from some_database.something table1
join some_set table2 on table2.code=table1.code
where table1.date_ >= :_startdate and table1.date_ <= :_enddate

which is saved in a string in Python. If I do

x = session.execute(script_str, {'_startdate': start_date, '_enddate': end_date})

then

x.fetchall()

will give me the table I want.

Now the situation is, table2 is no longer available to me in the Oracle database, instead it is available in my python environment as a DataFrame. I wonder what is the best way to fetch the same table from the database in this case?

Upvotes: 1

Views: 58

Answers (1)

van
van

Reputation: 76962

You can use the IN clause instead.

First remove the join from the script_str:

script_str = """
select  table1.id, table1.value
from    something table1
where   table1.date_ >= :_startdate and table1.date_ <= :_enddate
"""

Then, get codes from dataframe:

codes = myDataFrame.code_column.values

Now, we need to dynamically extend the script_str and the parameters to the query:

param_names = ['_code{}'.format(i) for i in range(len(codes))]
script_str += "AND table1.code IN ({})".format(
    ", ".join([":{}".format(p) for p in param_names])
)

Create dict with all parameters:

params = {
    '_startdate': start_date,
    '_enddate': end_date,
}
params.update(zip(param_names, codes))

And execute the query:

x = session.execute(script_str, params)

Upvotes: 1

Related Questions