Querying and retrieving data from Mysql table using python

Question

I am trying to retrieve data from mysql table using python but keep getting an error. Please see my attempt below and the data:

import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="root", passwd="xxxxxxx", db="world")
cursor = db.cursor()
t=['red', 'yellow']

for x in t:
    cursor.execute("select * from mytable where colours=%s," [x])

I got the following error:TypeError: string indices must be integers, not str

I learnt Mysql stores data as tuples, hence I would need change %s to a tuple but don't know how.

Any suggestions? thanks.

cursor.execute("select * from mytable where colours=%s",[x]) comma misplaced ? — Nagendra Nigade
– Nagendra Nigade, Commented May 23, 2015 at 10:01
@NagendraNigade, well spotted, many thanks for you good eyes. — user2274879
– user2274879, Commented May 23, 2015 at 10:06

MaNKuR · Accepted Answer · 2015-05-23 10:12:20Z

2

Try this and check if this resolve your problem

import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="root", passwd="xxxxxxx", db="world")
cursor = db.cursor()
t=['red', 'yellow']

for x in t:
    cursor.execute("select * from mytable where colours=%s" % repr(x))

Please note that here the Sql Query will build like

select * from mytable where colours='red'   #which is actual and correct query

Though I have not tested yet and there could be alternate way of doing this. The actual problem with OP problem is that it build Sql Query like

select * from mytable where colours=red  # Check the missing quotation around text red

edited May 23, 2015 at 10:12

answered May 23, 2015 at 10:07

MaNKuR

2,7141 gold badge22 silver badges31 bronze badges

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

7 Comments

user2274879 Over a year ago

good one, it worked. Please can you leave some explanation below. Thank you.

MaNKuR Over a year ago

Can you please accept the answer and tell me where you did not get it.

user2274879 Over a year ago

I ran into a different problem and would really appreciate your suggestion. Here is what I fetched out ('red', '[1,2,3,4]'). Can you convert '[1,2,3,4]' to just a list?

MaNKuR Over a year ago

I dont know if i am right but it seems you want sql query with in clause.. So if this true then you can try using select * from mytable where range in %s' % str(tuple(x['red'])) where x['red'] = [1,2,3,4] # you can use repr as well

user2274879 Over a year ago

I am not doing it for red alone, I am iterating over lots of other data.

|

maggick · Accepted Answer · 2015-05-23 12:27:51Z

0

You want to iterate over the element in t. Therefore you do not need the [] around x:

import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="root", passwd="xxxxxxx", db="world")
cursor = db.cursor()
t=['red', 'yellow']

for x in t:
    cursor.execute("select * from mytable where colours=%s", x)

edited May 23, 2015 at 12:27

answered May 23, 2015 at 10:02

maggick

1,42214 silver badges23 bronze badges

1 Comment

user2274879 Over a year ago

thanks for the attempt, Nagendra Nigade has already spotted the error in my code.

Collectives™ on Stack Overflow

Querying and retrieving data from Mysql table using python

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related