Store data in SQLite DB using Python at the terminal

Aim

Create an easy way to add names to a database using the command line.

Background

We are teaching our 11 month old baby girl to sign (Baby sign). I have a tendency to try to document these sorts of things so I thought I would start recording the signs we have learnt along the way. The signs added to the database will be the ones that we as parents know, not the baby. This will be added later.

Scope

• Use Python
• Give each record a date stamp (current date)
• Add multiple names at once
• Don't duplicate entries (for exact matches)
• Provide a method of querying names in the database

Out of scope

• Error handling list of strings added at the command line
• Making a relative link to signs.py to call from any directory
• Making the python script available on the local network
• Making the python script available in the cloud (e.g. AWS lambda)
• Adding the database to the cloud
• Backups

• Sync to mobile devices

• The list goes on...

Make DB

sqlite3 signs

Create table

DROP TABLE signs;

CREATE TABLE signs (date TEXT NOT NULL, name TEXT NOT NULL);

signs.py

"""
The database and table names are defined 
here and only here. Change dtname if required. 
""" 
dtname = 'signs'
db = dtname #database
tn = dtname #tablename


def getTodaysDate():
    """
    Return todays date in ISO8601 format.
    """
    dt = datetime.datetime.now()
    return convertDateToISO8601(dt) 


def convertDateToISO8601(dt):
    """
    Formats date object to ISO8601.
    """
    dtISO8601 = dt.isoformat()
    return dtISO8601


def createConnection(db_file):
    """
    Connect to the existing database 
    called 'signs'.
    """
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except sqlite3.Error as e:
        print(e)
    return None


def queryAllRecords(conn, tableName):
    """
    Uses a connection to a database to 
    query all of the names that have 
    been added to the database.
    """ 
    try:
        c = conn.cursor()
        c.execute('SELECT name FROM {}'.format(
            tableName))
        rows = c.fetchall()
        rows = [r[0] for r in rows]
        return rows
    except sqlite3.Error as e:
        print(e)
    return None


def queryRecordsAndPrint(conn, tableName):
     """
     Prints a list of signs already 
     added to the DB.
     """
     qrp = queryAllRecords(conn, tableName)
     [print(r) for r in qrp]


def insertRecord(conn, tn, dt, name):
    """
    Insert a single record that 
    includes todays date and a name 
    using an existing connection. 
    """
    try:
        c = conn.cursor()
        c.execute("INSERT INTO {} VALUES ('{}', '{}')".format(
            tn, dt, name))
        conn.commit()
    except sqlite3.Error as e:
        print(e)
    return None


def main(opt):
    """
    The main function is called to parse 
    data provided in the terminal and 
    depending on the flags provided print 
    out a query of all records currently 
    in the database.

    Args
    ----
    opt: OptionParser object

        - opt.signslist: list of strings

        - opt.query: boolean - returns true if 
            called at the command line    
    """
    if opt.signsList:
         # Convert string representation 
         #of a list to list 
         argList = ast.literal_eval(opt.signsList)

         # get todays date and convert 
         #date to iso8601 format
         todaysDate = getTodaysDate()

         # connect to sqlite
         conn = createConnection(db)

         # Get all existing records
         allSigns = queryAllRecords(conn, tn)

         # for each item check if it exists 
         #in sqlite
         for item in argList: 
             if item in allSigns:
                 continue 
             else:
                 insertRecord(
                     conn, 
                     tn, 
                     todaysDate, 
                     item)

         # Get all existing records
         allSigns = queryAllRecords(conn, tn)

         # print the total number of 
         #signs in the database
         print('Total signs in DB: {}'.format(
             len(allSigns)))

    if opt.query:
        conn = createConnection(db)
        queryRecordsAndPrint(conn, tn)


if __name__ == "__main__": 
  """
  Command line execution.  

  Flags
  -----
    -l / --list:
        String representation of a list.

    -q / --query: 
        No value, returns a list of names.

  Example
  -------
    python signs.py --list "['cat', 'dog']" --query
    python signs.py -l "['cat', 'dog']" -q
  """
  import datetime
  import sys
  import ast
  import sqlite3
  from optparse import OptionParser

  # add flags to use in the terminal
  parser = OptionParser()

  # add a list
  parser.add_option("-l", "--list", dest="signsList",
      help="list to parse", metavar="LIST")

  # add a query
  parser.add_option("-q", "--query",
      action="store_true", dest="query", default=False) 
  (options, args) = parser.parse_args()

  # run main function
  main(options)

Example

python signs.py --list "['cat', 'dog']" --query

# Returns 
Total signs in DB: 2
cat
dog