#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Atlantic City, NJ
    Posts
    327
    Rep Power
    13

    Class Goes Here?


    I have some code I'm using that connects to a MySQL database for a website I'm trying to put together. Its basically just a bunch of functions. It looks like this:

    Code:
    #!C:/Python24/Python.exe
    
    import MySQLdb
    
    def add_names(fname, lname, pnumber, buildid, divid):
    	"""Add the firstname, lastname,
    	and phonenumber to the names_table
    	table and link them"""
    	db = MySQLdb.connect(host="localhost", user="webuser", passwd="password", db="phonedirectory")
    	cursor = db.cursor()
    	cursor.execute("INSERT INTO names_table (firstname, lastname, phonenumber) VALUES (%s, %s, %s)", (fname, lname, pnumber))
    	cursor.execute("SELECT nameid FROM names_table WHERE firstname=(%s) and lastname=(%s) and phonenumber=(%s)", (fname, lname, pnumber))
    	nid = cursor.fetchone()
    	cursor.execute("INSERT INTO keys_table (nameid, buildingid, divisionid) VALUES (%s, %s, %s)", (nid[0], buildid, divid))
    	cursor.close()
    	db.close()
    	
    def get_nameid(fname, lname, pnumber):
    	"""Returns the nameid for the
    	corresponding firstname,
    	lastname, and phonenumber"""
    	db = MySQLdb.connect(host="localhost", user="webuser", passwd="password", db="phonedirectory")
    	cursor = db.cursor()
    	nameid = cursor.execute("SELECT nameid FROM names_table WHERE firstname=%s and lastname=%s and phonenumber=%s", (fname, lname, pnumber))
    	return nameid[0]
    	cursor.close()
    	db.close()
    Their are a few more functions in the program but you get the idea.

    Now, I think I'm beginning to realize what classes are for. Obviously the mysql stuff has to be initiliazed within every funciton. So, I'm thinking I should make this a class and use __init__ to initiliaze all the cursor for MySQLdb. Good?

    Would it look like this?

    Code:
    class db:
           def_init__(self, host="localhost", user="webuser", passwd="password", db="phone")
           self.db = MySQLdb.connect(host,user,passwd,db)
           self.cursor = self.db.cursor()
    and then the rest of the functions can simply call self.cursor every time? Am I on the right track here?

    Thanks in advance.
    I'll learn this stuff someday.
  2. #2
  3. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Dec 2004
    Location
    Meriden, Connecticut
    Posts
    1,797
    Rep Power
    155
    Very good! I think that is one of the more popular reasons why people like using classes. Using self.value_name instead of having to use a list so that your value can be used all around your program. Anyways, I would stick with using a class. It provides much more organization for your code and you wont have to use list near as much. Maybe even never.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2005
    Posts
    78
    Rep Power
    10
    Originally Posted by Shiner_Man
    Now, I think I'm beginning to realize what classes are for. Obviously the mysql stuff has to be initiliazed within every funciton. So, I'm thinking I should make this a class and use __init__ to initiliaze all the cursor for MySQLdb. Good?

    Am I on the right track here?
    I suspect not... I'm definitely no DB guru, but if you do what you've suggested, you'll end up holding connections open semi-indefinitely. This could end up being a problem unless you're careful. If you're not doing a lot of transactions I'd use closures/higher order functions (regardless of whether or not you use classes):
    Code:
    def transact(transaction):
        db = MySQLdb.connect(host="localhost", user="webuser", passwd="password", db="phonedirectory")
        cursor = db.cursor()
        ret = transaction(cursor)
        cursor.close()
        db.close()
        return ret
    
    def add_names(fname, lname, pnumber, buildid, divid):
        """Add the firstname, lastname,
           and phonenumber to the names_table
           table and link them"""
        def transaction(cursor):
            cursor.execute("INSERT INTO names_table (firstname, lastname, phonenumber) VALUES (%s, %s, %s)", (fname, lname, pnumber))
            cursor.execute("SELECT nameid FROM names_table WHERE firstname=(%s) and lastname=(%s) and phonenumber=(%s)", (fname, lname, pnumber))
            nid = cursor.fetchone()
            cursor.execute("INSERT INTO keys_table (nameid, buildingid, divisionid) VALUES (%s, %s, %s)", (nid[0], buildid, divid))
        return transact(transaction)
    	
    def get_nameid(fname, lname, pnumber):
        """Returns the nameid for the
        corresponding firstname,
        lastname, and phonenumber"""
        def transaction(cursor):
            nameid = cursor.execute("SELECT nameid FROM names_table WHERE firstname=%s and lastname=%s and phonenumber=%s", (fname, lname, pnumber))
            return nameid[0]
        return transact(transaction)
    --OH.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Atlantic City, NJ
    Posts
    327
    Rep Power
    13
    Originally Posted by hydroxide
    I suspect not... I'm definitely no DB guru, but if you do what you've suggested, you'll end up holding connections open semi-indefinitely. This could end up being a problem unless you're careful. If you're not doing a lot of transactions I'd use closures/higher order functions (regardless of whether or not you use classes):
    Code:
    def transact(transaction):
        db = MySQLdb.connect(host="localhost", user="webuser", passwd="password", db="phonedirectory")
        cursor = db.cursor()
        ret = transaction(cursor)
        cursor.close()
        db.close()
        return ret
    
    def add_names(fname, lname, pnumber, buildid, divid):
        """Add the firstname, lastname,
           and phonenumber to the names_table
           table and link them"""
        def transaction(cursor):
            cursor.execute("INSERT INTO names_table (firstname, lastname, phonenumber) VALUES (%s, %s, %s)", (fname, lname, pnumber))
            cursor.execute("SELECT nameid FROM names_table WHERE firstname=(%s) and lastname=(%s) and phonenumber=(%s)", (fname, lname, pnumber))
            nid = cursor.fetchone()
            cursor.execute("INSERT INTO keys_table (nameid, buildingid, divisionid) VALUES (%s, %s, %s)", (nid[0], buildid, divid))
        return transact(transaction)
    	
    def get_nameid(fname, lname, pnumber):
        """Returns the nameid for the
        corresponding firstname,
        lastname, and phonenumber"""
        def transaction(cursor):
            nameid = cursor.execute("SELECT nameid FROM names_table WHERE firstname=%s and lastname=%s and phonenumber=%s", (fname, lname, pnumber))
            return nameid[0]
        return transact(transaction)
    --OH.

    Can't I simply close each transaction in every function as I had above:

    Code:
    self.cursor.close()
    self.db.close()
    I'll learn this stuff someday.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2005
    Posts
    78
    Rep Power
    10
    Originally Posted by Shiner_Man
    Can't I simply close each transaction in every function as I had above:

    Code:
    self.cursor.close()
    self.db.close()
    Probably not if you've put the opening code in __init__()... what if you want to call add_names() twice... The second time the cursor and database will be closed... *BOOM*

    It really depends on your design though: if you can guarantee that each object will be accessed only once, then your idea would work... but it's not a happy design, as you will still have to put the same boilerplate in every function.

    If you can't guarantee that each object will only be accessed once then you may be better off closing the cursor and db in a __del__() method (though you'd STILL be better off using closures unless you don't mind connections being held open)

    --OH.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Atlantic City, NJ
    Posts
    327
    Rep Power
    13
    It sounds like their is no correct way of doing this in a class...
    I'll learn this stuff someday.
  12. #7
  13. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Dec 2004
    Location
    Meriden, Connecticut
    Posts
    1,797
    Rep Power
    155
    You may also be able to simply declare your values as global.
    Code:
    global db
    And then they can be used throughout your entire program.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Atlantic City, NJ
    Posts
    327
    Rep Power
    13
    Code:
    global db
    global cursor
    
    db = MySQLdb.connect(host="localhost", user="webuser", passwd="password", db="phonedirectory")
    cursor = db.cursor()
    hmmm...maybe this will resolve the closing issues. Thanks.
    I'll learn this stuff someday.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2005
    Posts
    78
    Rep Power
    10
    Originally Posted by Shiner_Man
    It sounds like their is no correct way of doing this in a class...
    It's not that, it's that there's not one correct way - it's going to depend on what your requirements are.

    If you want to only hold connection briefly while you are actually transacting, you should use the closured approach. It's trivial to turn them into methods of a class.

    If you want to open one connection at the start of your program and close it at the end, then the following should work:
    Code:
    from weakref import WeakValueDictionary
    
    class ConnObj(object):
        refs = WeakValueDictionary()
        db = None
        cursor = None
        def __new__(typ, *args, **kwds):
            ret = object.__new__(typ, *args, **kwds)
            if not typ.db:
                typ.db = MySQLdb.connect(host="localhost", user="webuser",
                                         passwd="password", db="phonedirectory")
                typ.cursor = typ.db.cursor()
                
            typ.refs[id(ret)] = ret
            return ret
    
        def __del__(self):
            if not self.refs.keys():
                self.cursor.close()
                self.db.close()
                self.db = self.cursor = None
    The explicit setting of typ.db to None may not be necessary if a closed database evaluates to False.

    This will close the connection when there are no objects which subclass from ConnObj and will reopen the connection when there are. This means that it may open andclose once, or may open and close many times - It all depends on your design. It's the only clean way I can think of to avoid explicitly calling a close() method.

    Don't use globals for non-constants. It leads to pain and suffering when you try and refactor your code ;-P

    --OH.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Atlantic City, NJ
    Posts
    327
    Rep Power
    13
    Okay well just to let you all know how this has played out. I have one file that holds all the functions for the database inserts, queries, etc. This is some of it:

    Code:
    import MySQLdb
    
    host="localhost"
    user="user"
    passwd="password"
    database="empdatabase"
    
    def get_data():
    	"""Get the firstname, lastname, phonenumber, 
    	building, and division for all users"""
    	db = MySQLdb.connect(host=host, user=user, passwd=passwd, db=database)
    	cursor = db.cursor()
    	cursor.execute("""SELECT names_table.firstname 
    	, names_table.lastname 
    	, names_table.phonenumber 
    	, buildings_table.buildingname 
    	, divisions_table.divisionname
    	FROM names_table
    	INNER JOIN buildings_table 
    	ON keys_table.buildingid = buildings_table.buildingid
    	INNER JOIN divisions_table 
    	ON keys_table.divisionid = divisions_table.divisionid 
    	INNER JOIN keys_table 
    	ON names_table.nameid = keys_table.nameid 
    	ORDER BY names_table.lastname""")
    	infolist = cursor.fetchall()
    	cursor.close()
    	db.close()
    	return infolist
    
    def search_data(search_string):
    	"""Search for a specific employee name"""
    	db = MySQLdb.connect(host=host, user=user, passwd=passwd, db=database)
    	cursor = db.cursor()
    	cursor.execute("""SELECT names_table.firstname
    	, names_table.lastname
    	, names_table.phonenumber
    	, buildings_table.buildingname
    	, divisions_table.divisionname
    	FROM names_table
    	INNER JOIN buildings_table
    	ON keys_table.buildingid = buildings_table.buildingid
    	INNER JOIN divisions_table
    	ON keys_table.divisionid = divisions_table.divisionid
    	INNER JOIN keys_table
    	ON names_table.nameid = keys_table.nameid
    	WHERE firstname LIKE '%""" + search_string + """%'
    	OR lastname LIKE '%""" + search_string + """%'
    	ORDER BY names_table.lastname""")
    	infolist = cursor.fetchall()
    	cursor.close()
    	db.close()
    	return infolist
    
    def add_names(fname, lname, pnumber, buildid, divid):
    	"""Add the firstname, lastname, and phonenumber
    	to the names_table table and link them"""
    	db = MySQLdb.connect(host=host, user=user, passwd=passwd, db=database)
    	cursor = db.cursor()
    	cursor.execute("INSERT INTO names_table (firstname, lastname, phonenumber) VALUES (%s, %s, %s)", (fname, lname, pnumber))
    	cursor.execute("SELECT nameid FROM names_table WHERE firstname=(%s) and lastname=(%s) and phonenumber=(%s)", (fname, lname, pnumber))
    	nid = cursor.fetchone()
    	cursor.execute("INSERT INTO keys_table (nameid, buildingid, divisionid) VALUES (%s, %s, %s)", (nid[0], buildid, divid))
    	cursor.close()
    	db.close()
    Then what I'm doing is calling these functions in the psp pages to try and seperate the presentation(html) from the application(python) a little better. Something like this:

    Code:
    <% 
    import pdmods
    
    if form.has_key("form_search"):
    	# 
    	tup = pdmods.search_data(form["form_search"].value)
    	for x in range(len(tup)):
    		#indent
    %>
       <tr>
       	<td><%= tup[x][0] %></td>
    	<td><%= tup[x][1] %></td>
    	<td><%= tup[x][2] %></td>
    	<td><%= tup[x][3] %></td>
    	<td><%= tup[x][4] %></td>
       </tr>
    So the bottom line right now is that it works perfectly. I'm sure their is a more efficient way to do it but I just can't wrap my head around the suggestions you guys have given me.

    Thanks for the input.
    I'll learn this stuff someday.

IMN logo majestic logo threadwatch logo seochat tools logo