sqlite3 Module UseUse a SQL database with the sqlite3 module. Create a table, and insert values into it with execute and executemany.
This page was last reviewed on Mar 26, 2024.
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.
This page was last updated on Mar 26, 2024 (new).
© 2007-2024 Sam Allen.