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

filter_none

edit
close

play_arrow

link
brightness_4
code

# 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

chevron_right


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

filter_none

edit
close

play_arrow

link
brightness_4
code

# 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)

chevron_right


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

filter_none

edit
close

play_arrow

link
brightness_4
code

# Import SQLite
using SQLite
  
# Connect to the database(class)
db = SQLite.DB("class")  
  
# Drop Table(Student)
SQLite.execute(db, "DROP TABLE Student")

chevron_right


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

filter_none

edit
close

play_arrow

link
brightness_4
code

# 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)")

chevron_right


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

filter_none

edit
close

play_arrow

link
brightness_4
code

# 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")

chevron_right


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

filter_none

edit
close

play_arrow

link
brightness_4
code

# 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'")

chevron_right


Output:

Database Content:




My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.