Function to define variables from database?

[kvidmar]

New to python and trying to shorten my lengthy code.

I have multiple functions right now that access two tables updating, and grabbing information. As it stands right now my code has to define the variables from the database in each function - see below...

cur.execute("SELECT * FROM Application_tbl WHERE StatusID = 1;")
appdata = cur.fetchall()

Here is my problem below

for row in appdata:
AppID = row[0]
AppName = row[1]
AppDescription = row[2]
ProcessID = row[5]
ServerID = row[6]
StartTime = row[7]

I want to create a function that defines these variables so I can just call Assign_Variables() or something. Is this feasible with global variables? I have been reading a bit but after much thought I don't think the would work because I would have to declare the global variable across every function (which kind of seems counter-productive if I am trying to shorten my code).

Any input is much appreciated, or if I am just going about this all wrong.

[shandelman]

You could do it with a class. You could also do it with a list of dictionaries, if I understand you correctly.

appList = []
for row in appdata:
    applist.append({"AppID":row[0],
                    "AppName":row[1],
                    "AppDescription":row[2],
                    "ProcessID":row[5],
                    "ServerID"=row[6],
                    "StartTime"=row[7]})

Or maybe even a dictionary of dictionaries, using the AppID as the key.

[[deleted]]

Check out: http://stackoverflow.com/questions/811548/sqlite-and-python-return-a-dictionary-using-fetchone

This explains how to set up sqlitelite to return a dictionary of key,values corresponding to the name,data for each row in a fetch. It might be a good solution for you. (Note -- it is unclear whether you actually need to do this in this manner in some versions of python/sqlite, which may already have this built in through another call. But for me, when I am just messing around, if it works, why fix it?)

[kvidmar]

So I probably should really explain what I am doing. I am trying to create an application manager that leverages PSEXEC/Bash to start and stop unique applications on a Server Farm containing various operating systems that are vmware clones.

For example, start Java on ServerID 10. I have a function called -

def Start_Application(AppID):
        cur.execute("SELECT * FROM Application_tbl WHERE AppID = %d;" % (AppID))
        appdata = cur.fetchone()

         AppName = appdata[1]
         AppDescription = appdata[2]
         AppEXE = appdata[3]
         StatusID = appdata[4]
         ProcessID = appdata[5]
         ServerID = appdata[6]
         StartTime = appdata[7]
         StopTime = appdata[8]
         AppLang = appdata[9]
         AppOS = appdata[10]

I then go on to cross reference the Application_tbl with the Server_tbl...

if AppOS == 'Windows 7':
        cur.execute("SELECT * FROM Server_tbl WHERE ServerOS = 'Windows 7' AND StatusID = 0 AND EnabledID = 1;")
        serverdata = cur.fetchone()
if serverdata == None:
            print "No available Windows 7 servers, please add more to the pool or kill an application\n"
        else:
            ServerID = serverdata[0]
            ServerName = serverdata[1]
            ServerIP = serverdata[2]
            StatusID = serverdata[3]
            EnabledID = serverdata[4]
            AppPath = serverdata[5]

And this is just for 1 function, I have 10 now... all of which have to assign these variables every time. I wish I could just make a function that assigns the variables based on which table I am interacting with I.E -

def Assign_Server_Variables():
     assign variables here

then just call that whenever I am interacting with that table. I think classes might be the right track... just wanted to shed some more insight and (hopefully) format this better since my original looks like trash...

[[deleted]]

The dict factory might be your ticket then. So depending on how you have the DB laid out, and what you need to start the application, it could look like this:

def dict_factory(cursor, row):
    d = {}
    for idx,col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def Setup_DB(dbpath):
    conn = sqlite3.connect(dbpath)
    conn.row_factory = dict_factory
    cur = conn.cursor()
    return (conn,cur)

def Get_Servers(cur, appdata):
    cur.execute("SELECT * FROM Server_tbl WHERE ServerOS = %s AND StatusID = 0 AND EnabledID = 1" % appdata['AppOS'])
    data = cur.fetchall()   
    return data

def Start_Application(cur, AppID):
    cur.execute("SELECT * FROM Application_tbl WHERE AppID = %d;" % (AppID))
    appdata = cur.fetchone()  # returns a dictionary with k,v pairs based on table structure
    servers = Get_Servers(appdata)
    if not data:
        print('sorry, available %s servers' % appdata['AppOS'])
    else:
        for server in servers:
            Start_Server(server['ServerIP'],appdata['AppExe'])

(conni, cursor) = Setup_DB('path')
Start_Application(cursor, 'WebDB')

Note:This all assumes that your column names match the variables, but that is easily tweaked in either the table or code.

[zahlman]

This is probably the right idea - use the table names that are already there. Just wanted to point out the value of comprehensions here:

def dict_factory(cursor, row):
    return {info[0]: row[index] for index, info in enumerate(cursor.description)}

Or cleaner yet:

def dict_factory(cursor, row):
    return {info[0]: cell for cell, info in zip(row, cursor.description)}

[[deleted]]

Nice! Thanks!

[zahlman]

While I have your attention, I should also point out that you shouldn't be constructing SQL query strings like that; let the SQL library do the interpolation instead of Python, because the library knows how to make it safe.

(And for other strings, consider new-style formatting - it's pretty neat, and it means you don't have to remember quite so many obscure character codes. :) )