Python program that takes input and runs queries on a mySQL database on it

Question

I've been slowly learning Python so I want to know what the most Python-esque way of doing things are.

The scenario is that I'm connected to a database with a Customer and a SalesRep table in a database called sports2000. I also have a flat file containing a list of customer id's and salesrep names. My end goal is to update these customers to have these corresponding salesreps assigned to them. I want to do three things:

1. Display information about all the customers in this list.
2. Display information about the customers in the list in Massachusetts.
3. Update the customers to have their new sales representatives.

Any ideas or better ways to make this code more Python-esque would be great as I'm trying to write code that looks like it was written by a competent Python developer.

import MySQLdb as mdb
class PrimeCustomer:
 id = 0
 newRep = ""
with open('input.txt', 'r') as f:
 lines = f.readlines()
primeCustomers = []
for line in lines:
 words = line.split()
 tt = PrimeCustomer()
 tt.id = int(words[0])
 tt.newRep = words[1]
 primeCustomers.append(tt)
try:
 db = mdb.connect('localhost', 'root', '', 'sports2000')
 cur = db.cursor()
 # Create a string to use for the SQL IN operator
 customerNumbers = '('
 for prime in primeCustomers:
 customerNumbers += str(prime.id)
 customerNumbers += ', '
 # Remove the trailing comma and space and add ending parenthesis 
 customerNumbers = customerNumbers[:-2]
 customerNumbers += ')'
 cur.execute("SELECT Name, custNum from customers where custNum in {}".format(customerNumbers))
 row = cur.fetchone()
 while row is not None:
 print ", ".join([str(c) for c in row])
 row = cur.fetchone()
 # Get a list of all the new salesReps for Massachusetts customers
 cur.execute("SELECT Name, custNum from customers where State = 'MA'")
 row = cur.fetchone()
 while row is not None:
 for prime in primeCustomers:
 if row[1] == prime.id:
 print row[0], prime.newRep
 row = cur.fetchone()
 # Go through each customer and assign them the sales rep
 # with the given name
 for prime in primeCustomers:
 cur.execute("UPDATE customer " + \
 "SET salesRep = " + \
 "(SELECT salesRep FROM SalesRep WHERE repName = '{}' LIMIT 1) ".format(prime.newRep) + \
 "WHERE customerNumber = {}".format(prime.id))
 db.commit()
except Exception as e:
 db.rollback()
 print e
finally:
 if cur:
 cur.close()
 if db:
 db.close()

• Michael Brandon Morris: Not a Python dev, but I don't think names like mdb and tt are very useful to anyone who would read your code. Furthermore, the global exception catch probably isn't ideal either.
• joshualan: @zondo Done, I've indented the code.
• joshualan: @MichaelBrandonMorris I hear you, I'll definitely use more aptly named variables.

Answer 1

A comment on this bit:

customerNumbers = '('
for prime in primeCustomers:
 customerNumbers += str(prime.id)
 customerNumbers += ', '
# Remove the trailing comma and space and add ending parenthesis 
customerNumbers = customerNumbers[:-2]
customerNumbers += ')'

can be written as:

customerNumbers = '(' + ', '.join(str(x) for x in primeCustomers.id) + ')'

My final comment has to do with the use of fetchone() instead of fetchall(). Is there a reason you do it like that? Instead of having the while statements which you have to evaluate in every loop you can just fetch all results and process them in a less "expensive" for loop like that:

rows = cur.fetchall()
for row in rows:
 #do stuff with the results of every row.