[Python] Best practices to pass a subquery to a dynamic query in Python with Pandas

I'm currently working with Python and Pandas to query data from my databse. I have one query to get some customer information (this is obviously not the real query, it's simplified without joins etc.):

def customer_query(con, date):
stmt = """
SELECT
first_name
last_name
dob
FROM
customer
"""
return pd.read_sql(
stmt,
con
)


I do it with pandas so that I can easily export the dataset to csv without any hassle.

The requirements changed and I need to generate two different datasets based on circumstances around the customer. The original query still holds, we still need first_name, last_name etc, so I don't want to create two entirely separate queries. I want to add a where-clause to my query like so:

def customer_query(con, date):
stmt = """
SELECT
first_name
last_name
dob
FROM
customer
where id in sub)
"""
return pd.read_sql(
stmt,
con,
params={"sub": "SELECT customer_id FROM different_table_1"},
)


I cannot just put the subquery in the statement with a parameter. What I wish to do is put the subquery as a parameter. This way I could pass the subquery as an argument and generate two different datasets. That doesn't work with pandas though. The only thing I can come up with is to execute the subquery on it's own, grab the customer ids from it and pass those to my "customer_query" function. This isn't as nice as executing everything in one SQL statement, but I don't have any other idea. I also refrain from the idea to build the sql statement with f strings or something.

EDIT: I forgot to mention that I'm connection to an Oracle DB and the "con" object is a cx_Oracle connection. Apparently my thought of solution is not valid since cx_Oracle does not really support passing a list as a parameter.

Continue reading...