Written by Sümeyye Sever (notes I took while learning Python)

SQLite is a lightweight, serverless, self-contained database engine that is widely used for small to medium-sized applications. It's designed to be easy to set up and use, making it a popular choice for embedded systems, mobile applications, and local development.

Benefits of SQLite

• No need for a separate database server.
• Simple file-based storage.
• Easy to back up and transfer by copying the database file.
• Ideal for small applications or as an embedded database.

Limitations

• Not suitable for high-traffic, large-scale web applications.
• Lacks some advanced features of server-based databases like PostgreSQL or MySQL.
• Limited concurrency: Write operations are serialized.

Using SQLite in Python

1. Create a new project and inside the main.py file import the sqlite3 module

import sqlite3

2. Now create a connection to a new database (if the database does not exist then it will be created).

db = sqlite3.connect("books-collection.db")

3. Run main.py and you should see a new file appear in PyCharm called books-collection.db

4. Next we need to create a cursor which will control our database.

cursor = db.cursor()

So a cursor is also known as the mouse or pointer. If we were working in Excel or Google Sheet, we would be using the cursor to add rows of data or edit/delete data, we also need a cursor to modify our SQLite database.

5. Let's create a table in our database. Add this code below all the previous lines.

cursor.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title varchar(250) NOT NULL UNIQUE, author varchar(250) NOT NULL, rating FLOAT NOT NULL)")