SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process. Applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then later port the application to a production database system.
SQLite databases are stored in a file on disk (usually with a “.db” extension). If you attempt to connect to a database file that doesn’t exist, SQLite with create a new database, assign it the name you passed to the connect function and save it to your current working directory.

Typical sqlite setup and usage in Python is as follows:

  • Create a connection object
  • Initialize a database cursor
  • Construct a query for the dataset of interest
  • Pass the query string to the cursor’s execute method
  • Iterate over the cursor’s result set
import sqlite3

db     = sqlite3.connect(<filename>.db)
cursor = db.cursor()
SQL    = "SELECT * FROM SAMPLE_TABLE"
cursor.execute(SQL) 

# Iterate over cursor and print queried records.
for record in cursor: print(record)



The result will be a list of tuples, so data elements can be accessed by row or selectively by referencing components by index offset.

Creating Datebases and Tables with sqlite3

If the database file passed to the sqlite3.connect method doesn’t exist, a new database with the name specified will be created. The following example creates a database consisting of 2 tables: The first table holds closing stock prices, the second contains a mapping between ticker symbols and company names (for more information on SQLite datatypes and the resulting affinity mappings of common datatypes for other RDBMS, checkout this page from the SQLite official documentation):

"""
Creating a new database with two tables using sqlite3.

=============================
Table 1                     |
=============================
TABLENAME: `CLOSING_PRICES` |
                            |
FIELDS   : DATE   TEXT      |
           TICKER TEXT      |
           CLOSE  REAL      |
=============================

=============================
Table 2                     |
=============================
TABLENAME: `TICKER_MAPPING` |
                            |
FIELDS   : TICKER       TEXT|
           COMPANY NAME TEXT|     
=============================

"""
import sqlite3

# Create new database `sample.db`. Notice `sample.db` is now 
# listed in your working directory.
db = sqlite3.connect("sample.db")

# Initiate cursor object.
cursor = db.cursor()

# Specify the DDL to create the two tables.
tbl1_ddl = """CREATE TABLE CLOSING_PRICES (
              DATE   TEXT,
              TICKER TEXT,
              CLOSE  REAL)"""

tbl2_ddl = """CREATE TABLE TICKER_MAPPING (
              TICKER       TEXT,
              COMPANY_NAME TEXT)"""

# Call the `cursor.execute` method, passing tbl1_ddl & tbl2_ddl as arguments.
cursor.execute(tbl1_ddl)
cursor.execute(tbl2_ddl)

# IMPORTANT! Be sure to commit changes you want persisted. Without
# commiting, changes will not be saved.
db.commit()

# Close connection to `sample.db`.
db.close()


To verify that your tables have been created, run the following:

# Restablish connection to `sample.db`.
db     = sqlite3.connect('sample.db')
cursor = db.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
print(cursor.fetchall())
db.close()

Loading Data into SQLite Database Tables

The following example demonstrates two methods of loading data into SQLite tables. The first method assumes the data is already available within the current Python session. The second method assumes data is being loaded from a delimited data file. For the second example, refer to ticker_data.csv, which can be found here.

"""
==========================================================
 Method #1: Data already avialable in Python session     |
==========================================================
Insert four records into `CLOSING_PRICES` table based on the closing 
prices of AXP, GE, GS & UTX on 7.22.2016.
"""

# Reestablish connection to `sample.db` database.
db     = sqlite3.connect('sample.db')
cursor = db.cursor()


# Single records can be inserted using the `cursor.execute` method.
cursor.execute("INSERT INTO TICKER_MAPPING VALUES ('AXP',  'American Express Company')")
cursor.execute("INSERT INTO TICKER_MAPPING VALUES ('GE' ,  'General Electric Company')")
cursor.execute("INSERT INTO TICKER_MAPPING VALUES ('GS' ,  'Goldman Sachs Group Inc')")
cursor.execute("INSERT INTO TICKER_MAPPING VALUES ('UTX' , 'United Technologies Corporation')")


# We can insert several records at once if we create a list of tuples of the 
# data to insert, then call `cursor.executemany`.
closing_prices = [
    ('20160722', 'AXP',  64.28),
    ('20160722', 'GE' ,  32.06),
    ('20160722', 'GS' , 160.41),
    ('20160722', 'UTX', 105.13)
    ]


cursor.executemany(
    "INSERT INTO CLOSING_PRICES VALUES (?,?,?)", closing_prices
    )


# Not forgetting to commit changes and close connection.
db.commit()
db.close()


The (?,?,?) in cursor.executemany serve as placeholders for columns in the target table. There should be one ? for each column in the target table.

A more common scenario may be loading data from delimited data file into an SQLite database table. The syntax is similiar, with added file handling constructs:

"""
=====================================
 Method #2: Data read in from .csv  |
=====================================
Requires `ticker_data.csv` file.
"""
import sqlite3
import csv


# Reestablish connection to `sample.db` database.
db     = sqlite3.connect('sample.db')
cursor = db.cursor()


# Open `ticker_data.csv`, and create a csv.reader instance. Then call 
# `executemany` on the records read from file to load into the database.
with open('ticker_data.csv', 'r') as f:

    fcsv = csv.reader(f)

    # Read records from file into list.
    recs_to_load = [record for record in fcsv]

    # Load records into CLOSING_PROCES table.
    cursor.executemany("INSERT INTO CLOSING_PRICES VALUES (?,?,?)", recs_to_load)


# Not forgetting to commit changes and close connection.
db.commit()
db.close()

Retrieving Table Data from SQLite Databases

To retrieve SQLite database records, an iterator in the form of a database cursor is returned, which is traversed to obtain to returned dataset elements:

import sqlite3

# Reestablish connection to `sample.db` database.
db     = sqlite3.connect('sample.db')
cursor = db.cursor()


#construct a query to retrieve data from `CLOSING_PRICES`.
SQL = "SELECT * FROM CLOSING_PRICES"


# call `cursor.execute` on query string.
cursor.execute(SQL)


# `cursor` can now be iterated over.
for rec in cursor: print(rec)


# Not forgetting to commit changes and close connection.
db.commit()
db.close()


Headers need to be extracted from the cursor.description attribute:

# obtain reference to table headers.
import sqlite3

# re-establish connection to `sample.db` database.
db     = sqlite3.connect('sample.db')
cursor = db.cursor()

# Construct query to retrieve data from CLOSING_PRICES table.
SQL = "SELECT * FROM CLOSING_PRICES"

# Call `cursor.execute` on SQL.
cursor.execute(SQL)

# Capture table headers into `headers` list.
headers = [i[0] for i in cursor.description]

# Not forgetting to commit changes and close connection. 
db.commit()
db.close()

Using Bind Variables with SQLite

The following demonstrates the use of bind variables in SQLite for dynamic data retrieval:

import sqlite3

# Reestablish connection to `sample.db` database.
db     = sqlite3.connect('sample.db')
cursor = db.cursor()

# Bind variable key-value pairs.
params = {'symbol':'GE','date':'20161125'}
SQL    = "SELECT * FROM CLOSING_PRICES WHERE TICKER=:symbol AND DATE!=:date"
cursor.execute(SQL, params)

# Get headers.
headers = [i[0] for i in cursor.description]

# Read records into list iterating over cursor.
records = [record for record in cursor]

# Not forgetting to commit changes and close connection. 
db.commit()
db.close()


Final Note

At times, it can be useful to interact with SQLite databases from a graphical interface, especially as the number of database tables grows. One such tool is SQLiteStudio, a versatile SQLite IDE that includes the tools necessary to manage databases, schemas, tables and related objects. I encourage you to check it out. Until next time, happy coding!