Python int too large to put in SQLite

Question

I am getting the error

OverflowError: Python int too large to convert to SQLite INTEGER

from the following code block. The file is about 25gb, so it must be read in parts.

length = 6128765
# Works on partitions of the dataset
def work(path, fields, partitions, rows, fn):
    import numpy as np
    div = rows // partitions

    parts = np.arange(0, rows, div)
    for i in parts:
        df = pd.read_csv(path, sep='|', names=fields, skiprows=i, nrows=div, encoding='latin-1')
        fn(df)
        del df

def to_sql(df):
    # We can use the pandas.to_sql command to put the data in our SQLlite database:
    df.to_sql("main", sqlite_engine, if_exists='append', index=False)

work("path_to.txt', column_names, 105, length, to_sql)

I am using SQLite3 through a sqlalchemy object to interact with it. All columns in the database are TEXT types because I was trying to alleviate this problem.

Anyone have any guidance?

Answer 1

The Problem

I just encountered the same error message. It seems from what is said in this github issue that this is a problem of sqlite3 not handling unsigned 64-bit numbers.

Overflow Errors

By the way, an Overflow Error happens when you get out of the bounds of the number type.

Example

If you add 1 to the number 15, which is stored as a 4-bit integer you get 0, because 16 is 10000 in binary, but only the last four bits (0000) can be stored.

[1111] + [0001] = 1 [0000]

The Solution?

So the only kind of workaround I see, would be to store your integer as a string.