# 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()


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()