Learning courses
Learn Python
Databases
SQLite

Introduction to Databases (SQLite)

Introduction

In this module, we will learn about databases and how to work with them in Python using SQLite. Databases are essential for storing, managing, and retrieving data in various applications. We will start by understanding the basics of databases, including what they are, how they work, and why they are important. We will then focus on SQLite, a lightweight and self-contained database engine that is widely used in Python applications. We will learn how to create databases, tables, and perform CRUD (Create, Read, Update, Delete) operations using SQLite in Python. Let’s dive into the world of databases with Python!

Basic Concepts

  • Databases: A database is an organized collection of data, typically stored and accessed electronically from a computer system. Databases are used to store and manage data efficiently, allowing users to perform various operations like querying, updating, and deleting data.

  • SQLite: SQLite is a C library that provides a lightweight disk-based database. It doesn’t require a separate server process to be run, making it ideal for embedded systems and small to medium-sized applications. SQLite databases are self-contained, serverless, and can be used in various programming languages, including Python.

  • CRUD Operations: CRUD stands for Create, Read, Update, and Delete, which are the basic operations that can be performed on data in a database. These operations allow users to interact with the data stored in the database and manipulate it as needed.

  • SQL: SQL (Structured Query Language) is a standard language for interacting with databases. It is used to perform various operations like creating tables, inserting data, querying data, updating records, and deleting records in a database.

Using SQLite in Python

To work with SQLite databases in Python, we can use the built-in sqlite3 module, which provides an interface for interacting with SQLite databases. The sqlite3 module allows us to connect to a database, create tables, insert data, query data, update records, and delete records. Let’s see how we can use SQLite in Python:

sqlite_example.py
import sqlite3

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

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

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

# Insert data into the table
cursor.execute('''
    INSERT INTO users (name, age) VALUES ('Alice', 30)
''')

# Query the table
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

for row in rows:
    print(row)

# Commit the changes and close the connection
conn.commit()
conn.close()

In the example above, we first connect to a SQLite database using the sqlite3.connect() function. We then create a cursor object to execute SQL queries. We create a table named users with columns id, name, and age. We insert a record into the table and query all records from the table. Finally, we commit the changes and close the connection to the database.

Update and Delete Operations

In addition to creating and querying data, we can also perform update and delete operations on records in a SQLite database. Let’s see how we can update and delete records in a table:

sqlite_update_delete.py
import sqlite3

# Connect to the SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Update a record in the table
cursor.execute('UPDATE users SET age = 35 WHERE name = "Alice"')

# Delete a record from the table
cursor.execute('DELETE FROM users WHERE name = "Alice"')

# Query the table after update and delete operations
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

for row in rows:
    print(row)

# Commit the changes and close the connection
conn.commit()
conn.close()

In this example, we update the age of the user named Alice to 35 and then delete the record of the user named Alice from the users table. We then query the table to see the updated data and print the results.

Summary

In this module, we learned about databases, SQLite, and how to work with SQLite databases in Python. We explored the basic concepts of databases, CRUD operations, and SQL. We used the sqlite3 module to connect to a database, create tables, insert data, query data, update records, and delete records. Databases are an essential part of many applications, and understanding how to work with them in Python is a valuable skill for any developer. We encourage you to practice working with databases and explore more advanced topics in database management and SQL.

References

Now that you have learned the basics of working with databases in Python, you can explore more advanced topics like database normalization, transactions, and working with multiple tables. Databases play a crucial role in modern applications, and having a good understanding of database management can help you build robust and scalable applications.

DatabasesMySQL