Executing the command

This guide will help you to get started with execution functionality of the sqlite package.

To execute any command we have to use the execute class of the package. This class contains several tools for a proper & efficient execution of our command. To execute command:

sqlite.execute(
    [
        ["command for table1_db1", "command for table2_db1"],
        ["command for table1_db2", "command for table2_db2"]
    ]
)

Output:

result = [
    [
        "Result for table1_db1",
        "Result for table2_db1"
    ],
    [
        "Result for table1_db2",
        "Result for table2_db2"
    ]
]

Analysis:

The execution command will return a numpy array for faster processing & execution. This array consist of database wise result. For example result[0] will be the result for first database:

[
    "Result for table1_db1",
    "Result for table2_db1"
]

This is the result for first database in the resultant numpy array. Further, the database array consist of results of commands executed on table. For example: The result[0][1] will be the result of second table of first database.

Using the tools of execution for better analysis

The execute class provides several tools to help execute our command selectively and properly. These tools are often used to get different outputs on the same command execution. Sounds weird 😊, let's understand these tools.

returnDict

This parameter is used when we have to get the result in the form of dictionary where keys are the names of the databases. For example:

sqlite.execute(
    "SELECT * FROM WORLD WHERE SOMEONE LIKE %YOU%",
    db="datab.db",
    returnDict=True
).get

It will return something like:

{"datab.db": [["You"]]}

This can also be achieved by using the dict attribute:

sqlite.execute(
    "SELECT * FROM WORLD WHERE SOMEONE LIKE %YOU%",
    db="datab.db"
).dict

It will return the same result. This is useful when we don't know the position of database and we want to get the result of database(s) by their name(s). By default it is False.

verbose

This parameter is used when we have to debug our query or database. This will log all the steps that are taken internally by the library so as to provide a good debug experience and let you know where your program crashed (in case of any bugs). For example:

sqlite.execute(
    "SELECT * FROM WORLD WHERE SOMEONE LIKE %YOU%",
    db="datab.db",
    verbose=True
).get

It will log something like:

[8120] SQL-Tools: Starting execution
[8120] SQL-Tools: Parsing database(s)
[8120] SQL-Tools: Parsing commands
[8120] SQL-Tools: Connected
[8120] SQL-Tools: Creating the pointer
[8120] SQL-Tools: Executing command database: datab.db
[8120] SQL-Tools: Preparing results
[8120] SQL-Tools: Calculating time

Pattern: [process id] SQL-Tools: Step

By default it is False.

err

Sometimes this parameter is life saver when we don't want our program to break in between due to any error. This will prevent all the errors raised by any function in the respective function. Surely, we can do this by using exception handling but it will take at least 4 lines of code and makes the program messy. It's good to provide this parameter and get rid of it. This parameter is available in every function of the library. For example:

sqlite.execute(
    "SHOW TABLES",
    db="datab.db"
)

This will raise error because the SHOW command is not available in SQLite. To prevent this we can use two approaches:

Approach 1:

try:
    sqlite.execute(
        "SHOW TABLES",
        db="datab.db"
    )
except Exception:
    pass

Approach 2:

sqlite.execute(
    "SHOW TABLES",
    db="datab.db",
    err=False
)

Now, you can decide by yourself which approach is better. By default it is True.

simplify

This is not very useful parameter but can be used to prevent the first slicing of our result when we have only one database to work upon. It will automatically show the result for first database only. By default it is False. For example:

sqlite.execute(
    "SELECT * FROM WORLD WHERE SOMEONE LIKE %YOU%",
    db="datab.db",
    simplify=True
).get

It will return something like:

[
    "Result for table1_db1",
    "Result for table2_db1"
]

Instead of:

[
    [
        "Result for table1_db1",
        "Result for table2_db1"
    ]
]

asyncExec

This parameter prove to be very useful when we have big queries and we want to process it in background. There are multiple approaches to do this. But all of them requires at least 6 lines of code to be written by you & makes the code messy (as always).

This can be done by simply passing this parameter as True and a callback function. This function us called when the command(s) get executed with the result as parameter.By default it is False.

def callback(r):
    print(f"Result from callback: {r}")

sqlite.execute(
    "SELECT * FORM WORLD WHERE SOMEONE LIKE %YOU%",
    db="datab.db",
    asyncExec=True,
    callback=callback
)