Trying to mogrify a query ....

mogrify.py

from random import choice

import psycopg2

STATEMENT = 'WITH u (uid, name) AS (VALUES %(users)s) SELECT u.uid, u.name FROM u WHERE u.name ~ \'%(partial_name)s\';'


def create_data(total: int) -> list:
    data = []

    first_names = ('Jack', 'Thomas', 'James', 'Joshua', 'Daniel', 'Harry', 'Samuel', 'Joseph', 'Matthew', 'Callum',
                   'Luke', 'William', 'Lewis', 'Oliver', 'Ryan', 'Benjamin', 'George', 'Liam', 'Jordan', 'Adam',
                   'Alexander', 'Jake', 'Connor', 'Cameron', 'Nathan', 'Kieran', 'Mohammed', 'Jamie', 'Jacob',
                   'Michael', 'Ben', 'Ethan', 'Charlie', 'Bradley', 'Brandon', 'Aaron', 'Max', 'Dylan', 'Kyle', 'Reece',
                   'Robert', 'Christopher', 'David', 'Edward', 'Charles', 'Owen', 'Louis', 'Alex', 'Joe', 'Rhys')
    last_names = ('Smith', 'Brown', 'Wilson', 'Thomson', 'Robertson', 'Campbell', 'Stewart', 'Anderson',
                  'MacDonald', 'Scott', 'Reid', 'Murray', 'Taylor', 'Clark', 'Mitchell', 'Ross', 'Walker', 'Paterson',
                  'Young', 'Watson', 'Morrison', 'Miller', 'Fraser', 'Davidson', 'Gray', 'McDonald', 'Henderson',
                  'Johnston', 'Hamilton', 'Graham', 'Kerr', 'Simpson', 'Martin', 'Ferguson', 'Cameron', 'Duncan',
                  'Hunter', 'Kelly', 'Bell', 'Grant', 'MacKenzie', 'MacKay', 'Allan', 'Black', 'MacLeod', 'McLean',
                  'Russell', 'Gibson', 'Wallace', 'Gordon', 'Marshall', 'Stevenson', 'Wood', 'Sutherland', 'Craig',
                  'Wright', 'McKenzie', 'Kennedy', 'Jones', 'Burns', 'White', 'Muir', 'Murphy', 'Johnstone', 'Hughes',
                  'Watt', 'McMillan', 'Mcintosh', 'Milne', 'Munro', 'Ritchie', 'Dickson', 'Bruce', 'King', 'Crawford',
                  'Docherty', 'Millar', 'Cunningham', 'Sinclair', 'Williamson', 'Hill', 'McGregor', 'McKay', 'Boyle',
                  'Shaw', 'Fleming', 'Moore', 'Christie', 'Douglas', 'Donaldson', 'McAndrews', 'MacLean', 'Forbes',
                  'Mcintyre', 'Findlay', 'Jamieson', 'Aitken', 'Reilly', 'Thompson', 'Hay')

    for i in range(0, total):
        data.append({'uid': i, 'name': f'{choice(first_names)} {choice(last_names)}'})

    return data


def get_users(_data) -> list:
    return [tuple(d) for d in _data]


def get_users_as_str(_data) -> str:
    return ','.join(('(%(uid)s, \'%(name)s\')' % d) for d in _data)


data = create_data(100)
users = get_users_as_str(data)
# users = get_users(data)
partial_name = 'Adam'

print(STATEMENT % {'users': users, 'partial_name': partial_name})

# local container hosting the DB named AdventureWorks
# preloaded through portainer using the following:
# docker run -d -p 9000:9000 -v /var/run/docker.sock:/var/run/docker.sock -v ~/.portainer_data:/data --name portainer portainer/portainer
# but it is outside the scope of this
connection_details = {'host': 'localhost',
                      'port': '5432',
                      'database': 'AdventureWorks',
                      'user': 'postgres',
                      'password': 'postgres'}
conn = psycopg2.connect(**connection_details)
cursor = conn.cursor()

cursor.execute(STATEMENT % {'users': users, 'partial_name': partial_name})

# query = cursor.mogrify(STATEMENT, {'users': users, 'partial_name': partial_name})
# result = cursor.execute(query)

result = cursor.fetchall()

print(result)