Getting started with SQLite

Date: March 11th 2016
Last updated: March 11th 2016

This entry includes the basics to get up and running with SQLite3 using python.

# Install to linux OS (Ubuntu LTS 14.04)
sudo apt-get install sqlite3

# create test database
sqlite3 test.db
# opens if it exists
# creates it if it doesnt exist

# sqlite shell
ray@computer:~/$ sqlite3 test.db
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> #<==== waiting for user entry
...
sqlite> .exit #<==== closes sqlite shell

Now over in python import the required modules and connect to SQLite.

• connect
• cursor
• execute
• fetch

# import modules
import sqlite3 as lite

# connect to the test.db created above
con = lite.connect('test.db')
con
<sqlite3.Connection object at 0x7f72f6920f10>

# a database cursor is a control structure that enables 
#    traversal over the records in a database. 
# Cursors facilitate retrieval, addition and removal of records.
cur = con.cursor()
cur
<sqlite3.Cursor object at 0x7f72f49615e0>

# get sqlite version 
# select is an SQL statement
cur.execute('SELECT SQLITE_VERSION()')
<sqlite3.Cursor object at 0x7f72f49615e0>

cur.fetchone() #<=== only works once without assignment to a new variable
('3.8.2',)