CODEWITHSUNDEEP
pandasamazon-redshift
scott martin
scott martin

Reputation: 1293

Python - Passing values from one Dataframe to another query

I am trying to perform a SQL query in Python where in I am trying to pass output of a Dataframe as part of the where clause.

import psycopg2
import pandas as pd
con = psycopg2.connect (db1_details)
con1 = psycopg2.connect (db2_details)

This has all the bills generated

data = pd.read_sql("""select bill_id from sales where date > '2019-01-01'""", con = con)

Now I am trying to pull all customers that created bill after 2019-01-01 as obtained in the above query and is stored in data

customer_details = f"""select cust_name, bill_id from customers where bill_id in {data}"""

I am not sure how to pass in value from dataframe to another query as part of the loop.

Edit:

View of data.head()

bill_id
1001
1002
1003
1006
1007

Upvotes: 2

Views: 1170

Answers (3)

Jon Scott
Jon Scott

Reputation: 4354

You could join the 2 tables like this

select distinct c.cust_name, c.bill_id 
from customers as c
join sales as s on c.bill_id=s.bill_id and s.date > '2019-01-01'

That could be more efficient.

Upvotes: 0

jezrael
jezrael

Reputation: 863031

If column name is bill_id and need loop per each unique customer:

for cust in data['bill_id'].unique():
    customer_details = f"""select cust_name, bill_id from customers where bill_id in ({cust})"""
    print (customer_details)
    select cust_name, bill_id from customers where bill_id in (1001)
    select cust_name, bill_id from customers where bill_id in (1002)
    select cust_name, bill_id from customers where bill_id in (1003)
    select cust_name, bill_id from customers where bill_id in (1006)
    select cust_name, bill_id from customers where bill_id in (1007)

    data = pd.read_sql(customer_details, con=con1)

Or if need all unique customers:

all_data = ', '.join(data['bill_id'].unique().astype(str))
customer_details = f"""select cust_name, bill_id from customers where bill_id in ({all_data})"""
print (customer_details)
select cust_name, bill_id from customers where bill_id in (1001, 1002, 1003, 1006, 1007)

data = pd.read_sql(customer_details, con=con1)

Upvotes: 1

tawab_shakeel
tawab_shakeel

Reputation: 3739

  1. Fetch unique bill_ids from data
  2. convert those list into tuple and send this into query 
unique_bill_id = list(data["bill_id"].unique())
if len(unique_bill_id ) == 1:
     unique_bill_id.append(unique_key[0])

query = "select cust_name, bill_id from customers where bill_id in {};".format(tuple(unique_bill_id))
df = pd.read_sql_query(query,con=con1)

Upvotes: 1

Related Questions