Working with Databases in Julia

There are several ways through which data handling can be performed in Julia. Julia can be connected to a lot of databases whose connectors directly connect to Database Independent Interface (DBI) packages such as MySQL, SQLite, PostgreSQL, etc. These can be used for firing queries and obtaining the required output. In this article, we’ll discuss SQLite as a reference to connect with Julia, creating, dropping, inserting, updating, and querying the table using Julia.

Connecting SQLite with Julia

To connect Julia with SQLite, a package named SQLite is imported into the current session. It needs to be ensured that the SQLite server is running. The following command can be used to do the same:

using SQLite

To connect to a specific database, SQLiteDB() function and SQLite.DB() function can be used for Julia Version 3 and Julia Version 4 respectively.

For Julia Version 3:

db = SQLiteDB("dbname.sqlite")

For Julia Version 4:

db = SQLite.DB("dbname.sqlite")

After building a successful connection, queries can be executed using query() function and SQLite.query() function for Julia Version 3 and Julia Version 4 respectively.

For Julia Version 3:

query(db, "A SQL query")

For Julia Version 4:

SQLite.query(db, "A SQL query")

Install and import SQLite Package

SQLite Package in Julia can be installed using the function Pkg.add(). For importing it, 'using' keyword can be used followed by the package name.

Example:

# Install SQLite Package
using Pkg
Pkg.add("SQLite")

# Import SQLite
using SQLite

# Connect to the database(class)
db = SQLite.DB("class")  

# Show tables in the database
SQLite.tables(db) # Empty Database with no tables

Output:
 

Create Tables in SQLite Using Julia

Tables in SQLite can be created using function execute() in Julia. This function executes the query on the provided database by taking the database connection object and the 'CREATE TABLE' SQL query to be fired as parameters.

Syntax: 

SQLite.execute(db,query)

Parameter values:

db: represents the database connection object

query: represents the SQL query to be fired on the database

# Import SQLite
using SQLite

# Connect to the database(class)
db = SQLite.DB("class")  

# Create a Table(Student)
SQLite.execute(db, "CREATE TABLE IF NOT EXISTS Student(Roll_no REAL, 
                                                       Name TEXT)")

# Show tables in the database
SQLite.tables(db)

Output:
 

Drop Tables in SQLite Using Julia

Tables in SQLite can be dropped using function execute() in Julia. This function executes the query on the provided database by taking the database connection object and the 'DROP TABLE' SQL query to be fired as parameters.

Example:

# Import SQLite
using SQLite

# Connect to the database(class)
db = SQLite.DB("class")  

# Drop Table(Student)
SQLite.execute(db, "DROP TABLE Student")

Output:

Insert into Table in SQLite Using Julia

Records can be inserted into a table in SQLite using function execute() in Julia. This function executes the query on the provided database by taking the database connection object and the 'INSERT INTO' SQL query to be fired as parameters.

Example:

# Import SQLite
using SQLite

# Connect to the database(class)
db = SQLite.DB("class")  

# Create a Table(Student)
SQLite.execute(db,"CREATE TABLE IF NOT EXISTS Student(Roll_no REAL, 
                                                      Name TEXT)")

# Insert data into Table
SQLite.execute(db, "INSERT INTO Student VALUES('Harry', 1)")

SQLite.execute(db, "INSERT INTO Student VALUES('Peter', 2)")

SQLite.execute(db, "INSERT INTO Student VALUES('Katy', 3)")

SQLite.execute(db, "INSERT INTO Student VALUES('Mia', 4)")

Output:

Database Content:

Updating a Table in SQLite Using Julia

Records can be updated into a table in SQLite using function execute() in Julia. This function executes the query on the provided database by taking the database connection object and the 'UPDATE' SQL query to be fired as parameters.

Example:

# Import SQLite
using SQLite

# Connect to the database(class)
db = SQLite.DB("class")  

# Update data present in the table(Student)
SQLite.execute(db,"UPDATE Student SET Name = 'Simon' WHERE Roll_no = 1")

Output:

Database Content:

Querying a Table in SQLite Using Julia

Queries can be fired on a table in SQLite using function execute() in Julia. This function executes the query on the provided database by taking the database connection object and the 'SELECT' SQL query to be fired as parameters.

Example:

# Import SQLite
using SQLite

# Connect to the database(class)
db = SQLite.DB("class")  

# Fire queries on the given Table(Student)
SQLite.execute(db, "SELECT * from Student WHERE Name='Simon'")

Output:

Database Content: