# To make this executable directly with uv run:
# /// script
# dependencies = [
#   "psycopg[binary]",
#   "sqlalchemy",
# ]
# ///

from sqlalchemy import create_engine, text


connection_url = "postgresql+psycopg://postgres@localhost:5432/postgres"

for query in (
    "SELECT * FROM test WHERE id IN :ids",
    "SELECT * FROM test WHERE id = ANY(:ids)",
):
    for ids in ([1, 2], [], (1, 2), {1, 2}):
        print(query, ids)
        engine = create_engine(connection_url)
        with engine.connect() as connection:
            connection.execute(text("CREATE TEMPORARY TABLE test (id serial PRIMARY KEY)"))
            connection.execute(text("INSERT INTO test(id) VALUES(1)"))
            connection.execute(text("INSERT INTO test(id) VALUES(2)"))
            connection.execute(text("INSERT INTO test(id) VALUES(3)"))

            try:
                results = connection.execute(
                    text(query),
                    {"ids": ids},
                )
                print(f"with {type(ids)}", results.fetchall())
            except Exception as e:
                print(f"Failed with {type(ids)}:", e)

        print("\n\n")
        engine.dispose()