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 tested code examples. Pages are continually updated to stay current, with code correctness a top priority.
Sam Allen is passionate about computer languages. In the past, his work has been recommended by Apple and Microsoft and he has studied computers at a selective university in the United States.