Getting results from SHOW queries in Python + PostgreSQL

[michalc]

I'm wanting to run SHOW queries from Python on a PostgreSQL database, but am running into problems. Specifically running:

import adbc_driver_postgresql.dbapi as db

with db.connect('postgresql://postgres:[email protected]:5432/') as conn:
    with conn.cursor() as cur:
        cur.execute('SHOW TRANSACTION ISOLATION LEVEL')

results in an error, the key bits of which seem to be:

Traceback (most recent call last):
  File [...], line 5, in <module>
    cur.execute('SHOW TRANSACTION ISOLATION LEVEL')
  File "[...]", line 730, in execute
    handle, self._rowcount = _blocking_call(
                             ^^^^^^^^^^^^^^^
  File "adbc_driver_manager/_lib.pyx", line 1606, in adbc_driver_manager._lib._blocking_call_impl
  File "adbc_driver_manager/_lib.pyx", line 1599, in adbc_driver_manager._lib._blocking_call_impl
  File "adbc_driver_manager/_lib.pyx", line 1241, in adbc_driver_manager._lib.AdbcStatement.execute_query
  File "adbc_driver_manager/_lib.pyx", line 260, in adbc_driver_manager._lib.check_error
adbc_driver_manager.ProgrammingError: INVALID_ARGUMENT: [libpq] Failed to execute query: could not begin COPY: ERROR:  syntax error at or near "SHOW"
LINE 1: COPY (SHOW TRANSACTION ISOLATION LEVEL) TO STDOUT (FORMAT bi...
              ^

Query was: COPY (SHOW TRANSACTION ISOLATION LEVEL) TO STDOUT (FORMAT binary). SQLSTATE: 42601

It looks like it tries to the the SHOW as a COPY, but this isn't allowed. Is there a way around this, say for example by not running the queries as a COPY?

(Context: am writing a SQLAlchemy dialect, and the SQLAlchemy existing base PostgreSQL dialect tries to run such queries)

[lidavidm]

There is an option indeed: https://arrow.apache.org/adbc/current/python/api/adbc_driver_postgresql.html#adbc_driver_postgresql.StatementOptions.USE_COPY

I think I need to:

• Update the documentation to highlight this
• Add a recipe to demonstrate it
• Possibly detect this and either retry the query without COPY or add to the error message to explain this

For future reference: the docs are being updated by #2859

[michalc]

Ah thank you! (So far I’m not sure how to use the option, but will nose around a bit more)

[lidavidm]

Ok, I do have a recipe, apparently I just never put it into the docs itself 🤦

arrow-adbc/docs/source/python/recipe/postgresql_execute_nocopy.py

Lines 36 to 42 in 9365ac8

	with conn.cursor(
	adbc_stmt_kwargs={
	adbc_driver_postgresql.StatementOptions.USE_COPY.value: False,
	}
	) as cur:
	cur.execute("SHOW ALL")
	print(cur.fetch_arrow_table().schema)

[michalc]

(Context: am writing a SQLAlchemy dialect, and the SQLAlchemy existing base PostgreSQL dialect tries to run such queries)

Also if curious, have made a start on this at https://github.com/michalc/pgarrow