Python Forum
Building a class for sqlite3 interaction
Thread Rating:
  • 3 Vote(s) - 3 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Building a class for sqlite3 interaction
#1
Hi,

Python: python2.7
OS : Centos7

I am posting this since ive grown desperate after getting daily headaches trying to implement SQL interaction into a current production script.

After creating several methods to be able to interact with sqlite3 in a smooth way i realized that this really should be turned into a class rather than a series of methods.

The problem is... i have never writen a new class before and writing this as my first class without external support is just silly.

So i need help with this since i can't find guides or documentations that makes it clear where to even start.



So far i have written methods for creating connection to a database, creating a new database(with preset default tables), getting data from the database, editing data in the database. So far i have verified that connecting to and creating new database methods work as intended, i have not yet tested getting or setting any data.

Since the scripts that will use these methods to interact with databases are long and bit too much to explain in short terms i wont go into any details. But the arguments i will use when interacting with database is 'database-name', 'column' and 'row-index'.
I know i just left out table. The reason is that in the beginning of this scripts of methods for database interaction i have stated lists which summed up is (
Table1 = ['column_A','column_B','column_C']
Table2 = ['column_AB','column_BB','column_CB']
)
So basicly i only need to point at which column i want to interact with and the script will check which table the specified column is in and target said table.

So the more i work on this script the more i understand that it would be best to create a class out of it, but again i have no idea where to start.

My best guess is to have the arguments db-name, row-index and column and to create classmethods for create, get and set (including submethods as connect to db) and additional classmethods for checking which table the column specified is in.


I do apologize if it is vague or unclear what im trying to achieve, please point at what is unclear and i will do my best to clarify. I am fairly new to python and programming in general and im self-taught so there is most likely huge gaps in my knowledge on some basics.

I do understand that this is alot to ask, but i am in over my head and there is no time or room for me to get more educated before going at it again. The alternative is to write some poor inefficient script which i really hate doing. I want to make a structured efficient solution from start, no placeholders :P.


In any case, thank you for having the patience for reading this far and again i do apologize if it is incoherent or just makes no sense.


Best regards,
Nuzvee
Reply
#2
Please show the code that you have tried, and we will give class suggestions
Also please note python 2.7 is all but obsolete, and will officially be obsolete in 11 months 16 days and 16 hours!
Reply
#3
And to be honest this is very strange db schema. Either you don't need multiple tables or script checking all the tables to find the columns is strange to put it politely.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#4
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---------########################
##################################################################
Reply
#5
Anyone have any input? or at least say if i am right in wanting to turn this into a class?
Reply
#6
(Jan-28-2019, 07:08 AM)Nuzvee Wrote: Anyone have any input? or at least say if i am right in wanting to turn this into a class?

Is it even sensible? Confused This is all made up without any knowledge of best practice when working with python or any code at all.

This part regarding database interaction is keeping me from any further development on the entire project since it will heavily rely on how the data is kept and interacted with.

Getting desperate :)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  building class zarize 4 2,324 Mar-10-2020, 08:11 AM
Last Post: zarize
  Building a class for polygons and triangles in 2-space crosshairpvp 0 1,533 Nov-22-2019, 02:04 AM
Last Post: crosshairpvp
  Odd Python / C Library interaction embedka 0 1,497 Oct-09-2019, 12:21 PM
Last Post: embedka
  RTL_Power interaction 25141928 0 1,672 Apr-08-2019, 07:26 PM
Last Post: 25141928
  Pygame Button interaction Padalinkiller1 1 7,817 Nov-16-2017, 02:49 PM
Last Post: metulburr
  Interaction with games such as BO3 nmills688 1 2,694 Oct-16-2017, 08:58 PM
Last Post: nilamo

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020