DataConnectionObject
RelationalDatabase
Import

Data Science
Tabular Processing

Show Relationships between Tables in a SQLite Database

Access and run queries on a sample SQLite database

Open in Cloud
Download Notebook

This example uses the Chinook Database, a well-known sample database from a digital media store, to demonstrate how to download it, connect to and make SQLite queries.

Specify the URL to a SQLite file:

In[1]:=

url="https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite";

Download the SQLite file to a temporary file:

In[2]:=

dbfile=URLDownload[url,FileNameJoin[{$TemporaryDirectory,"Chinook.sqlite"}]]

Out[2]=

File

/tmp/Chinook.sqlite



Connect to the database and define a query:

In[3]:=

dc=DataConnectionObject["ConnectionName""SQLite","FilePath"dbfile,"Location""SELECT Name, Composer, Milliseconds FROM Track"]

Out[3]=

DataConnectionObject

Connection name: SQLite

Location: SELECT Name, Composer, Milliseconds FROM Track



Execute the query and import the data as a Tabular object:

In[4]:=

data=Import[dc]

Out[4]=

Tabular

Row count: 3503

Column count: 3



Convert song durations to minutes:

In[5]:=

data=TransformColumns[data,"Duration"Function[N@UnitConvert[Quantity[#Milliseconds,"Miliseconds"],"Minutes"]]]

Out[5]=

Tabular

Row count: 3503

Column count: 4



Make a

Histogram

of song durations:

In[6]:=

Histogram[data"Duration"]

Out[6]=

In order to get more information about the structure of the database, create a

RelationalDatabase

object representing its schema:

In[7]:=

rd=RelationalDatabase[dbfile]

Out[7]=

Visualize the structure of the database and its foreign keys. Each vertex represents a table in the database. Edges represent common keys in the tables:

In[8]:=

Graph[Join@@(Thread[#rd[#,"ForeignKeys","ToTable"]]&/@rd["Tables"]),VertexLabels"Name"]

Out[8]=

External Links

GitHub - lerocha/chinook-database: Sample database for SQL Server, Oracle, MySQL, PostgreSQL, SQLite, DB2

Related Symbols

DataConnectionObject
RelationalDatabase
Import

Publisher Information

Contributed by: Felipe Amorim