Sqlite3

The sqlite3 database is a well-known and efficient database that can handle SQL statements. In Python, we have access to it with the sqlite3 module.

By calling connect, we create a new database or load one that already exists in the working directory. With the connection, we can execute statements.

Example

This program creates a new database called "animals" and then creates a table and populates the table with data. It then reads in data from the database.

Step 1 For the example, we delete any existing "animals" file so that the program can be run multiple times without any errors.

Step 2 We call connect on "sqlite3" to create a new "animals" database file (this call will connect to an existing file if one exists).

Step 3 Once we have made a connection, we must get a cursor to execute statements upon.

Step 4 We use a SQL statement (specified as a string) to create a table called "cats" that stores 2 columns, color and weight.

Step 5 Though we can insert rows one-by-one, with executemany() we can insert all the data into a list of tuples in a more efficient way.

Step 6 We can use a SELECT statement to read rows from the sqlite3 database as tuples—each row has all its fields stored within a tuple.

import sqlite3, os

# Step 1: delete the database if it exists.
try:
    os.remove("animals")
except:
    pass

# Step 2: connect to the database.
con = sqlite3.connect("animals")

# Step 3: get a cursor.
cur = con.cursor()

# Step 4: create the table.
cur.execute("CREATE TABLE cats(color, weight)")

# Step 5: create list of tuples, and use executemany to insert with VALUES.
cats = [("orange", 20), ("grey", 15), ("black", 16)]
cur.executemany("INSERT INTO cats VALUES(?, ?)", cats)
con.commit()

# Step 6: get cats as tuples from table.
for c in cur.execute("SELECT * FROM cats"):
    print(f"cat color = {c[0]} size = {c[1]}")cat color = orange size = 20
cat color = grey size = 15
cat color = black size = 16

Python is a batteries-included language, and every installation comes with a powerful and efficient SQL database. And sqlite3 is written in C for maximum performance, even when used from Python.

Dot Net Perls is a collection of pages with code examples, which are updated to stay current. Programming is an art, and it can be learned from examples.

Donate to this site to help offset the costs of running the server. Sites like this will cease to exist if there is no financial support for them.

Sam Allen is passionate about computer languages, and he maintains 100% of the material available on this website. He hopes it makes the world a nicer place.

This page was last updated on Mar 26, 2024 (new).

Home

Changes