How to use pragma_table_info?

[peng...@gmail.com]

I can get the columns of a table via the sqlite3 command line interface.

sqlite> SELECT c.name FROM pragma_table_info('foo') c
   ...> ;
x
y

z

But when I try it with apsw,

import apsw
import sys
connection = apsw.Connection(sys.argv[1])
cursor = connection.cursor()
cursor.execute("SELECT c.name FROM pragma_table_info('foo') c")

I got the following error. Is there a way to make it work with apsw? Thanks.

Traceback (most recent call last):
  File "./main.py", line 9, in <module>
    cursor.execute("SELECT c.name FROM pragma_table_info('foo') c")
apsw.SQLError: SQLError: no such table: pragma_table_info

[Roger Binns]

On 7/26/21 1:38 PM, peng...@gmail.com wrote:
> I can get the columns of a table via the sqlite3 command line interface.
>
> sqlite> SELECT c.name FROM pragma_table_info('foo') c

[...]

> But when I try it with apsw,

[...]

> cursor.execute("SELECT c.name FROM pragma_table_info('foo') c")
>
> I got the following error. Is there a way to make it work with apsw? Thanks.
>
> Traceback (most recent call last):
>   File "./main.py", line 9, in <module>
>     cursor.execute("SELECT c.name FROM pragma_table_info('foo') c")
> apsw.SQLError: SQLError: no such table: pragma_table_info

Pragmas as functions were added in SQLite 3.16 (January 2017). Your
SQLite shell is newer than that, and your APSW is older than that. This
will tell you what version of SQLite and APSW you are using:

print(apsw.sqlitelibversion(), apsw.apswversion())

Roger

[peng...@gmail.com]

> Pragmas as functions were added in SQLite 3.16 (January 2017). Your
SQLite shell is newer than that, and your APSW is older than that. This
will tell you what version of SQLite and APSW you are using:

> print(apsw.sqlitelibversion(), apsw.apswversion())

Here is what I have. But does this mean that the apsw version is newer than sqlite3 version?

>>> print(apsw.sqlitelibversion(), apsw.apswversion())
3.9.2 3.9.2-r1

[Roger Binns]

On 7/27/21 8:04 AM, peng...@gmail.com wrote:
> Here is what I have. But does this mean that the apsw version is newer
> than sqlite3 version?
>
>>>> print(apsw.sqlitelibversion(), apsw.apswversion())
> 3.9.2 3.9.2-r1

Both your APSW and SQlite version were released at the same time, almost
6 years ago. You would need to upgrade the SQLite used to get
pragma_table_info() function.

You can also just use the pragma as is:

query="pragma table_info('foo')"
for cid,name,type,notnull,dflt_value,pk in c.execute(query):
do something

The doc is at https://sqlite.org/pragma.html#pragma_table_info

Roger

[peng...@gmail.com]

I have to use the following to install the latest version.

pip install --user https://github.com/rogerbinns/apsw/releases/download/3.37.0-r1/apsw-3.37.0-r1.zip --global-option=fetch --global-option=--version --global-option=3.37.0 --global-option=--all --global-option=build --global-option=--enable-all-extensions

https://linuxtut.com/en/711f4324da14cbd7741c/

I can not directly use `pip install apsw` to get the latest version.

Could you make `pip install apsw` install the latest version?

[Roger Binns]

On 2/27/22 16:46, peng...@gmail.com wrote:
> I have to use the following to install the latest version.
> pip install --user
> https://github.com/rogerbinns/apsw/releases/download/3.37.0-r1/apsw-3.37.0-r1.zip

That (from apsw doc) works nicely, and you get exactly what you
expected. Unfortunately pip is not very good with C code extensions,
and even worse when other components are needed such as apsw needing
SQLite. The issues are discussed in:

https://github.com/rogerbinns/apsw/issues/310

> Could you make `pip install apsw` install the latest version?

Despite the difficulties I expect this to happen soon. 3.38.0 is about
a week away from release and removes support for CPython before 3.7.
Once that is out I will be trying to get pypi working as detailed in the
issue above.

Roger