Derek Wang wrote:
> sqlite biggest int is supposedly 9,223,372,036,854,775,807 (9 and a bit
> Quintillion), but when the number is larger than 1E+17, it loses some
> accuracy when retrieving.
In plain SQL, everything works fine up to the limit:
create table t(i notoriously big integer);
with recursive b(i) as (values (9223372036854775803) union all select i+1
from b limit 10) insert into t select i from b;
select i from t;
9223372036854775803
9223372036854775804
9223372036854775805
9223372036854775806
9223372036854775807
9.22337203685478e+18
9.22337203685478e+18
9.22337203685478e+18
9.22337203685478e+18
9.22337203685478e+18
Same in plain Python, when using %s formatting:
import sqlite3
db=sqlite3.connect("':memory:")
db.execute("create table t(i notoriously big integer)")
for i in [10**17, 10**17+3, 10**18, 10**18+3, 10**19, 10**19+3]:
db.execute("insert into t values(%s)" % (i,))
for row in db.execute("select i from t"):
print(row[0])
100000000000000000
100000000000000003
1000000000000000000
1000000000000000003
1e+19
1e+19
In any case, when using properly parameterized commands, you will not be
able to insert values that are too large:
db.execute("select ?", (9223372036854775807,)).fetchall()
[(9223372036854775807,)]
db.execute("select ?", (9223372036854775808,)).fetchall()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
OverflowError: Python int too large to convert to SQLite INTEGER
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
