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:
Julia
# 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
Julia
# 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:
Julia
# 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:
Julia
# 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:
Julia
# 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:
Julia
# 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: