SQLite3 math functions Python

Question

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.

Answer 1

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).