You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@arrow.apache.org by "michalc (via GitHub)" <gi...@apache.org> on 2025/05/25 10:28:54 UTC
[D] Getting results from `SHOW` queries in Python + PostgreSQL [arrow-adbc]
GitHub user michalc created a discussion: Getting results from `SHOW` queries in Python + PostgreSQL
I'm wanting to run `SHOW` queries from Python on a PostgreSQL database, but am running into problems. Specifically running:
```python
import adbc_driver_postgresql.dbapi as db
with db.connect('postgresql://postgres:password@127.0.0.1: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:
```
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
adbc_driver_manager.ProgrammingError: INVALID_ARGUMENT: Failed to prepare query: ERROR: syntax error at end of input
LINE 1: CREATE TABLE id(int)
```
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)
GitHub link: https://github.com/apache/arrow-adbc/discussions/2857
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Getting results from `SHOW` queries in Python + PostgreSQL [arrow-adbc]
Posted by "michalc (via GitHub)" <gi...@apache.org>.
GitHub user michalc added a comment to the discussion: Getting results from `SHOW` queries in Python + PostgreSQL
> (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
GitHub link: https://github.com/apache/arrow-adbc/discussions/2857#discussioncomment-13273246
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Getting results from `SHOW` queries in Python + PostgreSQL [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
GitHub user lidavidm added a comment to the discussion: Getting results from `SHOW` queries in Python + PostgreSQL
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](https://arrow.apache.org/adbc/current/python/recipe/postgresql.html) to demonstrate it
- Possibly detect this and either retry the query without COPY or add to the error message to explain this
GitHub link: https://github.com/apache/arrow-adbc/discussions/2857#discussioncomment-13262753
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Getting results from `SHOW` queries in Python + PostgreSQL [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
GitHub user lidavidm added a comment to the discussion: Getting results from `SHOW` queries in Python + PostgreSQL
Ok, I _do_ have a recipe, apparently I just never put it into the docs itself :facepalm:
https://github.com/apache/arrow-adbc/blob/9365ac82421c1bfa4a414a8ea848bdc5d7fe899a/docs/source/python/recipe/postgresql_execute_nocopy.py#L36-L42
GitHub link: https://github.com/apache/arrow-adbc/discussions/2857#discussioncomment-13262962
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Getting results from `SHOW` queries in Python + PostgreSQL [arrow-adbc]
Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
GitHub user lidavidm edited a comment on the discussion: Getting results from `SHOW` queries in Python + PostgreSQL
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](https://arrow.apache.org/adbc/current/python/recipe/postgresql.html) 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 https://github.com/apache/arrow-adbc/pull/2859
GitHub link: https://github.com/apache/arrow-adbc/discussions/2857#discussioncomment-13262753
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Getting results from `SHOW` queries in Python + PostgreSQL [arrow-adbc]
Posted by "michalc (via GitHub)" <gi...@apache.org>.
GitHub user michalc edited a discussion: Getting results from `SHOW` queries in Python + PostgreSQL
I'm wanting to run `SHOW` queries from Python on a PostgreSQL database, but am running into problems. Specifically running:
```python
import adbc_driver_postgresql.dbapi as db
with db.connect('postgresql://postgres:password@127.0.0.1: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)
GitHub link: https://github.com/apache/arrow-adbc/discussions/2857
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Getting results from `SHOW` queries in Python + PostgreSQL [arrow-adbc]
Posted by "michalc (via GitHub)" <gi...@apache.org>.
GitHub user michalc edited a discussion: Getting results from `SHOW` queries in Python + PostgreSQL
I'm wanting to run `SHOW` queries from Python on a PostgreSQL database, but am running into problems. Specifically running:
```python
import adbc_driver_postgresql.dbapi as db
with db.connect('postgresql://postgres:password@127.0.0.1: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:
```
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)
GitHub link: https://github.com/apache/arrow-adbc/discussions/2857
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org
Re: [D] Getting results from `SHOW` queries in Python + PostgreSQL [arrow-adbc]
Posted by "michalc (via GitHub)" <gi...@apache.org>.
GitHub user michalc added a comment to the discussion: Getting results from `SHOW` queries in Python + PostgreSQL
Ah thank you! (So far I’m not sure how to use the option, but will nose around a bit more)
GitHub link: https://github.com/apache/arrow-adbc/discussions/2857#discussioncomment-13262820
----
This is an automatically sent email for user@arrow.apache.org.
To unsubscribe, please send an email to: user-unsubscribe@arrow.apache.org