Python - automating MySQL query: passing parameter

Question

Hello Everybody,

The code in the sequence is working fine, but looking to improve the MySQL code to a more efficient format.

The first case is about a function that received a parameter and returns the customerID from MySQL db:

def clean_table(self,customerName):
    getCustomerIDMySQL="""SELECT customerID
    FROM customer
    WHERE customerName = %s;"""

    self.cursorMySQL.execute(getCustomerIDMySQL,(customerName))
    for getID_row in self.cursorMySQL:
        customerID=getID_row[0]

    return customerID

In the case we know before hand that the result will be just one output, how to get the same thing into my getID_row, without using "for" statement?

For the second case, the function is running with the table name ('customer') on it...

def clean_tableCustomer(self):
    cleanTableQuery = """TRUNCATE TABLE customer;"""
    self.cursorMySQL.execute(cleanTableQuery)

    setIndexQuery = """ALTER TABLE customer AUTO_INCREMENT = 1;"""
    self.cursorMySQL.execute(setIndexQuery)

then, how to replace the table name as a parameter passed through the function? Here is how I tried to get this done:

def clean_table(self,tableName):
    cleanTableQuery = """TRUNCATE TABLE %s;"""
    self.cursorMySQL.execute(cleanTableQuery,(tableName))

    setIndexQuery = """ALTER TABLE %s AUTO_INCREMENT = 1;"""
    self.cursorMySQL.execute(setIndexQuery,(tableName))

But MySQL didn't work this time.

All comments and suggestions are highly appreciated.

Answer 1

Unfortunately, you cannot parametrize the name of a table (see this post). You will have to use Python string operations to do what you are attempting here.

Hope this helps, it took me a while to find out when I ran into this issue.

Answer 2

For the first case (simple, but easy to get a KeyError when there is no row):

customerID = self.cursorMySQL.fetchone()[0]

More correct is to implement a new method for the cursor class:

def autofetch_value(self, sql, args=None):
    """ return a single value from a single row or None if there is no row
    """
    self.execute(sql, args)
    returned_val = None

    row = self.fetchone()
    if None != row:
        returned_val = row[0]

    return returned_val

For the second case:

def clean_table(self,tableName):
    cleanTableQuery = """TRUNCATE TABLE %s;""" % (tableName,)
    self.cursorMySQL.execute(cleanTableQuery)

    setIndexQuery = """ALTER TABLE %s AUTO_INCREMENT = 1;""" % (tableName,)
    self.cursorMySQL.execute(setIndexQuery)

Make sure you sanitize the data, since the cursor won't.