CODEWITHSUNDEEP
pythonsqlsqlite
nad
nad

Reputation: 2850

Python sqlite3 select rows with only a certain variable value in them on a specific column

I have a table with following columns

command_create_table = """CREATE TABLE if not exists place (
name VARCHAR(100),
lat DOUBLE(100, 10),
lng DOUBLE(100,10),
vicinity VARCHAR(100),
typeOfPlace VARCHAR(100));"""

This typeOfPlace column can contain types such as food, restaurant, museum etc. Now based on an user input which I capture in a variable called typeVar that indicates a specific value in typeOfPlace column, I want to retrieve items. So I have below code:

connection = sqlite3.connect("places.db")

cursor = connection.cursor()      
cursor.execute("SELECT * from place WHERE typeOfPlace=typeVar")
ans= cursor.fetchall()

But I am getting error 

cursor.execute("SELECT * from place WHERE typeOfPlace=typeVar")
OperationalError: no such column: typeVar

What am I missing? Please suggest.

Upvotes: 0

Views: 2636

Answers (2)

sjw
sjw

Reputation: 6543

Try:

cursor.execute("SELECT * from place WHERE typeOfPlace=?", (typeVar,))

The trailing comma after typeVar might look odd, but params expects a sequence, and adding the trailing comma makes the value passed a tuple of length 1.

Never use string formatting to pass in values to an SQL query. Bad things can happen if the variable contains SQL syntax. Look up SQL injection attacks for more details on why you should not do this. Passing parameters separately as I’ve done here is the way to go.

Upvotes: 3

Colin Ricardo
Colin Ricardo

Reputation: 17249

The issue is that you're checking for the literal string "typeVar", not the user input.

Instead of

cursor.execute("SELECT * from place WHERE typeOfPlace=typeVar")

try:

cursor.execute("SELECT * from place WHERE typeOfPlace={}".format(typeVar)).

Upvotes: 0

Related Questions