Customize Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorized as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customized advertisements based on the pages you visited previously and to analyze the effectiveness of the ad campaigns.

No cookies to display.

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();
Buy Me A Coffee
Thank you for visiting. You can now buy me a coffee!