Ozgur's Blog

Random ramblings of personal nature

SQLite with Python - 20 Mins


Introduction

Sqlite is a wonderful database solution that can be used with Python with no additional libraries. And sometimes we really do need a small scale database solution to store our data in an orderly fashion - SQLite is a solution that fits the bill here.

Usage

Adding SQLite Support

To use SQLite within our Python script we need to import it first:

import sqlite3

Creating&Connecting the Database File

Let's try to connect to our database file. The connect() function creates the database file if it can't find it

db = sqlite3.connect("test.db")

After this command we can use the Sqlite3 functions by db variable.

Creating our table

Although we can use graphical tools like DB Browser for SQLite our scenario here involves that we don't have access to anything but Python.

So let's create our users table by using DDL (Data Definition Language)

db = sqlite3.connect("test.db")

sql = """CREATE TABLE users(
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT,
    username TEXT

);"""
db.execute(sql)

The db.execute() format runs the query we have written.

Entering data to our table

Because I am a lazy feth I am going to create a string insert statement and populate it with data by string formatting. Not the best way to do it, and I most certainly don't recommend this usage on serious web projects, but it is super simple and fast:

name = input("Please enter your name")
username = input("Please enter your username")

db = sqlite3.connect("test.db")
insert_query = """INSERT INTO 
    users (name, username) 
    VALUES
    ('{}','{}')""".format(name, username)
db.execute(insert_query)
db.commit()

The main difference from DDL is the addition of db.commit() at the end of our script. This saves the entered data in the Sqlite db. Queries that manipulate the structure of the database (Creating, updating and dropping tables) do not need this statement, but your inserts, updates and deletes must end with this statement to commit your changes.

Getting data from our table

To get our data we are going to use a select query, probably not surprisingly:

db = sqlite3.connect("test.db")

select_query = """
SELECT * FROM users
"""
result_set = db.execute(select_query)
for result in result_set:
    print(result[0]) # id
    print(result[1]) # name
    print(result[2]) # username

Sure I know there are ways to get the data by their tablenames but we are being quick and dirty here. And this gets the job done.

Updating & Deleting Data

These two queries use a similar structure so I am going to discuss them in one fell swoop.

Let's change our first user's name to Test:

db = sqlite3.connect("test.db")

update_query = """
    UPDATE users SET name = '{}' WHERE id = {};
""".format("Test",1)
db.execute(update_query)
db.commit()

And delete it

delete_query = """
    DELETE FROM users WHERE id = {}
""".format(1)
db.execute(delete_query)
db.commit()

Boom!

Dropping Tables

Now for a destructive encore let's drop the users table!

db = sqlite3.connect("test.db")
drop_query="DROP TABLE users;"
db.execute(drop_query)

Thank you for reading!