Creating simple Databases with SQLite

Overview

Teaching: 15 min
Exercises: 15 min

Questions

• How to use SQLite?

Objectives

• Learn how to read and write data on a SQLite database file

We will store the data on crystals.json inside a SQLite3 file-base database:

import json
import sqlite3

data=json.load(open("crystals.json"))

Creating a database

db = sqlite3.connect(':memory:')
db=sqlite3.connect("crystals.db")

Creating the Table for the 3 values, one string and two real numbers.

cursor=db.cursor()
cursor.execute('''CREATE TABLE crystals(id INTEGER PRIMARY KEY, formula TEXT, band_gap real, energy_pa real)''')
db.commit()

Inserting data:

for i in data:
    cursor.execute('''INSERT INTO crystals(formula,band_gap,energy_pa) VALUES(?,?,?)''',(i['formula'], i['band_gap'], i['energy_pa']))

db.commit()

With the code above all the entries in the original data in json are now stored in a database.

cursor.execute('''SELECT formula, band_gap, energy_pa FROM crystals''')

cursor.fetchone()
cursor.fetchall()

All the original values can be recovered:

cursor.execute('''SELECT formula, band_gap, energy_pa FROM crystals''')
all_rows = cursor.fetchall()
all_rows
for row in all_rows:
    print("{0} : {1} {2}".format(row[0],row[1],row[2]))

Key Points

• SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language.