Python SQL select row from specific variable field

Question

I'd like to grab a specific value from a row based on a random variable. Here's an example table the PID column is an "auto-increment primary key integer" and the other 2 columns are TEXT

example-table

PID    NAME    PHONE
---    ----    -----
1      bill    999-9999
2      joe     888-8888

I'd like to throw a random variable at the table

randomVariable = raw_input('Enter something: ')

> 1

and have the code return the name

> bill

I know I can use something like...

randomVariable = raw_input('Enter something: ')
sql = ("SELECT name FROM example_table WHERE pid='%s'" % randomVariable)
result = cursor.execute(sql)
print result

> bill

Apparently using '%s' isn't secure and it is suggested to use '?' in it's place.

randomVariable = raw_input('Enter something: ')
sql = ("SELECT name FROM example_table WHERE pid=?", randomVariable)
result = cursor.execute(sql)
print result

But this doesn't seem to work for me. I end up with...

"ValueError: operation parameter must be str or unicode"

I realize I could just grab all the rows and put them into a variable which I could then iterate over till I find what I'm looking for but I'm thinking that wouldn't be very efficient with a large database. can anyone help point me in the right direction with this?

RichardTheKiwi · Accepted Answer · 2011-01-25 10:49:24Z

3

I believe you're meant to use it like this

randomVariable = raw_input('Enter something: ')
sql = "SELECT name FROM example_table WHERE pid=?"
result = cursor.execute(sql, randomVariable)
print result

answered Jan 25, 2011 at 10:49

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

1 Comment

shload Over a year ago

Ah man finally! I got the result I've been looking for. This has been one of those programming catchas that I have rarely run into...I just knew the solution was going to be simple. Thanks cyberkiwi

atx · Accepted Answer · 2011-01-25 10:50:33Z

1

Validate the user input, and %s is fine. Storing your rows and putting them into a list is not a good idea at all, since the amount of rows will grow over time, taking up a huge amount of memory when not even in use. To guard against SQL injection, you could validate input using something like a typecast to an int, put that in a try/except block, and this would stop all malicious input such as ' OR 1=1--

answered Jan 25, 2011 at 10:50

atx

5,0993 gold badges30 silver badges42 bronze badges

2 Comments

shload Over a year ago

thanks for the input malfry. Though you lost me at, "you could validate input using something like a typecast to an int,". I got what I was looking for with cyberkiwis post, but i'm always happy to take advice and improve on my code. If you wouldn't mind elaborating or posting a link I could learn from I'd appreciate it =)

atx Over a year ago

Visit the Wikipedia article (en.wikipedia.org/wiki/SQL_injection) for info on SQL Injecction. You can typecast something to an int in Python like this: int(some_user_input)

Collectives™ on Stack Overflow

Python SQL select row from specific variable field

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related