Python - automating MySQL query: passing parameter

Question

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.

mluebke · Accepted Answer · 2016-06-07 17:47:33Z

3

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.

edited Jun 7, 2016 at 17:47

answered Jan 8, 2010 at 0:01

mluebke

8,8697 gold badges38 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stephen Ostermiller · Accepted Answer · 2024-03-30 08:43:30Z

0

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.

edited Mar 30, 2024 at 8:43

Stephen Ostermiller♦

25.8k18 gold badges96 silver badges117 bronze badges

answered Jan 7, 2010 at 23:47

avpx

1,93215 silver badges13 bronze badges

Collectives™ on Stack Overflow

Python - automating MySQL query: passing parameter

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related