循环中查询的Postgres联合

SELECT id, name, role FROM employees
UNION
SELECT id, name, role FROM contractors;

import psycopg2

conn = psycopg2.connect(database="yourdb", user="youruser", password="yourpass", host="yourhost", port="yourport")
cursor = conn.cursor()

tables = ["employees", "contractors"]
all_data = []

for table in tables:
    cursor.execute(f"SELECT id, name, role FROM {table}")
    data = cursor.fetchall()
    all_data.extend(data)

conn.close()

import psycopg2

conn = psycopg2.connect(database="yourdb", user="youruser", password="yourpass", host="yourhost", port="yourport")
cursor = conn.cursor()

tables = ["employees", "contractors"]
query = " UNION ".join([f"SELECT id, name, role FROM {table}" for table in tables])

cursor.execute(query)
all_data = cursor.fetchall()

conn.close()

import psycopg2

conn = psycopg2.connect(database="yourdb", user="youruser", password="yourpass", host="yourhost", port="yourport")
cursor = conn.cursor()

tables = ["employees", "contractors"]
query = " UNION ALL ".join([f"SELECT id, name, role FROM {table}" for table in tables])

cursor.execute(query)
all_data = cursor.fetchall()

# 在应用层处理重复数据
unique_data = list({(row[0], row[1]): row for row in all_data}.values())

conn.close()