appending a parameter to sql script in python

Question

Suppose a simple query in python as follows. The python code reads a "new city" from a user. The question is how to update the query by appending a list of cities (e.g., 'city1', 'city2', 'city3') to the where clause in the query.

mycursor = mydb.cursor()

sql = "select * FROM my_table 

WHERE (city = 'Seattle') 
or (city = 'Portland')"

mycursor.execute(sql)

mydb.commit()

desired sql script

mycursor = mydb.cursor()

sql = "select * FROM my_table 

WHERE (city = 'Seattle') 
or (city = 'Portland')
or (city =  'city1') 
or (city = 'city2)
or (city ='city3')"

mycursor.execute(sql)

mydb.commit()

Is there a reason you're not using WHERE city IN ('Seattle', 'Portland', ...)? — Barmar
– Barmar, Commented Jun 30, 2021 at 20:12

Barmar · Accepted Answer · 2021-06-30 20:21:23Z

2

Use city IN (...) rather than multiple city = operations. Then you can build the list of placeholders dynamically and replace them using parameters.

cities = ['Seattle', 'Portland', 'city1', 'city2', 'city3']
placeholders = ','.join(['%s']*len(cities))
sql = f'SELECT * FROM my_table WHERE city IN ({placeholders})'
mycursor.execute(sql, cities)

answered Jun 30, 2021 at 20:21

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Comments

Jaison Thomas · Accepted Answer · 2021-06-30 20:13:20Z

0

The solution would require string manipulation like so (assuming u take input to a variable newCity):

mycursor = mydb.cursor()
sql = "select * FROM my_table WHERE (city = 'Seattle') or (city = 'Portland')"

#put in for loop for as long as ud like 
newCity = input("insert new city")
sql = sql + " or (city = \'" + newCity + "\')"

mycursor.execute(sql)
mydb.commit()

answered Jun 30, 2021 at 20:13

Jaison Thomas

4032 silver badges9 bronze badges

Collectives™ on Stack Overflow

appending a parameter to sql script in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related