Ndtyjky

I'm new in Python and I'm making a desktop application for myself. One of my windows is "Add Company". Here is screenshot: https://ibb.co/hgyFAL

Until now, I'm collecting data from users and I'm storing in SQLite3 with below code:

import sqlite3 as sql

import os





def create_new_company(

    name,

    address,

    district,

    city,

    country,

    general_email,

    phone1,

    phone2,

    fax,

    tax_number,

    tax_administration,

    activity_area,

    contact,

    contact_task,

    is_client,

    is_supplier,

    is_in_mail_list,

    is_in_sms_list,

    is_in_black_list,

    note,

    is_active):



    db = sql.connect(str(os.getcwd() + '\Databases\main_db.sqlite3'))

    cursor = db.cursor()



    cursor.execute("""CREATE TABLE IF NOT EXISTS `companies` (

    `company_id`    INTEGER NOT NULL DEFAULT 0000001 PRIMARY KEY AUTOINCREMENT UNIQUE,

    `name`  TEXT,

    `address`   TEXT,

    `district`  TEXT,

    `city`  TEXT,

    `country`   TEXT,

    `general_email` TEXT,

    `phone1`    NUMERIC,

    `phone2`    NUMERIC,

    `fax`   NUMERIC,

    `tax_number`    TEXT,

    `tax_administration`    NUMERIC,

    `activity_area` TEXT,

    `contact`   TEXT,

    `contact_task`  TEXT,

    `is_client` INTEGER,

    `is_supplier`   INTEGER,

    `is_in_mail_list`   INTEGER,

    `is_in_sms_list`    NUMERIC,

    `is_in_black_list`  NUMERIC,

    `note`  TEXT,

    `is_active` NUMERIC);""")



    values = (

        name,

        address,

        district,

        city,

        country,

        general_email,

        phone1,

        phone2,

        fax,

        tax_number,

        tax_administration,

        activity_area,

        contact,

        contact_task,

        is_client,

        is_supplier,

        is_in_mail_list,

        is_in_sms_list,

        is_in_black_list,

        note,

        is_active

    )

    script= """INSERT INTO companies VALUES (

    NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""



    cursor.execute(script, values)

    db.commit()

    db.close()

Until last week, I didn't care the code is good or not. But application is growing and I'm afraid to can't manage the code in future. Actually even now I can't manage and its just have 3 windows. :/

So, I start asking myself "how it can be better" and I searched on the internet for my question. I found good sources and great advice and I updated my code with the following:

class SQLCommands:

    sqlite_file = str(os.getcwd() + '\test.db')



    def __init__(self):

        if os.path.exists(self.sqlite_file):

            pass

        else:

            self.create_new_database(self.sqlite_file)



    def create_new_database(self, database_path):

        with sqlite3.connect(database_path) as conn:

        c = conn.cursor()



        c.execute("""CREATE TABLE IF NOT EXISTS `companies` (

            `company_id`    INTEGER NOT NULL PRIMARY KEY UNIQUE,

            `name`  TEXT);""")



        self.close(conn)



    def connect(self, database_path):

        with sqlite3.connect(database_path) as conn:

            c = conn.cursor()

            return conn, c



    def close(self, conn):

        conn.commit()

        conn.close()

With the last update I think is better and basic functions are ok. But now I'm stuck here and I need a road map.

I imagine a function like below but I couldn't bring the end:

class SQLCommands:

    sqlite_file = str(os.getcwd() + '\test.db')



    # def other_fucntions

    #     ...some codes here...

    #     .....



    def insert(self, table, column, value):

        conn, c = self.connect(self.sqlite_file)

        values = (value,)

        script = """INSERT INTO {} VALUES (?)""".format(table)  # I'm not sure if the correct way is this



        c.execute(script, value)

        conn.commit()

        conn.close()



        del value, script, conn, c

I imagine to use like this:

class Company(tk.Toplevel):

    def __init__(self):

        self.init_ui()



    def init_ui(self):

        company_name_lbl = ttk.Label(self, text="Company Name").grid(

            row=1, column=0, sticky='w')

        self.company_name_entry = ttk.Entry(self)

        self.company_name_entry.grid(row=1, column=1)



        company_address_lbl = ttk.Label(self, text="Company Address").grid(

            row=1, column=0, sticky='w')

        self.company_address_entry = ttk.Entry(self)

        self.company_address_entry.grid(row=1, column=1)



        save_btn = ttk.Button(self, text="Save", command=self.insert_to_sql)





    def insert_to_sql(self):

        value_list = ["NULL", self.company_name_entry.get(), self.company_address_entry.get()]

        column_header_list =  ["company_id", "name", "adress"]



        sql = SQLCommands()



        for i in range(len(value_list)):

            sql.insert(table="companies", column=column_header_list[i], value=value_list[i])

My future goal is learning Django and carrying my app to the web with Django. So, SQLCommands class must not depend on Tkinter.

Am I on the right track? Is this right way to do? If it is, how I can complete the code "SQLCommands.insert"

My English may not be clear but I try my best.

Sorry in advance and thanks a lot in advance.
Alperen