We have answer of your question!

100% solved queries, no empty question

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


0

Advertisement


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?

Question author Whan | Source

Answer


1


Advertisement


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)
Answer author Van

Advertisement


Tickanswer.com is providing the only single recommended solution of the question How to filtering a list of names from Python when querying through SQLAlchemy under the categories i.e python , sqlalchemy , . Our team of experts filter the best solution for you.

Related Search Queries:

You may also add your answer!