CODEWITHSUNDEEP
pythonmysqldatabase
William Calhoun
William Calhoun

Reputation: 95

User Input to mySQL database

I want a simple python program to prompt the user for a title and search a mySQL database. I have the database constructed but I can't figure out how to query it properly.

import MySQLdb
servername = "localhost";
username = "username";
password = "password";
conn = MySQLdb.connect(user=username,host=servername, passwd=password, db=db)
cursor = conn.cursor()
user_input = raw_input("What do you want to watch?:")
query= ("SELECT * from videos where title LIKE %s")
cursor.execute(query,user_input)

IF, I hardcode the query to say "where title LIKE '%HARDCODE%' ", then it works fine. I figure the solution is pretty simple but I for the life of me can't figure it out. Any help is much appreciated! I've tried every variation I could find online but to no avail. Some others I tried:

query= ("SELECT * from videos where title LIKE CONCAT('%',%s,'%')")

cursor.execute(query,(user_input,))

query= ("SELECT * from videos where title LIKE (search)"
        "VALUES (%s)", user_input)

... They all don't work.

Errors all seem to revolve around me passing my variable user_input through correctly.

Upvotes: 0

Views: 8057

Answers (3)

Stickman Dev.
Stickman Dev.

Reputation: 31

The solution to your problem is putting your input in brackets ( ,) so the result would look something like this: 

user_input = (raw_input("What do you want to watch?:"), )

Don't forget to put the comma ,

In full you have:

import MySQLdb
servername = "localhost";
username = "username";
password = "password";
conn = MySQLdb.connect(user=username,host=servername, passwd=password, db=db)
cursor = conn.cursor()
user_input = (raw_input("What do you want to watch?:"), )
query= ("SELECT * from videos where title LIKE %s")
cursor.execute(query,user_input)

Upvotes: 0

Strik3r
Strik3r

Reputation: 1057

I Think the solution given by @ddsu should have solved your problem. in case if not, in this i am going to use the same line of code but i am posting a full code for ur problem

    import MySQLdb
    servername = "localhost"
    username = "username"
    password = "password"
    conn = MySQLdb.connect(user=username,host=servername, passwd=password, db=db)
    cursor = conn.cursor()
    user_input = raw_input("What do you want to watch?:")
    query= ("SELECT * from videos where title LIKE %"+user_input+"%")
    cursor.execute(query)

Upvotes: 0

dacuna
dacuna

Reputation: 1096

You can create your query like:

c.execute("SELECT * FROM videos WHERE title LIKE %s", ("%" + user_input + "%",))

Upvotes: 3

Related Questions