4
\$\begingroup\$

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()
\$\endgroup\$
3
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ Jun 27, 2016 at 19:37
  • \$\begingroup\$ @zondo Done, I've indented the code. \$\endgroup\$
    – joshualan
    Jun 28, 2016 at 0:28
  • \$\begingroup\$ @MichaelBrandonMorris I hear you, I'll definitely use more aptly named variables. \$\endgroup\$
    – joshualan
    Jun 28, 2016 at 0:28

1 Answer 1

2
\$\begingroup\$

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.
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.