How to fetch data in loop in python mysql [duplicate]

Question

I have a list and i want to pass all value of list to the where condition of Query.I am using This in Maya Python script Editor using maya cmds

code

list = [1,2,3]
db = MySQLdb.connect("host","root","password","test" )  
for num in list:             
   cursor = db.cursor()
   cursor.execute('select  Name from project WHERE projectID = %s '%(num))
   name  = cursor.fetchone() 
   print(name)      
   cursor.close()

Error

Error: ProgrammingError: file C:\Program Files\Autodesk\Maya2014\Python\lib\site-packages\MySQLdb\connections.py line 38: 1064 #

I think part of error message is missing. Seems like a syntax error, can you verify table and field names? Also, given that the list is ints I would give a try to: cursor.execute('select Name from project WHERE ProjectID = %d;' % (num)) — urban
– urban, Commented Dec 29, 2017 at 9:13
If you feel that the duplicate target does not answer your question, then please include the full traceback of the error, not just a truncated snippet. — Ilja Everilä
– Ilja Everilä, Commented Dec 29, 2017 at 9:59
i have already given full snippet of error.I am using this code in maya 2014 @IljaEverilä Everilä — python
– python, Commented Dec 29, 2017 at 10:03
Your question's first part is strongly related to, if not a duplicate of stackoverflow.com/questions/589284/…. That "snipper of error" is almost next to useless without the traceback (and the rest of the error message). Note that if those credentials you edited out were real, they're still visible in the edit history. — Ilja Everilä
– Ilja Everilä, Commented Dec 29, 2017 at 10:14

M. Radević · Accepted Answer · 2017-12-29 09:02:58Z

0

Try using cursor.executemany('select Name from project WHERE ProjectID = %s', list) without the for loop.

Doing so will be better for your performance, as the database won't be used in every for loop, just once.

Also avoid calling things list, tuple etc.

answered Dec 29, 2017 at 9:02

M. Radević

1224 bronze badges

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

3 Comments

Ilja Everilä Over a year ago

@Barmar Damn, I'm blind and missed it's executemany(). But in that case it should be a sequence of param sequences... Also did MySQLdb support multiple result sets with executemany()?

M. Radević Over a year ago

Convert it to a list of tuples. More here:dev.mysql.com/doc/connector-python/en/…

Ilja Everilä Over a year ago

A note about executemany() from the DB-API 2.0 spec: "Use of this method for an operation which produces one or more result sets constitutes undefined behavior, and the implementation is permitted (but not required) to raise an exception when it detects that a result set has been created by an invocation of the operation."

Collectives™ on Stack Overflow

How to fetch data in loop in python mysql [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related