Python: SQLite3

SQLite3 is a relational database with great advantages including better performance, portability and ease of use. It is mainly used for the purpose of internal data storage in applications. Python sqlite3 is a highly sophisticated library that enables us create and maintain SQLite database.

Clone demo from github

Before sqlite3 can be imported we need to ensure the necessary packages are installed and available.

yum install python-dev*
yum install libsqlite*
pip install pysqlite

Import sqlite, create a database and table

#!/usr/bin/python
import sqlite3

conn = sqlite3.connect("JaiMaruthy.db")
conn.execute('''CREATE TABLE IF NOT EXISTS HOSTLIST
                 (hostid INTEGER PRIMARY KEY AUTOINCREMENT,
                 hostname TEXT NOT NULL,
                 application TEXT NOT NULL,
                 database TEXT NOT NULL);''')
conn.commit()
conn.close()

In the above code snippet, we have imported the sqlite3 module. We use sqlite3.connect() function to create and connect to a Database named “JaiMaruthy.db”.

execute() function provided by sqlite3 is used to run a SQL query on a database for operations including create, remove, update and delete tables in a sqlite database.

We execute the “create table” query to create a table called “HOSTLIST” with three columns to store “hostname”, “application” and “database” details. Note that, ‘hostid’ is defined as “primary key with Auto increment”, to uniquely identify entries in the table.

Update and list entries from table

#!/usr/bin/python
import sqlite3

conn = sqlite3.connect("JaiMaruthy.db")
conn.execute('''CREATE TABLE IF NOT EXISTS HOSTLIST
                 (hostid INTEGER PRIMARY KEY AUTOINCREMENT,
                 hostname TEXT NOT NULL,
                 application TEXT NOT NULL,
                 database TEXT NOT NULL);''')
conn.execute('INSERT INTO HOSTLIST(hostname,application,database) VALUES("myservername", "tomcat", "MySql")')
list_entries=conn.execute("SELECT * FROM HOSTLIST")
for entry in list_entries:
    print(entry)

conn.commit()
conn.close()

(1, u'myservername', u'tomcat', u'MySql')

In the above code snippet, we again use the execute() function, to update the table “HOSTLIST” that we created earlier. We again run a query to select and display all entries from the table.

Python interprets each entry as a tuple (an ordered collection of unchangeable information) and hence can be only be accessed by looping through them.

Now that we have seen how to use sqlite, lets write a nicer and tidier piece of code.

DBops.py

In this file, we define seperate functions to create DB, create, remove, update and delete tables.

#!/usr/bin/python
import sqlite3
from sqlite3 import Error

def create_connection(db_file):
    """ create database connection """
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)
        conn.close()

def create_table(db_file):
    """ create HOSTLIST table in database """
    try:
        conn = sqlite3.connect(db_file)
        conn.execute('''CREATE TABLE IF NOT EXISTS HOSTLIST
                 (hostid INTEGER PRIMARY KEY AUTOINCREMENT,
                 hostname TEXT NOT NULL,
                 application TEXT NOT NULL,
                 database TEXT NOT NULL);''')
    except Error as e:
        print(e)
    finally:
        conn.commit()
        conn.close()


def listhosts(db_file, name):
    """ Query one or all hosts in HOSTLIST
         IF name is 'ALL' query all hosts, else query 'name' 
         Example: 
         listhosts(db_file, "ALL")
         listhosts(db_file, "servername.domain.com")"""
    
    try:
        conn = sqlite3.connect(db_file)
        rows = conn.execute("SELECT * FROM HOSTLIST").fetchall()
        if name == 'ALL':
            for row in rows:
                print(row)
        else:
            for row in rows:
                for entry in row:
                    if entry == name:
                       print(entry)
                       status = "found"
                       break
                    else:
                       status = "not found"
                if status == "found":
                    break
            if status == "not found":
                print("Hostname not found")
    except Error as e:
        print(e)
    finally:
        conn.close()


def add_host(db_file, host_name, application_name, database_name):
    """ Add new host to table """
    try:
        conn = sqlite3.connect(db_file)
        conn.execute("INSERT INTO HOSTLIST(hostname,application,database) VALUES(?,?,?)", (host_name, application_name, database_name))
    except Error as e:
        print(e)
    finally:
        conn.commit()
        conn.close()

def delete_host(db_file, host_name, application_name, database_name):
    """ Remove host from table """
    try:
        conn = sqlite3.connect(db_file)
        conn.execute("DELETE FROM HOSTLIST WHERE hostname=?", (host_name,))
    except Error as e:
        print(e)
    finally:
        conn.commit()
        conn.close()

Import and use DBops.py

DBops can now be imported and used as a module and the defined functions can be called, as in the below example code snippet.

#!/usr/bin/python
from DBops import *

db_file="maruthy.db"
host_name="server1.domain.com"
application_name="tomcat"
database_name="Oracle"

create_connection(db_file)
create_table(db_file)
add_host(db_file, host_name, application_name, database_name)
listhosts(db_file, "ALL")

#delete_host(db_file, host_name, application_name, database_name)

In this post we looked at how to perform DB operations on sqlite DB using python’s sqlite3 module. sqlite3 library has more to it than this, as described in Python’s sqlite3 documentation. sqlite3 can be used to build robust and
feature rich applications that uses SQLite DB to store data.