Pooja Danu's Blog
Python SQLite

Python SQLite

Pooja Danu's photo
Pooja Danu
·

2 min read

Table of contents

In the world of database management systems (DBMS), SQLite stands out as a lightweight yet powerful solution for managing relational databases. Developed as a self-contained, serverless, and zero-configuration database engine, SQLite offers simplicity, reliability, and efficiency for a wide range of applications.

What is SQLite?

SQLite is an open-source, relational database management system that is widely used due to its simplicity, portability, and versatility. Unlike traditional DBMS, SQLite operates without the need for a separate server process, making it serverless and self-contained. It stores the entire database as a single cross-platform file on disk, making it easy to deploy and manage. SQLite implements a large subset of SQL (Structured Query Language) and supports most of the standard SQL features, including transactions, indexes, triggers, and views.

Why Use SQLite?

  • SQLite is designed to be lightweight, with a small memory footprint and minimal dependencies. It's an ideal choice for applications with low resource requirements.

  • Unlike traditional SQL databases that require a separate server process, SQLite operates as a library linked directly into the application. This eliminates the need for complex server setup and configuration.

  • SQLite databases are platform-independent and can be used seamlessly across various operating systems, including Windows, macOS, Linux, iOS, and Android.

Connection to SQLite Database

import sqlite3

connect = sqlite3.connect('example.db')
c = connect.cursor()

#create a table named 'employees'
c.execute("""CREATE TABLE IF NOT EXISTS employees (
            firstname TEXT,
            lastname TEXT,
            pay INTEGER
            )""")

#insert data
c.execute("INSERT INTO employees VALUES ('Jooe', 'Doe', 500)")
c.execute("INSERT INTO employees VALUES ('Minnie', 'Mo', 900)")
c.execute("INSERT INTO employees VALUES ('Sooey', 'Macd', 550)")

connect.commit()
connect.close()

We import the sqlite3 module, which provides an interface for working with SQLite databases in Python.

connect = sqlite3.connect('example.db')

This line establishes a connection to the SQLite database file named example.db. If the file doesn't exist, it will be created. The connection is assigned to the variable connect.

The line c = connect.cursor() creates a cursor object associated with the SQLite database connection (connect). Cursors are used to execute SQL commands and navigate through the result set returned by queries.

commit() close() commit the changes made to the database (i.e., saving the changes permanently) and then close the connection to the database.

Conclusion

SQLite offers a compelling solution for developers seeking a lightweight, versatile, and efficient database management system. With its serverless architecture, cross-platform compatibility, and rich feature set, SQLite is well-suited for a wide range of applications, from embedded systems to mobile apps to desktop software.