Working with Databases in Julia
Last Updated :
05 Sep, 2020
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:
Julia
using Pkg
Pkg.add( "SQLite" )
using SQLite
db = SQLite.DB( "class" )
SQLite.tables(db)
|
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
Julia
using SQLite
db = SQLite.DB( "class" )
SQLite.execute(db, "CREATE TABLE IF NOT EXISTS Student(Roll_no REAL,
Name TEXT)")
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:
Julia
using SQLite
db = SQLite.DB( "class" )
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:
Julia
using SQLite
db = SQLite.DB( "class" )
SQLite.execute(db,"CREATE TABLE IF NOT EXISTS Student(Roll_no REAL,
Name TEXT)")
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:
Julia
using SQLite
db = SQLite.DB( "class" )
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:
Julia
using SQLite
db = SQLite.DB( "class" )
SQLite.execute(db, "SELECT * from Student WHERE Name='Simon'" )
|
Output:
Database Content:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...