0

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 #

python
  • 83
  • 1
  • 16
  • 1
    Right, it should be `cursor.execute('select ...', (num,))` – Barmar Dec 29 '17 at 09:01
  • 1
    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 Dec 29 '17 at 09: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ä Dec 29 '17 at 09:59
  • i have already given full snippet of error.I am using this code in maya 2014 @IljaEverilä Everilä – python Dec 29 '17 at 10:03
  • Your question's first part is strongly related to, if not a duplicate of https://stackoverflow.com/questions/589284/imploding-a-list-for-use-in-a-python-mysqldb-in-clause. 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ä Dec 29 '17 at 10:14

1 Answers1

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.

M. Radević
  • 102
  • 4
  • @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()`? – Ilja Everilä Dec 29 '17 at 09:06
  • Convert it to a list of tuples. More here:https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html – M. Radević Dec 29 '17 at 09:08
  • A note about `executemany()` from the [DB-API 2.0 spec](https://www.python.org/dev/peps/pep-0249/#executemany): "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." – Ilja Everilä Dec 29 '17 at 09:19