[Solved] Logarithm function in sqlite query?

I really need to perform an update on over 400,000 rows using the logarithm function. Unfortunately, the SQL logarithm function does not seem to exist in sqlite. Is there any way I can derive the logarithm function or import the LOG function?

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


I also found out why it took so long. The primary key in the database was not marked as a primary key. So the code that I was using followed:

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)

Solution #1:

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

If you’re using Python, this is possible with both the pysqlite and APSW modules.

Respondent: CL.

Solution #2:

It turns out that math functions (including logarithm) are implemented in Sqlite but disabled in standard Sqlite compilation on Ubuntu. In order to enable them you should download Sqlite sources from here (“Obtaining the code”) and compile according to the instructions (“Compiling”). The necessary option -DSQLITE_ENABLE_MATH_FUNCTIONS will be enabled by default. After compilation you will get the sqlite3 executable in the build directory, this should be run as a local file:

$ ./sqlite3 <database-file>

BEFORE the compilation I recommend to install readline so that history browsing (the arrows) and auto-completion work correctly in the Sqlite command line.

sudo apt install libreadline-dev
Respondent: Marcin Wojnarski

Solution #3:

What makes you think it will be O(n^2)? If anything, it should be linear: O(n).

Just about the only reason why it is so slow if you use auto-commit mode – this would cause SQLite to flush buffers to disk on every single UPDATE.

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

Respondent: mvp

Solution #4:

Use log10(number) function.

For anti log, use power(10, number) function.

Respondent: Patrick

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.