SQLite - Python: A Beginner's Guide

Hello there, future database wizards! I'm thrilled to be your guide on this exciting journey into the world of SQLite and Python. As someone who's been teaching computer science for years, I can assure you that by the end of this tutorial, you'll be manipulating databases like a pro. So, let's dive in!

Installation

Before we start our adventure, we need to make sure we have the right tools. The good news is, if you have Python installed (which I'm assuming you do), you already have SQLite! It comes bundled with Python, so there's no need for additional installations. Isn't that convenient?

Python sqlite3 module APIs

Now, let's talk about the star of our show: the sqlite3 module. This module is your ticket to working with SQLite databases in Python. It provides a set of functions and methods that we'll be using throughout this tutorial.

Here's a table of the main methods we'll be using:

Method	Description
connect()	Creates a connection to the database
cursor()	Creates a cursor object to execute SQL commands
execute()	Executes a single SQL command
executemany()	Executes an SQL command against all parameter sequences
commit()	Commits the current transaction
rollback()	Rolls back any changes to the database since the last call to commit()
close()	Closes the database connection

Don't worry if these seem a bit mysterious now. We'll be exploring each of these in detail as we go along.

Connect To Database

Let's start by connecting to a database. It's like knocking on the door of your data's house!

import sqlite3

# Connect to database (or create it if it doesn't exist)
conn = sqlite3.connect('my_first_db.db')

print("Database connected successfully!")

# Don't forget to close the connection when you're done
conn.close()

In this example, we're importing the sqlite3 module and using its connect() function to create a connection to a database named 'my_first_db.db'. If this database doesn't exist, SQLite will create it for us. How thoughtful!

Create a Table

Now that we're connected, let's create a table. Think of a table as a spreadsheet within your database.

import sqlite3

conn = sqlite3.connect('my_first_db.db')
cursor = conn.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS students
                  (id INTEGER PRIMARY KEY,
                   name TEXT NOT NULL,
                   age INTEGER)''')

print("Table created successfully!")

conn.commit()
conn.close()

Here, we're creating a table called 'students' with three columns: id, name, and age. The IF NOT EXISTS clause is our safety net – it prevents errors if we accidentally try to create the same table twice.

INSERT Operation

Time to add some data to our table. It's like filling out our spreadsheet!

import sqlite3

conn = sqlite3.connect('my_first_db.db')
cursor = conn.cursor()

# Insert a single row of data
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Alice", 22))

# Insert multiple rows of data
students_data = [("Bob", 19), ("Charlie", 21), ("David", 20)]
cursor.executemany("INSERT INTO students (name, age) VALUES (?, ?)", students_data)

print("Data inserted successfully!")

conn.commit()
conn.close()

In this example, we're inserting data into our 'students' table. We use execute() for a single insert and executemany() for multiple inserts. The ? marks are placeholders for our data, which helps prevent SQL injection attacks. Safety first!

SELECT Operation

Now, let's retrieve our data. It's like asking our database to show us what it remembers!

import sqlite3

conn = sqlite3.connect('my_first_db.db')
cursor = conn.cursor()

# Select all rows from the students table
cursor.execute("SELECT * FROM students")

# Fetch all rows
rows = cursor.fetchall()

for row in rows:
    print(f"ID: {row[0]}, Name: {row[1]}, Age: {row[2]}")

conn.close()

Here, we're selecting all rows from our 'students' table and printing them out. The * in our SQL query means "give me everything". It's like saying "Open sesame!" to your database.

UPDATE Operation

Sometimes, we need to change our data. Let's update a student's age:

import sqlite3

conn = sqlite3

Credits: Image by storyset