Reading SQL table from Python with merge condition-CodePudding


import pandas as pd

conn = pyodbc.connect("Driver={??};"
                      "Server=??;"
                      "Database=??;"
                      "Trusted_Connection=yes;")


df1 = pd.read_sql_query("SELECT TOP 10000 * FROM table1", conn)

df2 = pd.read_sql_query("SELECT * FROM table2  (((where id_key = id(from table1) ))) ", conn)

Hello,

I have two tables in SQL server. I wanted to pull the data from table2 that has the same ID which mean id_key = id(from table1).

CodePudding user response：

get df1's id as a tuple:

ids = tuple(df1['id'].to_list())
print(ids)
'''
(1, 2)
'''

then, use format and read sql:

sql= 'select*from table where id_key in {}'.format(ids)
print(sql)
'''
select*from table where id_key in (1, 2)
'''

df2=pd.read_sql(sql,conn)

full code:

import pandas as pd

conn = pyodbc.connect("Driver={??};"
                      "Server=??;"
                      "Database=??;"
                      "Trusted_Connection=yes;")


df1 = pd.read_sql_query("SELECT TOP 10000 * FROM table1", conn)

ids = tuple(df1['id'].to_list())
df2_sql = 'SELECT * FROM table2 where id_key in {}'.format(ids)

df2 = pd.read_sql_query(df2_sql, conn)