Python Sqlite Tutorial: Getting Started With Sqlite In Python

SQLite is a lightweight, serverless, and self-contained relational database engine that is widely used for embedded systems and small to medium-scale applications.

Python's built-in sqlite3 module provides a convenient way to interact with SQLite databases.

In this tutorial, we'll cover the basics of using SQLite in Python, including creating databases, executing SQL queries, and working with data.

Prerequisites:

Before getting started, ensure that you have Python installed on your system. Additionally, since SQLite is a C library, you don't need to install it separately; it comes bundled with Python.

Creating a SQLite Database:

To get started with SQLite in Python, you first need to create a database. In SQLite, a database is simply a file. Let's create a new SQLite database and connect to it.

import sqlite3

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

In this example, example.db is the name of the SQLite database file. If the file doesn't exist, SQLite will create it.

Creating a Table:

Once connected to the database, you can create tables to organize your data. In SQLite, tables are created using SQL statements.

Let's create a simple users table:

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

# Execute a query to create a users table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL
    )
''')

# Commit the changes to the database
connection.commit()

In this example, the users table has three columns: id (integer and primary key), username (text), and email (text). The IF NOT EXISTS clause ensures that the table is created only if it doesn't already exist.

Inserting Data:

Now that we have a table, let's insert some data into it.

# Insert data into the users table
cursor.execute("INSERT INTO users (username, email) VALUES (?, ?)", ('john_doe', '[email protected]'))

# Commit the changes to the database
connection.commit()

Here, we use a parameterized query to insert data into the users table.

The ? placeholders are filled with the actual values provided in the tuple (('john_doe', '[email protected]')).

Querying Data:

You can retrieve data from the database using SELECT queries. Here's an example of retrieving all users from the users table:

# Query all users
cursor.execute("SELECT * FROM users")

# Fetch all rows
rows = cursor.fetchall()

# Display the results
for row in rows:
    print(row)

The execute method is used to execute a SELECT query, and fetchall retrieves all rows as a list. The loop then prints each row.

Updating and Deleting Data:

You can also update or delete data in the database using UPDATE and DELETE queries.

# Update the email of a user
cursor.execute("UPDATE users SET email = ? WHERE username = ?", ('[email protected]', 'john_doe'))

# Delete a user
cursor.execute("DELETE FROM users WHERE username = ?", ('john_doe',))

# Commit the changes to the database
connection.commit()

Here, we update the email of a user and then delete the user with the username 'john_doe'.

Closing the Connection:

After performing your operations, it's essential to close the connection to the database.

# Close the connection
connection.close()

Closing the connection ensures that any changes made are persisted to the database.

Conclusion:

This Python SQLite tutorial covers the basics of using SQLite with Python, from creating a database to executing SQL queries.

SQLite is a great choice for small to medium-sized projects or applications that don't require the complexities of a full-fledged database server.

As you explore more advanced topics, you may want to dive into features like transactions, error handling, and using SQLite with asynchronous programming.

The sqlite3 module documentation is a valuable resource for more in-depth information: SQLite3 Python Documentation. Happy coding!