Logarithmic function in sqlite query?

I actually need to execute update

on over 400,000 lines using the log function. Unfortunately the SQL logarithm function doesn't seem to exist in sqlite. Is there a way to get the logarithm function or import the function LOG

?

The only other way I know how to do this is I believe the order is O (n ^ 2) via python. This approach will take too long (I tried, it took about 1.5 hours to get through 6% on my slow computer).

EDIT:

I also figured out why it took so long. The primary key in the database has not been marked as a primary key. Thus, the code I used was:

for row in database:
    ...calculations for the row...
    ...sql update for the specific row which follows:...
    for search_row in database:
        if search_row[id] = row[id]:
            ...update values here...

      

Incredibly inefficient ... O (n ^ 2)

+4


source to share


3 answers


There is no built-in logarithm function; you have to define your own function.



If you are using Python this is possible with both pysqlite and APSW .

+4


source


Why do you think it will be O(n^2)

? In any case, it should be linear O(n)

.

The immediate reason why this is so slow if you use autocommit mode is that it will cause SQLite to flush buffers to disk on every update.



If you start a transaction then use a SELECT / UPDATE loop and finally commit, your update should be very fast (assuming your log function is not very slow).

+1


source


Use a function log10(number)

.

Use the function for write protectionpower(10, number)

.

-1


source







All Articles