Writing to a SQLite DB from Python Data Structures

This note demonstrates writing to a SQLite database directly from a Python data structure.

import sqlite3

db_path = 'writing-to-sqlite-db-from-python-data-structures/customers.db'

Set up Connections and Cursors

Think of the cursor as an object that you “send out to do something with the database.”

conn = sqlite3.connect(db_path)
c = conn.cursor()

Create Table

• The following statement ensures that multiple customers with the same first_name and last_name cannot be inserted. A primary key over both columns would have the same effect.
• The IF NOT EXISTS clause tells the database to ignore the instruction silently if the table already exists.

create_table_statement = \
'''
CREATE TABLE IF NOT EXISTS customers (
    first_name text,
    last_name text,
    email text,
    PRIMARY KEY (first_name, last_name)
)
'''

c.execute(create_table_statement)

<sqlite3.Cursor at 0x104bbe650>

Commit the changes.

conn.commit()

Close database connection.

conn.close()

Insert into Table

The OR IGNORE clause tells the database that you want to silently ignore records that would violate the constraint.

conn = sqlite3.connect(db_path)
c = conn.cursor()

Note that there are two entries with the same first_name and last_name below, which would violate the primary key constraint. Note that only one entry prints in the section below.

insert_statement = \
'''
INSERT OR IGNORE INTO customers
VALUES 
    ('Ryan', 'Wingate', '[email protected]'),
    ('Ryan', 'Wingate', '[email protected]')
'''

c.execute(insert_statement)

<sqlite3.Cursor at 0x104bdd030>

conn.commit()

conn.close()

Read from Table

conn = sqlite3.connect(db_path)
c = conn.cursor()

select_statement = \
'''
SELECT *
FROM customers
'''

Other alternatives to fetchall() are fetchone() and fetchmany(8).

c.execute(select_statement)
c.fetchall()

[('Ryan', 'Wingate', '[email protected]')]

conn.commit()

conn.close()