SQLite with Python

SQLite with Python: Create Tables and Insert Data in a Local Database 3 Common Mistakes

by

Intro


In this SQLite with Python tutorial, we covered creating tables, inserting data, updating and deleting records, running SQL queries, managing a local database file, and using Python’s sqlite3 module for efficient database operations step-by-step.

SQLite with Python, Most developers think databases always need servers. But with SQLite, you can store data locally using Python — no setup, no server. In this tutorial, you’ll learn how to create tables and insert data into an SQLite database using Python.

What is SQLite?

SQLite is a lightweight, serverless, file-based database that stores all data in a single local file. It does not require any separate database server or complex setup, making it ideal for beginners and local development.
SQLite is widely used in mobile apps, desktop applications, and small-to-medium projects because it runs directly from a file, it is fast, reliable, and easy to integrate with languages like Python.

Why Use SQLite with Python?

SQLite with Python is great for beginners because there’s no setup or server involved — it just works. You can focus on learning Python and databases instead of dealing with configuration.

It’s also perfect for offline apps. Your data stays on the local machine, so everything works even without an internet connection.

If you’re building small tools, scripts, or quick prototypes, SQLite is a simple and reliable choice before moving to bigger databases.

Creating an SQLite Database Locally

Creating an SQLite database is very simple because SQLite is serverless and file-based. When you create a database, it’s just a .db file stored on your system — no installation or background service required.

You can even manage and view this .db file using tools like DB Browser for SQLite. It lets you open the database, view tables, insert data, and run SQL queries visually, which is perfect for beginners.

Once created, this .db file can be copied, shared, or backed up like any normal file.

Connecting SQLite Database with Python

Python comes with a built-in module called sqlite3, so you don’t need to install anything extra. This module allows Python scripts to directly read and write data into an SQLite .db file.

Here’s a simple example showing how to connect, create a table, and insert data in 

import sqlite3

conn = sqlite3.connect("./test.db")
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS "quotes"(
    "S.no" INTEGER PRIMARY KEY AUTOINCREMENT,
    "Quote" TEXT,
    "Quote Description" TEXT
)
''')

cursor.execute('''
INSERT INTO "quotes" ("Quote", "Quote Description")
VALUES(?,?)
''', (
    "A little progress each day adds up to big results",
    "Small consistent steps leads to major achievements over time."
))

conn.commit()
conn.close()

Common Mistakes Beginners Make

When you’re just starting with SQLite and Python, these small mistakes can cause a lot of confusion—even though the fix is simple.

Forgetting to commit changes

If you don’t call conn.commit(), your insert or update queries won’t be saved. The code runs fine, but when you reopen the database, the data is just… gone.

Using the wrong database file path

SQLite works with a local .db file. If the file path is incorrect, Python may create a new database instead of using the existing one—making it look like your data disappeared.

Closing the connection too early

If you close the connection before committing or before all queries finish executing, your operations may fail or not persist properly. Always commit first, then close the connection.

Watch the Full Video Tutorial

Now it’s time to go through the implementation of SQLite with Python from the video tutorial below.

Now you can confidently use SQLite with Python to create tables, insert data, update records, delete rows, run SQL queries, and build a fully functional local database application using Python’s sqlite3 module.

Leave a Comment