python sqlite only compares first 16-ish characters of string and ignores rest when searching table

user_12879048 0
Viewed 40

I have a sqlite table in Python:

CREATE TABLE lookup (  id string PRIMARY KEY  );

In this table I inserted a lot of rows and then I created an index:

CREATE INDEX intindex ON lookup(id);

I now want to test if my string is in the table. I tried the following:

checkval = cursor.execute("""SELECT EXISTS (SELECT 1 
                              FROM lookup
                              WHERE id=?
                              LIMIT 1)""", (str(mystring), )).fetchone()[0]

But the problem is, this only seems to check if the first 16 or so characters of the string are identical. For example, I know that my table contains:

'95592037576585500895905906368332492139177507248814568869956683982249018785792'

However, if I check the non-existing value:

'95592037576585501898360696547541541617886282712262516411315613154848368326494'

then I also get TRUE.

Checking:

'95592137576585500895905906368332492139177507248814568869956683982249018785792'

does correctly result in FALSE.

Why is it not comparing the full string?

sqlite python
2 Answers

The problem is that your id is of type string instead text: ""STRING" has an affinity of NUMERIC, not TEXT.". When I insert those large values sqlite3 seems to store them as floating points instead of text. Floating points have limited number of digits it can store accurately.

CREATE TABLE lookup ( id text PRIMARY KEY not null );

Also you may get an implicit index on the primary key.

Allan_Wind_97060

In SQLite there is no data type STRING.
If you use it as the data type of a column then as it is explained in Determination Of Column Affinity, the column will have NUMERIC affinity.
So, values like:

'95592037576585500895905906368332492139177507248814568869956683982249018785792'

and

'95592037576585501898360696547541541617886282712262516411315613154848368326494'

will be considered as floating point numbers and because of precision limits, their value will be:

9.5592037576586E+76

so actually they are equal.
See the demo.

For strings there is only the TEXT data type in SQLite and you should change the column's definition to that.

Also you should not use LIMIT in your code.
EXISTS does not perform a full scan in the query because it returns as soon as it finds the first row matching the condition.

user_104988280
Related