# Pastebin Q9HUPkiT cur = sql = b'INSERT INTO listen (listened_at, track_name, user_name, data)\n VALUES %s\n ON..., user_name)\n DO NOTHING\n RETURNING listened_at, track_name, user_name' argslist = [(1400000000, 'Immigrant Song 0', 'test', '{"user_id":1,"track_metadata":{"additional_info":{"recording_mbid":"2cfad20...40dc151","release_msid":null,"recording_msid":"4269ddbc-9241-46da-935d-4fa9e0f7f371"},"artist_name":"Led Zeppelin"}}')] template = b'(%s,%s,%s,%s)', page_size = 100, fetch = False def execute_values(cur, sql, argslist, template=None, page_size=100, fetch=False): '''Execute a statement using :sql:`VALUES` with a sequence of parameters. :param cur: the cursor to use to execute the query. :param sql: the query to execute. It must contain a single ``%s`` placeholder, which will be replaced by a `VALUES list`__. Example: ``"INSERT INTO mytable (id, f1, f2) VALUES %s"``. :param argslist: sequence of sequences or dictionaries with the arguments to send to the query. The type and content must be consistent with *template*. :param template: the snippet to merge to every item in *argslist* to compose the query. - If the *argslist* items are sequences it should contain positional placeholders (e.g. ``"(%s, %s, %s)"``, or ``"(%s, %s, 42)``" if there are constants value...). - If the *argslist* items are mappings it should contain named placeholders (e.g. ``"(%(id)s, %(f1)s, 42)"``). If not specified, assume the arguments are sequence and use a simple positional template (i.e. ``(%s, %s, ...)``), with the number of placeholders sniffed by the first element in *argslist*. :param page_size: maximum number of *argslist* items to include in every statement. If there are more items the function will execute more than one statement. :param fetch: if `!True` return the query results into a list (like in a `~cursor.fetchall()`). Useful for queries with :sql:`RETURNING` clause. .. __: https://www.postgresql.org/docs/current/static/queries-values.html After the execution of the function the `cursor.rowcount` property will **not** contain a total result. While :sql:`INSERT` is an obvious candidate for this function it is possible to use it with other statements, for example:: >>> cur.execute( ... "create table test (id int primary key, v1 int, v2 int)") >>> execute_values(cur, ... "INSERT INTO test (id, v1, v2) VALUES %s", ... [(1, 2, 3), (4, 5, 6), (7, 8, 9)]) >>> execute_values(cur, ... """UPDATE test SET v1 = data.v1 FROM (VALUES %s) AS data (id, v1) ... WHERE test.id = data.id""", ... [(1, 20), (4, 50)]) >>> cur.execute("select * from test order by id") >>> cur.fetchall() [(1, 20, 3), (4, 50, 6), (7, 8, 9)]) ''' from psycopg2.sql import Composable if isinstance(sql, Composable): sql = sql.as_string(cur) # we can't just use sql % vals because vals is bytes: if sql is bytes # there will be some decoding error because of stupid codec used, and Py3 # doesn't implement % on bytes. if not isinstance(sql, bytes): sql = sql.encode(_ext.encodings[cur.connection.encoding]) pre, post = _split_sql(sql) result = [] if fetch else None for page in _paginate(argslist, page_size=page_size): if template is None: template = b'(' + b','.join([b'%s'] * len(page[0])) + b')' parts = pre[:] for args in page: parts.append(cur.mogrify(template, args)) parts.append(b',') parts[-1:] = post > cur.execute(b''.join(parts)) E psycopg2.errors.InsufficientPrivilege: permission denied for table listen