SQLite3 math functions Python-CodePudding

After updating SQLite Version 3.5.0. It is possible to use the SQL math functions. If I use it in pycharm query it works well, but I can`t execute the query in python code.

Then I got the following error message:

pandas.io.sql.DatabaseError: Execution failed on sql 'Select log(increase.s1) From increase': no such function: log.

I execute it with the following code:

import pandas as pd

conn = sqlite3.connect('p1_database.db')

sql = "Select log(increase.s1) from increase"
pd.read_sql_query(sql, con=conn)

What is my mistake? I don`t see it.

CodePudding user response：

If math functions are not enabled in sqlite3 module then you can apply log function to the column and return a series.

import sqlite3
import pandas as pd
import math

with sqlite3.connect(":memory:") as conn:
    cur = conn.cursor()
    
    cur.execute("create table increase(s1 integer)")
    cur.execute("insert into increase (s1) values (1)")
    cur.execute("insert into increase (s1) values (2)")
    cur.execute("insert into increase (s1) values (100)")

    df = pd.read_sql_query('SELECT * FROM increase', conn)
    logseries = df['s1'].map(lambda x: math.log(x))
    log10series = df['s1'].map(lambda x: math.log10(x))
    for r in zip(df['s1'], logseries, log10series):
        print(f"{r[0]:3d} {r[1]:.3f} {r[2]:.3f}")

Output:

  1 0.000 0.000
  2 0.693 0.301
100 4.605 2.000

CodePudding user response：

The sqlite3 module from the standard Python library does not support math functions. But it allows to easily create new functions:

...
import math

conn = sqlite3.connect('p1_database.db')
conn.create_function('log', 1, math.log10)

sql = "Select log(increase.s1) from increase"
pd.read_sql_query(sql, con=conn)

Should do the job (I used math.log10 because the log function from Sqlite3 is actually a log10).