One place for hosting & domains

      Database

      How To Use Many-to-Many Database Relationships with Flask and SQLite


      The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      Flask is a framework for building web applications using the Python language, and SQLite is a database engine that you can use with Python to store application data. In this tutorial, you’ll modify an application built using Flask and SQLite by adding a many-to-many relationship to it.

      Although you can follow this tutorial independently, it is also a continuation of the How To Modify Items in a One-to-Many Database Relationships with Flask and SQLite tutorial in which we managed a multi-table database with a one-to-many relationship using a to-do application example. The application allows users to add new to-do items, categorize items under different lists, and modify items.

      A many-to-many database relationship is a relationship between two tables where a record in each table can reference several records in the other table. For example, in a blog, a table for posts can have a many-to-many relationship with a table for storing authors. Each post can reference many authors, and each author can reference many posts. Each post can have many authors, and each author can write many posts. Therefore, there is a many-to-many relationship between posts and authors. For another example, in a social media application, each post may have many hashtags, and each hashtag may have many posts.

      By the end of the tutorial, your application will have a new feature for assigning to-do items to different users. We will refer to the users that get assigned to-dos with the word assignees. For example, you can have a household to-do item for Cleaning the kitchen, which you can assign to both Sammy and Jo—each to-do can have many assignees (that is, Sammy and Jo). Also each user can have many to-dos assigned to them (that is, Sammy can be assigned multiple to-do items), this is a many-to-many relationship between to-do items and assignees.

      At the end of this tutorial, the application will include an Assigned to tag with the names of the assignees listed.

      Todo Application

      Prerequisites

      Before you start following this guide, you will need:

      Step 1 — Setting Up the Web Application

      In this step, you will set up the to-do application ready for modification. You will also review the database schema to understand the structure of the database. If you followed the tutorial in the prerequisites section and still have the code and the virtual environment on your local machine, you can skip this step.

      To demonstrate adding a many-to-many relationship to a Flask web application, you will use the previous tutorial’s application code, which is a to-do management web application built using Flask, SQLite, and the Bootstrap framework. With this application users can create new to-dos, modify and delete existing to-dos, and mark to-dos as complete.

      Clone the repository and rename it from flask-todo-2 to flask_todo with the following command:

      • git clone https://github.com/do-community/flask-todo-2 flask_todo

      Navigate to flask_todo:

      Then create a new virtual environment:

      Activate the environment:

      Install Flask:

      Then, initialize the database using the init_db.py program:

      Next, set the following environment variables:

      • export FLASK_APP=app
      • export FLASK_ENV=development

      FLASK_APP indicates the application you are currently developing, which is app.py in this case. FLASK_ENV specifies the mode—set it to development for development mode; this will allow you to debug the application. (Remember not to use this mode in a production environment.)

      Then run the development server:

      If you go to your browser, you’ll have the application running at the following URL: http://127.0.0.1:5000/.

      To stop the development server, use CTRL + C.

      Next, you will go through the database schema to understand the current relationships between tables. If you are familiar with the contents of the schema.sql file, you can skip to the next step.

      Open the schema.sql file:

      The file contents are as follows:

      flask_todo/schema.sql

      DROP TABLE IF EXISTS lists;
      DROP TABLE IF EXISTS items;
      
      CREATE TABLE lists (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          title TEXT NOT NULL
      );
      
      CREATE TABLE items (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          list_id INTEGER NOT NULL,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          content TEXT NOT NULL,
          done INTEGER NOT NULL DEFAULT 0,
          FOREIGN KEY (list_id) REFERENCES lists (id)
      );
      

      In the schema.sql file, you have two tables: lists for storing lists (such as Home or Study), and items for storing to-do items (such as Do the dishes or Learn Flask).

      The lists table has the following columns:

      • id: The ID of the list.
      • created: The list’s creation date.
      • title: The list’s title.

      The items table has the following columns:

      • id: The ID of the item.
      • list_id: The ID of the list the item belongs to.
      • created: The item’s creation date.
      • content: The item’s content.
      • done: The item’s state, the value 0 indicates the item has not been done yet, while 1 indicates item completion.

      In the items table you have a foreign key constraint, in which the list_id column references the id column of the lists parent table. This is a one-to-many relationship between items and lists, indicating that a list can have multiple items, and items belong to a single list:

      FOREIGN KEY (list_id) REFERENCES lists (id)
      

      In the next step, you will use a many-to-many relationship to create a link between two tables.

      Step 2 — Adding an Assignees Table

      In this step, you will review how to implement a many-to-many relationship and joins table. Then you’ll add a new table for storing assignees.

      A many-to-many relationship links two tables where each item in a table has many related items in the other table.

      Let’s say you have a simple table for to-do items as follows:

      Items
      +----+-------------------+
      | id | content           |
      +----+-------------------+
      | 1  | Buy eggs          |
      | 2  | Fix lighting      |
      | 3  | Paint the bedroom |
      +----+-------------------+
      

      And a table for assignees like so:

      assignees
      +----+------+
      | id | name |
      +----+------+
      | 1  | Sammy|
      | 2  | Jo   |
      +----+------+
      

      Let’s say you want to assign the to-do Fix lighting to both Sammy and Jo, you could do this by adding a new row in the items table like so:

      items
      +----+-------------------+-----------+
      | id | content           | assignees |
      +----+-------------------+-----------+
      | 1  | Buy eggs          |           |
      | 2  | Fix lighting      | 1, 2      |
      | 3  | Paint the bedroom |           |
      +----+-------------------+-----------+
      

      This is the wrong approach because each column should only have one value; if you have multiple values, basic operations such as adding and updating data become cumbersome and slow. Instead, there should be a third table that references primary keys of related tables—this table is often called a join table, and it stores IDs of each item from each table.

      Here is an example of a join table that links between items and assignees:

      item_assignees
      +----+---------+-------------+
      | id | item_id | assignee_id |
      +----+---------+-------------+
      | 1  | 2       | 1           |
      | 2  | 2       | 2           |
      +----+---------+-------------+
      

      In the first row, the item with the ID 2 (that is, Fix lighting) relates to the assignee with the ID 1 (Sammy). In the second row, the same item also relates to the assignee with the ID 2 (Jo). This means that the to-do item is assigned to both Sammy and Jo. Similarly, you can assign each assignee to multiple items.

      Now, you will modify the to-do application’s database to add a table for storing assignees.

      First, open schema.sql to add a new table named assignees:

      Add a line to delete the assignees table if it already exists. This is to avoid potential future issues when reinitiating the database, such as an already existing assignees table with different columns, which might break the code unexpectedly if it does not follow the same schema. You also add the SQL code for the table:

      flask_todo/schema.sql

      DROP TABLE IF EXISTS assignees;
      DROP TABLE IF EXISTS lists;
      DROP TABLE IF EXISTS items;
      
      CREATE TABLE lists (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          title TEXT NOT NULL
      );
      
      CREATE TABLE items (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          list_id INTEGER NOT NULL,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          content TEXT NOT NULL,
          done INTEGER NOT NULL DEFAULT 0,
          FOREIGN KEY (list_id) REFERENCES lists (id)
      );
      
      CREATE TABLE assignees (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL
      );
      

      Save and close the file.

      This new assignees table has the following columns:

      • id: The ID of the assignee.
      • name: The name of the assignee.

      Edit the init_db.py program to add a few assignees to the database. You use this program to initialize the database:

      Modify the file to look as follows:

      flask_todo/init_db.py

      import sqlite3
      
      connection = sqlite3.connect('database.db')
      
      with open('schema.sql') as f:
          connection.executescript(f.read())
      
      cur = connection.cursor()
      
      cur.execute("INSERT INTO lists (title) VALUES (?)", ('Work',))
      cur.execute("INSERT INTO lists (title) VALUES (?)", ('Home',))
      cur.execute("INSERT INTO lists (title) VALUES (?)", ('Study',))
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (1, 'Morning meeting')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (2, 'Buy fruit')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (2, 'Cook dinner')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (3, 'Learn Flask')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (3, 'Learn SQLite')
                  )
      
      cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Sammy',))
      cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Jo',))
      cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Charlie',))
      cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Ashley',))
      
      connection.commit()
      connection.close()
      

      Save and close the file.

      In the highlighted lines, you use the cursor object to execute an INSERT SQL statement to insert four names into the assignees table. You use the ? placeholder in the execute() method and pass a tuple containing the name of the assignee to safely insert data into the database. Then you commit the transaction with connection.commit() and close the connection using connection.close().

      This will add four assignees to the database, with the names Sammy, Jo, Charlie, and Ashley.

      Run the init_db.py program to reinitialize the database:

      You now have a table for storing assignees in the database. Next you will add a join table to create a many-to-many relationship between items and assignees.

      Step 3 — Adding a Many-to-Many Join Table

      In this step, you will use a join table to link to-do items with assignees. First you’ll edit your database schema file to add the new join table, edit the database initialization program to add a few assignments, then use a demonstration program to display the assignees of each to-do.

      Open schema.sql to add a new table:

      Because the table joins items and assignees, you will call it item_assignees. Add a line to delete the table if it already exists, then add the SQL code for the table itself:

      flask_todo/schema.sql

      DROP TABLE IF EXISTS assignees;
      DROP TABLE IF EXISTS lists;
      DROP TABLE IF EXISTS items;
      DROP TABLE IF EXISTS item_assignees;
      
      
      CREATE TABLE lists (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          title TEXT NOT NULL
      );
      
      CREATE TABLE items (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          list_id INTEGER NOT NULL,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          content TEXT NOT NULL,
          done INTEGER NOT NULL DEFAULT 0,
          FOREIGN KEY (list_id) REFERENCES lists (id)
      );
      
      CREATE TABLE assignees (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL
      );
      
      CREATE TABLE item_assignees (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          item_id INTEGER,
          assignee_id INTEGER,
          FOREIGN KEY(item_id) REFERENCES items(id),
          FOREIGN KEY(assignee_id) REFERENCES assignees(id)
      );
      

      Save and close the file.

      This new item_assignees table has the following columns:

      • id: The ID of the entry that establishes a relationship between to-dos and assignees; each row represents a relationship.
      • item_id: The ID of the to-do item that will be assigned to the assignee with the corresponding assignee_id.
      • assignee_id: The ID of the assignee who will get assigned the item with the corresponding item_id.

      The item_assignees table also has two foreign key constraints: one that links the item_id column with the id column of the items table, and another one linking between the assignee_id column with the id column of the assignees table.

      Open init_db.py to add a few assignments:

      Modify the file to look as follows:

      flask_todo/init_db.py

      import sqlite3
      
      connection = sqlite3.connect('database.db')
      
      
      with open('schema.sql') as f:
          connection.executescript(f.read())
      
      cur = connection.cursor()
      
      cur.execute("INSERT INTO lists (title) VALUES (?)", ('Work',))
      cur.execute("INSERT INTO lists (title) VALUES (?)", ('Home',))
      cur.execute("INSERT INTO lists (title) VALUES (?)", ('Study',))
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (1, 'Morning meeting')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (2, 'Buy fruit')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (2, 'Cook dinner')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (3, 'Learn Flask')
                  )
      
      cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
                  (3, 'Learn SQLite')
                  )
      
      cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Sammy',))
      cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Jo',))
      cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Charlie',))
      cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Ashley',))
      
      # Assign "Morning meeting" to "Sammy"
      cur.execute("INSERT INTO item_assignees (item_id, assignee_id) VALUES (?, ?)",
                  (1, 1))
      
      # Assign "Morning meeting" to "Jo"
      cur.execute("INSERT INTO item_assignees (item_id, assignee_id) VALUES (?, ?)",
                  (1, 2))
      
      # Assign "Morning meeting" to "Ashley"
      cur.execute("INSERT INTO item_assignees (item_id, assignee_id) VALUES (?, ?)",
                  (1, 4))
      
      # Assign "Buy fruit" to "Sammy"
      cur.execute("INSERT INTO item_assignees (item_id, assignee_id) VALUES (?, ?)",
                  (2, 1))
      
      connection.commit()
      connection.close()
      

      In the highlighted code, you assign to-do items to assignees by inserting into the item_assignees join table. You insert the item_id of the to-do item you want to assign to the assignee with the ID corresponding to the assignee_id value. In the first highlighted line, you assign the to-do item Morning meeting, which has an ID of 1, to the assignee Sammy, who has an ID of 1. The rest of the lines follow the same pattern. Once again, you use the ? placeholders to safely pass the values you want to insert in a tuple to the cur.execute() method.

      Save and close the file.

      Run the init_db.py program to reinitialize the database:

      Run the list_example.py program that displays the to-do items you have on the database:

      Here is the output:

      Output

      Home Buy fruit | id: 2 | done: 0 Cook dinner | id: 3 | done: 0 Study Learn Flask | id: 4 | done: 0 Learn SQLite | id: 5 | done: 0 Work Morning meeting | id: 1 | done: 0

      This displays the to-do items under the lists they belong to. You have each item’s content, its ID, and whether it’s completed or not (0 means the item is not completed yet, and 1 means it’s completed). You now need to display the assignees of each to-do.

      Open list_example.py to modify it to display item assignees:

      Modify the file to look as follows:

      flask_todo/list_example.py

      from itertools import groupby
      from app import get_db_connection
      
      conn = get_db_connection()
      todos = conn.execute('SELECT i.id, i.done, i.content, l.title 
                            FROM items i JOIN lists l 
                            ON i.list_id = l.id ORDER BY l.title;').fetchall()
      
      lists = {}
      
      for k, g in groupby(todos, key=lambda t: t['title']):
          # Create an empty list for items
          items = []
          # Go through each to-do item row in the groupby() grouper object
          for item in g:
              # Get the assignees of the current to-do item
              assignees = conn.execute('SELECT a.id, a.name FROM assignees a 
                                        JOIN item_assignees i_a 
                                        ON a.id = i_a.assignee_id 
                                        WHERE i_a.item_id = ?',
                                        (item['id'],)).fetchall()
              # Convert the item row into a dictionary to add assignees
              item = dict(item)
              item['assignees'] = assignees
      
              items.append(item)
      
          # Build the list of dictionaries
          # the list's name (ex: Home/Study/Work) as the key
      
          # and a list of dictionaries of to-do items
          # belonging to that list as the value
          lists[k] = list(items)
      
      
      for list_, items in lists.items():
          print(list_)
          for item in items:
              assignee_names=", ".join(a['name'] for a in item['assignees'])
      
              print('    ', item['content'], '| id:',
                    item['id'], '| done:', item['done'],
                    '| assignees:', assignee_names)
      
      

      Save and close the file.

      You use the groupby() function to group to-do items by the title of the list they belong to. (See Step 2 of How To Use One-to-Many Database Relationships with Flask and SQLite for more information.) While going through the grouping process, you create an empty list called items, which will hold all of the to-do item data, such as the item’s ID, content, and assignees. Next, in the for item in g loop, you go through each to-do item, get the assignees of the item, and save it in the assignees variable.

      The assignees variable holds the result of a SELECT SQL query. This query gets the assignee’s id (a.id) and the assignee’s name (a.name) from the assignees table (which is aliased to a to shorten the query). The query joings the id and name with the item_assignees join table (aliased to i_a) on the condition a.id = i_a.assignee_id where the i_a.item_id value equals that of the current item’s ID (item['id']). Then you use the fetchall() method to get the results as a list.

      With the line item = dict(item), you convert the item into a dictionary because a regular sqlite3.Row object does not support assignment, which you will need to add assignees to the item. Next, with the line item['assignees'] = assignees, you add a new key 'assignees' to the item dictionary to access the item’s assignees directly from the item’s dictionary. Then you append the modified item to the items list. You build the list of dictionaries that will hold all of the data; each dictionary key is the to-do list’s title, and its value is a list of all the items that belong to it.

      To print the results, you use the for list_, items in lists.items() loop to go through each to-do list title and the to-do items that belong to it, you print the list’s title (list_), then loop through the to-do items of the list. You added a variable named assignee_names, the value of which uses the join() method to join between the items of the generator expression a['name'] for a in item['assignees'], which extracts the assignee’s name (a['name']), from the data of each assignee in the item['assignees'] list. This joined list of assignee names, you then print with the rest of the to-do item’s data in the print() function.

      Run the list_example.py program:

      Here is the output (with assignees highlighted):

      Output

      Home Buy fruit | id: 2 | done: 0 | assignees: Sammy Cook dinner | id: 3 | done: 0 | assignees: Study Learn Flask | id: 4 | done: 0 | assignees: Learn SQLite | id: 5 | done: 0 | assignees: Work Morning meeting | id: 1 | done: 0 | assignees: Sammy, Jo, Ashley

      You can now display the assignees of each to-do item with the rest of the data.

      You have now displayed the assignee names of each to-do item. Next, you will use this to display the names below each to-do item in the web application’s index page.

      Step 4 — Displaying Assignees in the Index Page

      In this step, you’ll modify the index page of the to-do management application to show the assignees of each to-do item. You will first edit the app.py file, which contains the code for the Flask application, then edit the index.html template file to display the assignees below each to-do item on the index page.

      First, open app.py to edit the index() view function:

      Modify the function to look as follows:

      flask_todo/app.py

      @app.route('/')
      def index():
          conn = get_db_connection()
          todos = conn.execute('SELECT i.id, i.done, i.content, l.title 
                                FROM items i JOIN lists l 
                                ON i.list_id = l.id ORDER BY l.title;').fetchall()
      
          lists = {}
      
          for k, g in groupby(todos, key=lambda t: t['title']):
              # Create an empty list for items
              items = []
              # Go through each to-do item row in the groupby() grouper object
              for item in g:
                  # Get the assignees of the current to-do item
                  assignees = conn.execute('SELECT a.id, a.name FROM assignees a 
                                          JOIN item_assignees i_a 
                                          ON a.id = i_a.assignee_id 
                                          WHERE i_a.item_id = ?',
                                          (item['id'],)).fetchall()
                  # Convert the item row into a dictionary to add assignees
                  item = dict(item)
                  item['assignees'] = assignees
      
                  items.append(item)
      
              # Build the list of dictionaries
              # the list's name (ex: Home/Study/Work) as the key
      
              # and a list of dictionaries of to-do items
              # belonging to that list as the value
              lists[k] = list(items)
      
          conn.close()
          return render_template('index.html', lists=lists)
      

      Save and close the file.

      This is the same code you used in the list_example.py demonstration program in Step 3. With this, the lists variable will contain all the data you need, including assignee data, which you will use to access assignee names in the index.html template file.

      Open the index.html file to add assignee names following each item:

      • nano templates/index.html

      Modify the file to look as follows:

      flask_todo/templates/index.html

      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} Welcome to FlaskTodo {% endblock %}</h1>
          {% for list, items in lists.items() %}
              <div class="card" style="width: 18rem; margin-bottom: 50px;">
                  <div class="card-header">
                      <h3>{{ list }}</h3>
                  </div>
                  <ul class="list-group list-group-flush">
                      {% for item in items %}
                          <li class="list-group-item"
                          {% if item['done'] %}
                          style="text-decoration: line-through;"
                          {% endif %}
                          >{{ item['content'] }}
                          {% if not item ['done'] %}
                              {% set URL = 'do' %}
                              {% set BUTTON = 'Do' %}
                          {% else %}
                              {% set URL = 'undo' %}
                              {% set BUTTON = 'Undo' %}
                          {% endif %}
                          <div class="row">
                              <div class="col-12 col-md-3">
                                  <form action="{{ url_for(URL, id=item['id']) }}"
                                      method="POST">
                                      <input type="submit" value="{{ BUTTON }}"
                                          class="btn btn-success btn-sm">
                                  </form>
                              </div>
      
                              <div class="col-12 col-md-3">
                                  <a class="btn btn-warning btn-sm"
                                  href="https://www.digitalocean.com/community/tutorials/{{ url_for("edit', id=item['id']) }}">Edit</a>
                              </div>
      
                              <div class="col-12 col-md-3">
                                  <form action="https://www.digitalocean.com/community/tutorials/{{ url_for("delete', id=item['id']) }}"
                                      method="POST">
                                      <input type="submit" value="Delete"
                                          class="btn btn-danger btn-sm">
                                  </form>
                              </div>
                          </div>
      
                          <hr>
                          {% if item['assignees'] %}
                              <span style="color: #6a6a6a">Assigned to</span>
                              {% for assignee in item['assignees'] %}
                                  <span class="badge badge-primary">
                                      {{ assignee['name'] }}
                                  </span>
                              {% endfor %}
                          {% endif %}
      
                          </li>
                      {% endfor %}
                  </ul>
              </div>
          {% endfor %}
      {% endblock %}
      

      Save and close the file.

      With this modification, you added a line break below each item using the <hr> tag. If the item has any assignees (which you know via the statement if item['assignees']), you display a gray Assigned to text and loop through the item assignees (that is, the item['assignees'] list), and display the assignee name (assignee['name']) in a badge.

      Finally, run the development server:

      Then visit the index page: http://127.0.0.1:5000/.

      Each to-do item can now have many assignees, and you can assign each assignee multiple to-dos. The index page displays all of the items and the assignees of each item.

      Todo Application

      You can access the final code from this repository.

      Conclusion

      In this tutorial, you have learned what a many-to-many relationship is, how to use it in a Flask and SQLite web application, how to join between tables, and how to group relational data in Python.

      You now have a complete to-do application in which users can create new to-do items, mark an item as complete, edit or delete existing items, and create new lists. And each item can be assigned to different assignees.

      To learn more about web development with Python and Flask see these Flask tutorials.



      Source link

      How To Import and Export a MongoDB Database on Ubuntu 20.04


      The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      MongoDB is one of the most popular NoSQL database engines. It is famous for being scalable, powerful, reliable and easy to use. In this article we’ll show you how to import and export your MongoDB databases.

      We should make clear that by import and export we mean those operations that deal with data in a human-readable format, compatible with other software products. By contrast, the backup and restore operations create or use MongoDB specific binary data, which preserve the consistency and integrity of your data and also its specific MongoDB attributes. Thus, for migration it’s usually preferable to use backup and restore, as long as the source and target systems are compatible.

      Backup, restore, and migration tasks are beyond the scope of this article. For more information refer to How To Back Up, Restore, and Migrate a MongoDB Database on Ubuntu 20.04.

      Prerequisites

      To complete this tutorial you will need the following:

      Step One — Importing Information Into MongoDB

      To learn how importing information into MongoDB works let’s use a popular sample MongoDB database about restaurants. It’s in .json format and can be downloaded using wget like this:

      • wget https://raw.githubusercontent.com/mongodb/docs-assets/primer-dataset/primer-dataset.json

      Once the download completes you should have a file called primer-dataset.json (12 MB size) in the current directory. Let’s import the data from this file into a new database called newdb and into a collection called restaurants.

      Use the mongoimport command like this:

      • sudo mongoimport --db newdb --collection restaurants --file primer-dataset.json

      The result will look like this:

      Output

      2020-11-11T19:37:55.607+0000 connected to: mongodb://localhost/ 2020-11-11T19:37:57.841+0000 25359 document(s) imported successfully. 0 document(s) failed to import

      As the above command shows, 25359 documents have been imported. Because we didn’t have a database called newdb, MongoDB created it automatically.

      Let’s verify the import.

      Connect to the newly created newdb database:

      You are now connected to the newdb database instance. Notice that your prompt has changed, indicating that you are connected to the database.

      Count the documents in the restaurants collection with the command:

      The result will show 25359, which is the number of imported documents. For an even better check you can select the first document from the restaurants collection like this:

      The result will look like this:

      [secondary label Output]
      {
          "_id" : ObjectId("5fac3d937f12c471b3f26733"),
          "address" : {
              "building" : "1007",
              "coord" : [
                  -73.856077,
                  40.848447
              ],
              "street" : "Morris Park Ave",
              "zipcode" : "10462"
          },
          "borough" : "Bronx",
          "cuisine" : "Bakery",
          "grades" : [
              {
                  "date" : ISODate("2014-03-03T00:00:00Z"),
                  "grade" : "A",
                  "score" : 2
              },
      ...
          ],
          "name" : "Morris Park Bake Shop",
          "restaurant_id" : "30075445"
      }
      

      Such a detailed check could reveal problems with the documents such as their content, encoding, etc. The json format uses UTF-8 encoding and your exports and imports should be in that encoding. Have this in mind if you edit manually the json files. Otherwise, MongoDB will automatically handle it for you.

      To exit the MongoDB prompt, type exit at the prompt:

      You will be returned to the normal command line prompt as your non-root user.

      Step Two — Exporting Information From MongoDB

      As we have previously mentioned, by exporting MongoDB information you can acquire a human readable text file with your data. By default, information is exported in json format but you can also export to csv (comma separated value).

      To export information from MongoDB, use the command mongoexport. It allows you to export a very fine-grained export so that you can specify a database, a collection, a field, and even use a query for the export.

      A simple mongoexport example would be to export the restaurants collection from the newdb database which we have previously imported. It can be done like this:

      • sudo mongoexport --db newdb -c restaurants --out newdbexport.json

      In the above command, we use --db to specify the database, -c for the collection and --out for the file in which the data will be saved.

      The output of a successful mongoexport should look like this:

      Output

      2020-11-11T19:39:57.595+0000 connected to: mongodb://localhost/ 2020-11-11T19:39:58.619+0000 [###############.........] newdb.restaurants 16000/25359 (63.1%) 2020-11-11T19:39:58.871+0000 [########################] newdb.restaurants 25359/25359 (100.0%) 2020-11-11T19:39:58.871+0000 exported 25359 records

      The above output shows that 25359 documents have been imported — the same number as of the imported ones.

      In some cases you might need to export only a part of your collection. Considering the structure and content of the restaurants json file, let’s export all the restaurants which satisfy the criteria to be situated in the Bronx borough and to have Chinese cuisine. If we want to get this information directly while connected to MongoDB, connect to the database again:

      Then, use this query:

      • db.restaurants.find( { "borough": "Bronx", "cuisine": "Chinese" } )

      The results are displayed to the terminal:

      Output

      • 2020-12-03T01:35:25.366+0000 connected to: mongodb://localhost/
      • 2020-12-03T01:35:25.410+0000 exported 323 records

      To exit the MongoDB prompt, type exit:

      If you want to export the data from a sudo command line instead of while connected to the database, make the previous query part of the mongoexport command by specifying it for the -q argument like this:

      • sudo mongoexport --db newdb -c restaurants -q "{"borough": "Bronx", "cuisine": "Chinese"}" --out Bronx_Chinese_retaurants.json

      Note that we are escaping the double quotes with backslash () in the query. Similarly, you have to escape any other special characters in the query.

      If the export has been successful, the result should look like this:

      Output

      2020-11-11T19:49:21.727+0000 connected to: mongodb://localhost/ 2020-11-11T19:49:21.765+0000 exported 323 records

      The above shows that 323 records have been exported, and you can find them in the Bronx_Chinese_retaurants.json file that we specified.

      Use cat and less to scan the data:

      • cat Bronx_Chinese_retaurants.json | less

      Use SPACE to page through the data:

      Output

      • date":{"$date":"2015-01-14T00:00:00Z"},"grade":"Z","score":36}],"na{"_id":{"$oid":"5fc8402d141f5e54f9054f8d"},"address":{"building":"1236","coord":[-73.8893654,40.81376179999999],"street":"238 Spofford Ave","zipcode":"10474"},"borough":"Bronx","cuisine":"Chinese","grades":[{"date":{"$date":"2013-12-30T00:00:00Z"},"grade":"A","score":8},{"date":{"$date":"2013-01-08T00:00:00Z"},"grade":"A","score":10},{"date":{"$date":"2012-06-12T00:00:00Z"},"grade":"B","score":15}],
      • . . .

      Press q to exit. You can now import and export a MongoDB database.

      Conclusion

      This article has introduced you to the essentials of importing and exporting information to and from a MongoDB database. You can continue further reading on How To Back Up, Restore, and Migrate a MongoDB Database on Ubuntu 20.04.

      You can also consider using replication. Replication allows you to continue running your MongoDB service uninterrupted from a slave MongoDB server while you are restoring the master one from a failure. Part of the replication is the operations log (oplog), which records all the operations that modify your data. You can use this log, just as you would use the binary log in MySQL, to restore your data after the last backup has taken place. Recall that backups usually take place during the night, and if you decide to restore a backup in the evening you will be missing all the updates since the last backup.



      Source link

      How To Back Up, Restore, and Migrate a MongoDB Database on Ubuntu 18.04


      The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      MongoDB is one of the most popular NoSQL database engines. It is famous for being scalable, robust, reliable, and easy to use. In this article, you will back up, restore, and migrate a sample MongoDB database.

      Importing and exporting a database means dealing with data in a human-readable format that is compatible with other software products. In contrast, MongoDB’s backup and restore operations create or use MongoDB-specific binary data, which preserves not only the consistency and integrity of your data but also its specific MongoDB attributes. Thus, for migration, it’s usually preferable to use backup and restore as long as the source and target systems are compatible.

      Prerequisites

      Before following this tutorial, please make sure you complete the following prerequisites:

      Except otherwise noted, all of the commands that require root privileges in this tutorial should be run as a non-root user with sudo privileges.

      Step 1 — Using JSON and BSON in MongoDB

      Before continuing further with this article, some basic understanding of the matter is needed. If you have experience with other NoSQL database systems such as Redis, you may find some similarities when working with MongoDB.

      MongoDB uses JSON and BSON (binary JSON) formats for storing its information. JSON is the human-readable format that is perfect for exporting and, eventually, importing your data. You can further manage your exported data with any tool that supports JSON, including a simple text editor.

      An example json document looks like this:

      Example of JSON Format

      {"address":[
          {"building":"1007", "street":"Park Ave"},
          {"building":"1008", "street":"New Ave"},
      ]}
      

      JSON is convenient to work with, but it does not support all the data types available in BSON. This means that there will be the so-called ‘loss of fidelity’ of the information if you use JSON. For backing up and restoring, it’s better to use the binary BSON.

      Second, you don’t have to worry about explicitly creating a MongoDB database. If the database you specify for import doesn’t already exist, it is automatically created. Even better is the case with the collections’ (database tables) structure. In contrast to other database engines, in MongoDB, the structure is again automatically created upon the first document (database row) insert.

      Third, in MongoDB, reading or inserting large amounts of data, such as this article’s tasks, can be resource-intensive and consume much of your CPU, memory, and disk space. This is critical considering that MongoDB is frequently used for large databases and Big Data. The simplest solution to this problem is to run the exports and backups during the night or non-peak hours.

      Fourth, information consistency could be problematic if you have a busy MongoDB server where the information changes during the database export or backup process. One possible solution for this problem is replication, which you may consider when you advance in the MongoDB topic.

      While you can use the import and export functions to backup and restore your data, there are better ways to ensure the full integrity of your MongoDB databases. To backup your data, you should use the command mongodump. For restoring, use mongorestore. Let’s see how they work.

      Step 2 — Using mongodump to Back Up a MongoDB Database

      Let’s cover backing up your MongoDB database first.

      An essential argument to mongodump is --db, which specifies the name of the database you want to back up. If you don’t specify a database name, mongodump backs up all of your databases. The second important argument is --out, which defines the directory into which the data will be dumped. For example, let’s back up the newdb database and storing it in the /var/backups/mongobackups directory. Ideally, we’ll have each of our backups in a directory with the current date like /var/backups/mongobackups/10-29-20.

      First create that directory /var/backups/mongobackups:

      • sudo mkdir /var/backups/mongobackups

      Then run mongodump:

      • sudo mongodump --db newdb --out /var/backups/mongobackups/`date +"%m-%d-%y"`

      You will see an output like this:

      Output

      2020-10-29T19:22:36.886+0000 writing newdb.restaurants to 2020-10-29T19:22:36.969+0000 done dumping newdb.restaurants (25359 documents)

      Note that in the above directory path, we have used date +"%m-%d-%y" which automatically gets the current date. This will allow us to have backups inside the directory like /var/backups/10-29-20/. This is especially convenient when we automate the backups.

      At this point you have a complete backup of the newdb database in the directory /var/backups/mongobackups/10-29-20/newdb/. This backup has everything to restore the newdb properly and preserve its so-called “fidelity.”

      As a general rule, you should make regular backups and preferably when the server is least loaded. Thus, you can set the mongodump command as a cron job so that it runs regularly, e.g., every day at 03:03 AM.

      To accomplish this open crontab, cron’s editor:

      Note that when you run sudo crontab, you will be editing the cron jobs for the root user. This is recommended because if you set the crons for your user, they might not execute properly, especially if your sudo profile requires password verification.

      Inside the crontab prompt, insert the following mongodump command:

      crontab

      3 3 * * * mongodump --out /var/backups/mongobackups/`date +"%m-%d-%y"`
      

      In the above command, we omit the --db argument on purpose because you will typically want to have all of your databases backed up.

      Depending on your MongoDB database sizes, you may soon run out of disk space with too many backups. That’s why it’s also recommended to clean the old backups regularly or to compress them.

      For example, to delete all the backups older than seven days, you can use the following bash command:

      • find /var/backups/mongobackups/ -mtime +7 -exec rm -rf {} ;

      Similarly to the previous mongodump command, you can also add this as a cron job. It should run just before you start the next backup, e.g., at 03:01 AM. For this purpose, open crontab again:

      After that insert the following line:

      crontab

      3 1 * * * find /var/backups/mongobackups/ -mtime +7 -exec rm -rf {} ;
      

      save and close the file.

      Completing all the tasks in this step will ensure a proper backup solution for your MongoDB databases.

      Step 3 — Using mongorestore to Restore and Migrate a MongoDB Database

      When you restore your MongoDB database from a previous backup, you have the exact copy of your MongoDB information taken at a particular time, including all the indexes and data types. This is especially useful when you want to migrate your MongoDB databases. For restoring MongoDB, we’ll use the command mongorestore, which works with the binary backups that mongodump produces.

      Let’s continue our examples with the newdb database and restore it from the previously taken backup. As arguments, we’ll specify first the name of the database with the --db argument. Then with --drop, we’ll make sure that the target database is first dropped so that the backup is restored in a clean database. As a final argument we’ll specify the directory of the last backup, which will look something like this: /var/backups/mongobackups/10-29-20/newdb/.

      Once you have a timestamped backup, you can restore it using this command:

      • sudo mongorestore --db newdb --drop /var/backups/mongobackups/10-29-20/newdb/

      You will see an output like this:

      Output

      2020-10-29T19:25:45.825+0000 the --db and --collection args should only be used when restoring from a BSON file. Other uses are deprecated and will not exist in the future; use --nsInclude instead 2020-10-29T19:25:45.826+0000 building a list of collections to restore from /var/backups/mongobackups/10-29-20/newdb dir 2020-10-29T19:25:45.829+0000 reading metadata for newdb.restaurants from /var/backups/mongobackups/10-29-20/newdb/restaurants.metadata.json 2020-10-29T19:25:45.834+0000 restoring newdb.restaurants from /var/backups/mongobackups/10-29-20/newdb/restaurants.bson 2020-10-29T19:25:46.130+0000 no indexes to restore 2020-10-29T19:25:46.130+0000 finished restoring newdb.restaurants (25359 documents) 2020-10-29T19:25:46.130+0000 done

      In the above case, we are restoring the data on the same server where we created the backup. If you wish to migrate the data to another server and use the same technique, you should copy the backup directory, which is /var/backups/mongobackups/10-29-20/newdb/ in our case, to the other server.

      Conclusion

      You have now performed some essential tasks related to backing up, restoring, and migrating your MongoDB databases. No production MongoDB server should ever run without a reliable backup strategy, such as the one described here.



      Source link