sqlite_interop.py.bak

# Create a database if it doesn't already exists.
def create_new_db(database_path, creation_script):
    import os
    import sqlite3

    # Check wether the database file already exists.
    if not os.path.exists(database_path):
        # Even if it doesn't exist, you can already connect to it.
        connection_db_creation = sqlite3.connect(database_path)

        # Execute the instruction in this file.
        try:  # The file can be corrupt.
            with open(creation_script, "r") as creation_statement:
                connection_db_creation.executescript(creation_statement.read())
        except Exception as e:
            print(f"Error creating datbase: {e}. Aborting script ...")
            import sys
            sys.exit(1)  # Exit with error.

        # It seems safer to close the database here.
        connection_db_creation.commit()
        connection_db_creation.close()




# Function to turn python datatypes into SQLite ones.
def suggest_type(var):
    import datetime  # Determination of datatype.
    # Catch unforseen problems.
    try:
        # Determine the datatype of var and
        # suggest a corresponding datatype in SQLite.
        if isinstance(var, int):
            return "INTEGER"
        elif isinstance(var, float):
            return "REAL"
        elif isinstance(var, str):
            return "TEXT"
        elif isinstance(var, bool):
            return "BOOLEAN"
        elif isinstance(var, datetime.datetime):
            return "NUMERIC"
        # Turn anything unknown or undefined into simple TEXT.
        else:
            return "TEXT"
    except Exception as e:
        print(f'Warning: Could not determine type of variable. Using TEXT. ({e}).')
        return "TEXT"



# Export the results of a query to a spreadsheet.
def export_to_spreadsheet(cursor, statement, spreadsheet_file):
    # Execute the statement and fetch the results.
    cursor.execute(statement)

    # Build the table that should be exported in memory as a tuple.
    # Use the cursor to get column headers (cursor.description).
    sheet = [tuple([i[0] for i in cursor.description])]

    # Split the spreadsheet_file string at every point, take the last element of the resulting list
    # and convert the extension to lowercase for easier if statement conditions.
    file_extension = spreadsheet_file.split(".")[-1].lower()

    # Depending on the file extension use different logic to export.
    if spreadsheet_file.endswith(".ods"):
        # Straigtforward for Open Document format.
        import pyexcel_ods
        while True:
            row = cursor.fetchone()
            if not row:
                break
            sheet.append(row)
            pyexcel_ods.save_data(spreadsheet_file, {"Sheet 1": sheet})

    elif spreadsheet_file.endswith(".xlsx") or spreadsheet_file.endswith(".xls"):
        # Shame the user for using obsolete file formats.
        if spreadsheet_file.endswith(".xlsx"):
            print("1998 called. They want their file format back…")
        # Use openpyxl package for export to excel.
        import openpyxl
        # Create a new workbook. TODO: Pretty much the same as the "sheet" tuple.
        workbook = openpyxl.Workbook()
        # Have this workbook choose an active sheet / tab.
        workbook_sheet = workbook.active
        # Fill it with column headers first.
        workbook_sheet.append([i[0] for i in cursor.description])
        while True:
            row = cursor.fetchone()
            if not row:
                break
            workbook_sheet.append(row)
        # Finally the actual export.
        workbook.save(spreadsheet_file)

    else:
        unknown_extension = spreadsheet_file.split('.')[-1].lower()
        print(f"No export logic for {unknown_extension}.")