ThreaderSlash ThreaderSlash - 5 months ago 13
MySQL Question

Python - automating MySQL query: passing parameter

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;"""

for getID_row in self.cursorMySQL:

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;"""

setIndexQuery = """ALTER TABLE customer AUTO_INCREMENT = 1;"""

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;"""

setIndexQuery = """ALTER TABLE %s AUTO_INCREMENT = 1;"""

But MySQL didn't work this time.

All comments and suggestions are highly appreciated.


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 row is not None:
        returned_val = row[0]

    return returned_val

For the second case:

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

    setIndexQuery = """ALTER TABLE %s AUTO_INCREMENT = 1;""" % (tableName,)

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