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