how to select from sqlite3.db where i compare a result of equation that has variables from the db?-CodePudding

if i have a table like this:

("CREATE TABLE table (name text,x real, y real, state text) ")
[('name_1' ,x, y, 'active'), ('name_2' ,x, y, 'active')]

And two variable ref_1 = math.cos(some_input), ref_2 = math.cos(some_input)

and i want to SELECT only state = 'active' that their math.cos(x) < ref_1 and math.cos(y) < ref_2

what can i add to this line: curs.execute("SELECT * FROM table WHERE state = 'active #### code' to endup with what i want. m not used to work a lot around databases

This is the original line in my Code only lat and lon are variables inside the db

    active = 'active'
    curs.execute(f"SELECT * FROM drivers WHERE state = {active} and radius >= {math.sqrt( (lat - float(user_lat) )**2   (lon - float(user_lon) )**2)} and min_radius =<  {-math.sqrt( (lat - float(user_lat) )**2   (lon - float(user_lon) )**2)}  ")

i get this output:

     curs.execute(f"SELECT * FROM drivers WHERE state = {active} and radius >= {math.sqrt( (lat - float(user_lat) )**2   (lon - float(user_lon) )**2)} and min_radius =<  {-math.sqrt( (lat - float(user_lat) )**2   (lon - float(user_lon) )**2)}  ")
 NameError: name 'lat' is not defined

CodePudding user response：

You can use a f string to input different variables

curs.execute(f"SELECT * FROM table WHERE state = 'active and ref_1 < {math.cos(x)} code and ref_2 > {math.cos(y)}")

CodePudding user response：

import sqlite3
import math
user_lat = #
user_lon = #
radius_length = #
state = 'active'

linktoauth = sqlite3.connect('DRIVERS.db')
curs = linktoauth.cursor()

def _radius_len(x, a, y, b):
    result =  math.sqrt((x - a)**2   (y - b)**2)
    return result
linktoauth.create_function('rad', 4, _radius_len)
curs.execute(f"SELECT * FROM drivers WHERE state = ? and rad(lat, ?, lon, ?) <= ?  ",(state, user_lat, user_lon, radius_length,))