Cannot parse `IS TRUE` when connecting with Python library
(1.1) By zhangyushao (zhangysh1995) on 2020-08-06 07:39:19 edited from 1.0 [source]
Test case (sqlitetest.py ):
import sqlite3 ddl = ''' create table table_10_undef_undef ( `pk` int primary key, `col_bigint_signed` bigint , `col_float_signed` float , `col_double_signed` double , `col_char(20)_signed` char(20) , `col_varchar(20)_signed` varchar(20) , `col_tinyint_signed` tinyint , `col_smallint_signed` smallint ) ; ''' try: cu = sqlite3.connect('test.db') cu.execute(ddl) cu.commit() cu.execute("SELECT `col_char(20)_signed` FROM table_10_undef_undef WHERE ( `col_float_signed` / 0 ) IS TRUE ;") cu.close() except sqlite3.OperationalError as e: print(e)
xxx:~/remote/randgen$ rm test.db; python3 sqlitetest.py no such column: TRU
However, the query is executable without errors when using the shell:
sqlite> SELECT `col_char(20)_signed` FROM table_10_undef_undef WHERE ( `col_float_signed` / 0 ) IS TRUE ;
(2) By Gunter Hick (gunter_hick) on 2020-08-06 07:22:46 in reply to 1.0 [link] [source]
no such column: TRU would seem to indicate a typo in the query TRU instead of TRUE. Also, please note that SQLite uses double quotes for names and single quotes for string literals.
(3) By zhangyushao (zhangysh1995) on 2020-08-06 07:32:02 in reply to 2 [link] [source]
Sorry for the typo,
TRU should be
TRUE in the original query.
The output from the test case is:
xxx:$ rm test.db; python3 sqlitetest.py no such column: TRUE
I suspect it is a bug in the connector. After changing to the correct quotes
", it still have the same error:
cu.execute('SELECT "col_char(20)_signed" FROM table_10_undef_undef WHERE ( "col_float_signed" / 0 ) IS TRUE ;') xxx:~/remote/randgen$ rm test.db; python3 sqlitetest.py no such column: TRUE
(4) By zhangyushao (zhangysh1995) on 2020-08-06 07:34:06 in reply to 2 [link] [source]
And that the query is runnable in the shell, which means it is executable while the connector cannot parse it.
(5) By Keith Medcalf (kmedcalf) on 2020-08-06 07:48:09 in reply to 3 [link] [source]
Your Python is using an old version of SQLite3 that does not "do" TRUE and FALSE but rather expects them to be column references.
The version of SQLite3 used by python has nothing whatsoever to do with the version of SQLite3 used by anything that is not python ... python includes its own version of SQLite3 for use exclusively by python; so the fact that something that is not python behaves differently than something which is python is relevant only after you have demonstrated that both versions of SQLite3 are the same, which you have not done.
(6) By Keith Medcalf (kmedcalf) on 2020-08-06 07:52:16 in reply to 4 [link] [source]
The shell is not Python, and Python is not the shell.
You are assuming a correlation which does not exist.
(7) By zhangyushao (zhangysh1995) on 2020-08-06 08:43:08 in reply to 5 [link] [source]
Then what I need to do is to check my Python library version? If I cannot find the version which supports the same grammar, what could I do to make this query runnable except for calling the
./sqlite command explicitly?
(8) By J-L Hainaut (JLHainaut) on 2020-08-06 09:32:35 in reply to 5 [link] [source]
As far as I have experienced (since 2012 but this may change in the future), Python seems to work nicely when one replaces the sqlite3.ddl library of the Windows Python distribution by the last version from the SQlite site. Last tried with Python 2.7.13 (2016) and SQLite 3.32.2 (2020), not tried with Python 3 yet.
(9.2) By Keith Medcalf (kmedcalf) on 2020-08-06 11:30:35 edited from 9.1 in reply to 7 [link] [source]
In the CLI:
sqlite> .version SQLite 3.33.0 2020-08-01 07:08:50 c97253adc5c8f23e490e26d87ad21b5ede12fc30826a10e66be4e6c59e5b66fb zlib version 1.2.11 gcc-9.1.0
Python 3.8.5 (tags/v3.8.5:580fbb0, Jul 20 2020, 15:57:54) [MSC v.1924 64 bit (AMD64)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> import sqlite3 >>> sqlite3.connect('').execute('select sqlite_version(), sqlite_source_id()').fetchone() ('3.33.0', '2020-08-01 07:08:50 c97253adc5c8f23e490e26d87ad21b5ede12fc30826a10e66be4e6c59e5b66fb') >>> import apsw >>> apsw.Connection().execute('select sqlite_version(), sqlite_source_id()').fetchone() Row(sqlite_version='3.33.0', sqlite_source_id='2020-08-01 07:08:50 c97253adc5c8f23e490e26d87ad21b5ede12fc30826a10e66be4e6c59e5b66fb')
Notice that I have the same three versions everywhere because I build them all myself. Note also that the "source_id" does not correspond to a version of SQLite3 available from the sqlite.org fossil repository because I build it myself incorporating changes that modify the source hash. Also note that I have made changes to APSW as well, so the apsw procedure will not work on stock APSW.
Note that you can replace the side-by-each version of the sqlite3.so used by python with whatever version you want to use. I suspect that if you remove it entirely then the "system wide" shared-library will be used instead.
(10) By zhangyushao (zhangysh1995) on 2020-08-06 11:52:59 in reply to 9.0 [link] [source]
I followed this blog to build a new version, and it works now.
(11) By zhangyushao (zhangysh1995) on 2020-08-06 11:53:52 in reply to 8 [link] [source]
Thank you. I use Linux and I found a way to replace the library. See the reply above.