使用 psycopg2 对多组参数执行查询

我有一个要查询的表,但我想进行许多特定查询并返回一个包含任何满足其条件的结果的表,并忽略不存在的查询。

data = (
    (1, '2020-11-19'),
    (1, '2020-11-20'),
    (1, '2020-11-21'),
    (2, '2020-11-19'),
    (2, '2020-11-20'),
    (2, '2020-11-21')
)

string = """
    SELECT * FROM my_schema.my_table
    WHERE my_schema.my_table.song_id = %s
    AND my_schema.my_table.date = %s;
"""

execute_values(cursor, string, data)
results = cursor.fetchall()

希望这能说明我在这里想要实现的目标......

我想执行一系列选择语句,每个语句都有一对参数。如果该对参数在数据库中,则将其附加到结果表中。

for-loop 中手动执行此操作是唯一的方法吗?

stack overflow Execute a query for multiple sets of parameters with psycopg2
原文答案
author avatar

接受的答案

Executing many queries in a loop is not a good idea. Use a common table expression to deliver many pairs of parameters to a single query and get results for all of them, like in this Postgres example.

Python code:

data = (
    (1, '2020-11-19'),
    (1, '2020-11-20'),
    (1, '2020-11-21'),
    (2, '2020-11-19'),
    (2, '2020-11-20'),
    (2, '2020-11-21')
)
        
query = """
    with data(song_id, date) as (
        values %s
    )
    select t.*
    from my_table t
    join data d 
    on t.song_id = d.song_id and t.date = d.date::date
"""
execute_values(cursor, query, data)
results = cursor.fetchall()

答案:

作者头像

A simple and intuitive solution can be to use the "IN" clause with Tuple
i.e. (col1, col2) in ( (data11, data12), (data21, data22) ) e.g.

SELECT * FROM BOOKINGS WHERE (user_id,booked_at) in ((1, '2020-11-19'),(2, '2020-11-20') );"

Full code

import psycopg2
from psycopg2.extras import execute_values
import pandas as pd

# Ref - https://uibakery.io/sql-playground
connection = psycopg2.connect(host='psql-mock-database-cloud.postgres.database.azure.com', user='zvgyzkbybtzsnmvqkwzqmogy@psql-mock-database-cloud', password='tixzlbnnrjlbczfuzbmdwsxd', dbname='booking1665772869599ofknbwmmpsmnffue', port=5432) 

data = (
(125, '2021-11-18T08:08:59.839Z'),
(28, '2021-11-17T20:01:02.244Z'),
(78, '2021-11-17T15:57:27.186Z'))
    
string = "SELECT * FROM BOOKINGS WHERE (user_id,booked_at) in ( %s );"

with connection.cursor() as cursor:
  execute_values(cursor, string, data)
  results = cursor.fetchall()
  col_names = [desc[0] for desc in cursor.description]

df = pd.DataFrame(results, columns=col_names)
df