Sql query with variables (real or integer)

Question

I'm learning python... I tried to execute sql queries with parameters without success...

I tried:

from tkinter import*
import tkinter as tk
from tkinter import ttk
import sqlite3


realNumber = 2.0

database = sqlite3.connect('NumDB.db')
cursor = database.cursor()
cursor.execute("SELECT numColumn from numTable WHERE realNumber=?", ( realNumber ))
results = cursor.fetchall()
print(results)
cursor.close()
database.close()

it works when I define the variable as text:

realNumber "2.0"

or

cursor.execute("SELECT numColumn from numTable WHERE realNumber=?", ( str(realNumber)))

the type of realNumber is set on real in the database.

is it possible to use real or integer variables without converting to string? Thanks

MZM · Accepted Answer · 2022-12-10T08:08:44.113

Depending on the version of Python, sample code below don't require a type conversion: (Note: to avoid sql injection option 4 is best.)

def func1(ag):
  return f" variable {ag}" 
def func2(ag):
  return "SELECT numColumn from numTable WHERE realNumber=?", ( ag )
def func3(ag):
  return f"SELECT numColumn from numTable WHERE realNumber={ag}"
def func4(ag):
  sql = "SELECT numColumn from numTable WHERE realNumber={}"
  return sql.format(ag)

ag = 40.5
print(func1(ag))
print(func2(ag))
print(func3(ag))
print(func4(ag))

Output:
    variable 40.5
    ('SELECT numColumn from numTable WHERE realNumber=?', 40.5)
    SELECT numColumn from numTable WHERE realNumber=40.5
    SELECT numColumn from numTable WHERE realNumber=40.5


#---------------Original code in question-----------------
    from tkinter import*
    import tkinter as tk
    from tkinter import ttk
    import sqlite3
    
    realNumber = 2.0
    
    database = sqlite3.connect('NumDB.db')
    cursor = database.cursor()
    cursor.execute("SELECT numColumn from numTable WHERE realNumber={}".format(realNumber) )
    results = cursor.fetchall()
    print(results)
    cursor.close()
    database.close()

Sql query with variables (real or integer)

1 Answers1