Forem: Chukslord

Exploring MongoDB Data Structure with DbVisualizer's Nested Tree-Table View

Chukslord — Fri, 14 Jul 2023 01:33:12 +0000

Unleash the power of MongoDB's data structure with DbVisualizer's nested tree-table view - in this blog, we will tell you everything you need to know about the nested tree-table view in MongoDB and how can DbVisualizer help you explore these data structures. Have a read!

Welcome to the world of MongoDB data structure exploration with DbVisualizer's nested tree-table view! Understanding MongoDB's data structures is essential for database developers eager to expand their skills and harness the power of this popular NoSQL database.

In this tutorial, we'll guide you through exploring MongoDB's data structure using DbVisualizer's nested tree-table view. MongoDB offers a unique and flexible data structure, and DbVisualizer provides a cool feature that makes visualizing and navigating this unique data structure.

We'll start with the fundamentals of MongoDB's data model, explaining its differences and advantages compared to traditional databases. Then, we'll help you set up a connection to MongoDB in DbVisualizer and explore the nested tree-table view. You'll learn to navigate, understand document hierarchy, and identify field relationships.

By the end of this tutorial, you'll have the knowledge and skills to confidently visualize and analyze MongoDB data with DbVisualizer. So, let's dive in and start exploring MongoDB's data structure using DbVisualizer's nested tree-table view.

Prerequisites

The software and tools required for this tutorial include:

MongoDB: Follow the steps in their documentation to install MongoDB for your specific device.
DbVisualizer: a popular SQL client used by Tesla, NASA, Twitter, Apple, Uber, Netflix, and other popular companies.

It is recommended to have the latest versions of MongoDB and DbVisualizer installed to ensure compatibility and access to the latest features. Before proceeding with the tutorial, ensure that you have this software and tools set up on your machine.

Getting Started

Once you have installed MongoDB you can start the server using the following command:


brew services start mongodb-community@6.0

Navigate to the Mongo shell using the command:


mongo

Then switch to your desired database or create a new one if it doesn’t already exist using the following command:


use mydatabase

You can insert some dummy data in your database which we will be using during this tutorial using the following command:


var dummyData = [

  {

    "_id": "6141234567890123456789",

    "name": "John Doe",

    "age": 30,

    "address": {

      "street": "123 Main Street",

      "city": "New York",

      "country": "USA"

    },

    "hobbies": ["reading", "painting", "gaming"]

  },

  {

    "_id": "6141234567890123456790",

    "name": "Jane Smith",

    "age": 25,

    "address": {

      "street": "456 Elm Street",

      "city": "Los Angeles",

      "country": "USA"

    },

    "hobbies": ["photography", "traveling"]

  },

  {

    "_id": "6141234567890123456791",

    "name": "Mike Johnson",

    "age": 35,

    "address": {

      "street": "789 Oak Avenue",

      "city": "Chicago",

      "country": "USA"

    },

    "hobbies": ["hiking", "cooking", "gardening"]

  },

  {

    "_id": "6141234567890123456792",

    "name": "Emily Davis",

    "age": 28,

    "address": {

      "street": "321 Maple Lane",

      "city": "San Francisco",

      "country": "USA"

    },

    "hobbies": ["music", "yoga", "writing"]

  }

];

db.users.insertMany(dummyData);

What is the MongoDB Data Structure?

MongoDB, a widely used NoSQL database, uses a unique data structure that sets it apart from traditional relational databases. Understanding this data structure is pivotal for anyone looking to harness the power of MongoDB in their applications or data analysis tasks.

Unlike relational databases that store data in tables, MongoDB employs a document-based data model. This model is inherently flexible, allowing for the storage of diverse data types and structures in a single collection (the MongoDB equivalent of a 'table' in SQL databases).

The fundamental unit of data in MongoDB is a document, which is composed of key-value pairs. These documents are similar to JSON (JavaScript Object Notation) objects but are stored in a format called BSON (Binary JSON). BSON extends the JSON representation to include additional data types such as Date and binary data.

MongoDB Data Structure Example

Let's take a look at a simple example of a data structure in MongoDB. MongoDB is a NoSQL database that stores data in flexible, JSON-like documents called BSON (Binary JSON). Unlike traditional relational databases, MongoDB allows for dynamic and nested data structures.

Consider a collection called "users" that stores information about users. Each user document can have various fields like "name," "email," and "age." Here's an example of how a user document might look in MongoDB:


{

"_id": ObjectId("6091f7f0c541c546e088302a"),

"name": "John Doe",

"email": "johndoe@example.com",

"age": 30,

"address": {

"street": "123 Main Street",

"city": "New York",

"state": "NY",

"zip": "10001"

},

"interests": ["hiking", "reading", "cooking"]

}

In this example, each user document has a unique identifier field "_id" generated by MongoDB. Other fields like "name," "email," and "age" hold basic user information. The "address" field is a nested document containing the user's street, city, state, and zip code. Additionally, the "interests" field is an array that stores the user's hobbies or interests.

This flexible structure allows you to store and retrieve data in a way that suits your application's needs. You can easily query and manipulate these documents using MongoDB's powerful query language, making it a popular choice for handling structured and semi-structured data.

MongoDB Index Data Structure

MongoDB uses indexes to optimize query performance and enable efficient data retrieval. Indexes are created on specific fields or sets of fields within a collection, allowing quick access to relevant data. By utilizing indexes, MongoDB reduces the need for scanning the entire collection, resulting in faster query execution. However, careful consideration is needed to strike a balance between the number of indexes and their impact on write operations.

Can MongoDB Store Structured Data?

MongoDB is not limited to handling unstructured data. It can effectively store and manage structured data as well. With MongoDB's flexible document model, you can define schemas and enforce data structure consistency. Features like indexing and querying further enhance the management of structured data. MongoDB provides the scalability and flexibility to handle diverse data types, whether structured, semi-structured, or unstructured.

Advantages of MongoDB

The document-based data model in MongoDB provides several advantages over traditional databases:

It eliminates the need for complex and rigid table structures, allowing for dynamic and evolving data schemas. This flexibility enables developers to adapt their data models as application requirements change, without the need for extensive migrations.
The document-oriented approach simplifies the representation of complex relationships and hierarchies within the data. Nested documents and arrays can be effortlessly stored within a single document, allowing for rich data structures and eliminating the need for costly join operations prevalent in relational databases.
MongoDB's data model also facilitates horizontal scalability. By distributing data across multiple servers, known as sharding, MongoDB can handle massive volumes of data and high traffic loads, ensuring scalability and performance for modern applications.

In summary, MongoDB's document-based data model offers a flexible and efficient way to store and manage data. Its ability to handle diverse data types, support complex structures, and scale horizontally makes it a valuable tool for developers and data analysts seeking powerful and adaptable database solutions.

Connecting to MongoDB with DbVisualizer

To establish a connection to MongoDB using DbVisualizer, you'll need to create a user with appropriate database access. This user will be used for authentication during the connection setup with DbVisualizer.

Creating a MongoDB User

To create a user for MongoDB, follow these steps:

Connect to MongoDB: Open the MongoDB shell by running the mongo command in a terminal or command prompt. If necessary, specify the connection details such as the hostname, port, and authentication options.
Switch to the admin Database: To create a user, switch to the admin database, which is typically the database used for administrative tasks. Use the following command in the MongoDB shell:


use admin

Create the User: Use the db.createUser() method to create a new user. Provide the username and password for the user as well as any additional roles or privileges. Here's an example of creating a user with the username "myuser" and password "mypassword" with the readWrite role on a specific database:


db.createUser({

  user: "myuser",

  pwd: "mypassword",

  roles: [{ role: "readWrite", db: "mydatabase" }]

})

You can adjust the username, password, roles, and target database according to your requirements. The roles field specifies the user's permissions or roles within the specified database.

Verify User Creation: To verify that the user has been created successfully, you can use the getUsers command to list all users in the admin database:


db.getUsers()

The newly created user should be listed among the results.

By following these steps, you can create a user with the specified username, password, and roles in your MongoDB server.

Establishing a Connection with DbVisualizer

Now that we have a running MongoDB database in Kubernetes, we can connect DbVisualizer to it by following the steps below:

Go to the Connection tab. Click the "Create a Connection" button to create a new connection.

Select your database server type. For this tutorial, we will be choosing MongoDB as the driver.

In the Driver Connection tab, select MySQL and enter the following information:

Database server: localhost

Database Port: 27017

Database UserId: myuser(the user you you created earlier)

Database Password: mypassword(the password you set for the user you created earlier)

Note: If you are connecting to an online server, replace localhost with the online server host.

Click the "Connect" button to test the connection.

If you haven't updated your MongoDB driver, you will receive a prompt to do so.

Open the Driver Manager tab and update the driver to connect to your MongoDB database.

Click on “Connect” again to test your connection. If the connection is successful, you should see a message indicating that the connection was established. You can now browse the database using DbVisualizer.

DbVisualizer’s Nested Tree-Table View

The DbVisualizer nested tree-table view is a powerful feature that enhances the visualization of data structure in MongoDB. This view provides a hierarchical representation of the data, allowing you to explore the relationships between documents, fields, and subfields in a clear and intuitive manner.

The advantages of the nested tree table view in DbVisualizer include:

Clear Data Structure Representation: The nested tree-table view presents the data structure in a hierarchical format, making it easy to understand the organization and nesting of documents, fields, and subfields. This visual representation simplifies the comprehension of complex data structures, especially when dealing with deeply nested or hierarchical data.
Intuitive Navigation: With the nested tree-table view, you can effortlessly navigate through the data structure. It allows you to expand and collapse nodes at different levels, enabling you to focus on specific sections of the data for analysis or exploration. This intuitive navigation enhances efficiency and saves time when working with large and intricate data structures.
Relationship Visualization: The nested tree-table view visually represents the relationships between different elements in the data structure. You can easily identify parent-child relationships and understand how fields and subfields are related. This visualization aids in comprehending the data dependencies and assists in designing efficient queries and data analysis operations.
Improved Data Analysis: The nested tree-table view facilitates data analysis by providing a comprehensive overview of the data structure. You can analyze value distributions, identify patterns, and compare data across different levels of the hierarchy. This feature is particularly useful for gaining insights into the data and making informed decisions during data exploration and analysis tasks.

Overall, the nested tree-table view in DbVisualizer empowers users to effectively visualize and analyze the data structure in MongoDB. It simplifies the understanding of complex data relationships, enhances navigation through the data, and facilitates insightful data analysis.

Accessing the Nested Tree-table View

Now let’s access the nested tree-table view of our MongoDB database. Follow the steps below:

Explore the Database Structure: Once the connection is established, you will see the connected MongoDB database listed in the "Database" tab on the left-hand side of the DbVisualizer interface. Expand the database node to reveal the collections within the database.

Select a Collection: Choose the collection that you want to explore and visualize its data structure. Right-click on the collection and select "Open in New Tab" from the context menu.
Open the Nested Tree-Table View: In the Data Editor, you will see the documents of the collection displayed in the basic nested table view.

To access the nested tree-table view, click on the "Structure" button located at the top-right corner of the Data Editor.

This will switch the view to the nested tree-table representation.

Explore the Nested Tree-Table View: Once in the nested tree-table view, you can navigate through the data structure by expanding and collapsing nodes. Expand the nodes to reveal the nested fields and subfields. Expanding the nodes will allow you to explore the hierarchical relationships within the documents.

By following these steps, you can access the nested tree-table view in DbVisualizer and begin exploring and visualizing the MongoDB data structure. This view provides a clear representation of the document hierarchy, enabling you to delve into the nested fields and subfields for a comprehensive understanding of the data.

Exploring Document Hierarchy

After having explained how to access the nested tree-table view in DbVisualizer, it's crucial to understand how to interpret the information it presents. To better illustrate this, let's consider the hierarchical structure of an example BSON document that we created earlier:


{

  "_id": ObjectId("6141234567890123456789"),

  "name": "John Doe",

  "age": 30,

  "address": {

    "street": "123 Main Street",

    "city": "New York",

    "country": "USA"

  },

  "hobbies": ["reading", "painting", "gaming"]

}

The document consists of key-value pairs. Here's the hierarchical structure:

_id: ObjectId("6141234567890123456789")
name: "John Doe"
age: 30
address (nested field)
street: "123 Main Street"
city: "New York"
country: "USA"
hobbies: ["reading", "painting", "gaming"]

The document contains both top-level fields (_id, name, age, hobbies) and a nested field (address) with its own subfields (street, city, country). Understanding this hierarchical structure helps in visualizing the relationships between different parts of the document.

In the nested tree-table view of DbVisualizer, nested fields, and subfields are represented as hierarchical nodes. Taking the previous example, it would appear like this:

In the nested tree-table view of DbVisualizer, the address field is shown as a parent node with a hierarchical structure. By expanding the node, you can reveal its subfields (street, city, country). This visual representation enables clear visualization of the document's structure and the relationships between fields. Parent fields are displayed as nodes at higher levels, while their corresponding child fields and subfields are nested within them. You can identify these parent-child relationships and understand the data hierarchy by observing the nesting levels and indentation in the tree view.

Understanding Field Relationships and Their Importance in Data Analysis and Decision-Making

Field relationships are the connections and dependencies between different fields within your data. They play a significant role in data organization, analysis, and interpretation, ultimately contributing to effective data analysis and decision-making. Here are some key reasons why understanding field relationships is crucial:

Uncovering Data Structure: Field relationships provide a roadmap to the structure and hierarchy of your data. They reveal how different fields are related, whether as parent-child relationships or through other connections. Understanding these relationships allows you to grasp the overall organization of your data and navigate through its various components.
Enabling Querying Flexibility: Field relationships are essential for constructing powerful and flexible queries. When you understand the relationships between fields, you can query data based on specific criteria, filter results, and retrieve relevant information. This flexibility allows you to extract insights and answer complex questions by traversing related fields and exploring their connections.
Identifying Dependencies and Patterns: Field relationships help you identify dependencies and patterns within your data. By analyzing the relationships between fields, you can uncover correlations, associations, and dependencies that may not be immediately apparent. This knowledge can lead to valuable insights and inform decision-making processes.
Optimizing Data Modeling: Understanding field relationships is crucial when designing data models. By identifying the relationships between different fields, you can organize and structure your data in a way that aligns with its inherent connections. This optimization enhances data integrity, improves efficiency, and facilitates smoother data operations.

Conclusion

To wrap up, this tutorial took us on an exciting journey into the world of MongoDB data structures using DbVisualizer's nested tree-table view. We got a chance to explore the fantastic advantages of MongoDB's document-based data model and how it differs from the more traditional relational databases we're accustomed to. By setting up a connection to MongoDB in DbVisualizer and diving into the nested tree-table view, we unlocked a whole new way of visualizing our data in a fancy hierarchical format.

Let's give credit where it's due—this view blew our minds! Not only did it offer a crystal-clear representation of the data structure, but it also spoiled us with intuitive navigation and snazzy relationship visualization. It's like having a data analysis superhero by your side, making your life easier and your insights sharper.

Whether you're a seasoned database developer or a curious data analyst, you simply can't ignore the awesomeness of DbVisualizer when it comes to taming MongoDB. So, why wait? Try DbVisualizer’s nested tree-table view today, and get ready to unleash your inner data wizard. With its help, you'll conquer complex data hierarchies, discover hidden connections, and take your MongoDB data management skills to superhero levels. Trust us, it's a game-changer you don't want to miss out on!

FAQs (Frequently Asked Questions)

Q What is DbVisualizer's nested tree-table view?

DbVisualizer's nested tree-table view is a feature that allows users to visualize and navigate the data structure of MongoDB in a hierarchical format. It represents the relationships between documents, fields, and subfields, making it easier to understand and explore complex data structures.

Q How can I access the nested tree-table view in DbVisualizer?

To access the nested tree-table view in DbVisualizer, follow these steps:

Connect to your MongoDB database in DbVisualizer.
Choose the collection you want to explore and open it in a new tab.
In the Data Editor, click on the "Structure" button located at the top-right corner.
This will switch the view to the nested tree-table representation, allowing you to explore the hierarchical structure of the data.

Q What are the advantages of DbVisualizer's nested tree-table view?

The advantages of DbVisualizer's nested tree-table view include:

Clear representation of data structure: The hierarchical format simplifies the understanding of complex data structures.
Intuitive navigation: Expand and collapse nodes to focus on specific sections of the data.
Relationship visualization: Easily identify parent-child relationships and understand field relationships.
Improved data analysis: Comprehensive overview of the data structure facilitates data analysis and pattern identification.

Q How does MongoDB's data structure differ from traditional relational databases?

MongoDB uses a document-based data model instead of tables like relational databases. In MongoDB, data is stored in flexible, JSON-like documents called BSON. It allows for dynamic and nested data structures, making it more suitable for handling unstructured or semi-structured data. MongoDB's data structure offers scalability, flexibility, and efficient handling of diverse data types.

Q Why are field relationships important in data analysis and decision-making?

Field relationships help in organizing data, constructing flexible queries, identifying patterns, and optimizing data modeling. They provide insights into the structure and dependencies within the data, allowing for effective data analysis and informed decision-making. Understanding field relationships enhances data exploration, query design, and data model design.

Mastering the Trino Connection: Unleash the Power of DbVisualizer!

Chukslord — Fri, 14 Jul 2023 01:26:31 +0000

Unlock the potential of Trino and DbVisualizer in our latest blog post. Explore Trino's distributed SQL query capabilities and harness DbVisualizer's intuitive interface for data exploration, visualization, and optimization. Read now to unleash the power of the Trino-DbVisualizer connection!

In today's data-driven world, organizations face the challenge of handling massive volumes of data across various systems. To extract valuable insights, powerful tools are needed. Enter Trino - an open-source distributed SQL query engine that empowers organizations to process and query large datasets from multiple sources.

But to unleash Trino's full potential, you need a trusty sidekick like DbVisualizer. This superhero of database management and development tools offers a user-friendly interface and a complete platform for working with different databases. DbVisualizer acts as a centralized hub, effortlessly connecting you to Trino and other data stores like Hadoop, Cassandra, and MySQL.

With DbVisualizer, exploring databases, building queries, and visualizing data becomes a breeze. Its query builder tool simplifies query construction, making it easy to manipulate data visually. Moreover, DbVisualizer's data visualization powers are truly impressive, allowing you to create stunning charts, graphs, and dashboards. By connecting DbVisualizer with Trino, you seamlessly blend data from various sources into these visualizations, revealing a world of insights.

Prerequisites

Basic knowledge of databases and SQL.
Docker
DbVisualizer

What is Trino?

Trino, formerly known as PrestoSQL, is a powerful open-source distributed SQL query engine designed for large-scale data processing and analysis. It offers a unified interface to query data from various sources, including traditional databases and distributed storage systems. With its distributed architecture, Trino scales horizontally and processes queries in parallel, enabling efficient handling of massive datasets. It supports standard SQL syntax and provides advanced functions for complex data manipulation. Trino can push down query execution to data sources, reducing data movement and improving performance. Widely adopted by organizations, Trino is valued for its flexibility, speed, and ease of use, making it an indispensable tool for data analytics and real-time insights.

What is Trino SQL?

Trino SQL is a powerful language used to query data in Trino, the distributed SQL query engine. It follows the SQL standard and provides a familiar syntax for data analysis tasks. Trino SQL supports a wide range of operations, including querying, filtering, joining, aggregating, and transforming datasets. It includes advanced features such as subqueries and a rich set of functions for data manipulation. Trino SQL leverages the distributed nature of Trino for fast and scalable query execution. It supports various data sources and formats, making it versatile for heterogeneous environments. Overall, Trino SQL offers a robust and efficient solution for querying and analyzing data in Trino.

Setting Up Trino

For this tutorial, we will be running Trino locally on a docker container. Follow these steps to install Trino on your docker container:

Step 1: Pull the Trino Docker Image

The Trino project provides the "trinodb/trino" Docker image, which includes the Trino server and a default configuration. Pull the image from Docker Hub using the following command:


docker pull trinodb/trino

This command will download the latest version of the Trino Docker image.

Step 2: Run the Trino Container

Create a container from the Trino image using the following command:


docker run --name trino -d -p 8080:8080 trinodb/trino

This command creates a container named "trino" from the "trinodb/trino" image. The container runs in the background and maps the default Trino port, 8080, from inside the container to port 8080 on your workstation.

Step 3: Verify the Container

To verify that the Trino container is running, use the following command:


docker ps

This command displays all the running containers. Look for the "trino" container and ensure that it is listed with the appropriate status and port mapping.

Step 4: Wait for Trino to Start

When the Trino container starts, it might take a few moments for it to become fully ready. You can check its status using the following command:


docker logs trino

This command displays the container logs. Look for the "health: starting" status initially, and once it becomes ready, it should display "(healthy)".

Congratulations! You have successfully installed Trino on a Docker container. You can now access Trino by visiting http://localhost:8080 in your web browser and start running SQL queries against your Trino cluster.

Setting up the Trino Connection in DbVisualizer

Setting up the Trino connection in DbVisualizer is a straightforward process that allows you to unleash the power of Trino's distributed SQL query capabilities within the user-friendly environment of DbVisualizer. Here's how you can get started:

Now that we have a running Trino database in docker, we can connect DbVisualizer to it by following the steps below:

Go to the Connection tab. Click the "Create a Connection" button to create a new connection.

Select your server type. For this tutorial, we will be choosing Trino as the driver.

In the Driver Connection tab, enter the following information:

Database server: localhost

Database Port: 8080

UseId: “user_name”

Click the "Connect" button to test the connection.

If you haven't updated your Trino driver, you will receive a prompt to do so.

Open the Driver Manager tab and update the driver to connect to your Trino database.

Explore and Query Trino Data

With the Trino connection established in DbVisualizer, you are now ready to explore and query your Trino data. Utilize DbVisualizer's intuitive interface, query builder, and visualization tools to interact with Trino and extract valuable insights from your distributed datasets.

Now follow along as we walk you through the CLI capabilities of Trino as well!

Trino CLI

Trino CLI is your go-to command-line buddy for seamless interaction with Trino. The command-line interface allows interaction with Trino, providing capabilities to execute queries, manage connections, and retrieve results directly from your terminal. With its SQL prowess, you can write queries with ease, thanks to nifty features like auto-completion and syntax highlighting. Trino CLI goes the extra mile by allowing you to fine-tune your query experience through configurable session properties and optimized performance options. And guess what? It offers a plethora of output formats to jazz up your query results!

To run Trino CLI on your docker container, use the following command:


docker exec -it trino trino

Then enter your Trino SQL query in the terminal and run it to execute the query on your Trino server.

But hold on! There's an exciting alternative that takes your Trino journey to the next level. Imagine stepping into a world of graphical interfaces and advanced visualization wonders. That's where tools like DbVisualizer enter the scene. By harnessing the power of a JDBC driver, you can connect with Trino in DbVisualizer and unlock a universe of interactive exploration, query building, and mind-blowing visualizations. It's like adding a touch of magic to your Trino experience.

So, whether you're a command-line aficionado or prefer the captivating realm of graphical tools, Trino CLI and DbVisualizer offer you the best of both worlds. Get ready to embark on an exhilarating data exploration journey, fueled by the boundless potential of Trino and the seamless connectivity of DbVisualizer.

Executing Queries in DbVisualizer with Trino

DbVisualizer provides a powerful interface for writing and executing SQL queries against Trino. You can leverage its user-friendly query editor to compose SQL statements efficiently. Simply expand the Trino server tree, pick any catalog from the list, and create an SQL query commander by clicking on the play icon with a plus next to it.

You can start writing SQL queries in the SQL commander editor. A good query example is one to count the number of nations in the nation table:


select count(\*) from tpch.sf1.nation;

Click on the play button above the SQL commander to execute the query. You would get a result as in the image below:

Now we’ll visualize the queries in Trino with DbVisualizer. Follow along!

Visualizing Trino Queries with DbVisualier

By using SQL, we have the power to create a wide range of analytical queries on this table. For example, let's calculate the average length of the nation names across all regions:


SELECT

  regionkey,

  AVG(LENGTH(name)) AS avg_name_length

FROM

  tpch.sf1.nation

GROUP BY

  regionkey;

The modified query retrieves data from the nations table in Trino and calculates the average length of nation names (avg_name_length) for each region (regionkey). By grouping the results based on the regionkey column, the query provides a summary of the average name length for nations within each specific region.

Running the query above will provide you with the results seen in the table below:

You can use this statistic to create a visualization such as a line chart, bar chart or area chart. To create a visualization for this table, click on the rightmost button in the result tab toolbar.

Then select the values for the x and y axis of your chart by clicking on the select button above the chart panel. Select the avg_name_length as the x axis and the regionkey as the y axis.

Great! We have successfully created a line chart visualization of our Trino query data.

By default, the visualization displays a line chart, but don't let that limit you. Get creative and explore the various customization options available to you. You can try out options like line chart, point chart, area chart, stacked area chart, bar chart, stacked bar chart and pie chart by clicking on the chart icon above the chart panel to reveal a dropdown menu of various chart types.

Impressive, isn't it? DbVisualizer offers a range of customizable features. To explore these options, simply click on the tool button located at the top of the chart tab. From there, you have the freedom to fine-tune your charts according to your preferences. Once you've crafted the ideal chart, it's a breeze to export it as an image – just click on the document icon situated at the top of the chart tab.

Conclusion

In this tutorial, we've uncovered the power of Trino and DbVisualizer by unleashing the capabilities of distributed SQL queries for data analysis. Trino, the open-source SQL query engine, offers the muscle to handle massive data volumes across various systems.

With DbVisualizer as our trusty sidekick, we effortlessly connect to Trino and other data stores. Its user-friendly interface and comprehensive tools make exploring and querying data a breeze.

We've learned how to establish the Trino connection in DbVisualizer, executing SQL queries and retrieving results with ease. But the excitement doesn't stop there!

DbVisualizer's visualization capabilities let us create stunning charts to bring our data to life. We can customize these visualizations to suit our needs, and with a simple click, export them as image masterpieces.

By mastering the Trino connection with DbVisualizer, we can gain valuable insights and supercharge our data analysis. So, don't stop here—explore, experiment, and unlock the full potential of Trino and DbVisualizer using their documentation and blog in your data-driven journey, and until next time!

FAQ (Frequently Asked Questions)

Q: How do I install Trino on a Docker container?

To install Trino on a Docker container, use the command docker pull trinodb/trino to download the Trino Docker image. Then, create a container from the image using docker run --name trino -d -p 8080:8080 trinodb/trino. Verify the container status with docker ps and ensure it is running.

Q: How do I connect DbVisualizer to Trino?

In DbVisualizer, go to the Connection tab and click "Create a Connection." Choose Trino as the driver and enter the connection details such as localhost for the Database server and 8080 for the Database Port. Click "Connect" to establish the connection.

Q: How can I execute SQL queries in DbVisualizer with Trino?

To execute SQL queries in DbVisualizer with Trino, expand the Trino server tree, create an SQL commander, and write your SQL query in the editor. Click the play button to execute the query and view the results.

Q: How can I visualize Trino queries using DbVisualizer?

DbVisualizer allows you to visualize Trino queries by creating charts. After executing a query, click on the rightmost button in the result tab toolbar to show the chart panel. Select the desired values for the x and y axes, and customize the chart type and appearance as needed.

Q: Can I export the charts created in DbVisualizer as images?

Yes, you can export charts created in DbVisualizer as images. In the chart tab, click on the document icon located at the top to export the chart as an image file.

Automating Database Operations with Ansible and DbVisualizer

Chukslord — Fri, 14 Jul 2023 01:18:11 +0000

Streamline and automate your database tasks with Ansible and DbVisualizer

In this tutorial, we will explore how to automate database operations using Ansible and DbVisualizer. Ansible is a powerful automation tool that allows you to define and manage infrastructure as code, while DbVisualizer is a feature-rich database management tool that enables you to interact with various databases. By combining these two tools, Ansible automates repetitive database tasks and streamlines your workflows, while DbVisualizer helps you visualize your database during the process. We will cover the installation and configuration of Ansible and DbVisualizer and demonstrate how to automate common database operations using Ansible playbooks.

Prerequisites

Basic knowledge of databases and SQL.
Familiarity with Ansible concepts and syntax.
Access to a target database server.
Python 3.7 or later
PyMySQL installed using pip install PyMySQL
DbVisualizer

We’ll start from telling you a couple of things about Ansible, and then tell you how to automate your database operations. Follow along!

How Does Ansible Automate Database Commands?

Ansible makes automating database commands a breeze. It provides special modules designed to interact with different database systems, handling all the complicated stuff behind the scenes. With Ansible, you can effortlessly execute queries, manage users, create tables, and more. Simply define your tasks in Ansible playbooks using these modules, and watch as your database workflows become smooth and automated. Ansible's magical touch ensures that your automation processes are consistent and repeatable, saving you time and sparing you from pesky errors. So sit back, relax, and let Ansible take care of your database tasks with ease!

Getting Started with Ansible

Now that we have our prerequisites out of the way let’s get started with working with Ansible. Follow the steps below:

Step 1: Installing Ansible

Install Ansible on your machine by using the pip install ansible command in your terminal.
Verify the installation by running the ansible --version command.

Step 2: Configuring Ansible

Once you have successfully installed Ansible, follow the steps below to start automating:

Create a new directory where our Ansible magic will happen. Inside this directory, create an inventory file (inventory.ini). This file is like Ansible's map, guiding it to the target database server. For example:


db_server ansible_host=your_server_ip ansible_user=your_username ansible_password=your_password

Replace the placeholders in the code above with the information for the database you want Ansible to access.

Create a playbook file (database.yml) to define your automation tasks. This is Ansible's spellbook where we list all our automation tasks.


---

- name: Automate Database Operations

  hosts: database

  tasks:

    # list you automation tasks here

Automating Database Operations

With Ansible ready, let's start automating some database operations. Download DbVisualizer, then follow the steps below:

Step 1:Connect DbVisualizer to the MySQL Server

Open DbVisualizer and follow the steps below:

Go to the Connection tab. Click the "Create a Connection" button to create a new connection.

[Creating a Database Server Connection in DbVisualizer]

Select your database server type. For this tutorial, we will be choosing MySQL 8(Connector/J) as the driver.

[Choosing the Database Driver in DbVisualizer]

In the Driver Connection tab, select MySQL and enter the following information:

Database server: localhost

Database Port: 3306

Database UserId: root

Database Password: (the password you set in the MySQL deployment YAML file)

[Connection Details for the MySQL Database Server in DbVisualizer]

Step 2: Create a Database

First, let's create a playbook to automate the creation of a new database. Ansible has a module called mysql_db that we can use to create and manage MySQL databases.

Create a new playbook named create_database.yml and add the following content:


---

- name: Create database

  hosts: your_target_server

  become: yes

  vars:

    db_name: your_db_name

  tasks:

    - name: Create database

      mysql_db:

        name: "{{ db_name }}"

        state: present

This playbook introduces the vars keyword which we use to define variables that we can use within our playbook. In this case, we're creating a variable db_name to store the name of the database we want to create. Remember to replace the database information with real ones for your database in the code above.

To execute this playbook, run ansible-playbook create_database.yml in your terminal. This will instruct Ansible to create a new database on the target server.

[Running the create_databse.yml Playbook]

You should now be able to view your new database on DbVisualizer.

[Browsing Through our new Database on DbVisualizer]

Step 3: Create a Database User

Now, let's create a playbook that creates a new user in our database and gives them all privileges. We'll use the mysql_user module for this.

Create a new playbook named create_user.yml with the following content:


---

- name: Create MySQL user

  hosts: your_target_server

  become: yes

  vars:

    db_name: your_db_name

    db_user: your_db_user

    db_pass: your_db_password

  tasks:

    - name: Create MySQL user

      mysql_user:

        name: "{{ db_user }}"

        password: "{{ db_pass }}"

        priv: "{{ db_name }}.\*:ALL"

        state: present

In this playbook, we're using the mysql_user module to create a new user, assign them a password, and give them all privileges to the database we created in the previous step.

To execute this playbook, run ansible-playbook create_user.yml in your terminal.

[Running the create_user.yml Playbook]

You should now be able to access your database using your newly created user.

Step 4: Delete a Database

Finally, let's create a playbook that deletes a database. We'll use the mysql_db module again, but this time we'll set the state parameter to absent.

Create a new playbook named delete_database.yml and add the following content:


---

- name: Delete database

  hosts: your_target_server

  become: yes

  vars:

    db_name: your_db_name

  tasks:

    - name: Delete database

      mysql_db:

        name: "{{ db_name }}"

        state: absent

To execute this playbook, run ansible-playbook delete_database.yml in your terminal. This will instruct Ansible to delete the database on the target server.

Remember, always replace your_target_server, your_db_name, your_db_user, and your_db_password with actual values relevant to your setup. And don't forget to make sure your Ansible control node can SSH into your target server.

[Running the delete_database.yml Playbook]

By following these steps, you've automated the process of creating, managing users and deleting a MySQL database. But don't stop here. Ansible is highly flexible and powerful, so take the time to explore more complex tasks you can automate.

Handling Variables and Templating

To make your Ansible playbooks more flexible and reusable, you can utilize variables to parameterize your tasks. Variables allow you to store and manage values that can be used throughout your playbook. You can define variables directly in your playbook or create separate variable files.

Defining variables in your playbook:

You can create separate variable files, such as vars.yml, to store and manage your variables. This allows for easier management and reuse of variables across multiple playbooks. Here's an example:


---

- name: Example playbook

  hosts: all

  vars:

    database_name: mydatabase

    database_user: myuser

    database_password: mypassword

  tasks:

    - name: Example task

      mysql_db:

        name: "{{ database_name }}"

        state: present

        login_user: "{{ database_user }}"

        login_password: "{{ database_password }}"

In this example, we define variables database_name, database_user, and database_password directly in the playbook. These variables are then used within the mysql_db task to create a database with the specified name and credentials.

Using separate variable files:


---

# vars.yml

database_name: mydatabase

database_user: myuser

database_password: mypassword

Then in your playbook:


---

- name: Example playbook

  hosts: all

  vars_files:

    - vars.yml

  tasks:

    - name: Example task

      mysql_db:

        name: "{{ database_name }}"

        state: present

        login_user: "{{ database_user }}"

        login_password: "{{ database_password }}"

In this case, the variables are stored in a separate vars.yml file, and the vars_files keyword is used to include the variables in the playbook.

Advanced Automation Features

Ansible provides various advanced features that can enhance your automation efforts:

1. Using Conditionals

You can use conditionals to control the execution of tasks based on specific conditions. For example:


- name: Example conditional task

  command: /path/to/command

  when: ansible_distribution == 'Ubuntu'

This task will only be executed if the remote host's distribution is Ubuntu.

2. Using Loops

Utilize loops to iterate over a list of items or dynamically generated data. For example:


- name: Example loop task

  yum:

    name: "{{ item }}"

    state: latest

  with_items:

    - package1

    - package2

    - package3

This task will iterate over the list of packages and ensure they are installed.

3. Using Handlers

Handlers allow you to define tasks that are triggered by specific events. For example:


- name: Example handler task

  command: /path/to/restart_service

  notify: Restart Service


- name: Restart Service

  service:

    name: myservice

    state: restarted

The handler task will be triggered only if the task notifies it. In this case, it will restart the specified service.

4. Using Roles

Roles provide a way to organize and reuse common tasks and configurations across different playbooks. You can create a role with its own directory structure and defined tasks, variables, and templates. Here's an example directory structure for a role named webserver:


webserver/

├── tasks/

│   └── main.yml

├── templates/

│   └── nginx.conf.j2

├──

   ├── vars/

   │   └── main.yml

   └── meta/

       └── main.yml

The tasks/main.yml file would contain the main tasks for the role, while vars/main.yml would contain any role-specific variables. The templates/nginx.conf.j2 file would contain a Jinja2 template for an Nginx configuration file, which could be used by a task in the role. The meta/main.yml file would contain metadata about the role, such as its name and dependencies.

Once you've created a role, you can include it in your playbook like this:


---

- name: Example playbook

  hosts: webservers

  roles:

    - webserver

This will execute all of the tasks defined in the tasks/main.yml file of the webserver role on the webservers group of hosts.

With these advanced features, you can customize your automation further and create more complex and powerful playbooks and roles.

Conclusion

This tutorial has showcased the remarkable synergy between Ansible and DbVisualizer in automating database operations. By following the step-by-step instructions, you successfully installed, configured, and utilized both Ansible and DbVisualizer to automate tasks and visualize results. Leveraging Ansible's flexibility and DbVisualizer's intuitive interface, you can now save significant time and effort while ensuring the consistency and reliability of your database workflows.

DbVisualizer proved to be an indispensable companion, providing powerful data analysis and interpretation capabilities. By combining the automation prowess of Ansible with the visualization capabilities of DbVisualizer, you can unlock a new level of efficiency and effectiveness in your database automation journey. Don't hesitate to try DbVisualizertoday and experience firsthand the immense benefits it brings to automating and managing your databases.

FAQs (Frequently Asked Questions)

Q: What is Ansible?

Ansible is a powerful automation tool that allows you to define and manage infrastructure as code. It simplifies the process of automating repetitive tasks, such as provisioning servers, configuring software, and deploying applications. Ansible uses a simple and human-readable language called YAML to define its automation playbooks.

Q: How does Ansible automate database operations?

Ansible provides specialized modules for interacting with various database systems. These modules handle the complexities of executing database commands, managing users, creating tables, and more. By defining tasks in Ansible playbooks using these modules, you can automate common database operations and streamline your workflows.

Q: What are the benefits of automating database operations with Ansible and DbVisualizer?

Automating database operations with Ansible and DbVisualizer offers several benefits:

Time and effort savings: Automation eliminates the need for manual execution of repetitive tasks, saving time and effort.
Consistency and reliability: Ansible ensures consistent and repeatable execution of database operations, reducing the risk of errors.
Streamlined workflows: Automation allows for smoother and more efficient database workflows.
Visual data analysis: DbVisualizer provides powerful visualization capabilities, making it easier to analyze and interpret database results.
Scalability: Automation enables you to handle larger databases and scale your operations more effectively.

How To Build a Soccer Data Web Scraper with Django and Fauna

Chukslord — Fri, 17 Sep 2021 16:27:25 +0000

In this tutorial, we will be extracting data from the top 5 soccer leagues globally: the EPL, La Liga, Serie A, Bundesliga, and Ligue 1. However, to obtain this data, we will be extracting it from an official sports website (understat.com). We will then learn how to store and manage these data with Fauna.

Prerequisites

To fully understand this tutorial, you are required to have the following in place:

Python 3.7 or newer.
Basic understanding of Fauna.
Basic knowledge of Django.
A text editor.

With the above prerequisites out of the way, we can now begin building our web scraper application.

Introduction to Fauna

Fauna is a client-side serverless database that uses GraphQL and the Fauna Query Language (FQL) to support various data types and relational databases in a serverless API. You can learn more about Fauna in their official documentation here. If this is the first time you have heard about Fauna, visit my previous article here for a brief introduction.

Installing the required libraries

The libraries required for this tutorial are as follows:

numpy — fundamental package for scientific computing with Python
pandas — library providing high-performance, easy-to-use data structures, and data analysis tools
requests — is the only Non-GMO HTTP library for Python, safe for human consumption. (love this line from official docs :D)
BeautifulSoup — a python library for pulling data out of HTML and XML files.

To install the libraries required for this tutorial, run the following commands below:


pip install numpy

pip install pandas

pip install requests 

pip install bs4

Building the Python Web Scraper

Now that we have all the required libraries installed, let’s get to building our web scraper.

Importing the Python libraries


import numpy as np

import pandas as pd

import requests

from bs4 import BeautifulSoup

import json

Carrying out Site Research

The first step in any web scraping project is researching the web page you want to scrape and learn how it works. That is critical to finding where to get the data from the site; therefore, this is where we’ll begin.

We can see on the home page that the site has data for six European leagues. However, we will be extracting data for just the top 5 leagues(teams excluding RFPL).

We can also notice that data on the site starts from 2014/2015 to 2020/2021. Let’s create variables to handle only the information we require.


# create urls for all seasons of all leagues

base_url = 'https://understat.com/league'

leagues = ['La_liga', 'EPL', 'Bundesliga', 'Serie_A', 'Ligue_1']

seasons = ['2016', '2017', '2018', '2019', '2020']

The next step is to figure out where the data on the web page is stored. To do so, open Developer Tools in Chrome, navigate to the Network tab, locate the data file (in this example, 2018), and select the “Response” tab. After executing requests, this is what we'll get. get(URL)

After looking through the web page's content, we discovered that the data is saved beneath the "script" element in the teamsData variable and is JSON encoded. As a result, we'll need to track down this tag, extract JSON from it, and convert it to a Python-readable data structure.

Decoding the JSON data


  season_data = dict()

  for season in seasons:

    url = base_url+'/'+league+'/'+season

    res = requests.get(url)

    soup = BeautifulSoup(res.content, "lxml")

    # Based on the structure of the webpage, I found that data is in the JSON variable, under <script> tags

    scripts = soup.find_all('script')

    string_with_json_obj = ''

    # Find data for teams

    for el in scripts:

        if 'teamsData' in str(el):

          string_with_json_obj = str(el).strip()

    # strip unnecessary symbols and get only JSON data

    ind_start = string_with_json_obj.index("('")+2

    ind_end = string_with_json_obj.index("')")

    json_data = string_with_json_obj[ind_start:ind_end]

    json_data = json_data.encode('utf8').decode('unicode_escape')

   #print(json_data)

After running the python code above, you should get data that we’ve cleaned up.

Understanding the Data

When we start looking at the data, we realise it's a dictionary of dictionaries with three keys: id, title, and history. Ids are also used as keys in the dictionary's initial layer.

Therefore, we can deduce that history has information on every match a team has played in its league (League Cup or Champions League games are omitted).

After reviewing the first layer dictionary, we can begin to compile a list of team names.


# Get teams and their relevant ids and put them into separate dictionary

teams = {}

for id in data.keys():

    teams[id] = data[id]['title']

We see that column names frequently appear; therefore, we put them in a separate list. Also, look at how the sample values appear.


columns = []

# Check the sample of values per each column

values = []

for id in data.keys():

  columns = list(data[id]['history'][0].keys())

  values = list(data[id]['history'][0].values())

  break

Now let’s get data for all teams. Uncomment the print statement in the code below to print the data to your console.


# Getting data for all teams

    dataframes = {}

    for id, team in teams.items():

      teams_data = []

      for row in data[id]['history']:

        teams_data.append(list(row.values()))

      df = pd.DataFrame(teams_data, columns=columns)

      dataframes[team] = df

      # print('Added data for {}.'.format(team))

After you have completed this code, we will have a dictionary of DataFrames with the key being the team's name and the value being the DataFrame containing all of the team's games.

Manipulating Data to Table

When we look at the DataFrame content, we can see that metrics like PPDA and OPPDA (ppda and ppda allowed) are represented as total sums of attacking/defensive actions.

However, they are shown as coefficients in the original table. Let's clean that up.


for team, df in dataframes.items():

      dataframes[team]['ppda_coef'] = dataframes[team]['ppda'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)

      dataframes[team]['oppda_coef'] = dataframes[team]['ppda_allowed'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)

We now have all of our numbers, but for every game. The totals for the team are what we require. Let's look at the columns we need to add up. To do so, we returned to the original table on the website and discovered that all measures should be added together, with only PPDA and OPPDA remaining as means in the end. First, let’s define the columns we need to sum and mean.


cols_to_sum = ['xG', 'xGA', 'npxG', 'npxGA', 'deep', 'deep_allowed', 'scored', 'missed', 'xpts', 'wins', 'draws', 'loses', 'pts', 'npxGD']

cols_to_mean = ['ppda_coef', 'oppda_coef']

Finally, let’s calculate the totals and means.


for team, df in dataframes.items():

      sum_data = pd.DataFrame(df[cols_to_sum].sum()).transpose()

      mean_data = pd.DataFrame(df[cols_to_mean].mean()).transpose()

      final_df = sum_data.join(mean_data)

      final_df['team'] = team

      final_df['matches'] = len(df)

      frames.append(final_df)

    full_stat = pd.concat(frames)

    full_stat = full_stat[['team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'npxG', 'xGA', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts']]

    full_stat.sort_values('pts', ascending=False, inplace=True)

    full_stat.reset_index(inplace=True, drop=True)

    full_stat['position'] = range(1,len(full_stat)+1)

    full_stat['xG_diff'] = full_stat['xG'] - full_stat['scored']

    full_stat['xGA_diff'] = full_stat['xGA'] - full_stat['missed']

    full_stat['xpts_diff'] = full_stat['xpts'] - full_stat['pts']

    cols_to_int = ['wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'deep', 'deep_allowed']

    full_stat[cols_to_int] = full_stat[cols_to_int].astype(int)

In the code above, we reordered columns for better readability, sorted rows based on points, reset the index, and added column ‘position’.

We also added the differences between the expected metrics and real metrics.

Lastly, we converted the floats to integers where appropriate.

Beautifying the Final Output of the Dataframe

Finally, let’s beautify our data to become similar to the site data in the image above. To do this, run the python code below.


col_order = ['position', 'team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'xG_diff', 'npxG', 'xGA', 'xGA_diff', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts', 'xpts_diff']

 full_stat = full_stat[col_order]

 full_stat = full_stat.set_index('position')

 # print(full_stat.head(20))

To print a part of the beautified data, uncomment the print statement in the code above.

Compiling the Final Code

To get all the data, we need to loop through all the leagues and seasons then manipulate it to be exportable as a CSV file.


import numpy as np

import pandas as pd

import requests

from bs4 import BeautifulSoup

import json

# create urls for all seasons of all leagues

base_url = 'https://understat.com/league'

leagues = ['La_liga', 'EPL', 'Bundesliga', 'Serie_A', 'Ligue_1']

seasons = ['2016', '2017', '2018', '2019', '2020']

full_data = dict()

for league in leagues:

  season_data = dict()

  for season in seasons:

    url = base_url+'/'+league+'/'+season

    res = requests.get(url)

    soup = BeautifulSoup(res.content, "lxml")

    # Based on the structure of the webpage, I found that data is in the JSON variable, under <script> tags

    scripts = soup.find_all('script')

    string_with_json_obj = ''

    # Find data for teams

    for el in scripts:

        if 'teamsData' in str(el):

          string_with_json_obj = str(el).strip()

    # print(string_with_json_obj)

    # strip unnecessary symbols and get only JSON data

    ind_start = string_with_json_obj.index("('")+2

    ind_end = string_with_json_obj.index("')")

    json_data = string_with_json_obj[ind_start:ind_end]

    json_data = json_data.encode('utf8').decode('unicode_escape')

    # convert JSON data into Python dictionary

    data = json.loads(json_data)

    # Get teams and their relevant ids and put them into separate dictionary

    teams = {}

    for id in data.keys():

      teams[id] = data[id]['title']

    # EDA to get a feeling of how the JSON is structured

    # Column names are all the same, so we just use first element

    columns = []

    # Check the sample of values per each column

    values = []

    for id in data.keys():

      columns = list(data[id]['history'][0].keys())

      values = list(data[id]['history'][0].values())

      break

    # Getting data for all teams

    dataframes = {}

    for id, team in teams.items():

      teams_data = []

      for row in data[id]['history']:

        teams_data.append(list(row.values()))

      df = pd.DataFrame(teams_data, columns=columns)

      dataframes[team] = df

      # print('Added data for {}.'.format(team))

    for team, df in dataframes.items():

      dataframes[team]['ppda_coef'] = dataframes[team]['ppda'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)

      dataframes[team]['oppda_coef'] = dataframes[team]['ppda_allowed'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)

    cols_to_sum = ['xG', 'xGA', 'npxG', 'npxGA', 'deep', 'deep_allowed', 'scored', 'missed', 'xpts', 'wins', 'draws', 'loses', 'pts', 'npxGD']

    cols_to_mean = ['ppda_coef', 'oppda_coef']

    frames = []

    for team, df in dataframes.items():

      sum_data = pd.DataFrame(df[cols_to_sum].sum()).transpose()

      mean_data = pd.DataFrame(df[cols_to_mean].mean()).transpose()

      final_df = sum_data.join(mean_data)

      final_df['team'] = team

      final_df['matches'] = len(df)

      frames.append(final_df)

    full_stat = pd.concat(frames)

    full_stat = full_stat[['team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'npxG', 'xGA', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts']]

    full_stat.sort_values('pts', ascending=False, inplace=True)

    full_stat.reset_index(inplace=True, drop=True)

    full_stat['position'] = range(1,len(full_stat)+1)

    full_stat['xG_diff'] = full_stat['xG'] - full_stat['scored']

    full_stat['xGA_diff'] = full_stat['xGA'] - full_stat['missed']

    full_stat['xpts_diff'] = full_stat['xpts'] - full_stat['pts']

    cols_to_int = ['wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'deep', 'deep_allowed']

    full_stat[cols_to_int] = full_stat[cols_to_int].astype(int)

    col_order = [‘league’,’year’,'position', 'team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'xG_diff', 'npxG', 'xGA', 'xGA_diff', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts', 'xpts_diff']

    full_stat = full_stat[col_order]

    full_stat = full_stat.set_index('position')

    # print(full_stat.head(20))

    season_data[season] = full_stat

  df_season = pd.concat(season_data)

  full_data[league] = df_season

To analyse our data with Django, we need to export the data to a CSV file. To do this, copy and paste the code below.


data = pd.concat(full_data)

data.to_csv('understat.com.csv')

Setting up the Fauna Database

You must first establish an account before you can build a Fauna database. You may create another after you've created one by clicking the CREATE DATABASE button on the dashboard.

Creating the Database Collections

Create one collection in your database, the SOCCER_DATA collection. The SOCCER_DATA collection will store the data we extract to our CSV file in the database. For the History days and TTL, use the default values provided and save.

Creating the Collection Indexes

Create two indexes for your collections; check_name and get_all_teams_data. The get_all_teams_data index will allow you to scroll through data in the SOCCER_DATA collection. It has one term, which is the matches field. This term will enable you to match data with the matches for easy querying.

The check_name will allow you to scroll through data in the SOCCER_DATA collection. This index will enable matching with the team field to perform queries.

Generating the Database API Key

To generate an API key, go to the security tab on the left side of your dashboard, then click New Key to create a key. You will then be required to provide a database to connect to the key. After providing the information required, click the SAVE button.

After saving your key, Fauna will present you with your secret key in the image above (hidden here for privacy). Make sure to copy your secret key from the dashboard and save it somewhere you can easily retrieve it for later use.

Setting Up the Django Application

The Django application will collect data from the CSV file, store it in the Fauna database and display it on the homepage user interface. To create a Django project, run the commands below:


django-admin startproject FAUNA_WEBSCRAPER

Django-admin startapp APP

After creating the Django project, we need to create another application before diving into the main code logic. Follow the steps below to set up your Django application.

Add APP to installed apps in the settings.py file as seen in the code below:


INSTALLED_APPS = [

    'django.contrib.admin',

    'django.contrib.auth',

    'django.contrib.contenttypes',

    'django.contrib.sessions',

    'django.contrib.messages',

    'django.contrib.staticfiles',

    'APP',

]

Update the project urls.py file with python code below:


from django.contrib import admin

from django.urls import path, include

from django.conf import settings

from django.contrib.staticfiles.urls import staticfiles_urlpatterns

from django.contrib.staticfiles.urls import static

urlpatterns = [

    path('admin/', admin.site.urls),

    path('', include("APP.urls")),

]

urlpatterns += staticfiles_urlpatterns()

urlpatterns += static(settings.MEDIA_URL, document_root=settings.MEDIA_ROOT)

Create a new urls.py file in the APP folder, then paste the python code below.


from django.conf import settings

from django.conf.urls.static import static

from django.urls import path, include

from . import views

app_name = "App"

urlpatterns = [

    path("", views.index, name="index"),

]

Create two new folders in your APP directory to handle the application user interface(i.e. templates and static folder).
Create two new files in the templates folder, an HTML file and a CSS file. The HTML should be index.html, and the CSS file should be style.css. Copy and paste the code below in their respective files.


{% load static %}

<!DOCTYPE html>

<html>

<head>

  <link href="{% static 'style.css' %}" rel="stylesheet">

  <h1>Fauna Sport Aggregator</h1>

</head>

<body>

  <table class="content-table">

    <thead>

      <tr>

        <th>Position</th>

        <th>Team</th>

        <th>Matches</th>

        <th>Wins</th>

        <th>Draws</th>

        <th>Loses</th>

        <th>Points</th>

        </tr>

        </thead>

        <tbody>

        {% for team in team_list_data %}

          <tr>

            <td>{{team.position}}</td>

            <td>{{team.team}}</td>

            <td>{{team.matches}}</td>

            <td>{{team.wins}}</td>

            <td>{{team.draws}}</td>

            <td>{{team.loses}}</td>

            <td>{{team.points}}</td>

            </tr>

        {% endfor %}

        </tbody>

         </table>

       </body>

    </html>

#style.css
h1 {

    color: #0000A0;

    font-family: sans-serif;

   }

   .content-table {

    border-collapse: collapse;

      margin: 25px 0;

        font-size: 1.0em;

        font-weight: bold;

        font-family: sans-serif;

         min-width: 400px;

          border-radius: 5px 5px 0 0;

            overflow-hidden: hidden;

          box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);

        }

   

        .content-table thead tr {

          background-color: #0000A0;

          color: #FFFFFF;

          text-align: left;

          font-weight: bold;

        }

   

        .content-table th,

        .content-table td {

        padding: 15px 25px;

      }

   

      .content-table tbody tr {

        border-bottom: 1px solid #dddddd;

     }

   

      .content-table tbody tr:nth-of-type(even) {

        background-color: #f3f3f3;

        color: #0000A0;

        font-weight: bold

      }

   

      .content-table tbody tr:last-of-type {

        border-bottom: 2px solid #0000A0;

      }

   

        .content-table tbody tr:hover {

          background-color: #D3D3D3;

        }

Copy the CSV file generated earlier to the APP folder.
Copy and paste the python code below in the views.py file.


from django.shortcuts import render

import csv

from faunadb import query as q

from faunadb.objects import Ref

from faunadb.client import FaunaClient

# Create your views here.

client = FaunaClient(secret="fauna_secret_key", domain="db.eu.fauna.com",

  # NOTE: Use the correct domain for your database's Region Group.

  port=443,

  scheme="https")

def index(request):

team_data_list = []

headers_list = []

    paginate_data=client.query(q.paginate(q.match(q.index("get_all_teams_data"), "38")))

    all_data = [

        q.get(

            q.ref(q.collection("SOCCER_DATA"), paginate.id())

        ) for paginate in paginate_data["data"]

    ]

    team_list_data = [i["data"] for i in  client.query(all_data)]

    context={"team_list_data":client.query(team_list_data)}

    index = 0

    with open("understat.com.csv", 'r') as data:

        for line in csv.reader(data):

            index += 1

            if index > 1:

                team_dict = {}

                for i, elem in enumerate(headers_list):

                    team_dict[elem] = line[i]

                team_data_list.append(team_dict)

            else:

                headers_list = list(line)

    for record in team_data_list[:20]:

        try:

            team_data_check = client.query(q.get(q.match(q.index('check_name'),record["team"])))

        except:

            team_record = client.query(q.create(q.collection("SOCCER_DATA"), {

                    "data": {

                        "matches":record["matches"],

                        "position":record["position"],

                        "team":record["team"],

                        "wins": record["wins"],

                        "draws": record["draws"],

                        "loses":record["loses"],

                        "points":record["pts"]

                    }

                }))

    return render(request,"index.html",context)

Run the application with the following commands. 9.

python manage.py migrate

python manage.py runserver

After following the steps listed above, your SOCCER_DATA collection should be like the one in the image below.

Go to http://127:0.0.1.8000 to see your Django application. Your homepage should be similar to the one in the image below.

The Views File.

This file is where we built the logic for the backend. We started by importing the required modules as seen in the python code below:


from django.shortcuts import render

import csv

from faunadb import query as q

from faunadb.objects import Ref

from faunadb.client import FaunaClient

Next, we initialised the Fauna client using the secret key we generated earlier.


client = FaunaClient(secret="fnAETH3xELAAxp99WYA-8_xMLqFM1uMTfAwZYmZO", domain="db.eu.fauna.com",

  # NOTE: Use the correct domain for your database's Region Group.

  port=443,

  scheme="https")

We then needed to extract the data from our excel sheet before saving it into our SOCCER_DATA collection. We did this by reading and saving the data in the CSV file to a list variable team_data_list in our program.


team_data_list = []

headers_list = []

index = 0

    with open("understat.com.csv", 'r') as data:

        for line in csv.reader(data):

            index += 1

            if index > 1:

                team_dict = {}

                for i, elem in enumerate(headers_list):

                    team_dict[elem] = line[i]

                team_data_list.append(team_dict)

            else:

                headers_list = list(line)

Next, we needed to extract data from our list team_data_list variable, then stored them in the SOCCER_DATA collection. To do this, we iterated every data in the list and created a request with the Fauna client to save the data passed in the dictionary we defined to the collection. We only saved the first 20 data from the list for this tutorial, so we don’t exceed our free account rate limit.


for record in team_data_list[:20]:

        try:

            team_data_check = client.query(q.get(q.match(q.index('check_name'),record["team"])))

        except:

            team_record = client.query(q.create(q.collection("SOCCER_DATA"), {

                    "data": {

                        "matches":record["matches"],

                        "position":record["position"],

                        "team":record["team"],

                        "wins": record["wins"],

                        "draws": record["draws"],

                        "loses":record["loses"],

                        "points":record["pts"]

                    }

                }))

Next, we queried the data in the collection using the get_all_team_data1 Fauna index to match data for all teams with 38 matches. We paginated the queried data using Fauna’s paginate method, then created a list variable called team_list_data from it. Finally, we passed the list’s data to the front end user interface for display on a table.

Conclusion

In this article, we built a python web scraper to extract soccer data from a website. We also learned how to utilise this data in a Django application with Fauna's serverless database. We saw how easy it is to integrate Fauna into a Python application and got the chance to explore some of its core features and functionalities.

If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.

Written in connection with the Write with Fauna Program.

Building A Tweets Heat Map Charts with Tweepy, Django and Arctype

Chukslord — Mon, 05 Jul 2021 00:59:57 +0000

Have you ever wanted to track your tweet activity on Twitter? Then this article is for you. I love how Django’s ORM makes it easy to model a database and modify it with views (i.e., the app’s functional back-end).

In this tutorial, we will be learning how to manage tweet activity using the official Twitter API and generate a heat map from tweets using Arctype. To achieve this, we will be tracking some very popular celebrities’ tweets to create a heatmap like the one in the image above.

Prerequisites

To fully understand this tutorial, you should have the following:

Python 3.7 or newer.
Arctype
Basic understanding of SQL.
Basic understanding of Django.
A Twitter developer account.
A text editor.

Setting up the Twitter API

To make use of the Twitter official API, you need to set up your Twitter developer account. Go to https://developer.twitter.com and signup if you haven’t. To sign up, you will need to provide detailed information on how you want to utilize your access to the Twitter API. After providing the information, you will need to wait for Twitter to verify and enable your account.

When your account is verified, go to your developer dashboard, as seen in the image above.

At this point, you need to create a project which can house multiple applications. First, click on create project, then give your project a name, use case, and a description.

Now you need to create an application. To do this, click on create a new app and provide a name for your application.

After creating your Twitter application, you will see a page with your application keys, as seen in the image below. Copy and save them somewhere you can easily retrieve them.

Go to the Project menu to then open your project. In the apps section, click on the key icon, then generate an access token and access secret token for your application at the bottom of the page displayed.

Click on the generate button, then copy and save the access_token and access_secret_token somewhere you can easily retrieve them.

Getting Started with Django

To create the Django application, run the following code in your command line:


# creating the project

python manage.py startproject TweetsDaily

# change to the project directory

cd TweetsDaily

#creating the app

python manage.py startapp App

Now, go to the settings.py file and App to the list of installed apps as seen below:


INSTALLED_APPS = [

    'django.contrib.admin',

    'django.contrib.auth',

    'django.contrib.contenttypes',

    'django.contrib.sessions',

    'django.contrib.messages',

    'django.contrib.staticfiles',

    'App'

]

Next, go to the urls.py file in the project directory and edit as seen in the code below:


from django.contrib import admin

from django.urls import path, include

from django.conf import settings

urlpatterns = [

    path('admin/', admin.site.urls),

    path('', include("App.urls")),

]

Finally, go to the App folder and create a new urls.py file. Add the python code below to the file and save:


from django.conf import settings

from django.conf.urls.static import static

from django.urls import path, include

from . import views

app_name = "App"

urlpatterns = [

    path("", views.index, name="index"),

]

In the code above, we defined the index URL and connected it to the index view, which we will define in our views.py file.

Building Our Django Models

Here we will define the table models in the models.py file as see in the python code below:


from django.db import models

# Create your models here.

from django.db import models

# Create your models here.

class Tweets(models.Model):

    username= models.TextField()

    tweet_number = models.IntegerField()

    created_at = models.IntegerField()

    time = models.IntegerField()

    retweet_count= models.IntegerField()

    def __str__(self):

        return self.tweet_number

After saving, we need to migrate the database using the below command in the project directory.


#making migrations

python manage.py makemigrations

#migrating the database

python manage.py migrate

Writing Our Django Views

The views.py file in the Django project is where we write the main logic for the application.

Importing the Required LIbraries

Copy and paste the python code below at the top of the views.py file to import the required libraries.


from django.shortcuts import render

from tweepy import OAuthHandler

from tweepy import API

from tweepy import Cursor

from datetime import datetime, date, time, timedelta

from collections import Counter

from django.http import HttpResponse

from . models import Tweets

Initializing Tweepy

Copy and paste the code below in the views.py file and provide your real keys to initialize Tweepy.


consumer_key="api_key"

consumer_secret="api_secret_key"

access_token="access_token"

access_token_secret="access_token_secret"

auth = OAuthHandler(consumer_key, consumer_secret)

auth.set_access_token(access_token, access_token_secret)

auth_api = API(auth,wait_on_rate_limit=True)

We also need to create a list of the usernames of all celebrities whose tweets we want to analyze, as seen in the code below.


username=["Cristiano","BarackObama","rihanna","TheRock"]

Creating the Index View

Copy and paste the python code below in the views.py file to build the index view. This view is triggered by loading the application in the browser:


def index(request):

    tweet_count = 0

    for i in username:

        try:

            for status in Cursor(auth_api.user_timeline, id=i).items():

                tweet_count = tweet_count + 1

                if status.created_at.year == 2021 and status.created_at.month == 5:

                    tweets_save= Tweets.objects.create(username=i,tweet_number=tweet_count,created_at=status.created_at.day,time=status.created_at.hour,retweet_count=status.retweet_count)

                    tweets_save.save()

        except:

            pass

    return HttpResponse('<h1>Loaded Tweets Data</h1>')

In the code above, we are making a request to the Twitter API using Tweepy to retrieve all the user’s tweets. We are now running a loop through the user’s tweets to check for all the tweets made in May 2021. Finally, we will save all the tweets and the number of times the user’s followers retweeted them to the database. The applications will then send an HttpResponse “Loaded Tweets Data” to the application when run.

To run your application, copy and paste the below commands in your command line’s project directory:


#running the application

python manage.py runserver

If the application is running, you should see the result as seen below.

Go to http://127.0.0.1:8000 to view the running application and trigger the index view to save all the tweet data in the database.

Connecting Arctype to a MySQL Database

To make use of Arctype, we need to connect our database and set up a workspace. To do this, follow the instructions below.

Step 1

Open Arctype, click on “I have an existing database”, and select your database type. For this tutorial, we are using MySQL.

Step 2

Provide the required information for the database and click Save.

Step 3

You should now see the database in Arctype. Next, go to the Tweets table. You should have something similar to the image below.

Compare Retweets between Accounts Using MySQL

We currently have multiple tweet data for May in the year 2021 saved in the Tweet table. To make a heat map of the data, we need to sum up the retweet count for the tweets created each day in the month of may and group all the tweets that the user’s followers retweeted by the date and username. In addition, because Barack Obama and Rihanna are two of the most-followed people on Twitter, we should factor in the size of each account's following to get a clearer picture in our data.

To achieve this, click on the new query button, then save and run the SQL query below:


SELECT

  username,

  (

    CASE

      WHEN (username = 'rihanna') 

              THEN (SUM(retweet_count) / 102300000 * 100)

      WHEN (username = 'BarackObama') 

              THEN (SUM(retweet_count) / 129600000 * 100)

      WHEN (username = 'TheRock') 

              THEN (SUM(retweet_count) / 15200000 * 100)

      WHEN (username = 'Cristiano') 

              THEN (SUM(retweet_count) / 92400000 * 100)

    END

  ) AS tweet_heat,

  CONCAT('May ', created_at)

FROM

  app_tweets

GROUP BY

  created_at,

  username

ORDER BY

  created_at ASC

Running the code above will provide you with data, as seen in the image below.

Build a Twitter Heat Map Chart in Arctype

To create a heat map chart for the tweet data above, follow the instructions below:

Create a New Arctype Dashboard

Create a new tab, then select the New Dashboard button in the top menu. Then, click on the + Add button, then select ‘chart.’

Get Chart Data from a Saved Query

Click on Select Chart Data on the blank dashboard provided.

Edit the title and select the query you saved above (in my case, TweetsDailyStats) as the data source.

Assign Result Columns to Heat Map Axes

Select ‘Heat map’ as chart type, then drag and drop data arranged as seen in the image below.

Customizing an Arctype Heat Map

Finally, go to the advanced tab, then enter “Retweets Per Day” as the y-axis value.

After following the above instructions, you should have a heat map similar to the one in the image below.

The heat map above displays the retweets activity for every tweet made by the four celebrities in May 2021 grouped by the day the celebrity created the tweet.

Other Heat Map Examples

Visualizing User Tweets

Create a new query with the SQL query below. Then, run and save the query.


WITH alldates AS(

  SELECT

    distinct created_at

  FROM

    app_tweets

  ORDER BY

    1

),

alltimes AS(

  SELECT

    distinct time

  FROM

    app_tweets

  ORDER BY

    1

),

timetable AS(

  SELECT

    *

  FROM

    alldates,

    alltimes

  ORDER BY

    1

)

SELECT

  CONCAT('May ',t.created_at) AS day,

  CONCAT(t.time,':00') AS hour,

  COUNT(a.tweet_number) as tweets

FROM

  timetable t

LEFT OUTER JOIN app_tweets a ON (a.username = 'BarackObama' AND a.created_at = t.created_at AND a.time = t.time)

GROUP BY t.time, t.created_at

ORDER BY t.created_at, t.time

Create a new heat map chart using the query data you saved above. Then, configure the heat map to have the day field in the x-axis, hour in the y-axis, and the tweets in the z-axis.

The heat map above displays every tweet made by one of the celebrities (i.e., Barack Obama) in May 2021, grouped by the day and the hour of the day the user created the tweet.

Querying for a Twitter User's Retweets

Create a new query with the SQL query below. Then, run and save the query.


WITH alldates AS(

  SELECT

    distinct created_at

  FROM

    app_tweets

  ORDER BY

    1

),

alltimes AS(

  SELECT

    distinct time

  FROM

    app_tweets

  ORDER BY

    1

),

timetable AS(

  SELECT

    *

  FROM

    alldates,

    alltimes

  ORDER BY

    1

)

SELECT

  CONCAT('May ',t.created_at) AS day,

  CONCAT(t.time,':00') AS hour,

  SUM(a.retweet_count) as retweets

FROM

  timetable t

LEFT OUTER JOIN app_tweets a ON (a.username = 'BarackObama' AND a.created_at = t.created_at AND a.time = t.time)

GROUP BY t.time, t.created_at

ORDER BY t.created_at, t.time

Create a new heat map chart using the query data you saved above. Then, configure the heat map to have the day field in the x-axis, hour in the y-axis, and the retweets in the z-axis.

The heat map above displays the retweets activity for every tweet made by one of the celebrities (i.e., Barack Obama) in May 2021, grouped by the day and the hour of the day the user created the tweet.

Visualize Tweets-Retweets Ratio

Create a new query with the SQL query below. Then, run and save the query:


WITH alldates AS(

  SELECT

    distinct created_at

  FROM

    app_tweets

  ORDER BY

    1

),

alltimes AS(

  SELECT

    distinct time

  FROM

    app_tweets

  ORDER BY

    1

),

timetable AS(

  SELECT

    *

  FROM

    alldates,

    alltimes

  ORDER BY

    1

)

SELECT

  CONCAT('May ',t.created_at) AS day,

  CONCAT(t.time,':00') AS hour,

  CAST((SUM(a.retweet_count)/COUNT(tweet_number)) AS UNSIGNED) as ratio

FROM

  timetable t

LEFT OUTER JOIN app_tweets a ON (a.username = 'BarackObama' AND a.created_at = t.created_at AND a.time = t.time)

GROUP BY t.time, t.created_at

ORDER BY t.created_at, t.time

Create a new heat map chart using the query data you saved above. Then, configure the heat map to have the day field in the x-axis, hour in the y-axis, and the ratio in the z-axis.

Conclusion

In this article, you learned how to track tweet data with Tweepy and visualize them as a heat map on a dashboard with Arctype. In addition, you saw how easy it is to run SQL queries on your database using Arctype and got the chance to explore some of its core features and functionalities.

The source code of the dashboard application and the links to the Arctype SQL dashboard are available on Github. If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.

How To Build a Tweet Manager with Django, Tweepy and Fauna.

Chukslord — Tue, 22 Jun 2021 21:08:05 +0000

Authored in connection with the Write with Fauna program.

Tracking and managing tweet analytics has never been easier. This article will guide you to build a tweet management application using the Django web framework and Tweepy, then implementing the database with Fauna. This tweet manager will allow users to track tweet analytics, trends you tweeted, and save a report of the current tweet information in the database for easy reference.

Prerequisites

To fully understand this tutorial, you are required to have the following in place:

Python 3.7 or newer.
Basic understanding of Fauna.
Basic understanding of Django.
A text editor.

With the above prerequisites out of the way, we can now begin building our tweet management application.

Table of Content

Introduction to Fauna
Setting up the Twitter API
Setting up the Fauna Database
Building the Django Application
Conclusion

Introduction to Fauna

Fauna is a client-side serverless database that uses GraphQL and the Fauna Query Language (FQL) to support various data types and particularly relational databases in a serverless API. You can learn more about Fauna in their official documentation here. If this is the first time you hear about Fauna, visit my previous article here for a brief introduction.

Setting up the Twitter API

When your account is verified, go to your developer dashboard, as seen in the image above.

At this point, you need to create a project which can house multiple applications. Click on create project then give your project a name, use case, and a description.

Now you need to create an application. To do this, click on “create a new app” and provide a name for your application.

After creating your Twitter application, you will see a page with your application keys, as seen in the image below. Copy and save them somewhere you can easily retrieve them.

Click on the generate button, then copy and save the access_token and access_secret_token somewhere you can easily retrieve them.

Setting up the Fauna Database

To create a Fauna database, you have first to create an account. After making one, you can create a new database by clicking on the CREATE DATABASE button on the dashboard.

After clicking on the button as shown in the image above, you need to give your database a name then save it.

Creating the Database Collections

Create two collections in your database, the Followers collection, and the TweetsReport collection. The Followers collection will store the current number of followers the user has in the database, while the TweetsReport will store the reports generated in the database. For the History days and TTL use the default values provided and save.

Creating the Collection Indexes

Create two indexes for your collections; report_index and followers_index. The report_index index will allow you to scroll through data in the TweetsReport collection. It has one term, which is the status field. This term will enable you to match data with the status for easy querying.

The followers_index will allow you to scroll through data in the Followers collection. This index will also allow matching with the status field to perform queries.

Below are what your indexes will look like after saving them.

Generating the Database API Key

Fauna will present you with your secret key in the image above (hidden here for privacy) after saving your key. Make sure to copy your secret key from the dashboard and save it somewhere you can easily retrieve it for later use.

Building the Django Application

Now you will be learning how to implement the various functionalities of the tweet management application.

Cloning the Application Repository

Run the code below in your command line to clone the repo containing the Django app on Github and install all required libraries.

#cloning the repo
git clone https://github.com/Chukslord1/FAUNA_TWEETS_MANAGER.git


# installing tweepy
pip install django

# installing tweepy
pip install tweepy

# installing faunadb
pip install faunadb

Importing the Required Libraries

We will be doing all our application logic in the views.py file for our Django application. Go to your views.py to see the code below.

from django.shortcuts import render,redirect
from django.contrib import messages
from django.core.paginator import Paginator
from django.http import HttpResponseNotFound
from faunadb import query as q
import pytz
from faunadb.objects import Ref
from faunadb.client import FaunaClient
import hashlib
import datetime
import tweepy
from django.http import JsonResponse
import json

Initializing Fauna Client and Tweepy

To initialize the Fauna client and Tweepy, copy the code below and provide the API keys you created earlier where necessary in your views.py file. On the first line in the code below, we initialized our Fauna client by providing our secret key. In the following few lines, we initialized out Tweepy API by providing our api key, api secret key, access token, access token secret, username and screen name which is the same as the username.

client = FaunaClient(secret="fauna_secret_key")
api_key = "api_key"
api_secret = "api_secret"
access_token = "access_token"
access_token_secret= "access_token_secret"
username= "username"
screen_name=username

auth = tweepy.OAuthHandler(api_key, api_secret)

auth.set_access_token(access_token, access_token_secret)

api = tweepy.API(auth)
user=api.me()

Creating the Index Page

The index page is where all the data needed is rendered to the user on the dashboard. Also, this is where our application will generate the tweet reports. We will be focusing on building the logic of this page.

Firstly, we query the Tweepy for all tweets created that week. The code below implements this functionality.

# querying tweets created this week
tmpTweets = api.user_timeline(screen_name=username,count=100, include_rts = True)
today = endDate = datetime.datetime.now()
startDate = today - (datetime.timedelta(today.weekday() + 1))

tweets=[]

for tweet in tmpTweets:
    if endDate >= tweet.created_at >= startDate:
        tweets.append(tweet)
        words.extend(set(tweet.text.lower().split()) & all_trends)

# querying for the current trends in your location you tweeted on
COUNTRY_WOE_ID = 23424908 #where on earth id of Nigeria
all_trends = set()
country_trends = api.trends_place(COUNTRY_WOE_ID)

trends = json.loads(json.dumps(country_trends, indent=1))

for trend in trends[0]["trends"]:
    all_trends.add((trend["name"].lower().replace("#","")))

tweeted_keywords=(sorted([(i, words.count(i)) for i in set(words)], key=lambda x: x[1], reverse=True))

Next, we queried Tweepy for all the followers we have made since the last page visit. We saved the current number of followers in our Followers collection to get the new followers. The code below implements this functionality.

#querying number of new followers since last page visit

    # checking if the number of followers is saved. if not, save it
try:
    previous_follower = client.query(q.get(q.match(q.index("followers_index"), True)))
    previous_follower_count = client.query(q.get(q.match(q.index("followers_index"), True)))["data"]["follower_count"]
except:
    follower_count_create = client.query(q.create(q.collection("Followers"),{
            "data": {
                "follower_count": user.followers_count,
                "created_at": datetime.datetime.now(pytz.UTC),
                "status":True
            }
        }))
    previous_follower_count=user.followers_count

In the code above, we calculated the number of followers, then updated the document in the Followers collection if the number of followers retrieved from the database and the one retrieved from Tweepy are different since the last page visit.

# calculating new followers since last page visit
new_followers=user.followers_count-previous_follower_count

    #updating the database if there is a change in followers since last visit
if previous_follower_count == user.followers_count:
    pass
else:
    follower_count_update = client.query(q.update(q.ref(q.collection("Followers"), previous_follower["ref"].id()), {
            "data": {
                "follower_count": user.followers_count,
                "created_at": datetime.datetime.now(pytz.UTC),
                "status":True,
            }
        }))

Lastly, we created the report creation logic. The code below implements this functionality.

# generating a report for the tweets currently and saving in the database
if request.method=="POST":
    generate=request.POST.get("generated")
    report_date= datetime.datetime.now(pytz.UTC)
    report_details = "Number of followers :"+str(user.followers_count) + "\n Number following :"+str(user.friends_count) + "\n Number of Tweets This Week :"+str(len(tweets)) +  "\n New Followers: "+str(new_followers)+ "\n Trends You Tweeted On:"+str(tweeted_keywords)
    if generate == "True":
        report_create = client.query(q.create(q.collection("TweetsReport"), {
                "data": {
                    "report_date": report_date,
                    "report_details": report_details,
                    "status": True
                }
            }))

In the code above, when the user sends a POST request, the data required to create the report is collated and saved in the TweetsReport collection by making a query from the Fauna client.

The final code for our index view should be similar to the one below.

def index(request):
    # querying tweets created this week
    tmpTweets = api.user_timeline(screen_name=username,count=100, include_rts = True)
    today = endDate = datetime.datetime.now()
    startDate = today - (datetime.timedelta(today.weekday() + 1))

    tweets=[]
    new_followers=0
    words=[]
    all_trends = set()
    COUNTRY_WOE_ID = 23424908

    country_trends = api.trends_place(COUNTRY_WOE_ID)

    trends = json.loads(json.dumps(country_trends, indent=1))

    for trend in trends[0]["trends"]:
        all_trends.add((trend["name"].lower().replace("#","")))

    for tweet in tmpTweets:
        if endDate >= tweet.created_at >= startDate:
            tweets.append(tweet)
            words.extend(set(tweet.text.lower().split()) & all_trends)

    tweeted_keywords=(sorted([(i, words.count(i)) for i in set(words)], key=lambda x: x[1], reverse=True))

    try:
        previous_follower = client.query(q.get(q.match(q.index("followers_index"), True)))
        previous_follower_count = client.query(q.get(q.match(q.index("followers_index"), True)))["data"]["follower_count"]
    except:
        follower_count_create = client.query(q.create(q.collection("Followers"),{
            "data": {
                "follower_count": user.followers_count,
                "created_at": datetime.datetime.now(pytz.UTC),
                "status":True
            }
        }))
        previous_follower_count=user.followers_count

    new_followers=user.followers_count-previous_follower_count

    if previous_follower_count == user.followers_count:
        pass
    else:
        follower_count_update = client.query(q.update(q.ref(q.collection("Followers"), previous_follower["ref"].id()), {
            "data": {
                "follower_count": user.followers_count,
                "created_at": datetime.datetime.now(pytz.UTC),
                "status":True,
            }
        }))
    if request.method=="POST":
        generate=request.POST.get("generated")
        report_date= datetime.datetime.now(pytz.UTC)
        report_details = "Number of followers :"+str(user.followers_count) + "\n Number following :"+str(user.friends_count) + "\n Number of Tweets This Week :"+str(len(tweets)) +  "\n New Followers: "+str(new_followers)+ "\n Trends You Tweeted On:"+str(tweeted_keywords)
        if generate == "True":
            report_create = client.query(q.create(q.collection("TweetsReport"), {
                "data": {
                    "report_date": report_date,
                    "report_details": report_details,
                    "status": True
                }
            }))

    context={"followers":user.followers_count,"following":user.friends_count,"weekly_tweet":len(tweets),"new_followers":new_followers}
    return render(request,"index.html",context)

Creating the Reports Page

The user can view all the reports they have generated and saved to the database on this page.

def reports(request):
    get_reports= client.query(q.paginate(q.match(q.index("report_index"), True)))
    all_reports=[]
    for i in get_reports["data"]:
        all_reports.append(q.get(q.ref(q.collection("TweetsReport"),i.id())))
    reports=client.query(all_reports)
    context={"reports":reports}
    return render(request,"reports.html",context)

In the code above, we made a query to the Fauna client to retrieve all documents in the TweetsReport collection where the status field is True using the reports_index index and Fauna’s paginate method. This data is then rendered in context to the user interface to be viewed by the user.

Our Application’s URLs

Here, we imported the required modules and defined our app’s URLs connected to our created views.

from django.conf import settings
from django.conf.urls.static import static
from django.urls import path, include
from . import views

app_name = "App"

urlpatterns = [
    path("", views.index, name="index"),
    path("index", views.index, name="index"),
    path("reports", views.reports, name="reports"),


]

Conclusion

This article taught you how to build a tweet management application with Fauna's serverless database, Tweepy, and Django. We saw how easy it is to integrate Fauna into a Python application, query data from Twitter using Tweepy, and save them.

The source code of our tweet manager is available on Github. If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.

Building a Budget Manager with Django and Fauna

Chukslord — Fri, 14 May 2021 13:28:38 +0000

Written in connection with the Write with Fauna program.

This article will guide you to build a simple budget management application using the Django web framework and implement the database with Fauna. This budget manager would allow users to calculate their expenditures, keep track of their savings and get financial advice based on their budget reports.

Prerequisites

To fully understand this tutorial, you are required to have the following in place:

Python 3.7 or newer.
Basic understanding of Fauna.
Basic understanding of Django.
A text editor.

With the above prerequisites out of the way, we can now begin building our budget management application.

Introduction to Fauna

If this is the first time you hear about Fauna, visit my previous article here for a brief introduction.

Creating The Budget Report Database

To create a Fauna database, you have to first sign up for an account. After signing up, you can create a new database by clicking on the CREATE DATABASE button on the dashboard.

After clicking on the button as shown in the image above, you need to give your database a name then save it.

Creating The Collections

You need to create a collection in your database, the Budget_Report collection. The Budget_Report collection will store the documents for all the reports created on the application. For the History days and TTL, use the default values provided and save.

After saving, your collection should be similar to the one in the image below. However, you don’t have data stored in your collection yet.

Creating The Indexes

You will create two indexes for your collections; report_index, and report_previous_index. The report_index index will allow you to scroll through data in the Budget_Report collection. It has one term, which is the date field. This term will enable you to match data with the username for easy querying. The date field is unique.

The budget_previous_index also allows you to scroll through data in the Budget_Report collection. However, this index allows matching with the report field to perform queries.

Below are what your indexes will look like after saving them.

Creating an API key

To create an API key, go to the security tab on the left side of your dashboard, then click New Key to generate a key. You will then be required to provide a database to connect to the key. After providing the information required, click the SAVE button.

You will be presented with your secret key as in the image above (hidden here for privacy) after saving your key. Make sure to copy your secret key from the dashboard and save it somewhere you can easily retrieve it for later use.

Building the Django App

Now you will be learning how to implement the various functionalities of the budget management application.

Cloning the App

Run the code below in your command line to clone the repo containing the Django app on Github.

git clone https://github.com/Chukslord1/FAUNA_BUDGET_MANAGER.git

Importing the Required Libraries

from django.shortcuts import render,redirect
from django.contrib import messages
from django.http import HttpResponseNotFound
from faunadb import query as q
import pytz
from faunadb.objects import Ref
from faunadb.client import FaunaClient
import datetime

Initializing Fauna Client

To initialize the Fauna client, copy the code below and replace secret_key with the API key you created earlier.

client = FaunaClient(secret="secret_key")

The Budget Manager Logic

This application will allow the user to enter his monthly allowance, then calculate his budget report. This report will include the user’s; expenditure rate, extra funds, and a bit of financial advice from the system.

Implementing the Application Logic

def index(request):
    if request.method=="POST":
        income=int(request.POST.get("income"))
        rent=int(request.POST.get("rent"))
        food=int(request.POST.get("food"))
        utilities=int(request.POST.get("utilities"))
        insurance=int(request.POST.get("insurance"))
        date=request.POST.get("date")
        extra=income-(rent+food+utilities+insurance)
        save=20*income/100
        if extra>0:
            if extra>save:
                advice= "Your expenditure rate is very good keep it up 😉. At this rate you going to be rich 💸💸. You have an extra $"+str(extra)
            else:
                advice= "Your expenditure rate is just fine 😀. You have $"+str(extra)+" . You should try to save atleast "+" $"+str(save)+" "
        else:
            advice= "Your expenditure rate is very poor 😣 . At this rate you might go broke. You have $"+str(extra)+" . You should try to save atleast "+" $"+str(save)+" "
        try:
            report_date = client.query(q.get(q.match(q.index("report_index"), date)))
            messages.add_message(request, messages.INFO, 'Report Already Exists')
            return redirect("App:index")
        except:
            book = client.query(q.create(q.collection("Budget_Report"), {
                "data": {
                    "monthly_income":income,
                    "rent": rent,
                    "food": food,
                    "utilities":utilities,
                    "insurance":insurance,
                    "date":date,
                    "extra":extra,
                    "advice":advice,
                    "report":"True"
                }
            }))
            messages.add_message(request, messages.INFO, 'Report Created Successfully')
            return redirect("App:index")

    try:
        check_report= client.query(q.paginate(q.match(q.index("report_index"), datetime.datetime.today().strftime("%Y-%m"))))
        previous_report= client.query(q.paginate(q.match(q.index("report_previous_index"), "True")))
        all_reports=[]
        previous_reports=[]
        for i in check_report["data"]:
            all_reports.append(q.get(q.ref(q.collection("Budget_Report"),i.id())))
        for j in previous_report["data"]:
            if q.get(q.ref(q.collection("Budget_Report"),j.id())) in all_reports:
                pass
            else:
                previous_reports.append(q.get(q.ref(q.collection("Budget_Report"),j.id())))
        reports=client.query(all_reports)
        previous_reports=client.query(previous_reports)
        context={"reports":reports,"previous_reports":previous_reports}
        return render(request,"index.html",context)
    except:
        return render(request,"index.html")

In the python code above, we collected the user’s monthly income and other required information in the POST request sent. From this information, we calculated the extra funds and the amount the user needs to save. We then created an advice for the user based on the value of the save and extra variables. To add a report to the Budget_Report collection, we first checked if any report exists in the database carrying the date provided by making a request to the Fauna client with the report_index. If the data does not exist, you create a report and send a success message to the dashboard page.

Now we need to render the reports created on the dashboard for the user to see. The user needs to see all the reports for both the current month and other months. For the current month, we made a query to the Fauna client using the report_index to match all data for the current month, then saved it in the all_reports list to be queried and passed to the dashboard. While for the other months, we simply made a query to the Fauna client using the report_previous_index to match all active reports, stored the data in the previous_reports list, then eliminated the reports for the current month. Finally, we queried the previous_reports list then passed the data to be displayed on the dashboard.

After submitting the required information, the budget report will be displayed to you, as seen in the image below.

The App URLs

Here, we imported the required modules and defined our app’s urls connected to our created views.

from django.conf import settings
from django.conf.urls.static import static
from django.urls import path, include
from . import views

app_name = "App"

urlpatterns = [

    path("", views.index, name="index"),

]

Conclusion

In this article, you learned how to build a budget management application with Fauna's serverless database and Django. We saw how easy it is to integrate Fauna into a Python application and got the chance to explore some of its core features and functionalities.

The source code of our budget manager application is available on Github. If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.

Answer: How to insert row with attributes using table.insertRow?

Chukslord — Sun, 25 Apr 2021 01:13:43 +0000

answer re: How to insert row with attributes using table.insertRow?

Jan 20 '21

To set the class name for a tablerow you simply need to edit the className attribute as done below

<script>
var table = document.getElementById("tableID");
var rowCount = table.rows.length;
var row = table.insertRow(rowCount-1);
row.className = "rowdiv";
</script>

Open Full Answer

Building an E-Library with Django and Fauna

Chukslord — Thu, 22 Apr 2021 16:13:18 +0000

Written in connection with the Write with Fauna program.

This article will guide you to build a simple electronic library application using the Django web framework and implement the database with Fauna. This e-library would allow authenticated users to add books to the inventory, search for books, and view book details.

Prerequisites

To fully understand this tutorial, you are required to have the following in place:

Python 3.7 or newer.
Basic understanding of Fauna.
Basic understanding of Django.
A text editor.

Introduction to Fauna

If this is the first time you hear about Fauna, visit my previous article here for a brief introduction.

Creating The E-Library Database

To create a Fauna database, you have to first sign up for an account. Suppose you have not done that already. After signing up, you can create a new database by clicking on the CREATE DATABASE button on the dashboard.

After clicking on the button as shown in the image above, you need to give your database a name and save it.

Creating The Collections

You need to create two collections in your database; the Books collection and the Users collection. The Books collection will store the documents for all the books added to the e-library. While the Users collection will store users data registered on the platform. For the History days and TTL, use the default values provided and save.

Creating The Indexes

You will create three indexes for your collections; user,book_index, and book_index_all. The user index will allow you to scroll through data in the User collection. It has one term, which is the username field. This term will enable you to match data with the username for easy querying. The username field is unique.

Both book_index and book_index_all allow you to scroll through data in the Books collection. The book_index index has one term, which is the title field. The book_index_all index also has one term, which is the availability field.

Creating an API key

Your secret key will be presented as in the image above (hidden here for privacy) after saving your key. Copy your secret key from the dashboard and save it somewhere you can easily retrieve it for later use.

Building the Django App

Now you will be learning how to implement the various functionalities of the e-library app.

Cloning the App

Run the code below in your command line to clone the repo containing the Django app on Github.

git clone https://github.com/Chukslord1/FAUNA_LIBRARY.git

Importing the Required Libraries

from django.shortcuts import render,redirect
from django.contrib import messages
from django.http import HttpResponseNotFound
from faunadb import query as q
import pytz
from faunadb.objects import Ref
from faunadb.client import FaunaClient
import hashlib
import datetime

Initializing Fauna Client

To initialize the Fauna client, copy the code below and replace secret_key with the API key you created earlier.

client = FaunaClient(secret="secret_key")

The Index Page

def index(request):
   return render(request,"index.html")

This page contains all the navigation for the e-library application.

The Login Functionality

def login(request):
   if request.method == "POST":
       username = request.POST.get("username").strip().lower()
       password = request.POST.get("password")

       try:
           user = client.query(q.get(q.match(q.index("user"), username)))
           if hashlib.sha512(password.encode()).hexdigest() == user["data"]["password"]:
               request.session["user"] = {
                   "id": user["ref"].id(),
                   "username": user["data"]["username"]
               }
               return redirect("App:index")
           else:
               raise Exception()
       except:
           messages.add_message(request, messages.INFO,"You have supplied invalid login credentials, please try again!", "danger")
           return redirect("App:login")
   return render(request,"login.html")

In the code above, you collected the username and password passed by the user in the POST request. You then made a query to the FQL(Fauna Query Language) client using the username as a match to check if the user exists in the database. If the user exists and the password in the database matches the one passed, you redirect the user to the index page.

The Register Functionality

def register(request):
   if request.method == "POST":

       username = request.POST.get("username").strip().lower()
       email = request.POST.get("email").strip().lower()
       password = request.POST.get("password")

       try:
           user = client.query(q.get(q.match(q.index("user"), username)))
           messages.add_message(request, messages.INFO, 'User already exists with that username.')
           return redirect("App:register")
       except:
           user = client.query(q.create(q.collection("Users"), {
               "data": {
                   "username": username,
                   "email": email,
                   "password": hashlib.sha512(password.encode()).hexdigest(),
                   "date": datetime.datetime.now(pytz.UTC)
               }
           }))
           messages.add_message(request, messages.INFO, 'Registration successful.')
           return redirect("App:login")
   return render(request,"register.html")

In the code above, you collected the username, email, and password passed by the user in the POST request. You then check if the username exists in the database by making a query to the FQL client and matching it with the username. If the user does not exist, then an account is created using the fields passed.

The Add Book Functionality


def add_book(request):
   if request.method=="POST":
       title= request.POST.get("title")
       genres=request.POST.get("genres")
       summary=request.POST.get("summary")
       pages=request.POST.get("pages")
       copies=request.POST.get("copies")
       author= request.POST.get("author")
       about=request.POST.get("about")
       try:
           book = client.query(q.get(q.match(q.index("book_index"), title)))
           messages.add_message(request, messages.INFO, 'Book Already Exists')
           return redirect("App:add_book")
       except:
           book = client.query(q.create(q.collection("Books"), {
               "data": {
                   "title":title,
                   "genres": genres,
                   "summary": summary,
                   "pages":pages,
                   "copies":copies,
                   "author":author,
                   "about":about,
                   "availability":"True"
               }
           }))
           messages.add_message(request, messages.INFO, 'Book added Successfully')
           return redirect("App:add_book")
   return render(request,"add-book.html")

In the code above, you collected the title and other book details passed in the POST request. You then queried the FQL client to check if the book matching the title passed exists in the database using the book_index index. If the book doesn’t exist, then a book is created in the Books collection using the details.

The Search Book Functionality


def search_books(request):
   if request.GET.get("search"):
       try:
           search=request.GET.get("search")
           book= client.query(q.get(q.match(q.index("book_index"), search)))["data"]
           context={"book":book}
           return render(request,"search-books.html",context)
       except:
           messages.add_message(request, messages.INFO, 'No book found.')
           return render(request,"search-books.html")
   else:
       try:
           books= client.query(q.paginate(q.match(q.index("book_index_all"), "True")))["data"]
           book_count=len(books)
           page_number = int(request.GET.get('page', 1))
           book = client.query(q.get(q.ref(q.collection("Books"), books[page_number-1].id())))["data"]
           context={"count":book_count,"book":book, "next_page": min(book_count, page_number + 1), "prev_page": max(1, page_number - 1)}
           return render(request,"search-books.html",context)
       except:
           messages.add_message(request, messages.INFO, 'No book found.')
           return render(request,"search-books.html")

In the code above, you checked if the user passed a search in the get request. If yes, you then query the FQL client for books whose title matches the search value using the book_index index. However, if the user didn’t create a search, you make a query to the FQL client for books whose availability matches the value “True” using the book_index_all index. You then paginated the data retrieved using the pagination method, thus getting the data where the page number passed matches the id of the book in the database.

The Details Functionality

def detail(request,slug):
   try:
       book= client.query(q.get(q.match(q.index("book_index"), slug)))["data"]
       context={"book":book}
       return render(request,"detail.html",context)
   except:
       return render(request,"detail.html")

In the code above, you passed slug as an argument that the function expects to pass from the url. You then query the FQL client using the book_index index to get the book whose title matches the slug passed. You then render the particular book to the front-end as context.

The App URLs

In the App/urls.py file, you will find the code below:

from django.conf import settings
from django.conf.urls.static import static
from django.urls import path, include
from . import views

app_name = "App"

urlpatterns = [
   path("", views.login, name="login"),
   path("register", views.register, name="register"),
   path("login", views.login, name="login"),
   path("index", views.index, name="index"),
   path("search-books", views.search_books, name="search_books"),
   path("detail/<slug>", views.detail, name="detail"),
   path("add-book", views.add_book, name="add_book"),


]

In the code above, you defined all the required urls and connected them to their respective view functions.

Conclusion

In this article, you learned how to build an electronic library application with Fauna's serverless database and Django. We saw how easy it is to integrate Fauna into a Python application and got the chance to explore some of its core features and functionalities.

The source code of our electronic library application is available on Github. If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.

How I Built a Resume Builder with Django and Fauna

Chukslord — Fri, 02 Apr 2021 09:24:04 +0000

Authored in connection with the Write With Fauna program.

For some time now, I have been learning about Fauna and exploring its serverless database management system by building various web applications with it. In this tutorial, I will walk you through a step-by-step guide that shows how I made a resume builder with Django and Fauna.

Prerequisites

To fully understand this tutorial, you are required to have the following in place:

Python 3.7 or newer.
Basic understanding of Fauna.
Basic understanding of Django.
A text editor.

Setting Up Fauna Database

To make use of Fauna, I had to set up required collections, indexes, and API keys. Below are the steps I took to achieve this:

Creating the Database

I created a database on Fauna’s dashboard for the project. To do this, login to your dashboard, click the New Database button and provide the required information.

Creating the Collections

I needed to create collections for storing our data in Fauna. Collections are like tables in a standard database system. I created two collections for the resume builder project, which are Users (storing registered users) and Resume_Info collection (storing information used to generate the user’s resume).

To create collections in Fauna, navigate to the Collections tab in Fauna’s dashboard side menu, click on the New Collection button and provide the information required as seen in the images below:

The TTL field is where you specify how long you want the data in the respective collections to last, while the History Days stores historical records. For this project, I used the default value of 30 for History days and left the TTL blank.

Creating the Fauna Indexes

I needed to create indexes for browsing through data in the database collections. I created two indexes for this project, one for each collection named users_index and resume_index. I then set appropriate Terms for them.

To create an index, navigate to the Indexes tab in Fauna’s dashboard side menu, click on the New Index button and provide the information required as seen in the images below:

For the users_index, I set the connected collection to Users, and the Terms for browsing data with this index is the username field. The Terms are fields in the collection that Fauna will match when querying a collection with an index.

For the resume_index, I set the connected collection to Resume_Info and the Terms to user. Tick the unique checkbox for both indexes before saving to specify that all data queried is unique.

Creating the Database Security Key

To enable Django to interact with Fauna, a secret key is required by the Fauna Driver. To do this, navigate to the Security tab in Fauna’s dashboard side menu, click on the New Key button and provide the information required as seen in the images below:

After generating the secret key, you will be presented with a screen like the one above (hidden here as “SECRET_KEY” for privacy). Copy your secret key from the dashboard and save it somewhere you can easily retrieve it for later use.

Creating The Resume Builder Django Project

To install Django and Fauna, simply run the command below:


pip install django

pip install faunadb

At this point, I created the Django project using the commands below:


# create the django project

django-admin startproject RESUME_BUILDER

# change directory to the django project

cd RESUME_BUILDER

# create the django app

django-admin startapp App

In the command above, I first created a project called RESUME_BUILDER, then changed the directory to the Django project. In the project directory, I created a new app called App.

I then edited my settings.py file in my project folder and added the following code. Navigate to the RESUME_BUILDER directory and edit our settings.py file by adding our app to the list of installed apps as seen below:


INSTALLED_APPS = [

 'django.contrib.admin',

 'django.contrib.auth',

 'django.contrib.contenttypes',

 'django.contrib.sessions',

 'django.contrib.messages',

 'django.contrib.staticfiles',

 'App'

]

I also defined the static and media paths at the end of the settings.py file as seen in the code below:


STATIC_URL = '/static/'

STATIC_ROOT=os.path.join(BASE_DIR, 'static')

MEDIA_ROOT =os.path.join(BASE_DIR, 'media')

MEDIA_URL = '/media/'

Next, I edited the urls.py file in the project folder with the code below, which links it to my app’s URLs.


from django.contrib import admin
from django.urls import path, include
from django.conf import settings
from django.contrib.staticfiles.urls import staticfiles_urlpatterns
from django.contrib.staticfiles.urls import static

urlpatterns = [
    path('admin/', admin.site.urls),
    path('', include("App.urls")),


]
urlpatterns += staticfiles_urlpatterns()
urlpatterns += static(settings.MEDIA_URL, document_root=settings.MEDIA_ROOT)

Finally, I made migrations to save and migrate all settings to the database. Run the code below in your command-line to make migrations.


python manage.py migrate

The Django Project Structure

I needed to structure the Django project in a particular way that follows Django’s rules and will make it easy to work with during this tutorial. To do this, navigate to the App folder in your project, then create a new file, urls.py.In the App folder, create two new folders; templates and static. The template folder will house our HTML files, while the static folder will house our CSS, Javascript, and other static files. To do this, run the command below on your command-line.


#change directory to our App

cd App

mkdir templates

mkdir static

These commands will structure the App folder as seen in the image below:

Defining the URL routes

A urls.py in the App folder will contain all defined URL routes for our app. To do this, create a urls.py file and add the code as seen below:


from django.conf import settings
from django.conf.urls.static import static
from django.urls import path, include
from . import views

app_name = "App"

urlpatterns = [
    path("", views.login, name="login"),
    path("login", views.login, name="login"),
    path("register", views.register, name="register"),
    path("index", views.index, name="index"),
    path("create-resume", views.create_resume, name="create-resume"),
    path("resume", views.resume, name="resume"),


]

Building the User Interface

The user interface entails all the HTML, CSS, and Javascript files required to build a user-friendly interactive web application.

Creating the Static Files

I created two new files in the static folder, styles.css and script.js. I then updated them with their respective CSS and Javascript codes as seen in the Github Gist here.

The Login Page

Users need to be authenticated to access the web application. In the templates folder, I created a new file named login.html and updated it with the code below:


{% load static %}

<!DOCTYPE html>

<html lang="en">

 <head>

 <meta charset="utf-8"/>

 <title>

 Fauna Resume Builder

 </title>

 <link href="{% static './style.css' %}" rel="stylesheet"/>

 </head>

 <body>

 <!-- partial:index.partial.html -->

 <div id="javascript_header">

 </div>

 <form method="POST">

 {% csrf_token %}

 <div class="form__header">

 <h1>

 Login/Sign In

 </h1>

 <p>

 Enter your login details below.

 </p>

 {% csrf_token %}

<ul class="messages">

 {% for message in messages %}

 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>

 {% endfor %}

</ul>

 </div>

 <div class="form-group">

 <label for="name">

 Username

 <span>

 *

 </span>

 </label>

 <input id="username" name="username" type="text"/>

 <div class="error" id="name__error">

 </div>

 </div>

 <div class="form-group">

 <label for="password">

 Password

 <span>

 *

 </span>

 </label>

 <input id="password" name="password" type="password"/>

 </div>

 <div class="line-break">

 </div>

 <input id="login" type="submit" value="Login"/>

 Don't have an account? <a href="register">Sign Up</a>

 </form>

 <!-- partial -->

 <script src="{% static './script.js' %}">

 </script>

 </body>

</html>

The Register Page

For users to successfully authenticate on the login page, they need to create login details on the register page. To achieve this, in the templates folder, I created a register.html file and added the below HTML code:


{% load static %}

<!DOCTYPE html>

<html lang="en">

 <head>

 <meta charset="utf-8"/>

 <title>

 Fauna Resume Builder

 </title>

 <link href="{% static './style.css' %}" rel="stylesheet"/>

 </head>

 <body>

 <!-- partial:index.partial.html -->

 <div id="javascript_header">

 </div>

 <form method="POST">

 {% csrf_token %}

 <div class="form__header">

 <h1>

 Register/Sign Up

 </h1>

 <p>

 Enter your login details below.

 </p>

 </div>

 <div class="form-group">

 <label for="name">

 Username

 <span>

 *

 </span>

 </label>

 <input id="username" name="username" type="text"/>

 <div class="error" id="name__error">

 </div>

 </div>

 <div class="form-group">

 <label for="email">

 Email

 <span>

 *

 </span>

 </label>

 <input id="email" name="email" type="email"/>

 <div class="error" id="name__error">

 </div>

 </div>

 <div class="form-group">

 <label for="password">

 Password

 <span>

 *

 </span>

 </label>

 <input id="password" name="password" type="password"/>

 </div>

 <div class="line-break">

 </div>

 <input id="register" type="submit" value="Register"/>

 Already have an account? <a href="login">Sign In</a>

 </form>

 <!-- partial -->

 <script src="{% static './script.js' %}">

 </script>

 </body>

</html>

The Dashboard Page

This page is where users can access the functionalities of the resume builder easily. To achieve this, I created an index.html file in the templates folder and added the below HTML code:


{% load static %}

<!DOCTYPE html>

<html lang="en">

 <head>

 <meta charset="utf-8"/>

 <title>

 Fauna Resume Builder

 </title>

 <link href="{% static './style.css' %}" rel="stylesheet"/>

 </head>

 <body>

 <!-- partial:index.partial.html -->

 <div id="javascript_header">

 </div>

 <form>

 <div class="form__header">

 <h1>

 Build Your Resume

 </h1>

 <p>

 Dashboard

 </p>

 </div>

 <div class="line-break">

 </div>

 <a class="button" href="create-resume"> Create/Edit Resume</a>

 <div class="line-break">

 </div>

 <a class="button" href="resume"> View Resume</a>

 <div class="line-break">

 </div>

 <a class="button" style="background-color:red;" href="login"> Logout </a>

 </form>

 <!-- partial -->

 <script src="{% static './script.js' %}">

 </script>

 </body>

</html>

The Create Resume Page

On this page, users submit their resume info for their resume to be generated. To achieve this page, I created a create-resume.html page in the templates folder and added the code below:


{% load static %}

<!DOCTYPE html>

<html lang="en">

 <head>

 <meta charset="utf-8"/>

 <title>

 Fauna Resume Builder

 </title>

 <link href="{% static './style.css' %}" rel="stylesheet"/>

 </head>

 <body>

 <!-- partial:index.partial.html -->

 <div id="javascript_header">

 </div>

 <form method="POST">

 {% csrf_token %}

 <ul class="messages">

 {% for message in messages %}

 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>

 {% endfor %}

 </ul>

 <div class="form__header">

 <h1>

 Build Your Resume

 </h1>

 <p>

 Enter the fields below to generate a resume file.

 </p>

 </div>

 <h2>

 Personal Details

 </h2>

 <div class="form-group">

 <label for="name">

 Full Name

 <span>

 *

 </span>

 </label>

 <input id="name" name="name" value="{{resume_info.full_name}}" type="text"/>

 <div class="error" id="name__error">

 </div>

 </div>

 <div class="form-group">

 <label for="address">

 Address

 </label>

 <input id="address" name="address" value="{{resume_info.address}}" type="text"/>

 </div>

 <div class="form-group">

 <label for="phone">

 Phone

 </label>

 <input id="phone" name="phone" value="{{resume_info.phone}}" type="text"/>

 </div>

 <div class="form-group">

 <label for="email">

 Email

 <span>

 *

 </span>

 </label>

 <input id="email" name="email" value="{{resume_info.email}}" type="email"/>

 <div class="error" id="email__error">

 </div>

 </div>

 <div class="form-group">

 <label for="about">

 About You

 </label>

 <textarea id="about" name="about" placeholder="{{resume_info.about_you}}"></textarea>

 </div>

 <div class="form-group">

 <label for="career">

 Career Objectives

 </label>

 <textarea id="career" name="career" placeholder="{{resume_info.career}}"></textarea>

 </div>

 <div class="form-group">

 <label for="education">

 Education

 </label>

 <textarea id="education" name="education" placeholder="{{resume_info.education}}"></textarea>

 </div>

 <div class="line-break">

 </div>

 <h2>

 Work Experience

 </h2>

 <h3>

 Most Recent Job

 </h3>

 <div class="form-date-group">

 <div class="form-group">

 <label for="job-1__start">

 Start Date

 </label>

 <input id="job-1__start" name="job-1__start" value="{{resume_info.job_1__start}}" type="date"/>

 </div>

 <div class="form-group">

 <label for="job-1__end">

 End Date

 </label>

 <input id="job-1__end" name="job-1__end" value="{{resume_info.job_1__end}}" type="date"/>

 </div>

 </div>

 <div class="form-group">

 <label for="job-1__details">

 Position Details

 </label>

 <textarea id="job-1__details" name="job-1__details" placeholder="{{resume_info.job_1__details}}"></textarea>

 </div>

 <div class="line-break">

 </div>

 <h3>

 Past Job

 </h3>

 <div class="form-date-group">

 <div class="form-group">

 <label for="job-2__start">

 Start Date

 </label>

 <input id="job-2__start" name="job-2__start" value="{{resume_info.job_2__start}}" type="date"/>

 </div>

 <div class="form-group">

 <label for="job-2__end">

 End Date

 </label>

 <input id="job-2__end" name="job-2__end" value="{{resume_info.job_2__end}}" type="date"/>

 </div>

 </div>

 <div class="form-group">

 <label for="job-2__details">

 Position Details

 </label>

 <textarea id="job-2__details" name="job-2__details" placeholder="{{resume_info.job_2__details}}"></textarea>

 </div>

 <div class="line-break">

 </div>

 <h3>

 Another Past Job

 </h3>

 <div class="form-date-group">

 <div class="form-group">

 <label for="job-3__start">

 Start Date

 </label>

 <input id="job-3__start" name="job-3__start" value="{{resume_info.job_3__start}}" type="date"/>

 </div>

 <div class="form-group">

 <label for="job-3__end">

 End Date

 </label>

 <input id="job-3__end" name="job-3__end" value="{{resume_info.job_3__end}}" type="date"/>

 </div>

 </div>

 <div class="form-group">

 <label for="job-3__details">

 Position Details

 </label>

 <textarea id="job-3__details" name="job-3__details" placeholder="{{resume_info.job_3__details}}"></textarea>

 </div>

 <div class="line-break">

 </div>

 <div class="form-group">

 <label for="references">

 References

 </label>

 <textarea id="references" name="references" placeholder="{{resume_info.references}}"></textarea>

 </div>

 <div class="line-break">

 </div>

 <input type="submit" value="Submit Info"/>

 <br>

 <a class="button"style="background-color:red;" href="index">Back to home</a>

 </form>

 <!-- partial -->

 <script src="{% static './script.js' %}">

 </script>

 </body>

</html>

The Resume Page

The resume page is where the generated resume generated would be displayed for the user to view. To achieve this page, I created a new file resume.html and added the code below:


<html>

<head>

 <title>{% if resume_info.full_name %}{{resume_info.full_name}}'s Resume {% endif %}</title>

 <style>

@import url('https://fonts.googleapis.com/css?family=Poppins:400,600&display=swap');

body {

 font-family: 'Poppins', sans-serif;

 background: #fafafa;

 color: rgba(0,0,0,0.75);

}

h1 {

 color: rgba(0,0,0,0.9);

}

h1, p {

 box-sizing: border-box;

 margin: 0px;

 padding: 0px 24px;

}

.line-break {

 width: 100%;

 height: 1px;

 margin: 16px auto;

 border-bottom: 1px solid #eee;

}

.resume {

 border-radius: 8px;

 box-sizing: border-box;

 display: flex;

 flex-direction: column;

 max-width: 800px;

 margin: 48px auto;

 padding: 16px 0px;

 background: white;

 box-shadow: 0 1px 3px rgba(0, 0, 0, 0.02), 0 1px 2px rgba(0, 0, 0, 0.14);

}

.resume-group {

 box-sizing: border-box;

 padding: 8px 0px;

 width: 100%;

 display: flex;

 border-left: 3px solid transparent;

 transition: 0.2s;

}

.resume-group:hover {

 border-left: 3px solid #64FFDA;

}

.left-col {

 width: 35%;

}

.right-col {

 width: 65%;

}

.instructions {

 opacity: 0.5;

 text-align: center;

 font-size: 0.8rem;

 margin: 16px auto;

}

</style>

</head>

<body>

 <div class="resume">

 {% if resume_info.full_name %}

 <h1>{{resume_info.full_name}}</h1>

 {% endif %}

 {% if resume_info.email %}

 <p>{{resume_info.email}}</p>

 {% endif %}

 {% if resume_info.phone %}

 <p>{{resume_info.phone}}</p>

 {% endif %}

 {% if resume_info.address %}

 <p>{{resume_info.address}}</p>

 {% endif %}

 <div class="line-break"></div>

 {% if resume_info.about_you%}

 <div class="resume-group">

 <div class="left-col"><p>ABOUT ME</p></div>

 <div class="right-col">

 <p>{{resume_info.about_you}}

</p>

</div>

</div>

{% endif %}

{% if resume_info.career %}

<div class="resume-group"><div class="left-col">

 <p>CAREER OBJECTIVES</p>

</div>

<div class="right-col">

 <p>{{resume_info.career}}</p>

</div>

</div>

{% endif %}

{% if resume_info.education %}

<div class="resume-group">

 <div class="left-col">

 <p>EDUCATION</p>

 </div>

 <div class="right-col">

 <p>{{resume_info.education}}</p>

</div>

</div>

{% endif %}

<div class="resume-group">

 <div class="left-col">

 <p>EMPLOYMENT EXPERIENCE</p>

 </div>

 <div class="right-col">

 <p></p>

 </div>

</div>

{% if resume_info.job_1__start %}

<div class="resume-group">

 <div class="left-col">

 <p>{{resume_info.job_1__start}} to {{resume_info.job_1__end}}</p>

 </div>

 <div class="right-col">

 <p>{{resume_info.job_1__details}}

</p>

</div>

</div>

{% endif %}

{% if resume_info.job_2__start %}

<div class="resume-group">

 <div class="left-col">

 <p>{{resume_info.job_2__start}} to {{resume_info.job_2__end}}</p>

 </div>

 <div class="right-col">

 <p>{{resume_info.job_2__details}}

</p>

</div>

</div>

{% endif %}

{% if resume_info.job_3__start %}

<div class="resume-group">

 <div class="left-col">

 <p>{{resume_info.job_3__start}} to {{resume_info.job_3__end}}</p>

 </div>

 <div class="right-col">

 <p>{{resume_info.job_3__details}}

</p>

</div>

</div>

{% endif %}

<div class="resume-group">

 <div class="left-col">

 <p>REFERENCES</p>

 </div>

 <div class="right-col">

 <p>{% if resume_info.references %}{{resume_info.references}}{% else %}Available upon request{% endif %}</p>

 </div>

</div>

</div>

<div class="instructions">Ctrl + P and "Save As PDF" to make a copy of this resume</div>

</body>

</html>

Building the Functionalities

We would now be focusing on how I created the app logic. In Django, the logic is built majorly in the views.py file in the App folder.

Importing the Required Modules

Firstly, I imported the modules required for this project to work in the views.py file as seen in the code below:


from django.shortcuts import render,redirect
from django.contrib import messages
from django.http import HttpResponseNotFound
from faunadb import query as q
import pytz
from faunadb.objects import Ref
from faunadb.client import FaunaClient
import hashlib
import datetime

Initializing the FQL Client and Index

To allow the python app to interact with Fauna easily, I needed to initialize the client and indexes using the secret key I saved earlier. To do this, I added the code below to the views.py file.


client = FaunaClient(secret="SECRET_KEY")

indexes = client.query(q.paginate(q.indexes()))

The Login Functionality

To create the logic for the login page, I added the code below to my views.pyas seen below:


def login(request):
    if request.method == "POST":
        username = request.POST.get("username").strip().lower()
        password = request.POST.get("password")

        try:
            user = client.query(q.get(q.match(q.index("users_index"), username)))
            if hashlib.sha512(password.encode()).hexdigest() == user["data"]["password"]:
                request.session["user"] = {
                    "id": user["ref"].id(),
                    "username": user["data"]["username"]
                }
                return redirect("App:index")
            else:
                raise Exception()
        except:
            messages.add_message(request, messages.INFO,"You have supplied invalid login credentials, please try again!", "danger")
            return redirect("App:login")
    return render(request,"login.html")

In the code above, I checked if a POST request was received. If one was received, I collected the required data expected to be obtained from the POST request, which is the username and the password of the user. I then used the data stored in the username variable as a matching parameter for the FQL client to utilize in querying the Users collection for a user matching the data in the username field using the users_index index. If the user existed, I checked if the password for that users matched the one provided. If the passwords matched, the user is successfully authenticated, and the username is stored in the session. However, if the user does not exist or the passwords provided don’t match, then a message, “You have supplied invalid login credentials” is rendered.

The Register Functionality

To create the logic for the register page, I added the code below to my views.pyas seen below:


def register(request):
    if request.method == "POST":
        username = request.POST.get("username").strip().lower()
        email = request.POST.get("email").strip().lower()
        password = request.POST.get("password")

        try:
            user = client.query(q.get(q.match(q.index("users_index"), username)))
            messages.add_message(request, messages.INFO, 'User already exists with that username.')
            return redirect("App:register")
        except:
            user = client.query(q.create(q.collection("Users"), {
                "data": {
                    "username": username,
                    "email": email,
                    "password": hashlib.sha512(password.encode()).hexdigest(),
                    "date": datetime.datetime.now(pytz.UTC)
                }
            }))
            messages.add_message(request, messages.INFO, 'Registration successful.')
            return redirect("App:login")
    return render(request,"register.html")

In the code above, I checked if a POST request was received. If one was received, I then collected the required data expected to be obtained from the POST request, which is the user’s username, email, and password. I then used the data stored in the username as a matching parameter for the FQL client to utilize in querying the Users collection for a user matching the data in the username field using the users_index index. If the user existed, a message, “ User already exists with that username” is rendered. However, if there is no match in the database, an account with the required info is created in the database by making a query to the Users collection using the FQL client.

The Dashboard

For the dashboard, no logic is required. I simply rendered the “index.html” file with the code below:


def index(request):
    return render(request,"index.html")

The Create Resume Functionality

I added the code seen below to the views.py file to implement the resume creation logic for the create resume functionality.


def create_resume(request):
    if request.method=="POST":
        username=request.session["user"]["username"]
        full_name=request.POST.get("name")
        address=request.POST.get("address")
        phone=request.POST.get("phone")
        email=request.POST.get("email")
        about_you=request.POST.get("about")
        education=request.POST.get("education")
        career=request.POST.get("career")
        job_1__start=request.POST.get("job-1__start")
        job_1__end=request.POST.get("job-1__end")
        job_1__details=request.POST.get("job-1__details")
        job_2__start=request.POST.get("job-2__start")
        job_2__end=request.POST.get("job-2__end")
        job_2__details=request.POST.get("job-2__details")
        job_3__start=request.POST.get("job-3__start")
        job_3__end=request.POST.get("job-3__end")
        job_3__details=request.POST.get("job-3__details")
        references=request.POST.get("references")
        try:
            resume = client.query(q.get(q.match(q.index("resume_index"), username)))
            quiz = client.query(q.update(q.ref(q.collection("Resume_Info"),resume["ref"].id()), {
                "data": {
                    "user":username,
                    "full_name": full_name,
                    "address": address,
                    "phone": phone,
                    "email":email,
                    "about_you":about_you,
                    "education":education,
                    "career":career,
                    "job_1__start":job_1__start,
                    "job_1__end":job_1__end,
                    "job_1__details":job_1__details,
                    "job_2__start":job_2__start,
                    "job_2__end":job_2__end,
                    "job_2__details":job_2__details,
                    "job_3__start":job_3__start,
                    "job_3__end":job_3__end,
                    "job_3__details":job_3__details,
                }
            }))
            messages.add_message(request, messages.INFO, 'Resume Info Edited Successfully. Download Resume Now')
            return redirect("App:create-resume")
        except:
            quiz = client.query(q.create(q.collection("Resume_Info"), {
                "data": {
                    "user":username,
                    "full_name": full_name,
                    "address": address,
                    "phone": phone,
                    "email":email,
                    "about_you":about_you,
                    "education":education,
                    "job_1__start":job_1__start,
                    "job_1__end":job_1__end,
                    "job_1__details":job_1__details,
                    "job_2__start":job_2__start,
                    "job_2__end":job_2__end,
                    "job_2__details":job_2__details,
                    "job_3__start":job_3__start,
                    "job_3__end":job_3__end,
                    "job_3__details":job_3__details,
                }
            }))
            messages.add_message(request, messages.INFO, 'Resume Info Saved Successfully. Download Resume Now')
            return redirect("App:resume")
    else:
        try:
            resume_info = client.query(q.get(q.match(q.index("resume_index"), request.session["user"]["username"])))["data"]
            context={"resume_info":resume_info}
            return render(request,"create-resume.html",context)
        except:
            return render(request,"create-resume.html")

In the code above, I checked if a POST request was received. If one was received, I collected the required data from it and used it to generate the user’s resume. I then checked if the user has already submitted resume information earlier by using the username of the user stored in the session as a matching parameter for the FQL client to utilize in querying the database for data that match the user field of the Resume_Info collection. If the user didn’t have any resume information saved before, then documents will be created using the resume information collected. However, if the user has saved his/her resume information earlier, then the resume info is updated using the new resume_info provided. I also rendered the resume info data for the user in the respective field to the create resume page using context.

The Resume Page Functionality

The only functionality this page has is to render already saved data on the resume page.

I achieved this by making a query using the FQL to get the resume info for the current user by using the username as a match for the user field in the Resume_Info collection. I then passed the data retrieved to context which will be rendered on the resume page. To achieve this I added the code below and add to my views.py file as seen below:


def resume(request):
    try:
        resume_info = client.query(q.get(q.match(q.index("resume_index"), request.session["user"]["username"])))["data"]
        context={"resume_info":resume_info}
        return render(request,"resume.html",context)
    except:
        return render(request,"resume.html")

Finally, I succeeded in building my resume builder web application. If you followed through with this tutorial, run your article by executing the below command.


python manage.py runserver

You can now view your application on your localhost

Conclusion

In this article, I walked you through a step-by-step guide showing how I built a resume builder using Fauna and Django. We saw how easy it is to integrate Fauna into a Python/Django application and got our hands dirty by exploring some of its core features and functionalities.

The source code of the resume builder application is available on Github, and you can access a live demo here. If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.

Building a Quiz App with Django and Fauna

Chukslord — Tue, 09 Mar 2021 19:19:30 +0000

In this article, we are going to explore building a quiz application using the Python Django web framework and Fauna. If this is your first time hearing about Fauna, please visit my previous article here for a brief introduction.

Creating The Quiz Database

The first step we are going to take is to create a new database in Fauna. To create a Fauna database, you have to first sign up for an account. if you have not done that already. After signing up, you can now create a new database by clicking on the CREATE DATABASE button on the dashboard.

After clicking on the button as shown in the image above, you then need to give your database a name and save it. For this tutorial, we are going to name our database Quiz.

Creating The Collections

You will also need to create four collections (one for Users,Quiz,Question and Answers). A collection could be thought of as a table in a database system. Click on CREATE COLLECTION then fill in the required fields. You will need to enter the name for the collection, the History Days and TTL. The History Days is the number of days you want Fauna to retain a historical record of any data in that collection while the TTL is an expiry date for data in the collection. For example, if the TTL is set to 5, any data stored in the collection will automatically be deleted 5 days after its last modified date.

Creating the Fauna Indexes

We will need to create eight (8) Fauna indexes that will allow us to scroll through data added to our database. The indexes are the; user, quiz_index, quiz_get_index, question_index, question_get_index,question_answer,answer_score_index and the answer_get_index. The user index will have a term for “username” and will be a unique field that will allow querying of the User collection. The quiz_index index will have a term for “name” and will be a unique field that will allow querying and creating new documents in the Quiz collection. The quiz_get_index index will have a term for “status” which will also allow querying of the Quiz collection. The question_index index will have a term for “question_asked” which will allow querying of the Question collection. The question_get_index will have a term for "name" which will allow querying and matching with of the data in the Question collection . The question_answer index will have a term for “correct_answer” which will also allow querying and matching of Question collection. The answer_score_index will have a term for “user” and “quiz” which will allow matching and querying of the Answers collection. The answer_get_index will have a term for “user” and “question” which will also allow matching and querying of the Answers collection.

Creating a Fauna API Key

Before you can begin building a Django app that uses Fauna, you need to create an API key that will allow our Django app to easily interact with the database. To create an API key, go to the security tab on the left side of your screen.

Click New Key to generate a key. You will then be required to provide a database to connect the key to, the role of the key, and an optional name for your API key. After providing the information required, click the SAVE button.

After saving your key, your secret key will be presented as in the image above (hidden here for privacy). Copy your secret key from the dashboard and save it somewhere you can easily retrieve it for later use.

Designing Our Quiz App UI

Prerequisites

From this point onwards, to follow this step by step guide on building a quiz app with Django, you need to have the following installed:

Python 3.7 or >3.7
Faunadb
Django

Installing the requirements

To install Fauna and Django you simply need to run the below command in your command-line interface.

pip install faunadb

Creating the Django Project

To create the Django project you need to run the command below in the command-line interface.

#creating the django project

django-admin startproject Quiz

#changing directory to the django project  
cd Quiz

#creating a django app  
django-admin startapp App

The above command first creates a project Quiz, then changes the directory to the project. In the project directory, the last command creates a new app called App.

The Django Project Structure

The Django project has to be structured in a particular way that follows Django’s rules and is easy for us to work with. Navigate to the App folder in your project, then create a new file urls.py. Next, in the App folder create two new folders templates and static. The template folder will house our HTML files, while the static folder will house our CSS, Javascript, and other static files. To do this, run the command below on your command-line.

# changing directory to our app  
cd App  
# creating the static and templates folders  
mkdir templates  
mkdir static

The “App” folder should now look like the image below.

Now go back to our Quiz directory and edit our settings.py file by adding our app to the list of installed apps as seen below.

INSTALLED_APPS = [  
 'django.contrib.admin',  
 'django.contrib.auth',  
 'django.contrib.contenttypes',  
 'django.contrib.sessions',  
 'django.contrib.messages',  
 'django.contrib.staticfiles',  
 'App',  
]

Inside the static and templates folders, we are going to create their respective files.

For the templates folder create a new file dashboard.html and add the HTML code below.

{% load static %}  

<html>  
 <head>  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  

 <!-- Optional theme -->  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">  

 <!-- Latest compiled and minified JavaScript -->  
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 QUIZ APP  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 Welcome {{user}}  
 </p>  
 <center>  
 <form class="form1">  
 {% if messages %}  
<ul class="messages">  
 {% for message in messages %}  
 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>  
 {% endfor %}  
</ul>  
{% endif %}  
 <a href="quiz"><button type="button" class="btn btn-rounded btn-primary" style="background-color:purple !important;">START QUIZ</button></a>  
 <br>  
 </form>  
 <form class="form1">  
 <a href="create-quiz"><button type="button" class="btn btn-rounded btn-primary" style="background-color:purple !important;">CREATE QUIZ</button></a>  
 <br>  
 </form>  
 <form class="form1">  
 <a href="create-questions"><button type="button" class="btn btn-rounded btn-primary" style="background-color:purple !important;">CREATE QUESTION</button></a>  
 <br>  
</form>  
 <form class="form1">  
 <a href="login"><button type="button" class="btn btn-rounded btn-primary" style="background-color:purple !important;">LOGOUT</button></a>  
 </form>  
 </center>  
 </div>  
 </body>  
</html>

This dashboard.html file is the dashboard page where a user can easily navigate between the site’s functions.

Next, create a login.html file, edit, and add the following HTML code as seen below.

{% load static %}  

<html>  
 <head>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 QUIZ APP  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 Hello   
 </p>  
 <form class="form1" method="POST">  
 {% csrf_token %}  
 {% if messages %}  
<ul class="messages">  
 {% for message in messages %}  
 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>  
 {% endfor %}  
</ul>  
{% endif %}  
 <input align="center" class="un" placeholder="Username" name="username" type="text"/>  
 <input align="center" class="pass" placeholder="Password" name="password" type="password"/>  
 <button align="center" type="submit" class="submit">  
 Sign in  
 </button>  
 </form>  
 <p align="center" class="forgot">  
 <a href="register">  
 Don't have an account? Register  
 </a>  
 </p>  
 </div>  
 </body>  
</html>

The login.html page is the page where users can easily be authenticated before accessing the site’s functions. Users are required to input their username and password to login.

The next page we are going to be creating is the register page.

Create a new file register.html, edit and add the HTML code as seen below.

{% load static %}  

<html>  
 <head>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 QUIZ APP  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 Create Account  
 </p>  
 <form class="form1" method="POST">  
 {% if messages %}  
<ul class="messages">  
 {% for message in messages %}  
 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>  
 {% endfor %}  
</ul>  
{% endif %}  
 {% csrf_token %}  
 <input align="center" class="un" placeholder="Username" name="username" type="text"/>  
 <input align="center" class="un" placeholder="email" name="email" type="email"/>  
 <input align="center" class="pass" placeholder="Password" name="password" type="password"/>  
 <button align="center" class="submit" type="submit">  
 Register  
 </button>  
 <center>  
 <a href="login">  
 Already have an account? Login  
 </a>  
 </center>  
 </form>  
 </div>  
 </body>  
</html>

The register.html page is where users can create new accounts to use for login authentication. Users are required to enter their username, email, and password.

The next page is the quiz.html page where users can see all the quizzes created. Create the quiz.html file and add the below code.

{% load static %}  

<html>  
 <head>  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  

 <!-- Optional theme -->  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">  

 <!-- Latest compiled and minified JavaScript -->  
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 QUIZ APP  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 There {% if count == 1 %} is {{count}} quiz {% else %} are {{count}} quizes {% endif %} available  
 </p>  
 <p align="center" class="sign">  
 Choose a Quiz To Take  
 </p>  
 <center>  
 <div class="card" style="width: 18rem;">  
 <div class="card-body">  
 </div>  
 <br>  
</div>  
<a class="btn btn-primary">{{quiz.name}}</a>  
 <nav aria-label="...">  
 <br>  
 <p><b>About Test</b></p>  
 <p>{{quiz.description}}</p>  
 <br>  
 <p>Total Questions: {{quiz.total_questions}} </p>  
 <br>  
 <a href="answer-quiz/{{quiz.name}}" class="btn btn-danger">Start Test</a>  
 <br>  
 <ul class="pagination">  
 <li class="page-item disabled">  
 <a class="page-link" href="?page={{ prev_page }}" tabindex="-1">Previous</a>  
 </li>  
 <li class="page-item">  
 <a class="page-link" href="?page={{next_page }}">Next</a>  
 </li>  
 </ul>  
</nav>  
</center>  
 </div>  
 </body>  
</html>

The next page we are going to create is the create_quiz.html page where users can create new quizzes for users to answer. Create the create_quiz.html page and add the code below.

{% load static %}  

<html>  
 <head>  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  

 <!-- Optional theme -->  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">  

 <!-- Latest compiled and minified JavaScript -->  
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 QUIZ APP  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 Create a Quiz  
 </p>  
 <center>  
 <form class="form1" method="POST">  
 {% csrf_token %}  
 {% if messages %}  
<ul class="messages">  
 {% for message in messages %}  
 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>  
 {% endfor %}  
</ul>  
{% endif %}  
 Quiz Name:  
 <input align="center" class="pass" type="text" name="quiz_name" />  
 <br>  
 Quiz Description:  
 <input align="center" class="pass" type="text" name="quiz_description" />  
 <br>  
 Total Questions:  
 <input align="center" class="pass" type="number" name="total_questions" />  
 <br>  
 <a href="dashboard" class="btn btn-primary">Back</a><button class="btn btn-primary" type="submit">Create Quiz </button>  
 </form>  
 </center>  
 </div>  
 </body>  
</html>

We also need to create the answer_quiz.html page where users can answer the quiz that they picked on the quiz.html page we created earlier. Copy and paste the code below in your answer_quiz.html file.

{% load static %}  

<html>  
 <head>  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  

 <!-- Optional theme -->  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">  

 <!-- Latest compiled and minified JavaScript -->  
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 QUIZ APP  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 Choose Any Option from A to D  
 </p>  
 <center>  
 <div class="card" style="width: 18rem;">  
 <div class="card-body">  
 </div>  
 <br>  
</div>  
<a class="btn btn-primary">SCIENCE TEST</a>  
{% if messages %}  
<ul class="messages">  
{% for message in messages %}  
<li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>  
{% endfor %}  
</ul>  
{% endif %}  
 {% if score %}  
 <h1>Total Score: {{score}} out of {{question_count}}<h1>  
 {% else %}  
 <nav aria-label="...">  
 <br>  
 <p><b>Question: {{question.question_asked}}</b></p>  
 <br>  
 <form method="POST" >  
 {% csrf_token %}  
 <p>A. {{question.answer_1}} <input type="radio" name="answer" value="{{question.answer_1}}" /></p>  
 <p>B. {{question.answer_2}} <input type="radio" name="answer" value="{{question.answer_2}}" /></p>  
 <p>C. {{question.answer_3}} <input type="radio" name="answer" value="{{question.answer_3}}" /></p>  
 <p>D. {{question.answer_4}} <input type="radio" name="answer" value="{{question.answer_4}}" /></p>  
 <input type="hidden" name="question" value="{{question.question_asked}}"/>  

 <ul class="pagination">  
 <li class="page-item disabled">  
 <a class="page-link" href="?page={{ prev_page }}" tabindex="-1">Previous</a>  
 </li>  
 <li class="page-item">  
 <a class="page-link" href="?page={{next_page }}">Next</a>  
 </li>  
 </ul>  
</nav>  
<button class="btn btn-danger" type="submit">Save Answer</button>  
</form>  
{% if finish %}  
<a href="?finish=true"><button class="btn btn-danger" >Finish</button></a>  
{% endif %}  
{% endif %}  
 <a href="{% url 'App:dashboard' %}" class="btn btn-primary">Back</a>  
</center>  
 </div>  
 </body>  
</html>

The final page is the create_questions.html page where the user can create the questions that will appear under a quiz. To create copy and paste the code below.

{% load static %}  

<html>  
 <head>  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  

 <!-- Optional theme -->  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">  

 <!-- Latest compiled and minified JavaScript -->  
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 QUIZ APP  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 Create a Question  
 </p>  
 <center>  
 <form class="form1" method="POST">  
 {% csrf_token %}  
 {% if messages %}  
<ul class="messages">  
 {% for message in messages %}  
 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>  
 {% endfor %}  
</ul>  
{% endif %}  
 Quiz Name:  
 <select align="center" class="pass" name="quiz_name">  
 {% for quiz in quiz_all %}  
 <option>{{quiz.data.name}}</option>  
 {% endfor %}  
 </select>  
 <br>  
 Question:  
 <input align="center" class="pass" type="text" name="question" />  
 <br>  
 Answer 1:  
 <input align="center" class="pass" type="text" name="answer_1" />  
 <br>  
 Answer 2:  
 <input align="center" class="pass" type="text" name="answer_2" />  
 <br>  
 Answer 3:  
 <input align="center" class="pass" type="text" name="answer_3" />  
 <br>  
 Answer 4:  
 <input align="center" class="pass" type="text" name="answer_4" />  
 <br>  
 Correct Answer:  
 <input align="center" class="pass" type="text" name="correct_answer" />  
 <br>  
 <a href="dashboard" class="btn btn-primary">Back</a><button class="btn btn-primary" type="submit">Create Question </button>  
 </form>  
 </center>  
 </div>  
 </body>  
</html>

We are done creating the HTML files needed. Now we need to create our CSS files. Navigate back to the static folder, create a new folder in it; Login folder.

In the Login folder, create a new file style.css and add the CSS code as seen in the GitHub gist link here.

We are done with the static and templates folders. Let’s now edit the urls.py and the views.py file. In the urls.py file, copy and add the following python code as seen below.

from django.conf import settings  
from django.conf.urls.static import static  
from django.urls import path, include  
from . import views  
app_name = "App"  

urlpatterns = [  
 path("", views.login, name="login"),  
 path("login", views.login, name="login"),  
 path("dashboard", views.dashboard, name="dashboard"),  
 path("quiz", views.quiz, name="quiz"),  
 path("register", views.register, name="register"),  
 path("create-quiz", views.create_quiz, name="create-quiz"),  
 path("create-questions", views.create_question, name="create-question"),  
 path("answer-quiz/<slug>", views.answer_quiz, name="answer-quiz"),  
]

In the urls.py file we imported the required Django modules needed and defined all the URLs we are going to be making use of in this project and connected them with the required view function needed for them to run.

from django.shortcuts import render,redirect  
from django.contrib import messages  
from django.core.paginator import Paginator  
from django.http import HttpResponseNotFound  
from faunadb import query as q  
import pytz  
from faunadb.objects import Ref  
from faunadb.client import FaunaClient  
import hashlib  
import datetime  




client = FaunaClient(secret="SECRET_KEY")  
indexes = client.query(q.paginate(q.indexes()))

In the views.py file we imported all the required Django modules and initialized the Fauna client using our secret key which we saved earlier.

Navigate back to the base directory and open the Quiz folder as seen in the image below.

from django.contrib import admin  
from django.urls import path, include  
from django.conf import settings  
from django.contrib.staticfiles.urls import staticfiles_urlpatterns  
from django.contrib.staticfiles.urls import static  

urlpatterns = [  
 path('admin/', admin.site.urls),  
 path('', include("App.urls")),  

]

In the base project’s urls.py we imported the modules required and linked the app URLs to our base path URLs.

Building Our Quiz App Logic

We would be going through all the steps to build the functionality of our quiz application.

Register Functionality

The first action the user takes when opening the web app is to register if they haven't already. Update the register function in the views.py with the python code below.

def register(request):
   if request.method == "POST":
       username = request.POST.get("username").strip().lower()
       email = request.POST.get("email").strip().lower()
       password = request.POST.get("password")

       try:
           user = client.query(q.get(q.match(q.index("users_index"), username)))
           messages.add_message(request, messages.INFO, 'User already exists with that username.')
           return redirect("App:register")
       except:
           user = client.query(q.create(q.collection("users"), {
               "data": {
                   "username": username,
                   "email": email,
                   "password": hashlib.sha512(password.encode()).hexdigest(),
                   "date": datetime.datetime.now(pytz.UTC)
               }
           }))
           messages.add_message(request, messages.INFO, 'Registration successful.')
           return redirect("App:login")
   return render(request,"register.html")

In the register view function, we first checked if the request sent from the register page is a POST request else we simply render the register page. If this was the case, then we are to expect some values (i.e the username, email, and password) in the POST request. We then make a request to Fauna with the get method of the FQL client and the users_index we created earlier to check the user's collection if a user with the match of the username already exists. If the user exists then a “User already exists with that username” message is displayed. If the user does not exist, a request is made with the FQL client to Fauna to create a document with provided details along with the date of registration. The user is now redirected to the login page to log in with his/her newly created login details. If you check the users collection you should see the data added as seen below.

On unsuccessful registration, a message as seen in the image below should be displayed.

Login Functionality

The second action the user takes when he opens the web app is to provide his login details for authentication. Update the login function in the views.py with the following python code to implement login functionality.

def login(request):
   if request.method == "POST":
       username = request.POST.get("username").strip().lower()
       password = request.POST.get("password")

       try:
           user = client.query(q.get(q.match(q.index("users_index"), username)))
           if hashlib.sha512(password.encode()).hexdigest() == user["data"]["password"]:
               request.session["user"] = {
                   "id": user["ref"].id(),
                   "username": user["data"]["username"]
               }
               return redirect("App:dashboard")
           else:
               raise Exception()
       except:
           messages.add_message(request, messages.INFO,"You have supplied invalid login credentials, please try again!", "danger")
           return redirect("App:login")
   return render(request,"login.html")

In the login views function, we first check if the request sent from the login page is a POST request else we simply render the login page. If this was the case, then we are to expect some values (i.e the username and password) in the POST request. After collecting the username and password, we then make use of the get method of the FQL client and the users_index we created earlier to make a request to Fauna to check if the user exists using the username as a match. If this user exists, we then check if the password passed is correct by hashing it and comparing it to the hashed password on Fauna. If the password is correct we then store the user’s data in the session and redirect him/her to the dashboard. If the user does not exist or the password doesn’t match, then we pass a “you have supplied invalid login credentials” message to the login page then redirect the user back to the login page. We then cached our function with a try-except to handle any errors that may come from querying the Fauna.

If the login was unsuccessful you should get a result as seen in the image below.

The Dashboard

Update your views.py file with the code below to create the dashboard view and render the logged in users data on the frontend.

def dashboard(request):
   if "user" in request.session:
       user=request.session["user"]["username"]
       context={"user":user}
       return render(request,"dashboard.html",context)
   else:
       return HttpResponseNotFound("Page not found")

On successful login, you should be redirected to the dashboard page as seen in the image below.

Create Quiz Functionality

We need to allow users to create new quizzes to participate in. To do this update your views.py file with the following code:

def create_quiz(request):
   if request.method=="POST":
       name=request.POST.get("quiz_name")
       description=request.POST.get("quiz_description")
       total_questions=request.POST.get("total_questions")
       try:
           quiz = client.query(q.get(q.match(q.index("quiz_index"), name)))
           messages.add_message(request, messages.INFO, 'A Quiz with that name already exists.')
           return redirect("App:create-quiz")
       except:
           quiz = client.query(q.create(q.collection("Quiz"), {
               "data": {
                   "status":"active",
                   "name": name,
                   "description": description,
                   "total_questions": total_questions,
               }
           }))
           messages.add_message(request, messages.INFO, 'Quiz Created Successfully.')
           return redirect("App:create-quiz")
   return render(request,"create_quiz.html")

In the code above, we checked if there a POST request was received. If it was, we collected the “name”,”description” and “total_questions” which are fields required to create a quiz. WE then used a “try-except” to cache querying our Quiz collection with our quiz_index index which checks if any other quiz in the database matches the name provided for the quiz to be created. If the query produces no results, we then allow the quiz to be created.

Quiz Page Functionality

The quiz page carries all the quizzes created and allows users to scroll through them and choose anyone to participate in. Update your views.py file with the following code:

def quiz(request):
   try:
       all_quiz=client.query(q.paginate(q.match(q.index("quiz_get_index"), "active")))["data"]
       quiz_count=len(all_quiz)
       page_number = int(request.GET.get('page', 1))
       quiz = client.query(q.get(q.ref(q.collection("Quiz"), all_quiz[page_number-1].id())))["data"]
       context={"count":quiz_count,"quiz":quiz, "next_page": min(quiz_count, page_number + 1), "prev_page": max(1, page_number - 1)}
       return render(request,"quiz.html",context)
   except:
       return render(request,"quiz.html")

In the code above, we made a query to the FQL client and created the query all_quiz using the quiz_get_index index to check for quizzes that are active. We then created a pagination quiz to query all data in the all_quiz query we created earlier using the page number requested by the user and rendered all the pagination and other required data using the context.’

As seen in the image above, the user can view all the quizzes on every page and click on Start Test to answer a quiz of his/her choice.

Create Questions Functionality

This functionality allows users to create questions that would appear in the different quizzes created. Update your views.py with the code below.

def create_question(request):
   quiz_all=client.query(q.paginate(q.match(q.index("quiz_get_index"), "active")))
   all_quiz=[]
   for i in quiz_all["data"]:
       all_quiz.append(q.get(q.ref(q.collection("Quiz"),i.id())))
   context = {"quiz_all":client.query(all_quiz)}
   if request.method=="POST":
       quiz_name=request.POST.get("quiz_name")
       question_asked=request.POST.get("question")
       answer_1=request.POST.get("answer_1")
       answer_2=request.POST.get("answer_2")
       answer_3=request.POST.get("answer_3")
       answer_4=request.POST.get("answer_4")
       correct_answer=request.POST.get("correct_answer")
       try:
           question_create = client.query(q.get(q.match(q.index("question_index"), question_asked)))
           messages.add_message(request, messages.INFO, 'This question already exists')
           return redirect("App:create-question")
       except:
           question_create = client.query(q.create(q.collection("Question"), {
               "data": {
                   "quiz_name": quiz_name,
                   "question_asked": question_asked,
                   "answer_1": answer_1,
                   "answer_2": answer_2,
                   "answer_3": answer_3,
                   "answer_4": answer_4,
                   "correct_answer": correct_answer,
               }
           }))
           messages.add_message(request, messages.INFO, 'Question Created Successfully.')
           return redirect("App:create-question")
   return render(request,"create-questions.html",context)

In the code above, we made a query to the FQL client using the quiz_get_index to check query all the data in the Quiz collection to be rendered on the create-questions.html page. We then appended all the data in the query above into the all_quiz list. At this point, we checked if a POST request was received. If it was, we collected all the data provided in the POST request required to create a question and made a query using the question_index index to check if there are any documents in our Question collection with the same data provided for the question to be created. If there was no match, we proceeded to create the question and rendered a “Question created successfully” message to the frontend while redirecting back to the create-questions.html page to avoid duplicate data on reload of the page.

The Answer Quiz Functionality

To allow users to answer questions and get scored on a quiz, update your views.py with the following code:

def answer_quiz(request,slug):
   question_all=client.query(q.paginate(q.match(q.index("question_get_index"), slug)))["data"]
   question_count=len(question_all)
   page_number = int(request.GET.get('page', 1))
   question = client.query(q.get(q.ref(q.collection("Question"), question_all[page_number-1].id())))["data"]
   if page_number==question_count:
       context={"question":question,"next_page": min(question_count, page_number + 1), "prev_page": max(1, page_number - 1),"finish":"true"}
   else:
       context={"question":question,"next_page": min(question_count, page_number + 1), "prev_page": max(1, page_number - 1)}
   if request.method=="POST":
       answer=request.POST.get("answer")
       question=request.POST.get("question")
       try:
           check_answer=client.query(q.get(q.match(q.index("answer_get_index"), request.session["user"]["username"],question)))["data"]
           messages.add_message(request, messages.INFO, 'You already answered this question')
       except Exception:
           answer_create = client.query(q.create(q.collection("Answers"), {
               "data": {
                   "user": request.session["user"]["username"],
                   "quiz": slug,
                   "question": question,
                   "answer": answer,
               }
           }))
           messages.add_message(request, messages.INFO, 'Answer Saved')
   if request.GET.get("finish")=="true":
       score=0
       check_answer=client.query(q.paginate(q.match(q.index("answer_score_index"), request.session["user"]["username"],slug)))
       all_answer=[]
       for i in check_answer["data"]:
           all_answer.append(q.get(q.ref(q.collection("Answers"),i.id())))
       answers=client.query(all_answer)
       for i in answers:
           try:
               mark_answer=client.query(q.get(q.match(q.index("question_answer"),i["data"]["answer"])))
               score=score+1
           except:
               score=score
       context={"score":score,"question_count":question_count}
   return render(request,"answer_quiz.html",context)

In the code above, we made a query question_all to get the quiz matching the slug passed for the particular page. We then counted the number of questions in the query question_count and created pagination of the Question collection by matching the id of the page of the question_all query requested by the user. We then checked if the current page number is equal to the last page in our query. If this is the case, we render “finish” to the context which will allow the user to click on the finish button and end the quiz. We also checked if a POST request was received to make an answer submission. If a POST was received, we check if the user already answered that particular question by making a query to the FQL client using the answer_get_index index to match the username of the current user and the question answered in the Answers. If the user hasn’t answered this question, we create a query to create this answer in the Answers collection. The user selects the answer for the current question and clicks on the save answer button before proceeding to the next question. Lastly, we checked if the user clicked on the finish button by checking if “finish==true”. We set the original score in the test and created a query check_answer to the FQL client to get the answers matching the particular quiz answered using the answer_score_index index created earlier. We then ran a for loop to append all the data in check_answer into the all_answer list. At this point, we created a query using all_answer. We then looped through all_answer to check if the answers saved by the users match the correct_answer field for each data in the Question collection using the question_answer index. We cached this checking query with a “try-except” so that for each time both answers match without any errors the score increase by one (1). Otherwise, the score remains the same. This loop runs matches all answers provided by the user till the final score is obtained. We then render this score to the frontend using context for the user to see.

After picking an option for each question the user has to click on THE Save Answer button to save the answer to the database and click on Next to proceed to the next question.

As seen in the image above, after answering the last question, the user can save his answer and click on the Finish button to end the quiz and see his total score for that quiz.

The score will be displayed similar to the one in the image below.

Conclusion

In this article, we built a quiz application with Fauna's serverless database and Django. We saw how easy it is to integrate Fauna into a Python application and got the chance to explore some of its core features and functionalities.

The source code of our quiz application is available on Github. If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.

Building an Appointment Scheduler App with Django and Fauna

Chukslord — Thu, 11 Feb 2021 08:31:37 +0000

If you have ever wanted to build an appointment scheduling application with Django and have been looking to explore Fauna serverless database then this article is for you. Personally, I have always had problems with remembering meetings and appointments I have planned out for the day. I decided to build a solution to this problem, with the appointment scheduling application I can easily check appointments I have for the day.

To build this, I made use of the Django Python web framework and Fauna. Django is a very popular web framework, but not so many people know about Fauna and how amazing it can be when managing databases.

What is Fauna?

Fauna is a client-side serverless document database that makes use of GraphQL and the Fauna Query Language (FQL) to support a variety of data types and particularly relational databases in a serverless API. You can learn more about Fauna in their official documentation here.

Creating A Fauna Database

To create a Fauna database, you have to first sign up for an account. if you have not done that already. After signing up, you can now create a new database by clicking on the CREATE DATABASE button on the dashboard.

After clicking on the button as shown in the image above, you then need to give your database a name and save it.

You will also need to create two collections (one for users and one for events). A collection could be thought of as a table in a database system. Click on CREATE COLLECTION then fill in the required fields. You will need to enter the name for the collection, the History Days and TTL. The History Days is the number of days you want Fauna to retain a historical record of any data in that collection while the TTL is an expiry date for data in the collection. For example, if the TTL is set to 5, any data stored in the collection will automatically be deleted 5 days after its last modified date.

After saving the collection you just created, you will be presented with a screen similar to the image above. A document is relatable to rows of data in a table like a normal database system.

Creating a Fauna Index

We will need to create a Fauna index that allows us to scroll through data added to our database. To do this, go to the DB Overview tab on the left side of your screen then click on the New Index button.

After clicking on the New Index button you will be presented with the above screen. You are required to select the collection you want to connect this particular index to. After choosing the collection, enter a name for your index, terms for your index, and values. Also tick the Unique checkbox to ensure the data entered for the username is unique. The terms field is used to specify what data you want the index to be able to browse. In this case, we are using a username as the terms. Click on save once you are done filling the required fields. We also have to create three indexes; one with the date and time attributes as unique terms of the Events collection, the other with user attribute as terms, and the last with user and date as terms. The indexes should be created as seen below.

Integrating Fauna with Python

Creating a Fauna API Key

Designing Our Appointment Scheduler UI

Prerequisites

From this point onwards, to follow this step by step guide on building an appointment app with Django, you need to have the following installed:

Python 3.7 or >3.7
Faunadb
Django

Installing the requirements

To install Fauna and Django you simply need to run the below commands in your command-line interface.

pip install django  
pip install faunadb

To check if Django was successfully installed run the command below in the command-line interface. This will provide you with the version of Django installed on your system.

django-admin --version

To check if Fauna installed properly, run the sample python code provided in Fauna’s Python driver documentation.

from faunadb import query as q  
from faunadb.objects import Ref   
from faunadb.client import FaunaClient  

client = FaunaClient(secret="your-secret-here")  

indexes = client.query(q.paginate(q.indexes()))  

print(indexes)

If your result after running is similar to the image below, then you are good to go.

Creating the Django Project

To create the Django project you need to run the command below in the command-line interface.

#creating the django project

django-admin startproject AppointmentScheduler

#changing directory to the django project  
cd AppointmentScheduler

#creating a django app  
django-admin startapp App

The above command first creates a project AppointmentScheduler, then changes the directory to the project. In the project directory, the last command creates a new app called App.

The Django Project Structure

# changing directory to our app  
cd App  
# creating the static and templates folders  
mkdir templates  
mkdir static

The “App” folder should now look like the image below.

Now go back to our AppointmentScheduler directory and edit our settings.py file by adding our app to the list of installed apps as seen below.

INSTALLED_APPS = [  
 'django.contrib.admin',  
 'django.contrib.auth',  
 'django.contrib.contenttypes',  
 'django.contrib.sessions',  
 'django.contrib.messages',  
 'django.contrib.staticfiles',  
 'App',  
]

Inside the static and templates folders, we are going to create their respective files.

For the templates folder create a new file index.html and add the HTML code below.

{% load static %}

<html>  
 <head>  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  

 <!-- Optional theme -->  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">  

 <!-- Latest compiled and minified JavaScript -->  
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 Appointment Scheduler  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 Welcome {{user}}  
 </p>  
 <center>  
 <form class="form1">  
 {% if messages %}  
<ul class="messages">  
 {% for message in messages %}  
 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>  
 {% endfor %}  
</ul>  
{% endif %}  
 <a href="create-appointment"><button type="button" class="btn btn-rounded btn-primary" style="background-color:purple !important;">Create Appointment</button></a>  
 </form>  
 <form class="form1">  
 <a href="today-appointment"><button type="button" class="btn btn-rounded btn-primary" style="background-color:purple !important;">Appointments Today</button></a>  
 </form>  
 <form class="form1">  
 <a href="all-appointment"><button type="button" class="btn btn-rounded btn-primary" style="background-color:purple !important;">All Appointments</button></a>  
 </form>  
 </center>  
 </div>  
 </body>  
</html>

This index.html file is the dashboard page where a user can easily navigate between the site’s functions.

Next, create a login.html file, edit, and add the following HTML code as seen below.

{% load static %}  

<html>  
 <head>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 Appointment Scheduler  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 Hello 👋  
 </p>  
 <form class="form1" method="POST">  
 {% csrf_token %}  
 {% if messages %}  
<ul class="messages">  
 {% for message in messages %}  
 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>  
 {% endfor %}  
</ul>  
{% endif %}  
 <input align="center" class="un" placeholder="Username" name="username" type="text"/>  
 <input align="center" class="pass" placeholder="Password" name="password" type="password"/>  
 <button align="center" type="submit" class="submit">  
 Sign in  
 </button>  
 </form>  
 <p align="center" class="forgot">  
 <a href="register">  
 Don't have an account? Register  
 </a>  
 </p>  
 </div>  
 </body>  
</html>

The login.html page is the page where users can easily be authenticated before accessing the site’s functions. Users are required to input their username and password to log in.

The next page we are going to be creating is the register page.

Create a new file register.html, edit and add the HTML code as seen below.

{% load static %}  

<html>  
 <head>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 Appointment Scheduler  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 Create Account  
 </p>  
 <form class="form1" method="POST">  
 {% if messages %}  
<ul class="messages">  
 {% for message in messages %}  
 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>  
 {% endfor %}  
</ul>  
{% endif %}  
 {% csrf_token %}  
 <input align="center" class="un" placeholder="Username" name="username" type="text"/>  
 <input align="center" class="un" placeholder="email" name="email" type="email"/>  
 <input align="center" class="pass" placeholder="Password" name="password" type="password"/>  
 <button align="center" class="submit" type="submit">  
 Register  
 </button>  
 <center>  
 <a href="login">  
 Already have an account? Login  
 </a>  
 </center>  
 </form>  
 </div>  
 </body>  
</html>

The register.html page is where users can create new accounts to use for login authentication. Users are required to enter their username, email, and password.

The next page is the today-appointment.html page where users can view appointments they scheduled and are taking place that present day. Users can view all their and click on the “click to complete” button to complete their appointments for the day.

Create the “today-appointment” HTML file, edit, and add the following code as seen below.

{% load static %}  

<html>  
 <head>  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  

 <!-- Optional theme -->  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">  

 <!-- Latest compiled and minified JavaScript -->  
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 Appointment Scheduler  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 You have {{count}} Appointments Today  
 </p>  
 <center>  
 <div class="card" style="width: 18rem;">  
 <div class="card-body">  
 <h5 class="card-title">{{appointment.name}}</h5>  
 <p class="card-text">{{appointment.description}}</p>  
 <p class="card-text">Date: {{appointment.date}}</p>  
 <p class="card-text">Time:{{appointment.time}}</p>  
 </div>  
 {% if appointment.status == "True" %}  
 <a class="btn btn-primary">Completed</a>  
 {% else %}  
 <a href="?complete=''&page={{page_num}}" class="btn btn-danger">Click to complete</a>  
 {% endif %}  
</div>  
 <nav aria-label="...">  
 <ul class="pagination">  
 <li class="page-item disabled">  
 <a class="page-link" href="?page={{ prev_page }}" tabindex="-1">Previous</a>  
 </li>  
 <li class="page-item">  
 <a class="page-link" href="?page={{next_page }}">Next</a>  
 </li>  
 </ul>  
</nav>  
<a href="dashboard"> <button class="btn btn-danger btn-rounded" type="button">Back</button></a>  
<a href="?delete=''&page={{page_num}}"> <button class="btn btn-danger btn-rounded" type="button">Delete</button></a>  
</center>  
</center>  
 </div>  
 </body>  
</html>

The last HTML page we are going to be creating is the all-appointments.html page. This page is where users can view all the appointments they have created (both past and present). Users can also see the appointments they completed and those they didn’t complete. Create a new file all-appintments.html, edit and add the following code as seen below to it.

{% load static %}  

<html>  
 <head>  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  

 <!-- Optional theme -->  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">  

 <!-- Latest compiled and minified JavaScript -->  
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>  
 <link href="{% static 'login/style.css' %}" rel="stylesheet"/>  
 <link href="{% static 'https://fonts.googleapis.com/css?family=Ubuntu' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 <link href="{% static 'path/to/font-awesome/css/font-awesome.min.css' %}" rel="stylesheet"/>  
 <title>  
 Appointment Scheduler  
 </title>  
 </head>  
 <body>  
 <div class="main">  
 <p align="center" class="sign">  
 You have {{count}} Appointments in Total  
 </p>  
 <center>  
 <div class="card" style="width: 18rem;">  
 <div class="card-body">  
 <h5 class="card-title">{{appointment.name}}</h5>  
 <p class="card-text">{{appointment.description}}</p>  
 <p class="card-text">Date: {{appointment.date}}</p>  
 <p class="card-text">Time:{{appointment.time}}</p>  
 </div>  
</div>  
{% if appointment.status == "True" %}  
<a class="btn btn-primary">Completed</a>  
{% else %}  
<a class="btn btn-danger">Not Completed</a>  
{% endif %}  
 <nav aria-label="...">  
 <ul class="pagination">  
 <li class="page-item disabled">  
 <a class="page-link" href="?page={{ prev_page }}" tabindex="-1">Previous</a>  
 </li>  
 <li class="page-item">  
 <a class="page-link" href="?page={{next_page }}">Next</a>  
 </li>  
 </ul>  
</nav>  
<a href="dashboard"> <button class="btn btn-danger btn-rounded" type="button">Back</button></a>  
<a href="?delete=''&page={{page_num}}"> <button class="btn btn-danger btn-rounded" type="button">Delete</button></a>  
</center>  
 </div>  
 </body>  
</html>

We need to create a page where users can schedule/create appointments. To do this, create a new folder called appoint. In the appoint folder, create a new file create-appointment.html, edit, and add the following HTML code as seen below.

{% load static %}  

<!DOCTYPE html>  
<html lang="en">  
 <head>  
 <meta charset="utf-8"/>  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  

<!-- Optional theme -->  
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">  

<!-- Latest compiled and minified JavaScript -->  
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>  
 <title>  
 Appointment Scheduler  
 </title>  
 <link href="{% static 'appointment/style.css' %}" rel="stylesheet"/>  
 <meta content="width=device-width, initial-scale=1" name="viewport"/>  
 </head>  
 <body>  
 <!-- partial:index.partial.html -->  
 <script src="https://code.jquery.com/jquery-2.1.0.min.js">  
 </script>  
 <body>  
 <div id="formWrapper">  
 <div id="form">  
 <div class="logo">  
 <h2>Create an Appointment</h2>  
 </div>  
 <form method="POST">  
 {% if messages %}  
 <ul class="messages">  
 {% for message in messages %}  
 <li{% if message.tags %} class="{{ message.tags }}"{% endif %} style="color:red;">{{ message }}</li>  
 {% endfor %}  
 </ul>  
 {% endif %}  
 {% csrf_token %}  
 <div class="form-item">  
 <p class="formLabel">  
 Appointment Name  
 </p>  
 <input autocomplete="off" class="form-style" id="email" name="name" type="text" required/>  
 </div>  
 <div class="form-item">  
 <p class="formLabel">  
 Appointment Description  
 </p>  
 <input autocomplete="off" class="form-style" id="email" name="description" type="text" required/>  
 </div>  
 <div class="form-item">  
 <p class="formLabel">  
 Appointment Time  
 </p>  
 <input autocomplete="off" class="form-style" id="email" name="time" type="time" required/>  
 </div>  
 <div class="form-item">  
 <p class="formLabel">  
 Appointment Date  
 </p>  
 <input autocomplete="off" class="form-style" id="email" name="date" type="date" required/>  
 </div>  
 <div class="form-item">  
 <a href="dashboard"> <button class="btn btn-danger btn-rounded" type="button">Back</button></a>  
 <input class="login pull-right" type="submit" value="Create"/>  
 <div class="clear-fix">  
 </div>  
 </div>  
 </div>  
 </form>  
 </div>  
 </body>  
 </body>  
</html>  
<!-- partial -->  
<script src="{% static 'appointment/script.js' %}">  
</script>

We are done creating the HTML files needed. Now we need to create our CSS files. Navigate back to the static folder, create two new folders in it; appointment and login folders.

In the appointment folder, create a new file script.js then add the Javascript code as seen in the GitHub gist link here. Create another file style.css and add the CSS code as seen in the GitHub gist link here. Now navigate to the “login” folder, create a new file style.css, edit and add the following CSS code to it as seen in the GitHub gist link here.

We are done with the static and templates folders. Let’s now edit the urls.py and the views.py file. In the urls.py file, copy and add the following python code as seen below.

from django.conf import settings  
from django.conf.urls.static import static  
from django.urls import path, include  
from . import views  

app_name = "App"  

urlpatterns = [  
 path("", views.login, name="login"),  
 path("login", views.login, name="login"),  
 path("dashboard", views.dashboard, name="dashboard"),  
 path("create-appointment", views.create_appointment, name="create-appointment"),  
 path("today-appointment", views.today_appointment, name="today-appointment"),  
 path("all-appointment", views.all_appointment, name="all-appointment"),  
 path("register", views.register, name="register"),  

]

# Create your views here.  
from django.shortcuts import render  

def login(request):  
 return render(request,"login.html")  

def create_appointment(request):  
 return render(request,"appoint/create-appointment.html")  



def dashboard(request):  
 return render(request,"index.html")  


def today_appointment(request):  
 return render(request,"today-appointment.html")  


def all_appointment(request):   
 return render(request,"all-appointment.html")  

def register(request):  
 return render(request,"register.html")

In the views.py file we imported all the required Django modules and defined all the view functions while rendered their respective HTML pages which we are going to be adding functionalities to later in this walkthrough.

Navigate back to the base directory and open the AppointmentScheduler folder as seen in the image below.

Open and edit the urls.py file and add the following python code as seen below.

from django.contrib import admin  
from django.urls import path, include  
from django.conf import settings  
from django.contrib.staticfiles.urls import staticfiles_urlpatterns  
from django.contrib.staticfiles.urls import static  

urlpatterns = [  
 path('admin/', admin.site.urls),  
 path('', include("App.urls")),  

]

In the base project’s urls.py we imported the modules required and linked the app URLs to our base path URLs.

Building Our Appointment Scheduler Logic

We would be going through all the steps to build the functionality of our appointment scheduling application.

Importing Required Modules

Edit and import the following modules we will need for our project as seen in the python code below.

# Create your views here.  
from django.shortcuts import render,redirect  
from django.contrib import messages  
from django.core.paginator import Paginator  
from django.http import HttpResponseNotFound  
from faunadb import query as q  
import pytz  
from faunadb.objects import Ref  
from faunadb.client import FaunaClient  
import hashlib  
import datetime

Initializing FQL Client

We will now initialize the FQL client which we will need to access our database from time to time within this project. To initialize the FQL client copy and paste the code below immediately after your imports above.

client = FaunaClient(secret="SECRET_KEY")  
indexes = client.query(q.paginate(q.indexes()))

Make sure to replace SECRET_KEY with your real secret key you stored safely earlier.

Register Functionality

The first action the user takes when opening the web app is to register if they haven't already. Update the register function in the views.py with the python code below.

def register(request):
    if request.method == "POST":
        username = request.POST.get("username").strip().lower()
        email = request.POST.get("email").strip().lower()
        password = request.POST.get("password")

        try:
            user = client.query(q.get(q.match(q.index("users_index"), username)))
            messages.add_message(request, messages.INFO, 'User already exists with that username.')
            return redirect("App:register")
        except:
            user = client.query(q.create(q.collection("users"), {
                "data": {
                    "username": username,
                    "email": email,
                    "password": hashlib.sha512(password.encode()).hexdigest(),
                    "date": datetime.datetime.now(pytz.UTC)
                }
            }))
            messages.add_message(request, messages.INFO, 'Registration successful.')
            return redirect("App:login")
    return render(request,"register.html")

In the register view function, we first checked if the request sent from the register page is a POST request else we simply render the register page. If this was the case, then we are to expect some values (i.e the username, email, and password) in the POST request. We then make a request to Fauna with the get method of the FQL client and the users_index we created earlier to check the users collection if a user with the match of the username already exists. If the user exists then a “User already exists with that username” message is displayed. If the user does not exist, a request is made with the FQL client to Fauna to create a document with provided details along with the date of registration. The user is now redirected to the login page to log in with his/her newly created login details. If you check the users collection you should see the data added as seen below.

On unsuccessful registration, a message as seen in the image below should be displayed.

Login Functionality

def login(request):
    if request.method == "POST":
        username = request.POST.get("username").strip().lower()
        password = request.POST.get("password")

        try:
            user = client.query(q.get(q.match(q.index("users_index"), username)))
            if hashlib.sha512(password.encode()).hexdigest() == user["data"]["password"]:
                request.session["user"] = {
                    "id": user["ref"].id(),
                    "username": user["data"]["username"]
                }
                return redirect("App:dashboard")
            else:
                raise Exception()
        except:
            messages.add_message(request, messages.INFO,"You have supplied invalid login credentials, please try again!", "danger")
            return redirect("App:login")
    return render(request,"login.html")

If the login was unsuccessful you should get a result as seen in the image below.

On successful login, you should be redirected to the dashboard page as seen in the image below.

Creating Appointment Functionality

Let’s now give our create-appointment page the functionality to schedule new appointments. To do this copy the python code below and update the create_appointment function in your views.py.

def create_appointment(request):
    if "user" in request.session:
        if request.method=="POST":
            name=request.POST.get("name")
            description=request.POST.get("description")
            time=request.POST.get("time")
            date=request.POST.get("date")
            try:
                user = client.query(q.get(q.match(q.index("events_index"), date,time)))
                messages.add_message(request, messages.INFO, 'An Event is already scheduled for the specified time.')
                return redirect("App:create-appointment")
            except:
                user = client.query(q.create(q.collection("Events"), {
                    "data": {
                        "name": name,
                        "description": description,
                        "time": time,
                        "date": date,
                        "user": request.session["user"]["username"],
                        "status": 'False',
                    }
                }))
                messages.add_message(request, messages.INFO, 'Appointment Scheduled Successfully.')
                return redirect("App:create-appointment")
        return render(request,"appoint/create-appointment.html")
    else:
        return HttpResponseNotFound("Page not found")

In the create_appointment view we first checked if the user is logged in by checking if his/her data is stored in the session. If the user is not logged in then this page cannot be accessed. We then check if a POST request was sent. If this is the case, then the data sent along in the POST request (i.e name, description, time, date) is collected. A request with the FQL client is then sent to Fauna to check if the date and time of the event to be scheduled is already existing. If they exist, then an “An Event is already scheduled for the specified time” message is displayed. If the date and time are not existing in the database, then a request with the FQL client is made to create the appointment with provided details.

If the appointment is scheduled successfully you should see an image like the one below.

Also if you check the Events collection you should see some new data as in the image below.

View Upcoming Appointments Functionality

We need to allow users to see their appointments scheduled for the current day. To do this, in the today_appointment function update the python code as seen below.

def today_appointment(request):
   if "user" in request.session:
       appointments=client.query(q.paginate(q.match(q.index("events_today_paginate"), request.session["user"]["username"],str(datetime.date.today()))))["data"]
       appointments_count=len(appointments)
       page_number = int(request.GET.get('page', 1))
       appointment = client.query(q.get(q.ref(q.collection("Events"), appointments[page_number-1].id())))["data"]
       context={"count":appointments_count,"appointment":appointment,"page_num":page_number, "next_page": min(appointments_count, page_number + 1), "prev_page": max(1, page_number - 1)}
       return render(request,"today-appointment.html",context)
   else:
       return HttpResponseNotFound("Page not found")

In the today_appointment view, we first checked if the user is logged in by making sure the data is stored in the session. If the user is not logged in then this page cannot be accessed. We then create a query appointments by making a request with the paginate method of the FQL client to Fauna to paginate and match the data to the username of the user and the current date stored in the Events collection using the events_today_paginate index we created earlier.

We then create a variable appointments_count to count the number of documents in the appointments query above and pass it as context to the frontend. To scroll through all the data, a GET request is sent with the page number. This page number is collected and the default is set to 1 (in case no page number is sent) and used to get the documents in the query. To do this we create a variable appointment and assign it to a query created by making a request with the get method of the FQL client to the Events collection and the id of the current document in the appointments query we created above. The appointment, page_num, next_page, and prev_page are all sent to the context to be rendered on the frontend. The prev_page is created by getting the maximum between 1 and the current page number to avoid going out of the index. While the next_page is created by getting the minimum between 1 and the current page number.

Completing and Deleting Appointments

To complete an event scheduled for the current day, a GET request is sent by clicking on the complete button in the template. When this request is sent the query for the current page appointment is retrieved and updated using the update method of the FQL client to change the status in the document to true. For deleting appointments, a GET request is sent by clicking on the delete button in the template. When this request is sent the query for the current page appointment is retrieved and deleted using the delete method of the FQL client. To add these functionalities, add the following code to the today_appointment function as seen below.

def today_appointment(request):
   if "user" in request.session:
       appointments=client.query(q.paginate(q.match(q.index("events_today_paginate"), request.session["user"]["username"],str(datetime.date.today()))))["data"]
       appointments_count=len(appointments)
       page_number = int(request.GET.get('page', 1))
       appointment = client.query(q.get(q.ref(q.collection("Events"), appointments[page_number-1].id())))["data"]
       if request.GET.get("complete"):
           client.query(q.update(q.ref(q.collection("Events"), appointments[page_number-1].id()),{"data": {"status": "True"}}))["data"]
           return redirect("App:today-appointment")
       if request.GET.get("delete"):
           client.query(q.delete(q.ref(q.collection("Events"), appointments[page_number-1].id())))
           return redirect("App:today-appointment")
       context={"count":appointments_count,"appointment":appointment,"page_num":page_number, "next_page": min(appointments_count, page_number + 1), "prev_page": max(1, page_number - 1)}
       return render(request,"today-appointment.html",context)
   else:
       return HttpResponseNotFound("Page not found")

The today page should be displayed as seen in the image below.

If you click on the click to complete button you should have a result as seen in the image below.

Viewing All Appointments

We need to allow users to see all their appointments scheduled for the current day. To do this, in the all_appointment function update the python code as seen below.

def all_appointment(request):
   if "user" in request.session:
       appointments=client.query(q.paginate(q.match(q.index("events_index_paginate"), request.session["user"]["username"])))["data"]
       appointments_count=len(appointments)
       page_number = int(request.GET.get('page', 1))
       appointment = client.query(q.get(q.ref(q.collection("Events"), appointments[page_number-1].id())))["data"]
       if request.GET.get("delete"):
           client.query(q.delete(q.ref(q.collection("Events"), appointments[page_number-1].id())))
           return redirect("App:all-appointment")
       context={"count":appointments_count,"appointment":appointment, "next_page": min(appointments_count, page_number + 1), "prev_page": max(1, page_number - 1)}
       return render(request,"all-appointment.html",context)
   else:
       return HttpResponseNotFound("Page not found")

In the all_appointment view, we first checked if the user is logged in by confirming that data is stored in the session. If the user is not logged in then this page cannot be accessed. We then create a query appointments by making a request with the paginate method of the FQL client to Fauna to paginate and match the data to the username of the user stored in the Events collection using events_index_paginate index we created earlier. We then create a variable appointments_count to count the number of documents in the appointments query above and pass it as context to the frontend.

To scroll through all the data, a GET request is sent with the page number. This page number is collected and the default is set to 1 (in case no page number is sent), and used to get the documents in the query. To do this we create a variable appointment and assign it to a query created by making a request with the get method of the FQL client to the Events collection and the id of the current document in the appointments query we created above.

The appointment, page_num, next_page, and prev_page are all sent to the context to be rendered on the frontend. The prev_page is created by getting the maximum between 1 and the current page number to avoid going out of the index. While the next_page is created by getting the minimum between 1 and the current page number.

Your all-appointment page should look like the one in the image below.

Conclusion

In this article, we built an appointment scheduler application with Fauna's serverless database and Django. We saw how easy it is to integrate Fauna into a Python application and got the chance to explore some of its core features and functionalities.

The source code of our appointment scheduler application is available on Github. If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.