July 29th, 2013, 11:02 PM
Python-mysql question concerning %s
I've done up the beginning of a script that connects to MySQL. What I'm trying to figure out is in the ("SELECT * FROM table") I want the user prompted to supply the table name. I've tried messing with the syntax but always get:
Traceback (most recent call last):
File "mysql.py", line 22, in <module>
cur.execute("SELECT * FROM %r") % (table)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "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 '%s' at line 1")
here is a screenshot:
Am I doing something simple wrong?
July 29th, 2013, 11:04 PM
I guess I am unaware as to how to add the image.
Originally Posted by MTGailey
#a simple script to pull some data from a mysql table
db = MySQLdb.Connect (host='localhost', user="root", passwd="brightleaf001", db="wastewater" )
cur = db.cursor()
table = raw_input("Enter a table name and press enter:\n")
cur.execute("SELECT * FROM %s") % (table)
for row in cur:
#loop to iterate
July 30th, 2013, 12:00 AM
You're closing the parenthesis before the % operator, so the method is receiving the literal string without any replacement going on.
So first, you need to move the "% (table)" bit inside the parenthesis.
Second, if you care at all about the data in this database, you need to make sure you are sanitizing the "table" variable, such as checking it against a list of valid table names. Otherwise you've just created a whopper of a SQL injection vulnerability.
July 30th, 2013, 12:44 PM
Thank you for the info. Im just learning all of this, so I will definatly look into your observations.