A database system is one of the best things any IT expert should know, because all the best programs around the globe need a file to hold their content, not just to save them but to easily access them. That is the best way to think about using well-defined database programs; they help users get information on a disk according to how they want it.

Why do I need a database?

There are several reasons why a database could be necessary:

1. Data organization: You may arrange and structure your data using a database. You can specify tables, fields, and the connections between various data points. This facilitates the rational and orderly storing and retrieval of information.

2. Data integrity: By applying restrictions and guidelines to the data, a database makes sure that the data is accurate. This lessens the likelihood of data inaccuracies and discrepancies. For instance, you may set restrictions to make sure that a given value must be unique or that a specific field cannot be left blank.

3. Data security: You can manage who has access to your data by using a database. Users can have their roles and permissions set, so you can control who can see, edit, and remove data. This guarantees that only authorized individuals may access and alter the data, protecting critical information in the process.

4. Data scalability: A database can expand with your data since it is made to manage massive volumes of data. It offers methods for effective data retrieval and storage, even when working with billions or even millions of records.

5. Data consistency: Atomicity, consistency, isolation, and durability (ACID) techniques are provided by a database to guarantee data consistency. These characteristics provide constant and dependable database transaction execution, even in the event of failures or simultaneous access.

6. Data analysis: A database offers methods and instruments for searching and interpreting data. To extract knowledge from your data, you may do intricate computations, aggregations, and queries. This can assist you in finding patterns or trends in your data and making well-informed judgments.

All things considered, a database is necessary for safely and effectively handling and arranging massive volumes of data. It offers a dependable and organized method for data retrieval, storage, and analysis, making it a vital resource for companies and institutions of all kinds.

sqllite3 database manager application using Python

By following these steps, you should be able to create your own personal SQLite 3 program in Python that can help you manage your apps, software, etc.

Further, this is a very lightweight and very simple database manager.

The code provided is a Python class that allows you to interact with SQLite databases. It contains three classes: fields, execute, filters, and commands.

The Fields Class

The Fields class contains methods that generate SQL commands to create fields of different data types. These methods include:

- character_field:

This method generates an SQL command to create a character field with a specified name, maximum length, and nullability.

def character_field(self, name: str, max_length: int = 20, null: bool = False) -> str:
        """
        Format Name CHAR(20) NOT NULL
        """
        format_str = "{} CHAR({}) NOT NULL" if not null else "{} CHAR({})"
        return format_str.format(name.upper(), max_length)

This code defines a method called character_field in the fields class. The method takes three parameters: name (a string), max_length (an integer with a default value of 20), and null (a boolean with a default value of False). The method returns a formatted string.

The purpose of this method is to generate a SQL field definition for a character field. The name parameter specifies the name of the field, the max_length parameter specifies the maximum length of the field (default is 20), and the null parameter specifies whether the field can be null (default is False).

The method uses string formatting to generate the SQL field definition. If null is False, the field definition will be in the format "{NAME} CHAR({MAX_LENGTH}) NOT NULL", where {NAME} and {MAX_LENGTH} are placeholders for the name and max_length parameters, respectively. If null is True, the field definition will be in the format "{NAME} CHAR({MAX_LENGTH})". The name parameter is converted to uppercase before formatting the string.

For example, if you call this method with name="username" and max_length=30, it will return the string "USERNAME CHAR(30) NOT NULL".

- text_field:

This method generates an SQL command to create a text field with a specified name.

def text_field(self, name: str) -> str:
        """
        Format Name TEXT
        """
        return "{} TEXT".format(name.upper())

This code defines a method called text_field in the fields class. The method takes one parameter: name (a string). The method returns a formatted string.

The purpose of this method is to generate a SQL field definition for a text field. The name parameter specifies the name of the field.

The method uses string formatting to generate the SQL field definition in the format "{NAME} TEXT", where {NAME} is a placeholder for the name parameter. The name parameter is converted to uppercase before formatting the string.

For example, if you call this method with name="description", it will return the string "DESCRIPTION TEXT".

- integer_field:

This method generates an SQL command to create an integer field with a specified name.

def integer_field(self, name: str) -> str:
        """
        Format Name INT
        """
        return "{} INT".format(name.upper())

This code defines a method called integer_field in the fields class. The method takes one parameter: name (a string). The method returns a formatted string.

The purpose of this method is to generate a SQL field definition for an integer field. The name parameter specifies the name of the field.

The method uses string formatting to generate the SQL field definition in the format "{NAME} INT", where {NAME} is a placeholder for the name parameter. The name parameter is converted to uppercase before formatting the string.

For example, if you call this method with name="age", it will return the string "AGE INT".

- bool_field:

This method generates an SQL command to create a boolean field with a specified name.

def bool_field(self, name: str) -> str:
        """
        Format Name BOOL
        """
        return "{} BOOL".format(name.upper())

This code defines a method called bool_field in the fields class. The method takes one parameter: name (a string). The method returns a formatted string.

The purpose of this method is to generate a SQL field definition for a boolean field. The name parameter specifies the name of the field.

The method uses string formatting to generate the SQL field definition in the format "{NAME} BOOL", where {NAME} is a placeholder for the name parameter. The name parameter is converted to uppercase before formatting the string.

For example, if you call this method with name="is_active", it will return the string "IS_ACTIVE BOOL".

The Execute Class

The Execute class is used to execute SQL commands on the SQLite database. It contains methods to execute SQL commands and to execute SQL commands and return the result. These methods include:

- __init__:

This method initializes the class and connects to the database.

def __init__(self, database: str = 'database.db'):
       self.conn = sqlite3.connect(database)

This code defines a class constructor for the execute class. The constructor takes one optional parameter, database (a string), with a default value of 'database.db'. The constructor initializes a connection to an SQLite database using the sqlite3.connect() method.

The sqlite3.connect() method creates a connection object that represents the database and allows you to execute SQL commands on it. The database parameter specifies the name of the database file to connect to. If the file does not exist, it will be created.

For example, if you create an instance of this class without passing any parameters (my_database = MyClass()), it will connect to a database file named 'database.db' in the same directory as the Python script. If you want to connect to a different database file, you can pass its name as a parameter (my_database = MyClass('my_database.db')).

- execute_command:

This method executes a given SQL command on the database.

def execute_command(self, sql_command: str):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql_command)
            self.conn.commit()
        except Exception as e:
            print(f"Error executing command: {e}")

The execute_command method takes one parameter: sql_command (a string). The method returns None.

The purpose of this method is to execute an SQLite command on the database. The sql_command parameter specifies the SQL command to execute.

The method uses a try-except block to catch any exceptions that might occur during the execution of the command. The cursor() method is used to create a cursor object, which is used to execute the SQL command passed as a parameter. The execute() method is used to execute the SQL command. The commit() method is used to commit the changes made by the SQL command to the database.

If an exception occurs during the execution of the command, the exception message is printed to the console.

For example, if you call this method with sql_command="CREATE TABLE users (id INT, name TEXT)", it will create a table named "users" with two columns "id" (integer) and "name" (text) in the connected SQLite database.

- execute_and_return:

This method executes a given SQL command on the database and returns the result.

def execute_and_return(self, sql_command: str):
        try:
            cursor = self.conn.cursor()
            result = cursor.execute(sql_command)
            return result
        except Exception as e:
            print(f"Error executing command: {e}")

The execute_and_return method takes one parameter: sql_command (a string). The method returns the result of the SQL command execution.

The purpose of this method is to execute an SQLite command on the database and return the result of the execution. The sql_command parameter specifies the SQL command to execute.

The method uses a try-except block to catch any exceptions that might occur during the execution of the command. The cursor() method is used to create a cursor object, which is used to execute the SQL command passed as a parameter. The execute() method is used to execute the SQL command, and the result of the execution is stored in the result variable. The method then returns this result variable.

If an exception occurs during the execution of the command, the exception message is printed to the console.

- __del__:

This method closes the connection to the database when the object is destroyed.

def __del__(self):
        self.conn.close()

The __del__ method takes no parameters. The method is a destructor and is called when the object is destroyed. The purpose of this method is to close the SQLite connection.

For example, if you create an instance of this class (my_database = MyClass()), you can execute an SQLite command and return its result by calling the execute_and_return() method (result = my_database.execute_and_return("SELECT * FROM users")). When you are done using the database, you can close the connection by destroying the object (del my_database).

The Filters Class

The Filters class contains methods to filter values and conditions in SQL commands. These methods include:

- values_filter:

This method formats a list of values into a string that can be used in an SQL command.

def values_filter(self, values: list) -> str:
        formated_values = []
        for value in values:
            if isinstance(value, str):
                formated_values.append("'{}'".format(value))
            elif isinstance(value, bool):
                formated_values.append(str(value).upper())
            else:
                formated_values.append(str(value))
        return ", ".join(formated_values)

The values_filter method takes one parameter: values (a list). The method returns a filtered list of values.

The purpose of this method is to format a list of values to be used in an SQL query. The values parameter specifies the list of values to be formatted.

The method iterates over the values in the list and formats them based on their type. If the value is a string, it is enclosed in single quotes. If the value is a boolean, it is converted to a string and converted to upper case. Otherwise, the value is converted to a string.

The formatted values are added to a new list called formated_values. The method then returns a string with the formatted values separated by commas using the join() method.

For example, if you call this method with values=[1, 'John', True], it will return the string "1, 'John', TRUE".

- conditions_filter:

This method formats a dictionary of conditions into a string that can be used in an SQL command.

def conditions_filter(self, conditions:list, sepertor:str = None):
        """
        Collects a list of conditional values and returns filtered conditional values
        """
        filtere_conditions = []
        for condition in conditions:
            value = re.split(r'(==|<|>|=)', condition)[-1].strip()    
            try:
                if (value.capitalize() == str(False)):
                    filtere_conditions.append(condition.replace(value, str(False).upper()))

                elif (value.capitalize() == str(True)):
                    filtere_conditions.append(condition.replace(value, str(True).upper()))

                elif (type(int(value)) is int):
                    filtere_conditions.append(condition.replace(value, str(int(value))))

            except Exception as e:
                if TypeError: filtere_conditions.append(condition.replace(
                        value, "'{}'".format(value.replace("'", '').replace('"', ''))
                    ))      
                else:
                    print(e)  

        sep = " {} ".format(sepertor) if sepertor is not None else " AND "
        return sep.join(filtere_conditions)

This code defines a method called conditions_filter in a class. The method takes two parameters: conditions (a list) and separator (a string with a default value of None). The method does not return anything.

The purpose of this method is to format a list of conditions to be used in an SQL query. The conditions parameter specifies the list of conditions to be formatted. The separator parameter specifies the separator to use between the conditions. If no separator is specified, the default value of None is used.

The method iterates over the conditions in the list and formats them based on their type. If the value in the condition is a boolean, it is converted to a string and converted to upper case. If the value is an integer, it is converted to a string. Otherwise, the value is enclosed in single quotes.

The formatted conditions are added to a new list called filtered_conditions. If a separator is specified, the method joins the filtered conditions using the separator. Otherwise, it returns the filtered conditions as a list.

Further, it checks if a separator is specified by theseparator parameter. If a separator is specified, it creates a string with the separator and assigns it to thesep variable. Otherwise, it assigns the string " AND " to thesep variable.

The method then joins the filtered conditions using the sep variable and returns the resulting string.

For example, if you call this method with conditions=["age > 18", "name == 'John'", "is_active == True"] and separator="OR", it will return the string "age > 18 OR name == 'John' OR is_active == TRUE".

The Commands Class

The Commands contain methods to create tables, insert data into tables, update data in tables, delete data from tables, and select data from tables. These methods include:

- create:

This method generates an SQL command to create a table with specified fields.

def create(self, table_name:str, columns:list) -> str:
       columns_formate_str = ','.join(columns)
       sql_command =  "CREATE TABLE {} ({})".format( table_name.upper(), columns_formate_str )
       self.execute_command(sql_command)

The create method takes two parameters: table_name (a string) and columns (a list). The method returns None.

The purpose of this method is to create an SQL command to create a table in the database. The table_name parameter specifies the name of the table. The columns parameter specifies the list of column definitions for the table.

The method uses the join() method to concatenate the elements of the columns list into a single string, separated by commas. It then uses string formatting to create the SQL command in the format "CREATE TABLE {TABLE_NAME} ({COLUMNS})". The table_name is converted to uppercase before formatting the string.

The SQL command is passed to the execute_command() method to execute it on the database.

For example, if you call this method with table_name="users" and columns=["id INT", "name TEXT", "age INT"], it will execute the SQL command "CREATE TABLE USERS (id INT, name TEXT, age INT)" on the connected database.

- insert:

This method generates an SQL command to insert data into a table.

def insert(self, table_name:str, columns:list, values:list):
       try:
           cursor = self.conn.cursor()
           columns_formate_str = ",".join(columns)
           values_formate_str = self.values_filter(values)
           command = "INSERT INTO {} ({}) VALUES ({})".format(table_name.upper(), columns_formate_str, values_formate_str)
           cursor.execute(command)
           self.conn.commit()
       except Exception as e:
           print(f"Error inserting data: {e}")

The insert method takes three parameters: table_name (a string), columns (a list), and values (a list). The method returns None.

The purpose of this method is to create an SQL command to insert data into a table in the database. The table_name parameter specifies the name of the table. The columns parameter specifies the list of column names for the table. The values parameter specifies the list of values to be inserted into the table.

The method uses the join() method to concatenate the elements of the columns list into a single string, separated by commas. It then calls the values_filter() method to format the values in the values list into a string that can be used in an SQL query.

The method then uses string formatting to create the SQL command in the format "INSERT INTO {TABLE_NAME} ({COLUMNS}) VALUES ({VALUES})". The table_name is converted to uppercase before formatting the string.

The SQL command is passed to the execute_command() method to execute it on the database.

For example, if you call this method with table_name="users", columns=["id", "name", "age"], and values=[1, 'John', 30], it will execute the SQL command "INSERT INTO USERS (id, name, age) VALUES (1, 'John', 30)" on the connected database.

- update:

This method generates an SQL command to update data in a table.

def update(self, table_name:str, update_values:list, conditions:list) -> None:
       update_values_formate_str = self.conditions_filter(update_values, sepertor=',') 
       conditions_formate_str = self.conditions_filter(conditions)

       sql_command = "UPDATE {} SET {} WHERE {}".format(
           table_name.upper(), update_values_formate_str , conditions_formate_str)  
       self.execute_command(sql_command)

The update takes three parameters: table_name (a string), update_values (a list), and conditions (a list). The method returns None.

The purpose of this method is to create an SQL command to update data in a table in the database. The table_name parameter specifies the name of the table. The update_values parameter specifies the list of values to be updated. The conditions parameter specifies the conditions that determine which rows to update.

The method calls the conditions_filter() method twice to format the update_values and conditions lists into strings that can be used in an SQL query. The update_values are formatted with a comma separator, while the conditions are formatted with the default "AND" separator.

The method then uses string formatting to create the SQL command in the format "UPDATE {TABLE_NAME} SET {UPDATE_VALUES} WHERE {CONDITIONS}". The table_name is converted to uppercase before formatting the string.

The SQL command is passed to the execute_command() method to execute it on the database.

For example, if you call this method with table_name="users", update_values=["age = 25", "is_active = True"], and conditions=["id = 1"], it will execute the SQL command "UPDATE USERS SET age = 25, is_active = TRUE WHERE id = 1" on the connected database.

- delete:

This method generates an SQL command to delete data from a table.

def delete(self, table_name:str, conditions:list) -> None:
       conditions_formate_str = self.conditions_filter(conditions) 
       sql_command = "DELETE FROM {} WHERE {}".format(
           table_name.upper(), conditions_formate_str )
       self.execute_command(sql_command)

The delete method takes two parameters: table_name (a string) and conditions (a list). The method returns None.

The purpose of this method is to create an SQL command to delete data from a table in the database. The table_name parameter specifies the name of the table. The conditions parameter specifies the conditions that determine which rows to delete.

The method calls the conditions_filter() method to format the conditions list into a string that can be used in an SQL query.

The method then uses string formatting to create the SQL command in the format "DELETE FROM {TABLE_NAME} WHERE {CONDITIONS}". The table_name is converted to uppercase before formatting the string.

The SQL command is passed to the execute_command() method to execute it on the database.

For example, if you call this method with table_name="users" and conditions=["age > 30", "is_active = True"], it will execute the SQL command "DELETE FROM USERS WHERE age > 30 AND is_active = TRUE" on the connected database.

- get_all

def get_all(self, table_name:str) -> list:
        """
        Foramte SELECT * FROM TABLE_NAME
        """
        sql_command = "SELECT * FROM {}".format(table_name.upper())
        return self.execute_and_return(sql_command).fetchall()

The purpose of this method is to retrieve all rows from a table in the database. The table_name parameter specifies the name of the table.

The method uses string formatting to create the SQL command in the format "SELECT * FROM {TABLE_NAME}". The table_name is converted to uppercase before formatting the string.

The SQL command is passed to the execute_and_return() method to execute it on the database and return a cursor object. The fetchall() method is called on the cursor object to retrieve all rows from the query result as a list.

For example, if you call this method with table_name="users", it will execute the SQL command "SELECT * FROM USERS" on the connected database and return a list of all rows in the "users" table.

- column_search

def columns_search(self, table_name:str, columns:list) -> list:
        """
        Foramte SELECT Col1, Col2, Col3 Col4 FROM TABLE_NAME
        """
        sql_command = "SELECT {} FROM {}".format(','.join(columns), table_name)
        return self.execute_and_return(sql_command).fetchall()

The columns_search method takes two parameters: table_name (a string) and columns (a list). The method returns a list.

The purpose of this method is to retrieve specific columns from a table in the database. The table_name parameter specifies the name of the table. The columns parameter specifies the list of column names to be retrieved.

The method uses the join() method to concatenate the elements of the columns list into a single string, separated by commas. It then uses string formatting to create the SQL command in the format "SELECT {COLUMNS} FROM {TABLE_NAME}".

The SQL command is passed to the execute_and_return() method to execute it on the database and return a cursor object. The fetchall() method is called on the cursor object to retrieve all rows from the query result as a list.

For example, if you call this method with table_name="users" and columns=["name", "age", "email"], it will execute the SQL command "SELECT name, age, email FROM users" on the connected database and return a list of rows containing only the specified columns.

- search

def search(self, table_name:str, conditions:list) -> list:
        """
        SELECT * FROM TABLE_NAME WHERE Conditions sql_command = "SELECT * FROM {} WHERE {}".format(
            table_name, self.conditions_filter(conditions))
        return self.execute_and_return(sql_command).fetchall()

The search method takes two parameters: table_name (a string) and conditions (a list). The method returns a list.

The purpose of this method is to retrieve rows from a table in the database that meet certain conditions. The table_name parameter specifies the name of the table. The conditions parameter specifies the conditions that determine which rows to retrieve.

The method calls the conditions_filter() method to format the conditions list into a string that can be used in an SQL query.

The method then uses string formatting to create the SQL command in the format "SELECT * FROM {TABLE_NAME} WHERE {CONDITIONS}".

The SQL command is passed to the execute_and_return() method to execute it on the database and return a cursor object. The fetchall() method is called on the cursor object to retrieve all rows from the query result as a list.

For example, if you call this method with table_name="users" and conditions=["age > 30", "is_active = True"], it will execute the SQL command "SELECT * FROM users WHERE age > 30 AND is_active = TRUE" on the connected database and return a list of rows that meet the specified conditions.

How to use this module in Python

Setting Up the Environment

Before we dive into the details, let's set up our environment. Make sure you have Python installed on your machine and have the module library available. You can install it using the following command:

pip install modules

Additionally, we will be using the datetime module for handling date and time operations. This module is a standard library in Python, so there is no need for any additional installation.

Creating a Table

The first operation we will cover is creating a table in the database. The create() method from the modules library allows us to define the table structure by specifying column names and their respective data types. For example, let's create a table called "settings" with columns for ID, username, date_created, bio, and is_superuser:

module.create(table_name='settings', columns=[
    module.integer_field(name='id'),
    module.character_field(name='username', null=False),
    module.character_field(name='date_created', null=False),
    module.text_field(name='bio'),
    module.bool_field(name='is_superuser')
])

Inserting Values

Once we have our table set up, we can start inserting data into it. The insert() method allows us to specify the table name, column names, and corresponding values for a new row. For example, let's insert a new row into the "settings" table:

module.insert(
    table_name='settings',
    columns=['id', 'username', 'date_created', 'bio', 'is_superuser'],
    values=[4, 'user12877', str(datetime.datetime.now()), 'this is my bio', False]
)

Updating Values

Updating existing values in the database is a common operation. The update() method in the modules library enables us to modify specific columns of rows that meet certain conditions. For example, let's update the username and is_superuser columns of rows in the "settings" table where the ID is less than 2, the username is "user12345", and is_superuser is False:

module.update(
    table_name='settings',
    update_values=['username = Redfox', 'is_superuser = True'],
    conditions=['id < 2', 'username = user12345', 'is_superuser = False']
)

Deleting Rows

Sometimes, we may need to remove specific rows from the database. The delete() method allows us to delete rows based on certain conditions. For instance, let's delete rows from the "settings" table where the ID is 1:

module.delete(table_name='settings', conditions=['id = 1'])

Retrieving All Data

To retrieve all rows from a table, we can use the get_all() method. This method fetches all rows from the specified table and returns them as a list. For example:

values = module.get_all(table_name='settings')

Retrieving Specific Columns

If we only need to retrieve specific columns from a table, we can use the columns_search() method. This method allows us to specify the table name and a list of columns to retrieve. It returns a list of rows containing only the specified columns. For example:

values = module.columns_search(table_name='settings', columns=['username', 'is_superuser'])

Searching with Conditions

The search() method enables us to search for rows in a table based on specific conditions. We can specify the table name and a list of conditions that determine which rows to retrieve. For example, let's search for rows in the "settings" table where the ID is greater than 1 and is_superuser is False:

values = module.search(table_name='settings', conditions=['id > 1', 'is_superuser = False'])

Conclusion

In this blog post, we explored how to perform various database operations using the modules library in Python. We covered creating tables, inserting values, updating values, deleting rows, retrieving all data, retrieving specific columns, and searching with conditions. With these operations at our disposal, we can efficiently manage and manipulate data in our databases using Python.

Remember to refer to the official documentation of the module library for more detailed information on each operation and its parameters. Happy coding!