Python Tips And Tricks — Using Built-In Database

Learn how to work with and use the built-in Python database SQLite3. This tutorial covers three key parts when working with databases namely Creating the database and tables, Inserting data and fetching data. In case you’re new to Python Programming and need a formal introduction to the language, we have made a full Python course on Youtube: https://youtu.be/L3v5tu_ang4.

The code and concepts presented in this article is also explained further in the following video on YouTube: https://youtu.be/gUBnNLfcZq4

Develop the Future, transforming lives, businesses and nations. Learn the skills you need for a $100k+ tech career. Sign us today https://vinsloev.com

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. For more information about the database see their official site: https://www.sqlite.org/about.html

The first thing you need to do is to import the library, no installation is needed. Following the import a connection is needed, this will allow us to read and write to the database. However, this is also the way that our database disk file is created. If no .db file is found matching the one you try to connect to the library will create one by default.

import sqlite3 as sl

def db():
    con = sl.connect('testdb.db')

The .db file is placed in the root folder in which the initiating python file is located. As illustrated below the file is create from a method within main.py and thereby placed next to it.

Next step is to create a table in our database file, as such is needed before we can insert any data. The previous connecting is reused and a CREATE TABLE USER sql statement is executed, which create the table User with three columns namely id, firstname and lastname.

def db():
    con = sl.connect('testdb.db')
    with con:
        con.execute("""
        CREATE TABLE USER (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            firstname TEXT,
            lastname TEXT
        );
    """)

When executing the above command no output is present in the console by default, unless you include it yourself. But as long as no exceptions is present you can count on the table creation has succeded.

Next we define the SQL statement needed to insert the data into the newly created USER table. Along with the statement we also create a data variable of three entities that we would like to insert. Here it’s three random users.

def db():
    con = sl.connect('testdb.db')
    
    sql = 'INSERT INTO USER (id, firstname, lastname) values(?, ?, ?)'
    data = [(1, 'Thomas', 'Hansen'),
            (2, 'Kim', 'Tippe'),
            (3, 'Megan', 'Hansen')]
    with con:
        con.executemany(sql, data)

We then use our active connection to execute the statement, again no output is expected. The last step is then to read from our database and ensure that the data has been stored as expected. To do so we will use a select all (*) statement with no additional conditions. This to ensure that all data in the table is returned. To print the data to the console we will use a simple for loop where each element in the data variable is printed.

def db():
    con = sl.connect('testdb.db')
    with con:
        data = con.execute("SELECT * FROM USER")
        for r in data:
            print(r)

The above code gives us the following output in the console.

Output:

(1, ‘Thomas’, ‘Hansen’)
(2, ‘Kim’, ‘Tippe’)
(3, ‘Megan’, ‘Hansen’)

We can then verify that the data printed matches the data that we inserted earlier in this tutorial.

This concludes the SQLite tutorial as we have now successfully created, inserted and fetched data from a database using a few lines of Python code.

For more Python Tips and Tricks like this visit the Vinsloev Academy page here on Medium or on YouTube, as this story is part of a larger series of build it yourself Python tools for Ethical hacking: https://www.youtube.com/channel/UC-OKxBgjKLBGHbueyIOWptw?sub_confirmation=1

For more we recommend the Book — Violent Python: A Cookbook for Hackers, Forensic Analysts, Penetration Testers and Security Engineers: https://amzn.to/3RWAjyY