Compare datasets in sql query with pandas

Question

Cannot see online and therefore not sure if this is possible? Can I make a query to sql that will compare columns in sql to existing pandas dataframe ?

EDIT: adding more of sample code

df_local = pd.DataFrame(data={'col1': [1, 2], 'col2': [3, 4]})
result = pd.read_sql(
f""" 
SELECT * FROM sql_dataset 
  WHERE sql_dataset.col1 =df.col1 
  AND
  sql_dataset.col2 =df.col2
""" )

yes you can friendorinol, but for that you will need to convert your read_sql statement to a pandas df — INGl0R1AM0R1
– INGl0R1AM0R1, Commented Jul 28, 2022 at 15:12
This would need a little more detail, but you absolutely can do this. If you provide some sample code we can probably help get you a more exact answer — ArchAngelPwn
– ArchAngelPwn, Commented Jul 28, 2022 at 15:15
Oh, this is nice to hear. I have added a little more of the sample code. Basically I just have data frame and sql table in the server and since the table is too big to pull completely, I just want to get all rows where two columns in sql table match two columns in my dataframe — euh
– euh, Commented Jul 28, 2022 at 17:32

Oneal R · Accepted Answer · 2022-07-29 11:55:16Z

1

Try this:

df_local = pd.DataFrame(data={'col1': [1, 2], 'col2': [3, 4]})
col1_list = df_local['col1'].values.tolist()
q_col1 = ', '.join([str(row) for row in col1_list])

col2_list = df_local['col2'].values.tolist()
q_col2 = ', '.join([str(row) for row in col2_list])

concat_list = ["'"+str(c1) + ' ' + str(c2)+"'" for c1, c2 in zip(col1_list, col2_list)]
q_concat = ', '.join([str(row) for row in concat_list])


q = "SELECT * FROM sql_dataset WHERE CONCAT(sql_dataset.col1, ' ', sql_dataset.col2) in (" + q_concat + ")"

result = pd.read_sql(q)

edited Jul 29, 2022 at 11:55

answered Jul 28, 2022 at 15:17

Oneal R

486 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

euh Over a year ago

Thanks for the answer but it's not what I am looking for. I would want to compare values from rows between columns in sql dataset and pandas data frame. Your answer would check for column names I belive

Oneal R Over a year ago

Oh ok sorry I think that I understand now, I've edited the answer do not hesitate telling me if it's better

euh Over a year ago

thanks again, this would partially work but in my example this would match rows 1,3, 1,4, 2,3 and 2,4. In my case I am looking to match only rows 1,3 and 2,4

Oneal R Over a year ago

Ok so I've edited again. I've tried to concatenate col1 and col2 to get an 'ID' that you can use to retrieve data. The printed query from python looks like this : "SELECT * FROM sql_dataset WHERE CONCAT(sql_dataset.col1, ' ', sql_dataset.col2) in ('1 3', '2 4')"

euh Over a year ago

oh, nice! that does it, I didn't know abut sql concat

Collectives™ on Stack Overflow

Compare datasets in sql query with pandas

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related