sqlite
  1. sqlite-python-sqlite
Previous

SQLite Connectivity in Python

SQLite is a lightweight relational database management system that is commonly used for embedded systems and small applications. Python provides out-of-the-box support for SQLite and makes it easy to connect to an SQLite database in your code.

Syntax

The syntax for connecting to an SQLite database in Python is as follows:

import sqlite3

# Connect to an existing database or create a new one
conn = sqlite3.connect('database_name.db')

# Create a cursor object to interact with the database
cursor = conn.cursor()

# Perform database operations
# ...

# Commit changes to the database
conn.commit()

# Close the connection
conn.close()

Example

Suppose we want to create an SQLite database for a music store and add tables for albums and artists. We can use the following code:

import sqlite3

# Connect to the database or create a new one
conn = sqlite3.connect('musicstore.db')

# Create a cursor object
cursor = conn.cursor()

# Create a table for artists
cursor.execute("""CREATE TABLE artists (
                id INTEGER PRIMARY KEY,
                name TEXT
            )""")

# Create a table for albums
cursor.execute("""CREATE TABLE albums (
                id INTEGER PRIMARY KEY,
                title TEXT,
                artist_id INTEGER,
                FOREIGN KEY (artist_id) REFERENCES artists (id)
            )""")

# Insert some sample data into the tables
cursor.execute("INSERT INTO artists VALUES (1, 'Pink Floyd')")
cursor.execute("INSERT INTO albums VALUES (1, 'Dark Side of the Moon', 1)")

# Commit changes to the database
conn.commit()

# Close the connection
conn.close()

Output

When we run the above code, it creates an musicstore.db database file (if it doesn't already exist) and adds two tables for artists and albums. It also inserts some sample data into the tables.

Explanation

In the example above, we import the sqlite3 module and connect to an SQLite database using the connect() method. We create a cursor object to interact with the database and execute SQL queries on it.

We create two tables for artists and albums and define their columns using the execute() method. We also specify a foreign key constraint for the artist_id column in the albums table using the REFERENCES keyword.

Next, we insert some sample data into the tables using the execute() method and commit the changes to the database using the commit() method. Finally, we close the connection to the database using the close() method.

Use

SQLite connectivity in Python can be used to create, read, update, and delete data from an SQLite database file. It's useful for storing data in a lightweight database system that doesn't require a full-fledged DBMS like MySQL or PostgreSQL.

Important Points

  • Always use parameterized queries to prevent SQL injection attacks.
  • Close the cursor and connection objects after you're done using them to free up memory.
  • Use transactions to group database operations and commit changes all at once for better performance.
  • Use the execute() method to execute SQL statements on the cursor object.

Summary

In this tutorial, we learned about SQLite connectivity in Python and how to connect to an SQLite database, create tables, insert data, and commit changes. We also saw some important points to keep in mind while working with SQLite databases in Python.

PHP SQLite
Published on: