Skip to content

Interacting with SQLite databases

Setup

collect data and populate it into a SQLite database

Using Beekeeper Studio

This is the preferred option of the author.

Database managers such as Beekeeper Studio are popular for a reason including:

  • Syntax highlighting of SQL code.
  • Autocompletion
  • Edit multiple SQL queries at the same time (or at least have multiple SQL queries open at the same time)
  • At a glance see table names, column names and types.

All of these makes query development much easier, and therefore quicker and less error prone.

Using sql2csv from csvkit


sql2csv --db sqlite:///iris.db --query "SELECT * FROM iris LIMIT 10;"



# Query a SQLite database with a query saved in an SQL text file
sql2csv example_query.sql --db sqlite:///iris.db

Using sqlite command line tool


# connect to iris.db SQLite database through command line tool
sqlite3 iris.db

Once inside the SQLite3 shell, numerous commands can be executed.


-- list of commands: https://www.tutorialspoint.com/sqlite/sqlite_commands.htm

-- see which SQLite database is connected to currently
.databases

-- see what tables
.tables

-- see what is the schema for a specific table
--.schema ?TABLE?
.schema iris

-- a query can also be entered and run against the database
"SELECT * FROM iris LIMIT 10;"

-- exit SQLite3 shell
.exit