SQLite3 Database cheatsheet

For me SQLite can quickly mock-up a database project, it can be run within any program and with any programming language, it does not rely on any rational database management system, it can run directly from the terminal and it uses binary file to store data.

First, to install sqlite3 using brew

brew install sqlite3

then run sqlite3 to create the database
This command will create a database on a test.db file

sqlite3 test.db

if you have a test.db file sqlite3 will connect to the database file, if you do not have the file present, sqlite3 will create the file.

To show what database file is currently using

.databases

To show what tables on the current database

.tables

To setup a column view to view from select

.mode column
.header on
.width 5 20 20
SELECT * FROM websites;

After this I like to use python to write some script to do CRUD (CREATE, READ, UPDATE, DELETE)
Currently python is a programming language require the less effort to setup and run, if you interest to learn python, you can follow my other tutorial – coming up

To connect to the database file or to create one

import sqlite3
# CREATE database name webscrap.db if not exists
conn = sqlite3.connect(‘webscrap.db’)
c = conn.cursor()
print("Open Database successfully")

To create table

# CREATE table
sql_create_table = ‘CREATE TABLE `websites` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`name` TEXT NOT NULL,
`date` INTEGER NOT NULL DEFAULT (datetime(‘now’,’localtime’))
);’
c.execute(sql_create_table)
c.commit()
print("Table created succefully")

To INSERT values into table

# INSERT
sql_insert = "INSERT INTO websites (name) VALUES (‘somethingtodelete.com’);"
c.execute(sql_insert)
conn.commit()
print("Records created successfully")
print()

To UPDATE values on table

# UPDATE
sql_update = ‘UPDATE websites set name = "candoit.com" WHERE id = 7;’
c.execute(sql_update)
conn.commit()
print ("Total number of rows updated :", conn.total_changes)
print()

To DELETE row on the table

# DELETE
sql_delete = ‘DELETE FROM websites where id = 10;’
c.execute(sql_delete)
conn.commit()
print ("Total number of rows deleted :", conn.total_changes)
print()

To READ data from the table

# READ
sql = "SELECT * FROM websites;"
cursor = c.execute(sql)
for row in cursor:
print("ID: ", row[0])
print("Website: ", row[1])
print("Create Date Time: ", row[2])
print()

Remember to close the connection at the end

conn.close();