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

Question

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.

sjw · Accepted Answer · 2018-04-17 23:29:34Z

3

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.

edited Apr 17, 2018 at 23:29

answered Apr 17, 2018 at 23:19

sjw

6,5512 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nad Over a year ago

getting error "SyntaxError: EOL while scanning string literal"

sjw Over a year ago

@nad I think I had the wrong type of quotation mark, edited if you can try again please.

nad Over a year ago

getting a different error now "TypeError: execute() takes no keyword arguments" pointing to the same line

sjw Over a year ago

@nad I’ve edited again and think it’s right this time. Third time lucky...

Colin Burke · Accepted Answer · 2018-04-17 23:10:08Z

0

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

edited Apr 17, 2018 at 23:10

answered Apr 17, 2018 at 22:55

Colin Burke

17.3k13 gold badges57 silver badges84 bronze badges

4 Comments

nad Over a year ago

so now if my variable input is "food", it is telling me "no such column as food". Clearly I am not checking for column named food but the rows where "typeOfPlace" column has value "food".

Colin Burke Over a year ago

If you do select * from place; what output do you get?

nad Over a year ago

it outputs the entire table

Colin Burke Over a year ago

And the table has the entries for 'food', etc.?

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related