SQLite And Python
Today I will be covering using SQLite in conjuction with Python you can find this notebook here . SQLite is pretty much the most light weight database backend around, it is easy to setup and use and dosent come with much of the overhead of other DBMS. It has a limited number of data types available, but if you can be creative in you storage requirements it can be a great solution. I recomend downloading the SQLite browser tool to help see the strucutre of what we will be creating.
Firstly lets import our modules:
import requests
import pandas as pd
import json
import sqlite3
I should mention we will be using cursors throughout the demo, think of a cursor as a worker thread, a layer between you and your database. In order to retrieve or create objects or data in your database you will need to use the cursor to handle those operations.
First lets define the name of our database, in this case I am going to use “example.db” as the database name.
conn = sqlite3.connect('example.db')
This conn will be used in conjection with a cursor to perform work in our example.db. Next lets create a table inside our database called User
c = conn.cursor()
c.execute('''CREATE TABLE USER (
User_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL);''')
conn.commit()
conn.close()
The table has been created, lets insert our first row of data.
c = conn.cursor()
c.execute("INSERT INTO USER (first_name, last_name) VALUES ('Ian','Fogelman')")
conn.commit()
Notice the conn.commit() this will be required before any operations can be saved to a database.
If we go back to our database we can see the record inside.
Lastly lets query the database table from our script and print the results. To do this we will once again leverage a cursor.
cursor = conn.execute("SELECT first_name,last_name from USER")
for row in cursor:
print("first_name : ", row[0])
print("last_name : ", row[1])