JustToThePoint English Website Version
JustToThePoint en español
JustToThePoint in Thai

Using Databases with Python: sqlite3

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. sqlite3 is a native Python library for accessing SQLite databases.

import sys
import sqlite3 as sql
from os import system, name
class Database: # We create a class Database with two properties.
    def __init__(self, name):
        self.connection = sql.connect(name) # sql.connect returns a Connection object that represents the database.
        self.cursor = self.connection.cursor() # It creates a Cursor object using the cursor method of the Connection object.

    def __del__(self): # Our destructor is called when an object of the class gets destroyed.
        self.connection.commit() # It saves all the modifications made since the last commit. In other words, it commits any pending transaction to the database.
        self.connection.close() # It closes the SQLite connection. It is important to be sure that any changes have already been committed or they will be lost.

    def commit(self): # It saves or commits the changes to the database.
        self.connection.commit() # It saves all the modifications being made since the last commit. In other words, it commits any pending transaction to the database.

    def execute(self, sql, params=None): # It executes SQL commands.
        self.cursor.execute(sql, params or ()) # It executes an SQL statement.

    def query(self, sql, params=None): # It queries our database and retrieves a set of rows.
        self.cursor.execute(sql, params or ()) # It executes an SQL statement.
        return self.fetchall() # It retrieves the query's result.

    def fetchall(self): # It retrieves the query's result.
        return self.cursor.fetchall() # It fetches all rows of a query result.

Text expanders are these nifty software programs that will take a phrase or word and automatically turn it into a predefined text. Let’s implement a TextExpander with Python.

class TextExpander:
    def __init__(self, database):
        self.db = Database(database) # db is an object or instance of our Database class.
        sql = "CREATE TABLE IF NOT EXISTS snippets(trigger TEXT PRIMARY KEY, text TEXT)" # We create the table snippets if and only if does not exist. It has two fields: trigger (name) and text (Lindsey Williams). Each value stored in an SQLite database belongs to one of the following storage classes: NULL, INTEGER, REAL, TEXT (it defines a text string), and BLOG.
        self.execute(sql) # It executes the SQL command and creates the table.

The data is stored in the database in the snippets table. A table is a collection of related data held in a table format within a database. The table consists of rows of records and each record consists of a number of fields. Each row in our table contains information about a single trigger. A primary key is a column that uniquely identifies each row in a table.

    def execute(self, query, params=None): # It executes SQL commands in our Database's object.
        try:
            self.db.execute(query, params or None)
            self.db.commit() # We commit after every insert or update.
        except Exception as err:
            print('Query Failed: %s\\nError: %s' % (query, str(err))) 
    
    def query(self, query, params=None): # It queries our Database's object.
        try:
            results = self.db.query(query, params or None)
            return results
        except Exception as err:
            print('Query Failed: %s\\nError: %s' % (query, str(err))) 
    
    def replaceTrigger(self, myTrigger): # It takes the trigger as an argument and returns its predefined text.
        trigger = (myTrigger, )
        sql = "SELECT text FROM snippets WHERE trigger=?"
        results = self.query(sql, trigger)
        for r in results:
            print(r[0])

    def allSnippets(self): # It prints all your text expansion shortcuts.
        sql = "SELECT trigger, text FROM snippets"
        results = self.query(sql)
        for r in results:
            print(r[0], ":", r[1])

    def insertSnippets(self, myTrigger, myText):  # It adds a snippet or updates it.
        sql = "INSERT OR REPLACE INTO snippets ( trigger, text ) VALUES (?, ?)"
        newTrigger = ( myTrigger, myText )
        results = self.execute(sql, newTrigger)

We are going to use Faker (pip install Faker) to test our application. It is a Python package that generates fake data for you.

user@pc:~$ python Python 3.9.5 (default, May 11 2021, 08:20:37) [GCC 10.3.0] on linux Type "help", "copyright", "credits" or "license" for more information. 
>>> from faker import Faker 
>>> fake = Faker() 
>>> fake.name() 'Lindsey Williams' 
>>> fake.address() '3346 Jeffrey Bypass\\nNorth Michael, MT 79279' 
>>> fake.sentence() 'Show summer now stuff total ready.'

if __name__ == "__main__":
    clear()
    tx = TextExpander('/home/nmaximo7/myPython/snippets.db')
    tx.insertSnippets("address", "3346 Jeffrey Bypass\\nNorth Michael, MT 79279")
    tx.insertSnippets("name", "Lindsey Williams")
    tx.insertSnippets("website", "http://justtothepoint.com")
    tx.insertSnippets("sentence", "Show summer now stuff total ready")
    tx.replaceTrigger(sys.argv[1]) # sys.argv is a list which contains the command-line arguments passed to the script. sys.argv[0] is the name of the script. sys.argv[0] is the first argument passed to the script.
    tx.replaceTrigger("name")
    tx.replaceTrigger("city") # This is about testing unexpected inputs.
    tx.allSnippets()

To run or debug our app in VS Code, select Run and Debug on the Debug start view or press F5. Our result is:

3346 Jeffrey Bypass # tx.replaceTrigger(sys.argv[1])
North Michael, MT 79279
Lindsey Williams # tx.replaceTrigger(“name”)
address : 3346 Jeffrey Bypass # tx.allSnippets()
North Michael, MT 79279 name : Lindsey Williams website : http://justtothepoint.com sentence : Show summer now stuff total ready

We need to create a launch.json file to pass command line parameters to our application: tx.replaceTrigger(sys.argv[1]). Select the option Open Configuration in the menu Run, and it will open the .vscode/launch.json file:

{    
    "version": "0.2.0",   
     "configurations": [       
        {         
             "name": "Python: Current File",         
             "type": "python",          
            "request": "launch",         
             "program": "${file}",        
             "args": ["address"], // We are going to pass "address" as the first and only argument to the script.          
             "console": "integratedTerminal"     
          }    
    ] 
}

SQLite and DB Browser for SQLite

It is always a good idea to install SQLite and/or DB Browser for SQLite (DB4S). DB Browser for SQLite (DB4S) is a visual, open source tool to create, design, and edit database files compatible with SQLite. To install it on Linux, their latest release is available as an AppImage, Snap packages, and distribution specific packages, e.g., Ubuntu, Debian, and derivates: sudo apt install sqlite sqlitebrowser.

We can also verify if the program has created the tables and filled them with the appropriate values in the snippets.db database. The SQLite project delivers a simple command-line tool that allows you to interact with the SQLite databases using SQL statements and commands. First, open a terminal and type sqlite3 snippets.db (SQLite Tutorial provides an easy way to master SQLite)

user@pc:~$ sqlite3 snippets.db # It connects to the SQLite database. SQLite version 3.34.1 2021-01-20 14:10:07 Enter ".help" for usage hints. # It will show all available commands. 
sqlite> .databases # It shows all the databases of the current connection. 
main: /home/nmaximo7/myPython/snippets.db r/w 
sqlite> .tables # It displays all the tables in the current database. 
snippets 
sqlite> select * from snippets; # Query all the rows from the table snippets.
address|3346 Jeffrey Bypass North Michael, MT 79279 name|Lindsey Williams website|http://justtothepoint.com sentence|Show summer now stuff total ready 
sqlite> .schema snippets # The ".schema" command shows the structure of a table. 
CREATE TABLE snippets(trigger VARCHAR PRIMARY KEY, text VARCHAR); 
sqlite> .quit # Exit sqlite3. Ctrl + D also works.

DB Browser for SQLite is a very user friendly tool that can be used to create, design, and edit database files compatible with SQLite. To open a database in DB Browser, click on the Open Database button in the toolbar, navigate to where you have stored the database file on your computer, select it, and click open.

SQLite and DB Browser for SQLite

To run a SQL query, select the Execute SQL tab in the left-hand pane and you will be presented with a three-pane window and a small toolbar (New tab, Open SQL file, Save SQL file, Execute all/selected SQL, etc.). The top one is labeled SQL 1. This is the SQL editor pane into which you could type your queries, e.g., select * from snippets; and then, click on Execute all/selected SQL ▶.

def clear(): # Clear screen in Python, [Geeks for Geeks](https://www.geeksforgeeks.org/clear-screen-python/).
    # for windows
    if name == 'nt':
        response = system('cls')
    # os.name is 'posix' for macOS and Linux
    else:
        response = system('clear')

SQLite inner join clause

Let’s suppose that we want to extend our TextExpander with new functionality. It will take a phrase or word (“gn”) and automatically turn it into a predefined text (“Good night my honey”), but it will give the user the option to add extra text to personalize it (“Good night my honey (♥_♥)”).

More specifically, the user could provide the program with a list of strings and the trigger will return the predefined text plus a random text from that list. We will only show the differences between the two scripts.

class TextExpander:
    def __init__(self, database):
        self.db = Database(database) # db is an object or instance of our Database class.
        sql = """CREATE TABLE IF NOT EXISTS snippets(trigger TEXT PRIMARY KEY, 
        text TEXT)"""  # We create the table snippets if and only if does not exist.
        self.execute(sql) # It executes the SQL command and creates the table.
        sql2 = """CREATE TABLE IF NOT EXISTS textalt (id integer PRIMARY KEY AUTOINCREMENT, 
        optionstext TEXT UNIQUE, trigger TEXT NOT NULL,
	    FOREIGN KEY (trigger) REFERENCES snippets (trigger))"""
        self.execute(sql2)

In relational databases, data is often distributed in many related tables. A table is associated with another table using foreign keys. A foreign key is a column or group of columns in a relational database table that refers to the primary key of another table. The foreign key links these two tables.

In our example, the table called textalt has an attribute “trigger” that is a foreign key referencing a primary key, trigger, in the snippets table. Since trigger is a foreign key, any value existing in a row of the textalt table must also exist as a snippet’s trigger in the snippets table. One textalt belongs to one snippet and one snippet has zero or many text alternatives.

[...]
    def replaceTrigger2(self, myTrigger):
        trigger = (myTrigger, )
        sql = """SELECT snippets.text, optionstext FROM snippets INNER JOIN 
        textalt on textalt.trigger = snippets.trigger WHERE snippets.trigger = ?;"""

How do we do it? Observe that the INNER JOIN clause selects all rows from both tables if and only if those rows meet or satisfy the conditions specified in the ON clause (textalt.trigger = snippets.trigger). In other words, we get the snippet (WHERE snippets.trigger = ?) and all their textalt linked or associated with it.

        results =  self.query(sql, trigger)
        if len(results)==0: # If the query brings no result, the trigger does not have any optionstext linked to it, we will use the old replaceTrigger method.
            self.replaceTrigger(myTrigger)
        else: # Otherwise, we will print the snippets.text and one optionstext randomly chosen from the query's result, so we can add an extra, random text to personalize the trigger.
            result = random.choice(results)
            print(result[0] + " " + result[1])

    def insertSnippetsAltTexts(self, myTrigger, altTexts):
        for altText in altTexts:
            sql = "INSERT OR REPLACE INTO textalt ( optionstext, trigger ) VALUES (?, ?)"
            newTextAlt = ( altText, myTrigger )
            results = self.execute(sql, newTextAlt)
        
if __name__ == "__main__":
    clear()
    tx = TextExpander('/home/nmaximo7/myPython/snippets.db')
    tx.insertSnippets("address", "3346 Jeffrey Bypass\\nNorth Michael, MT 79279")
    tx.insertSnippets("name", "Lindsey Williams")
    tx.insertSnippets("website", "http://justtothepoint.com")
    tx.insertSnippets("gn", "Good night my honey")
    tx.insertSnippetsAltTexts("gn", ["(♥_♥)", "Love all, trust a few, do wrong to none.", "We can only learn to love by loving"]) # You may want to visit [1 Line Art](https://1lineart.kulaone.com/#/) or [ASCII Art Archive](https://www.asciiart.eu/) for ideas.
    tx.replaceTrigger(sys.argv[1])
    tx.replaceTrigger("name")
    tx.replaceTrigger("city") 
    tx.replaceTrigger2("name")
    tx.replaceTrigger2("gn")

3346 Jeffrey Bypass # tx.replaceTrigger(sys.argv[1])
North Michael, MT 79279
Lindsey Williams # tx.replaceTrigger(“name”)
Lindsey Williams # tx.replaceTrigger2(“name”). name’s trigger does not have any optionstext linked to it, so it will go back to the old replaceTrigger method.
Good night my honey (♥_♥) # tx.replaceTrigger2(“gn”)

SQLAlchemy

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. “The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables,” SQLALchemy Documentation.

Installation: pip install sqlalchemy

user@pc:~$ python Python 3.9.5 (default, May 11 2021, 08:20:37) [GCC 10.3.0] on linux Type "help", "copyright", "credits" or "license" for more information. 
>>> import sqlalchemy 
>>> sqlalchemy.__version__ # Let's verify whether the installation was successful or not. '1.4.26'

Now it is time to replicate our TextExpander with SQLAlchemy.

from os import system, name
from sqlalchemy import create_engine, exc
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy import Table
from sqlalchemy.orm import sessionmaker, relationship, backref
import random

Mappers are responsible for moving data between objects and a database while keeping them independent of each other. SQLAlchemy ORM translates Python classes into/from tables and move data between instances of these classes and rows of these tables.

It all starts by creating a model. We will need to describe the database tables and define our own classes which will be mapped to those tables. These classes are defined in terms of a base class.

Base = declarative_base()
# Let's define our class Snippet. Within the class, we need to define details about the table to which we'll be mapping...      
class Snippet(Base):
    __tablename__ = 'snippets' # ... mainly, the table name, 
    id = Column(Integer, primary_key=True) # and all the names and datatypes of columns. We use Column to define a column. The first column is "id". It is an integer and it is also a primary_key.
    trigger = Column(String, unique=True) # Observe that the types of the Column are the first argument to Column. The most common types are: Integer, String, Text, DateTime, Float, Boolean, and PickleType. unique=True allows us to define unique constraints. 
    text = Column(String)
    textalts = relationship("TextAlt", backref=backref("snippets"))

The most common relationships are one-to-many relationships. relationship() returns a new property “textalts”. We are telling SQLAlchemy to point to the TextAlt class and that there’s a relationship between the Snippet and the TextAlt classes. textalts is a collection of TextAlt objects or instances associated to or linked with a Snippet instance.

    def __init__(self, trigger, text):
        self.trigger = trigger
        self.text = text
    
class TextAlt(Base):
    __tablename__ = 'textalt'
    id = Column(Integer, primary_key=True)
    optionstext = Column(String, unique=True)
    snippet_id = Column(Integer, ForeignKey("snippets.id"))

The ForeignKey class defines a dependency between two fields in different tables. A ForeignKey is how you make SQLAlchemy aware of the relationships between two tables. For example, we are telling SQLAlchemy that snippet_id is a foreign key related to the primary key (id) in the snippets table.

    def __init__(self, optionstext, snippet_id):
        self.optionstext = optionstext
        self.snippet_id = snippet_id

class TextExpander():
    def __init__(self):
        db_uri = "sqlite:///snippets2.db"
        engine = create_engine(db_uri) # We need to connect to our database. create_engine() produces an Engine object based on a URL. The Engine is the starting point for any SQLAlchemy application.
        Base.metadata.create_all(engine) # It creates all tables that they did not exist until now.
        Session = sessionmaker(bind=engine) # It creates a Session class, a kind of factory for new Session objects. All modifications tracked by Sessions will be applied to the underlying database together or none of them will. In other words, Sessions guarantee database consistency.
        self.session = Session() # It creates a session. It is our database handler.
        
    def addSnippet(self, trigger, text): # It adds new snippets.
        snippet = Snippet(trigger, text) # We create an instance of our class Snippet.
        self.session.add(snippet) # To persist our Snippet object, we use the method add from the Session class. The modification is pending and no SQL has yet been issued, so the object is still not a row in the database.
        try:
            self.session.commit() # We commit our change to the database.
        except exc.SQLAlchemyError as e:
            self.session.rollback() # If an exception is raised, we roll back this change to our database. 

    def getIdSnippet(self, trigger): # It gets the id (primary_key) from the table "snippet" 
        query = self.session.query(Snippet).filter(Snippet.trigger==trigger) # We create a new Query object which loads instances of Snippet and filter by the "trigger" attribute. Observe that trigger was defined as unique.
        instance = query.first() # We are only interested in the first result. 
        return instance.id

    def addTextAlt(self, trigger, optionstext): # It adds new TextAlt rows.
        textalt = TextAlt(optionstext, self.getIdSnippet(trigger)) # We create an instance of our class TextAlt. We use getIdSnippet() to get the id (primary key) of trigger.
        self.session.add(textalt)
        try:
            self.session.commit()
        except exc.SQLAlchemyError as e:
            self.session.rollback()

    def getSnippet(self, trigger): # It takes the trigger as an argument and returns its predefined text, and if it was added by the user, additional text.
        query = self.session.query(Snippet).filter(Snippet.trigger==trigger) # We create a new Query object which loads instances of Snippet and filter by the "trigger" attribute.
        instance = query.first() # We are only interested in the first result. 
        if len(instance.textalts)==0: # It is important to understand that the column textalts is filled automatically by SQLAlchemy and it may contain zero or more instances of rows of the "textalt" table  
            print(instance.text) # We print the snippet's predefined text.
        else: 
            textalt = random.choice(instance.textalts)  
            print(instance.text + " " + textalt.optionstext) # We print the snippet's predefined text and one random instance (row) of its textalts. We really only display its optiontext property (field). 

if __name__ == "__main__": 
    myTextExpander = TextExpander()
    myTextExpander.addSnippet(":h", "Hello World!")
    myTextExpander.addSnippet(":gn", "Good night.")
    myTextExpander.addTextAlt(":gn", "Sweet dreams my angel!")
    myTextExpander.addTextAlt(":gn", "Love all, trust a few, do wrong to none.")
    myTextExpander.addTextAlt(":gn", "We can only learn to love by loving")
    myTextExpander.addTextAlt(":gn", "(?_?)")
    myTextExpander.addSnippet(":gm", "Good morning.")
    myTextExpander.addTextAlt(":gm", "Rise and Shine!")
    myTextExpander.addTextAlt(":gm", "We rise by lifting others.")
    myTextExpander.addTextAlt(":gm", "Rise and shine pretty girl.")
    myTextExpander.getSnippet(":h")
    myTextExpander.getSnippet(":gn")
    myTextExpander.getSnippet(":gm")

The result is something like this: Hello World!
Good night. Love all, trust a few, do wrong to none.
Good morning. Rise and shine pretty girl.

Bitcoin donation

JustToThePoint Copyright © 2011 - 2022 PhD. Máximo Núñez Alarcón, Anawim. ALL RIGHTS RESERVED. Bilingual e-books, articles, and videos to help your child and your entire family succeed, develop a healthy lifestyle, and have a lot of fun.

This website uses cookies to improve your navigation experience.
By continuing, you are consenting to our use of cookies, in accordance with our Cookies Policy and Website Terms and Conditions of use.