DEV Community

Cover image for Simple Ranking with SQLite3
Kajiru
Kajiru

Posted on

Simple Ranking with SQLite3

#tutorial #database #sql #python

SQLite3 Simple Ranking

In this article, we will build a simple ranking system using sqlite3.

SQLite manages a single database as a single file.

To inspect the generated database file, it is convenient to use dedicated software such as:

The complete source code is shown at the end.

1. Preparing the Modules

In your working folder, create an executable file main.py and a database utility file util_db.py that contains all database-related logic.

The folder structure should look like this:

# Folder structure
working_folder/
 ├ main.py      # Main program
 └ util_db.py   # Database utility
Enter fullscreen mode Exit fullscreen mode

The initial state of util_db.py is as follows.

In the constructor, we prepare the path to the database file and the table name.

# util_db.py (initial state)
import datetime
import os
import sqlite3

class MyDB():

    def __init__(self, path, table):
        """ Constructor """
        self.dir_base = os.path.dirname(__file__)
        self.db_path = os.path.join(self.dir_base, path)
        self.db_table = table

    # Time
    def get_time(self):
        """ Get current datetime """
        return datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S")
Enter fullscreen mode Exit fullscreen mode

2. CRUD Pattern with SQLite3

CRUD stands for Create, Read, Update, and Delete, the four basic database operations.

Since sqlite3 is part of Python’s standard library, installation is usually not required.

For this simple ranking example, we use the following table structure:

Column name Meaning Example
uid Sequential ID 1, 2, 3, ...
name User name Alex
comment Comment I'm champion!!
score Score 123
time_stamp Created time 1970/01/01 00:00:00

2-1. Creating a Table

This method creates a new table.

We connect to the database using sqlite3.connect() and execute SQL with cur.execute().

The argument is a CREATE TABLE ... SQL statement.

# util_db.py (excerpt)
# Create table
def create_table(self):
    """ Create table """
    # SQLite
    con = sqlite3.connect(self.db_path)
    cur = con.cursor()
    sql = """
        CREATE TABLE IF NOT EXISTS {0}(
        uid INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT DEFAULT "noname",
        comment TEXT DEFAULT "nocomment",
        score INTEGER DEFAULT 0,
        time_stamp TEXT DEFAULT "1970/01/01 00:00:00")
        """.format(self.db_table)
    cur.execute(sql)
    con.commit()  # Important
    con.close()
Enter fullscreen mode Exit fullscreen mode

2-2. Inserting Data (Create)

This method inserts a new record into the table.

As before, we use an INSERT INTO ... SQL statement.

# util_db.py (excerpt)
# CRUD(Create)
def insert_record(self, name, comment, score):
    """ Insert a record """
    # SQLite
    con = sqlite3.connect(self.db_path)
    cur = con.cursor()
    sql = """
        INSERT INTO {0} VALUES(?, ?, ?, ?, ?)
        """.format(self.db_table)
    cur.execute(sql, (None, name, comment, score, self.get_time()))
    con.commit()  # Important
    con.close()
Enter fullscreen mode Exit fullscreen mode

2-3. Reading Data (Read)

This method retrieves data from the table.

Using a SELECT ... statement, records are sorted by score in descending order.

The limit argument controls how many top records are returned.

# util_db.py (excerpt)
# CRUD(Read)
def read_records(self, limit=10):
    """ Read records (up to limit) """
    # SQLite
    con = sqlite3.connect(self.db_path)
    cur = con.cursor()
    sql = """
        SELECT * FROM {0} ORDER BY score DESC LIMIT ?
        """.format(self.db_table)
    cur.execute(sql, (limit,))
    records = cur.fetchall()
    con.close()
    return records
Enter fullscreen mode Exit fullscreen mode

2-4. Updating Data (Update)

This method updates an existing record.

An UPDATE ... statement is used to update the record with the specified uid.

# util_db.py (excerpt)
# CRUD(Update)
def update_record(self, uid, name, comment, score):
    """ Update a record """
    # SQLite
    con = sqlite3.connect(self.db_path)
    cur = con.cursor()
    sql = """
        UPDATE {0} SET name=?, comment=?, score=?, time_stamp=? WHERE uid=?
        """.format(self.db_table)
    cur.execute(sql, (name, comment, score, self.get_time(), uid))
    con.commit()  # Important
    con.close()
    return self.read_record(uid)
Enter fullscreen mode Exit fullscreen mode

2-5. Deleting Data (Delete)

This method deletes a record from the table.

A DELETE ... statement is used to remove the record with the specified uid.

# util_db.py (excerpt)
# CRUD(Delete)
def delete_record(self, uid):
    """ Delete a record """
    # SQLite
    con = sqlite3.connect(self.db_path)
    cur = con.cursor()
    sql = """
        DELETE FROM {0} WHERE uid=?
        """.format(self.db_table)
    cur.execute(sql, (uid,))
    con.commit()  # Important
    con.close()
Enter fullscreen mode Exit fullscreen mode

Testing

The test procedure is as follows.

Here, a random name, comment, and score are generated and inserted into the table.

(If you uncomment other sections, you can test update and delete operations.)

# main.py
# Members
names = ["Alex", "Becky", "Christy", "David"]
comments = ["Champion!!", "Nice!!", "Good!!", "OMG...!!"]

# MyDB
my_db = util_db.MyDB("data.sqlite", "ranking")

# Create table
my_db.create_table()

# Insert (Random)
name = random.choice(names)
comment = random.choice(comments)
score = random.randint(30, 300)
my_db.insert_record(name, comment, score)

# Update
# my_db.update_record(1, "Thomas", "Hi, I'm hacker!!", 777)

# Delete
# my_db.delete_record(1)

# Read (Ranking)
records = my_db.read_records(limit=5)
print("= Ranking =")
for record in records:
    print("Record:", record)
Enter fullscreen mode Exit fullscreen mode

Execution Result

The output will look like this:

= Ranking =
Record: (4, 'David', 'Nice!!', 284, '2026/01/04 08:25:02')
Record: (11, 'Christy', 'Champion!!', 282, '2026/01/04 08:31:19')
Record: (8, 'Becky', 'Nice!!', 275, '2026/01/04 08:30:29')
Record: (5, 'David', 'Good!!', 269, '2026/01/04 08:25:54')
Record: (7, 'Christy', 'Good!!', 250, '2026/01/04 08:29:11')
Enter fullscreen mode Exit fullscreen mode

Complete Code

Below is the complete implementation.

# util_db.py
import datetime
import os
import sqlite3

class MyDB():

    def __init__(self, path, table):
        """ Constructor """
        self.dir_base = os.path.dirname(__file__)
        self.db_path = os.path.join(self.dir_base, path)
        self.db_table = table

    # Create table
    def create_table(self):
        """ Create table """
        con = sqlite3.connect(self.db_path)
        cur = con.cursor()
        sql = """
            CREATE TABLE IF NOT EXISTS {0}(
            uid INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT DEFAULT "noname",
            comment TEXT DEFAULT "nocomment",
            score INTEGER DEFAULT 0,
            time_stamp TEXT DEFAULT "1970/01/01 00:00:00")
            """.format(self.db_table)
        cur.execute(sql)
        con.commit()
        con.close()

    # CRUD(Create)
    def insert_record(self, name, comment, score):
        """ Insert a record """
        con = sqlite3.connect(self.db_path)
        cur = con.cursor()
        sql = """
            INSERT INTO {0} VALUES(?, ?, ?, ?, ?)
            """.format(self.db_table)
        cur.execute(sql, (None, name, comment, score, self.get_time()))
        con.commit()
        con.close()

    # CRUD(Read)
    def read_records(self, limit=10):
        """ Read records """
        con = sqlite3.connect(self.db_path)
        cur = con.cursor()
        sql = """
            SELECT * FROM {0} ORDER BY score DESC LIMIT ?
            """.format(self.db_table)
        cur.execute(sql, (limit,))
        records = cur.fetchall()
        con.close()
        return records

    def read_record(self, uid):
        """ Read one record """
        con = sqlite3.connect(self.db_path)
        cur = con.cursor()
        sql = """
            SELECT * FROM {0} WHERE uid=?
            """.format(self.db_table)
        cur.execute(sql, (uid,))
        record = cur.fetchone()
        con.close()
        return record

    # CRUD(Update)
    def update_record(self, uid, name, comment, score):
        """ Update a record """
        con = sqlite3.connect(self.db_path)
        cur = con.cursor()
        sql = """
            UPDATE {0} SET name=?, comment=?, score=?, time_stamp=? WHERE uid=?
            """.format(self.db_table)
        cur.execute(sql, (name, comment, score, self.get_time(), uid))
        con.commit()
        con.close()
        return self.read_record(uid)

    # CRUD(Delete)
    def delete_record(self, uid):
        """ Delete a record """
        con = sqlite3.connect(self.db_path)
        cur = con.cursor()
        sql = """
            DELETE FROM {0} WHERE uid=?
            """.format(self.db_table)
        cur.execute(sql, (uid,))
        con.commit()
        con.close()

    # Time
    def get_time(self):
        """ Get current datetime """
        return datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S")
Enter fullscreen mode Exit fullscreen mode 
# main.py
import random
import util_db

def main():
    """ Main process """

    names = ["Alex", "Becky", "Christy", "David"]
    comments = ["Champion!!", "Nice!!", "Good!!", "OMG...!!"]

    my_db = util_db.MyDB("data.sqlite", "ranking")
    my_db.create_table()

    name = random.choice(names)
    comment = random.choice(comments)
    score = random.randint(30, 300)
    my_db.insert_record(name, comment, score)

    records = my_db.read_records(limit=5)
    print("= Ranking =")
    for record in records:
        print("Record:", record)

if __name__ == "__main__":
    main()
Enter fullscreen mode Exit fullscreen mode

Final Notes

Thank you for reading until the end.

sqlite3 is a simple and easy-to-use library, making it perfect for learning SQL basics.

If you found this helpful, a 👍 would be greatly appreciated!

Top comments (0)