search

v1a0 / sqllex

The most pythonic ORM (for SQLite and PostgreSQL). Seriously, try it out!
https://v1a0.github.io/sqllex
GNU General Public License v3.0
92 stars 8 forks source link

sqlite3.OperationalError: no such table: PRAGMA_TABLE_INFO #19

Closed Phizilion closed 3 years ago

Phizilion commented 3 years ago

i wanted to run my code to server however i got this error. The code works on my computer, so it might be the server

My computer is on Windows 10, the server is using CentOS 7 Python 3.8


File "main.py", line 9, in <module>
    detectives = db["detectives"]
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 931, in __getitem__
    return SQLite3xTable(db=self, name=key)
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 653, in __init__
    self.columns = self.get_columns()
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 685, in get_columns
    return self.db.get_columns(table=self.name)
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 1582, in get_columns
    columns = self.execute(f"SELECT name FROM PRAGMA_TABLE_INFO('{table}')")
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 1390, in execute
    return self._execute_stmt_(script=script, values=values, request=request)
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 566, in t2l_wrapper
    ret = lister(func(*args, **kwargs))
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 405, in execute_wrapper
    ret_ = executor(conn, stmt)
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 382, in executor
    raise error
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 377, in executor
    cur.execute(stmt.request.script)
sqlite3.OperationalError: no such table: PRAGMA_TABLE_INFO
v1a0 commented 3 years ago

@Phizilion, I guess this problem might arise due to python3.8, highly recommend update up to python3.9. This will help avoid future compatibility issues.

I have no any ideas why it can't find default PRAGMA_TABLE_INFO, but I found this answer by @MKleusberg

The pragma_table_info(...) syntax is indeed valid. Here is some information on it. There are two interesting notes there:

  • This feature is experimental and is subject to change. Further documentation will become available if and when the table-valued functions for PRAGMAs feature becomes officially supported.
  • The table-valued functions for PRAGMA feature was added in SQLite version 3.16.0 (2017-01-02). Prior versions of SQLite cannot use this feature.

I can confirm the latter. On my Linux system I have SQLCipher 3.4.1 which is based on SQLite 3.15.2 and doesn't support the pragma_xxx syntax. When I'm switching to vanilla SQLite which is version 3.26 the query runs just fine.

columns = self.execute(f"SELECT name FROM PRAGMA_TABLE_INFO('{table}')")

img

Anyway I made fix for this issue, please update up to sqllex==0.1.9.2 and let me know is it fixed or not. (And update python!)

Phizilion commented 3 years ago

yes, this problem does not arise anymore. Right now I cannot use python 3.9, but as soon as something changes, I will write here