Tutorial: Databases

Converting Tables into a DataFrame

Let's start off with defining what a DataFrame is in Quorum. A DataFrame is essentially a container of a series of rows and columns that are read from a dataset. We can visualize this as a spreadsheet or our tables inside our database. DataFrames are useful in numerous ways in Quorum as they serve as the foundation for topics around Data Science. With a DataFrame, we can manipulate datasets, use descriptive statistics, and even create charts from datasets or in our case, our database tables.

For this tutorial, we will be using three different methods on how to convert a database table into a dataframe as well as preview our data in a chart. While all these methods to convert to a DataFrame are similar, it is important to know the many different methods to convert a database table into a DataFrame for the ease of the programmer.

We will want to connect our database once again as well as create a DataFrame object. To do this, we would create a new object called DataFrame, that allows us to use helper actions that give us more ways to organize and modify our datasets. As mentioned previously, we can use a DataFrame to be used with other libraries such as our Charts library. We will go ahead and create our object and name it "frame."

use Libraries.Data.Database.Database
use Libraries.Compute.Statistics.DataFrame

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

output database:IsConnected()

DataFrame frame

Saving onto our DataFrame object, we will be using the actions: FindAsDataFrame(text tableName), FindAsDataFrame(text tableName, text expressionFilter), and FindAsDataFrame(Find objectName) with the Database object.

Method 1

For this method we will be using FindAsDataFrame(text tableName) action onto our database and save it onto a DataFrame. Once again, let's work with the grocerylist table and what we will want to do is pass in the string "grocerylist" as a parameter for FindAsDataFrame(). This action returns the database table to run the query on and will be saved onto our DataFrame object, frame.

frame = database:FindAsDataFrame("grocerylist")

Below is the entire code for our first method of saving our database as a DataFrame!

use Libraries.Data.Database.Database
use Libraries.Compute.Statistics.DataFrame

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

output database:IsConnected()

DataFrame frame

frame = database:FindAsDataFrame("grocerylist")

output frame:ToText()

Method 2

For this method we will be using FindAsDataFrame(Find objectName) action onto our database and save it onto a DataFrame. What we need to do first is create a Find object which we will name it "find." To learn more about Finds we can click this link that shows us how to properly use the Find object. Using our Find object, we will be using the action SetTable(text tableName) and pass in our "grocerylist" table. This will help us connect our database onto Quorum. Now, similar to our first method, we will be calling the action, FindAsDataFrame(Find objectName) with our database object and passing in the 'find' object. This action returns the database table to run the query on and will be saved onto our DataFrame object, frame.

Find find
find:SetTable("grocerylist")

DataFrame frame

frame = database:FindAsDataFrame(find)

Below is the entire code for our first method of saving our database as a DataFrame!

use Libraries.Data.Database.Database
use Libraries.Compute.Statistics.DataFrame
use Libraries.Data.Database.Find

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

output database:IsConnected()

 Find find
 find:SetTable("grocerylist")

DataFrame frame

frame = database:FindAsDataFrame(find)

output frame:ToText()

Method 3

For this method we will be using FindAsDataFrame(text tableName, text expressionFilter) action onto our database and save it onto a DataFrame. Once again, let's work with the grocerylist table and what we will want to do is pass in the string "grocerylist" as a parameter for FindAsDataFrame(). Now that we are also adding a filter, for the second parameter, we will want to insert a string expression, so let's pass in "foodCalories = 50" into our action. This will only put in any records where foodCalories = 50 onto our DataFrame. This action returns the database table to run the query on and will be saved onto our DataFrame object, frame.

frame = database:FindAsDataFrame("grocery_list", "foodCalories = 50")

Below is the entire code for our first method of saving our database as a DataFrame!

use Libraries.Data.Database.Database
use Libraries.Compute.Statistics.DataFrame

/*
    this program finds all rows in a database using a filter to return ONLY the rows chosen by the filter
    ex. wanting to find all the foods with a calorie size of 50
*/
Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

output database:IsConnected()

DataFrame frame

frame = database:FindAsDataFrame("grocerylist", "FoodCalories = 50")
output frame:ToText()

Next Tutorial

In the next tutorial, we will discuss deletingData, which describes how to install delete a row in a data table.