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. see the following python codes:
#store into sqlite:
import sqlite3
sql_1 = """ CREATE TABLE IF NOT EXISTS bi (i integer,bi bigint,bi2 blob); """
conn = sqlite3.connect("bigint.db")
c = conn.cursor()
c.execute(sql_1)
c.execute("delete from bi")
conn.commit()
x = 10
for i in range(22):
x = 10*x
y = x + 3
s = 'insert into bi values (%s, %s, %s)' % (i, y, y)
c.execute(s)
conn.commit()
conn.close()
#retrieve from sqlite
import sqlite3
import pandas as pd
#The maximum INTEGER sqlite can store as a signed BIGINT is
# 9,223,372,036,854,775,807 (9 and a bit Quintillion)
# 9E18
conn = sqlite3.connect("bigint.db")
df = pd.read_sql_query("select * from bi order by i;", conn)
m = list(df['i'])
s = list(df['bi'])
t = list(df['bi2'])
#stored as int
for j in m:
x1 = int(m[j])
y1 = int(s[j])
x = 10
for i in range(x1+1):
x = 10*x
y = x + 3
if y != y1:
print(x1, y1, y-y1)
#store as blob
for j in m:
x1 = int(m[j])
y1 = int(t[j])
x = 10
for i in range(x1+1):
x = 10*x
y = x + 3
if y != y1:
print(x1, y1, y-y1)
conn.close()
------------------------------
result:
14 10000000000000004 -1
15 100000000000000000 3
16 1000000000000000000 3
17 10000000000000000000 3
18 100000000000000000000 3
19 1000000000000000000000 3
20 10000000000000000000000 3
21 99999999999999991611392 8388611
14 10000000000000004 -1
15 100000000000000000 3
16 1000000000000000000 3
17 10000000000000000000 3
18 100000000000000000000 3
19 1000000000000000000000 3
20 10000000000000000000000 3
21 99999999999999991611392 8388611
Summary: when the integer is larger than 1E17, it starts to lose accuracy after
it is stored in sqlite.
Thanks.
Derek Wang
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
