Python 3.4 mysql SELECT / WHERE query - syntax problem

I have a simple form which passes a single entered name to an action page.  The action page picks up the name entered and uses it in the function below.  The objective is to identify and print out information on those records which have  the form entered name.

The database name is office, the table being queried is members2

Below is the error message I am receiving. You will see that the name KANE is included in the error message - This is the name entered into the form for this particular test - proving to me that the form entry is available to the mysql query below.

Appreciate if you could have a look at the syntax - hopefully something obvious to the experienced eye !

many thanks

james

mysql query

def selectpeople(db,cursor):
      sql = ("""select * from members2
      where nom = %s""" %(name))
      cursor.execute(sql)
      all_names = cursor.fetchall()
      return all_names  

Open in new window


ERROR MESSAGE
mysql.connector.errors.ProgramingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['kane', 'Submit']' at line 2
jameskaneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Try using single quotes around the %s like: '%s'  That's what is normally expected in a MySQL query.  My little MySQL Py demo has the sql string set up differently.
sql = "select * from members2 \
      where nom = '%s' " %(name))

Open in new window

jameskaneAuthor Commented:
Thanks for looking at this Dave.  No luck so far I'm afraid.  I have tried your string set up with and without the " on the  %s.  I show the strings and the error codes below.  I have also tried the string set up I used with and without the '' on the %s and I show the strings and error codes below.  

As you know, I am using python 3 and my reading of the results below suggests that the %s should not be bracketed. The strings in the error messages seem distorted when the " are used ??  

But either way, the problem is not solved.   If I forget the WHERE clause ... sql = ("SELECT * FROM members2")..
I do get the full set of data printed out.

Below is the function which I use for printing out the data - maybe there is a problem there when the WHERE clause is used?

def displayAll_names(all_names):
      print("<table border='1'>")
      print("<tr>")
      print("<th>Member_ID</th>")
      print("<th>civilities</th>")
      print("<th>PreNom</th>")
      print("<th>Nom</th>")
      print("<th>adresse</th>")
      print("</tr>")
      for each in all_names:
            print("<tr>")
            print("<td>{0}</td>".format(each[0]))
            print("<td>{0}</td>".format(each[1]))
            print("<td>{0}</td>".format(each[2]))
            print("<td>{0}</td>".format(each[3]))
            print("<td>{0}</td>".format(each[4]))
            print("</tr>")

      print("</table>")	        

Open in new window


Tks again for the help,
james

ERROR MESSAGE TESTS

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['kane', 'Submit']' at line 1

sql = ("select * from members2 \
      where nom = %s " %(name))  

     
 ................................................................................................................    
     
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'kane', 'Submit']'' at line 1

     sql = ("select * from members2 \
      where nom = '%s' " %(name))  

     
 ======================================================================================
     
     
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['kane', 'Submit']' at line 2

 sql = ("""SELECT * FROM members2
      WHERE nom = %s """ %(name))

     
     .........................................................................................................................................
     
     
      mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'kane', 'Submit']'' at line 2
     
      sql = ("""SELECT * FROM members2
      WHERE nom = '%s' """ %(name))  

     
      =====================================================================================
Dave BaldwinFixer of ProblemsCommented:
The code I referred to is using Python 2.7.  I have 3.4 on another machine and I'll check it there.  It appears to me that the most basic problem is that the WHERE statement expects a single value and not a list.  That and the single quotes are what MySQL expects and that is true in all languages.  I have run essentially this same query in PHP, ASP, Python 2.7, Perl, and on the 'mysql' command line.  Strings in that context are normally single quoted.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Dave BaldwinFixer of ProblemsCommented:
The Python 3.4 code below is derived from the code on this page:  http://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html  I created several versions of it to check it.
import mysql.connector

config = {
  'user': 'username',
  'password': 'userpwd',
  'host': '10.202.46.41',
  'database': 'dibsites',
  'raise_on_warnings': True,
}

cnx = mysql.connector.connect(**config)

cursor = cnx.cursor()

query = ("SELECT ent_num, SortName, Descript FROM websitelist "
         "WHERE ent_num = '%s'" %(19))

wstart = "17"
wend = "92"

cursor.execute(query)

for (ent_num, SortName, Descript) in cursor:
  print("{}, {} was {}".format(
    SortName, Descript, ent_num))

cursor.close()
cnx.close()
print("Done with myseldibs.py")

Open in new window

jameskaneAuthor Commented:
Great !!!  problem solved.   All I needed to do was to add  name=name[0]   to the getData() function.

def getData():
      formData = cgi.FieldStorage()
      name = formData.getvalue('membername')
      name=name[0]
      return name

Thanks again for solution and learning AND for the extra example !!!

james
Dave BaldwinFixer of ProblemsCommented:
You're welcome, glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.