Using WHERE in query PyMySQL

Question

I am trying to create a program where a user can enter an operator i.e. <> or = and then a number for a database in pymysql. I have tried a number of different ways of doing this but unfortunately unsuccessful. I have two documents with display being one and importing display into the other document.

Docuemnt 1

def get_pop(op, pop):
if (not conn):
    connect();

query = "SELECT * FROM city WHERE Population %s %s"


with conn:
    cursor = conn.cursor()
    cursor.execute(query, (op, pop))
    x = cursor.fetchall()
    return x

Document two

 def city():
     op = input("Enter < > or =: ")
     population = input("Enter population: ")
     pop = display.get_pop(op, population)
     for p in pop:  
     print(pop)

I am getting the following error.

pymysql.err.ProgrammingError: (1064,......

Please help thanks

You can't do this. You can't parameterize the operator, you'll need to .format() (or similar) that in to the string — roganjosh
– roganjosh, Commented Apr 11, 2019 at 22:42

roganjosh · Accepted Answer · 2019-04-11 23:06:11Z

3

You can't do this. Parameterization works for values only, not operators or table names, or column names. You'll need to format the operator into the string. Do not confuse the %s placeholder here with Python string formatting; MySQL is awkward in that it uses %s for binding parameters, which clashes with regular Python string formatting.

The MySQL %s in a query string escapes the user input to protect against SQL Injection. In this case, I set up a basic test to see if the operation part submitted by the user was in a list of accepted operations.

def get_pop(op, pop):
    query = "SELECT * FROM city WHERE Population {} %s" # Add a placeholder for format

    with conn: # Where does this come from?
        cursor = conn.cursor()
        if op in ['=', '!=']:
            cursor.execute(query.format(op), (pop,))
            x = cursor.fetchall()
            return x

You'll want to come up with some reasonable return value in the case that if op in ['=', '!='] is not True but that depends entirely on how you want this to behave.

edited Apr 11, 2019 at 23:06

answered Apr 11, 2019 at 22:49

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

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

1 Comment

JD12 Over a year ago

Thanks for your help :)

J.R. · Accepted Answer · 2019-04-11 22:47:52Z

1

After checking that op indeed contains either "<>" or "=" and that pop indeed contains a number you could try:

query = "SELECT * FROM city WHERE Population " + op + " %s";

Beware of SQL injection.

Then

cursor.execute(query, (pop))

answered Apr 11, 2019 at 22:47

J.R.

1,98811 silver badges16 bronze badges

1 Comment

JD12 Over a year ago

Thanks for your help J.R.

Collectives™ on Stack Overflow

Using WHERE in query PyMySQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related