Building a class for sqlite3 interaction

[Nuzvee]

Here is the code ive written so far, i do realise it is a bit messy and "noobish" but it has been alot of trial and error and i have yet not cleaned it up since i now want to convert it into a class.

Note that the "check which table the specified column is in and then target that table" def is not written yet.

#############################################
###########----Start of Liststuff----########
#############################################

GeneralList = ['SerialNumber',\
               'mac_lom',\
               'IMC_IP',\
               'ESXi_IP'\
               ]

HWAdapterList = ['Slot_L',\
                'Slot_1',\
                'Slot_2',\
                'Slot_3',\
                'Slot_4',\
                'Slot_5'\
                ]

HWCPUList = ['CPU1',\
             'CPU2'\
             ]


HWHDDList = ['Disk_1',\
             'Disk_2',\
             'Disk_3',\
             'Disk_4',\
             'Disk_5',\
             'Disk_6',\
             'Disk_sum'\
             ]

HWMemoryList = ['DIMM_A1',\
                'DIMM_A2',\
                'DIMM_B1',\
                'DIMM_B2',\
                'DIMM_C1',\
                'DIMM_C2',\
                'DIMM_D1',\
                'DIMM_D2',\
                'DIMM_E1',\
                'DIMM_E2',\
                'DIMM_F1',\
                'DIMM_F2',\
                'DIMM_G1',\
                'DIMM_G2',\
                'DIMM_H1',\
                'DIMM_H2',\
                'DIMM_I1',\
                'DIMM_I2',\
                'DIMM_J1',\
                'DIMM_J2',\
                'DIMM_K1',\
                'DIMM_K2',\
                'DIMM_L1',\
                'DIMM_L2',\
                'DIMM_M1',\
                'DIMM_M2'\
                ]

HWPCIList = ['Slot_1',\
             'Slot_2',\
             'Slot_3',\
             'Slot_4',\
             'Slot_5',\
             'Slot_6',\
             'FRONT_NVME_1',\
             'MRAID',\
             'Slot_L'\
             ]

HWPSUList = ['PSU1_Status',\
             'PSU2_Status',\
             'PSU1_InWatt',\
             'PSU2_InWatt',\
             'PSU1_OutWatt',
             'PSU2_OutWatt',\
             'PSU1_FW',\
             'PSU2_FW'\
             ]



##################################################################
##############-----START OF SUBDEF---------#######################
################################################################## 


def __create_connection(_scriptID): # Untested
    dbid = "/deploy2.0/database/PodID-"+str(_scriptID)+".db"
    try:
        conn = sqlite3.connect(dbid)
        return conn
    except:
        print "create con fail"
        
    return None
    
def __create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
#    try:
    print create_table_sql
    c = conn.cursor()
    c.execute(create_table_sql)
#    except:
#        print "Fail"    
 
def __update_table(conn, fieldList, inValue):
    table = fieldList[0]
    column = fieldList[1]
    servid = fieldList[2]
    """
    update priority, begin_date, and end date of a task
    :param conn:
    :param fieldList:
    :return:
    """
    sql = ''' UPDATE {t}
              SET {c} = {v} 
              WHERE ID = {s}'''.format(t=table,c=column, v=inValue, s=servid)
    cur = conn.cursor()
    cur.execute(sql, task)
 
 
##################################################################
##############-----END OF SUBDEF----------########################
##################################################################    


##################################################################
##############-----START OF MAINDEF-------########################
##################################################################     

    
    
def get_sql(_scriptID, table, column, servid=1):
      
    getquery = """
SELECT ID,"""+column+"""
FROM """+table+"""
WHERE ID = """+str(servid)
    cur.execute(getquery)
    rows = cur.fetchall()
    for row in rows:
        print(row)
    return value

def set_sql(_script, fieldList, inValue):
    conn = __create_connection(_scriptID)
    with conn:
        __update_table(conn, fieldList, inValue)
    

def create_sql(_scriptID):
    conn = __create_connection(_scriptID)
#    cur = conn.cursor()
    
    general_tb = """CREATE TABLE IF NOT EXISTS General (\
ID integer PRIMARY KEY,\
SerialNumber text,\
mac_lom text,\
IMC_IP text,\
ESXi_IP text\
);"""
  


    HW_Adapter_tb = """CREATE TABLE IF NOT EXISTS HW_Adapter (
ID integer PRIMARY KEY,
Slot_L text,
Slot_1 text,
Slot_2 text,
Slot_3 text,
Slot_4 text,
Slot_5 text
);"""

    HW_CPU_tb = """CREATE TABLE IF NOT EXISTS HW_CPU (
ID integer PRIMARY KEY,
CPU1 text,
CPU2 text
);"""

    HW_HDD_tb = """CREATE TABLE IF NOT EXISTS HW_HDD (
ID integer PRIMARY KEY,
Disk_1 text,
Disk_2 text,
Disk_3 text,
Disk_4 text,
Disk_5 text,
Disk_6 text,
Disk_sum text
);"""

    HW_Memory_tb = """CREATE TABLE IF NOT EXISTS HW_Memory (
ID integer PRIMARY KEY,
DIMM_A1 text,
DIMM_A2 text,
DIMM_B1 text,
DIMM_B2 text,
DIMM_C1 text,
DIMM_C2 text,
DIMM_D1 text,
DIMM_D2 text,
DIMM_E1 text,
DIMM_E2 text,
DIMM_F1 text,
DIMM_F2 text,
DIMM_G1 text,
DIMM_G2 text,
DIMM_H1 text,
DIMM_H2 text,
DIMM_I1 text,
DIMM_I2 text,
DIMM_J1 text,
DIMM_J2 text,
DIMM_K1 text,
DIMM_K2 text,
DIMM_L1 text,
DIMM_L2 text,
DIMM_M1 text,
DIMM_M2 text
);"""

    HW_PCI_tb = """CREATE TABLE IF NOT EXISTS HW_PCI (
ID integer PRIMARY KEY,
Slot_1 text,
Slot_2 text,
Slot_3 text,
Slot_4 text,
Slot_5 text,
Slot_6 text,
FRONT_NVME_1 text,
MRAID text,
Slot_L text
);"""

    HW_PSU_tb = """CREATE TABLE IF NOT EXISTS HW_PSU (
ID integer PRIMARY KEY,
PSU1_Status text,
PSU2_Status text,
PSU1_InWatt text,
PSU2_InWatt text,
PSU1_OutWatt text,
PSU2_OutWatt text,
PSU1_FW text,
PSU2_FW text
);"""



    print general_tb
    if conn is not None:
        __create_table(conn, general_tb)
        __create_table(conn, HW_Adapter_tb)
        __create_table(conn, HW_CPU_tb)
        __create_table(conn, HW_HDD_tb)
        __create_table(conn, HW_Memory_tb)
        __create_table(conn, HW_PCI_tb)
        __create_table(conn, HW_PSU_tb)




    else:
        print "Error! Failed to connect to db"
    

##################################################################
##############-----END OF MAINDEF---------########################
##################################################################