How can I search a record in MySQL using Python

Question

def search(title="",author="",year="",isbn=""):
    con = mysql.connector.connect(host="localhost", user="root", passwd="junai2104", database="book")
    cur = con.cursor()
    sql_statement = "SELECT * FROM book WHERE title={} or author={} or year={} or isbn={} ".format(title,author,year,isbn)
    cur.execute(sql_statement)
    rows=cur.fetchall()
    con.close()
    return rows    
print(search(title='test2'))

How can I search a value in MySQL using Python argument? how to get a values from the argument?

How does formatting `'{}'.format('')` work out? Your sql statement will be syntactically erroneous. IIRC, you can pass arguments into `cur.execute` for sql binding. — TrebledJ, Jan 18 '19 at 07:27
Provide a schema description at least for filtering the rows. And how do you want to do it? In sql? In Python? How many records are going to return? — knh190, Jan 18 '19 at 07:30

score 0 · Answer 1 · answered Jan 18 '19 at 09:05

You have a couple of issues with your code:

In your SQL SELECT statement you are looking for values in text columns (TEXT, VARCHAR etc.). To do so you must add single quotes to your search qriteria, since you want to indicate a text literal. So WHERE title={} should be WHERE title='{}' (same goes for the other parameters).
When one or more of your arguments are empty, you will search for rows where the respective value is an empty text. So in your example search(title='test2') will trigger a search for an entry where the title column has the value 'test2' or any of the other three columns (author, year and isbn) has an empty text. If you inted to look for a title 'test2', this will only work if none of the other columns will ever contain an empty text. And even then, because of the three OR operators in your query, performance will be poor. What you should do instead is to evaluate each parameter individually and construct the query only with the parameters that are not empty.
By constructing your query with formatting a string, you will create a massive security issue in case the values of your search parameters come from user input. Your code is wide open for SQL injection, which is one of the simplest and most effective attacks on your system. You should always parametrize your queries to prevent this attack. By general principle, never create SQL queries by formating or concatenating strings with their parameters. Note that with parametrized queries you do not need to add single quotes to your query as wriitten in point 1.

How can I search a record in MySQL using Python

1 Answers1