CODEWITHSUNDEEP
pythonmysqlparametersautomation
ThreaderSlash
ThreaderSlash

Reputation: 1313

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

    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.

Upvotes: 2

Views: 2369

Answers (2)

avpx
avpx

Reputation: 1922

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.

Upvotes: 0

mluebke
mluebke

Reputation: 8799

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,)
    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.

Upvotes: 3

Related Questions