Hello Friends, Currently I am trying to develop a script to identify the USB vendors and Products using Vendor Ids and Product Ids. The script I found is working with the internet connection. So, I wanted to use that script without an internet connection. After thinking about that, the first solution that came up to my mind is "Maybe I can use a database to store the data." That means I need to add some kind of updater function to the script and store the data to use offline. So, this idea leads me to learn to use python for various SQLite database manipulations.

Introduction to SQLite3:

A database is an organized collection of data, generally stored in a computer system. Basically, we are using databases to store data. These data may be administrative information or special data like engineering data or economic data. There are many types of databases, but all of them can be divided into two categories.

SQL databases :

SQL or Structured Query Language is the simplest and easy to use language that allows users to manipulate large datasets stored in a database. Example: MySQL , SQLite , PostgreSQL

No SQL databases :

No-SQL databases provide a mechanism to store and retrieve data, but they do not use a relational database model. Generally, No-SQL databases using JSON and XML to store data and simply we are calling them serialized data structures.

SQLite's Noticeable Features.

SQLite does not require a server to be running or installed. Unlike MySQL and PostgreSQL, SQLite is server-less and integrated with the application that accesses the database. Another noticeable feature is SQLite requires minimal support from the operating system. This means SQLite is usable in any environment like iPhones, Android devices, game consoles etc. SQLite doesn't need to be configured before using it due to its serverless architecture.

Creating a New SQLite Database using python.

First of all, to interact with SQLite database, python needs a SQL interface compliant. sqlite3 standard module provides a SQL interface compliant with the DB-API 2.0.

Code Sample:

import sqlite3
from sqlite3 import Error
#Create database connection
def create_connection(file_name):
    try:
        con = sqlite3.connect(file_name)
        print "-"*30
        print("Connection Established.", sqlite3.version)
        print "-"*30
    except error as e:
        print("Error occured,", e)
    finally:
        if con:
            con.close()

create_connection(r"database.db")

in the first line of the above code sample, we import our sqlite3 standard module. In the second line, from that module, we import error, because if some error occurred, we can catch that error using try and except block. A special feature in this module is, first of all, you need to connect to the database file, if you connect to a database file that does not exist, the module will automatically create a database file.

Then, we create a function with the file_name parameter and create and store a Connection object in the con variable.

print("Connection Established", sqlite3.version)

is used to identify the connection is really established. Basically, after connecting to a database file, we replace this with our codes with our needs. After completing the operations we want to do after connecting to a database file, it's a good practice to close the connection with the database file. So, connection object stored in the variable named con combined with .close() method. Finally, we call our create_connection function. In that line, the prefix r means raw string. and database.db is the database file name we want to create. Especially note that you can specify the path to a directory if you need otherwise, the database file will create in the current working directory.

Eg:

create_connection(r"D:\sqlite\database.db")

If you want to create a database file in the memory, you need to pass the file name as :memory: to the connect() function.

Eg:

con = sqlite3.connect(':memory:')

References:

Official Python Documentation

cover photo