One place for hosting & domains

      SQLite

      How To Use Python-Markdown 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. With Flask, you can use SQLite as your database engine to store application data.

      Markdown is a markup language commonly used for the process of writing content in an easy-to-read text format. Using Markdown, you can format plain text with features such as headings, links, and images, you can then convert the text into HTML, which includes these formatting features. To learn how to use Markdown, check out the Markdown syntax standard.

      Python-Markdown is a Python library that allows you to convert Markdown text to HTML; it mostly follows the Markdown standard, with a few minor differences to the standard Markdown syntax.

      In this tutorial, you’ll use Flask, SQLite, and Python-Markdown to build a small note-taking web application that supports formatting text using Markdown. The app will allow users to display, create, and format notes with headings, links, lists, images, and other features. You’ll use the Bootstrap toolkit to style your application.

      Prerequisites

      Step 1 — Setting Up Dependencies

      In this step, you will activate your Python environment and install Flask and Python-Markdown using the pip package installer. Then you’ll create the database you will use to store notes and add some sample data to it.

      First, activate your programming environment if you haven’t already:

      Once you have activated your programming environment, install Flask and the Python-Markdown library using the following command:

      • pip install flask markdown

      Then create a database schema file called schema.sql, which will contain SQL commands to create a notes table. Open the file inside your flask_notes directory:

      Type the following SQL commands inside this file:

      flask_notes/schema.sql

      DROP TABLE IF EXISTS notes;
      
      CREATE TABLE notes (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          content TEXT NOT NULL
      );
      

      In this SQL file, you first delete any already existing table named notes, which could cause problems. This ensures that your notes table is exactly as described in this SQL. Note that this will delete all of the content you have in the database whenever you use these SQL commands, so ensure you don’t write any important content in the web application until you finish this tutorial and experiment with the final result.

      You use the CREATE TABLE notes statement to create the notes table with the following columns:

      • id: An integer that represents a primary key; the database will assign this a unique value for each entry (a note).
      • created: The note’s creation date; it will be automatically filled in with the time at which the note was added to the database.
      • content: The note’s content.

      Save and close the file.

      To create the database using the schema.sql file, open a file named init_db.py inside the flask_notes directory:

      Then add the following code:

      flask_notes/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 notes (content) VALUES (?)", ('# The First Note',))
      cur.execute("INSERT INTO notes (content) VALUES (?)", ('_Another note_',))
      cur.execute("INSERT INTO notes (content) VALUES (?)", ('Visit [this page](https://www.digitalocean.com/community/tutorials) for more tutorials.',))
      
      connection.commit()
      connection.close()
      

      Here, you first import the sqlite3 module. Next, you connect to a file called database.db that will be created once you execute this program. database.db is the database that will hold all your application’s data. You then open the schema.sql file and run it using the executescript() method that executes multiple SQL statements at once. This will create the notes table.

      Using a Cursor object, you execute a few INSERT SQL statements to create three notes. You’re using Markdown syntax here: the first note is an <h1> heading, the second note is in italic, and the third note contains a link. You use the ? placeholder in the execute() method and pass a tuple containing the note’s content to safely insert data into the database.

      Finally, you commit the changes and close the connection.

      Save and close the file.

      Run the program:

      After execution, a new file called database.db will appear in your flask_notes directory.

      You’ve activated your environment, installed Flask and Python-Markdown, and created the SQLite database. Next, you’ll retrieve the Markdown notes from the database, convert them to HTML, and display them on the application’s homepage.

      Step 2 — Displaying the Notes on the Index Page

      In this step, you will create a Flask application that connects to the database and displays the sample data you have in the database. You will convert the Markdown text in the database to HTML and then render it on the index page.

      First, create the app.py application file inside your flask_notes directory:

      Add the following code to it:

      flask_notes/app.py

      import sqlite3
      import markdown
      from flask import Flask, render_template, request, flash, redirect, url_for
      
      def get_db_connection():
          conn = sqlite3.connect('database.db')
          conn.row_factory = sqlite3.Row
          return conn
      

      You first import the sqlite3 module, the markdown package, and Flask helpers.

      The get_db_connection() function opens a connection to the database.db database file and then sets the row_factory attribute to sqlite3.Row. This gives you name-based access to columns, which means that the database connection will return rows that behave like regular Python dictionaries. Lastly, the function returns the conn connection object you’ll be using to access the database.

      Following that, add the next snippet of code:

      flask_notes/app.py

      #. . .
      
      app = Flask(__name__)
      app.config['SECRET_KEY'] = 'this should be a secret random string'
      

      Here, you create the Flask application object and set a secret key to secure sessions.

      Then add the following code:

      flask_notes/app.py

      #. . .
      
      @app.route('/')
      def index():
          conn = get_db_connection()
          db_notes = conn.execute('SELECT id, created, content FROM notes;').fetchall()
          conn.close()
      
          notes = []
          for note in db_notes:
             note = dict(note)
             note['content'] = markdown.markdown(note['content'])
             notes.append(note)
      
          return render_template('index.html', notes=notes)
      

      The index() function is a Flask view function, which is a function decorated using the special @app.route decorator. Flask converts this function’s return value into an HTTP response that an HTTP client, such as a web browser, will display.

      In the index() view function, you open a database connection and you execute a SELECT SQL statement to fetch the ID, the creation date, and the content for all rows of the notes table. You use the fetchall() method to get a list of all the rows and save this data in the db_notes variable. Then, you close the connection.

      To convert the content of the notes from Markdown to HTML, you create a new empty list called notes. You loop through the db_notes list and convert each note from an sqlite3.Row to a regular Python dictionary using the dict() Python function to allow assignment. Next, you use the markdown.markdown() function to convert the value of note['content'] to HTML. For example, calling markdown.markdown('#Hi') would return the string '<h1>Hi</h1>' because in Markdown # represents the <h1> heading. After modifying note['content'], you append the note to the notes list.

      Finally, you render a template file called index.html, passing it the notes list.

      After all the additions, your file will be as follows:

      flask_notes/app.py

      import sqlite3
      import markdown
      from flask import Flask, render_template, request, flash, redirect, url_for
      
      def get_db_connection():
          conn = sqlite3.connect('database.db')
          conn.row_factory = sqlite3.Row
          return conn
      
      
      app = Flask(__name__)
      app.config['SECRET_KEY'] = 'this should be a secret random string'
      
      
      @app.route('/')
      def index():
          conn = get_db_connection()
          db_notes = conn.execute('SELECT id, created, content FROM notes;').fetchall()
          conn.close()
      
          notes = []
          for note in db_notes:
             note = dict(note)
             note['content'] = markdown.markdown(note['content'])
             notes.append(note)
      
          return render_template('index.html', notes=notes)
      

      Save and close the file.

      Next, you’ll create a base template and the index.html template file.

      In your flask_notes directory, create a templates directory and open a file called base.html inside it:

      • mkdir templates
      • nano templates/base.html

      Add the following code inside base.html; note that you’re using Bootstrap here. If you are not familiar with HTML templates in Flask, read Step 3 of How To Make a Web Application Using Flask in Python 3:

      flask_notes/templates/base.html

      <!doctype html>
      <html lang="en">
        <head>
          <!-- Required meta tags -->
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
      
          <!-- Bootstrap CSS -->
          <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
      
          <title>{% block title %} {% endblock %}</title>
        </head>
        <body>
          <nav class="navbar navbar-expand-md navbar-light bg-light">
              <a class="navbar-brand" href="https://www.digitalocean.com/community/tutorials/{{ url_for("index')}}">FlaskNotes</a>
              <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
                  <span class="navbar-toggler-icon"></span>
              </button>
              <div class="collapse navbar-collapse" id="navbarNav">
                  <ul class="navbar-nav">
                  <li class="nav-item active">
                      <a class="nav-link" href="#">About</a>
                  </li>
                  </ul>
              </div>
          </nav>
          <div class="container">
              {% for message in get_flashed_messages() %}
                  <div class="alert alert-danger">{{ message }}</div>
              {% endfor %}
              {% block content %} {% endblock %}
          </div>
      
          <!-- Optional JavaScript -->
          <!-- jQuery first, then Popper.js, then Bootstrap JS -->
          <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
          <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
          <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        </body>
      </html>
      

      Most of the code in the preceding block is standard HTML and code required for Bootstrap. The <meta> tags provide information for the web browser, the <link> tag links the Bootstrap CSS files, and the <script> tags are links to JavaScript code that allows some additional Bootstrap features.

      The <title>{% block title %} {% endblock %}</title> tag allows the inheriting templates to define a custom title. You use the for message in get_flashed_messages() loop to display the flashed messages, like warnings and alerts. The {% block content %} {% endblock %} placeholder is where inheriting templates place the content so that all templates have the extra code this base template provides to avoid repetition.

      Save and close the file.

      Next, create the index.html file that will extend this base.html file:

      • nano templates/index.html

      Add the following code to it:

      flask_notes/templates/index.html

      
      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} Welcome to FlaskNotes {% endblock %}</h1>
          {% for note in notes %}
          <div class="card">
              <div class="card-body">
                  <span class="badge badge-primary">#{{ note['id'] }}</span>
                  <span class="badge badge-default">{{ note['created'] }}</span>
                  <hr>
                  <p>{{ note['content'] |safe }}</p>
              </div>
          </div>
          <hr>
          {% endfor %}
      {% endblock %}
      

      Here you extend base.html, you define a title, and you use a for loop to go through notes displaying each note in a Bootstrap card. You display the note’s ID, the note’s creation date, and the note’s content, which you converted into HTML in the index() view function.

      You use the |safe Jinja filter, which you apply to the content using |; this is similar to calling a function in Python (in a similar way to safe(note['content'])). The |safe filter allows the browser to render HTML code—if it was not applied, it will display the HTML as plain text. This is usually referred to as “escaping HTML,” which is a security feature that prevents malicious HTML from being interpreted in the browser that would result in a dangerous security vulnerability called cross-site scripting (XSS). Since the Python-Markdown library returns safe HTML, you can allow the browser to render it using the |safe filter. Remember not to use this filter until you are certain that the HTML code you allow is safe and trusted.

      For more, read Flask’s documentation on controlling autoescaping.

      Save and close the file.

      Set the environment variables Flask needs and run the application using the following commands:

      • export FLASK_APP=app
      • export FLASK_ENV=development
      • flask run

      The FLASK_APP environment variable specifies the application you want to run (the app.py file). The FLASK_ENV environment variable specifies the mode; development means that the application will run in development mode with the debugger running (remember to avoid using this mode in production). You run the application using the flask run command.

      Open a browser and type in the URL http://127.0.0.1:5000/.

      Flask Notes Index

      Here, you’ll find that each note is formatted and rendered as HTML in the browser instead of plain text.

      You created a Flask application that connects to the database, fetches notes, converts their content from Markdown text to HTML, and then renders them on the index page. Next, you’ll add a route to allow users to add new notes, which they can write using Markdown syntax.

      Step 3 — Adding New Notes

      In this step, you’ll add a new route that will allow users to take new notes. Users can use Markdown syntax to write their notes—your application will save the notes to the database, which it will then display on the index page with the proper formatting. You will also add a button to the navigation bar that takes users to this new page.

      You will use web forms to allow users to submit data in your Flask application and you’ll store user-submitted data in your database.

      First open the app.py file to add a new route:

      Add the following code to the end of the file:

      flask_notes/app.py

      #. . .
      
      @app.route('/create/', methods=('GET', 'POST'))
      def create():
          conn = get_db_connection()
      
          if request.method == 'POST':
              content = request.form['content']
              if not content:
                  flash('Content is required!')
                  return redirect(url_for('index'))
              conn.execute('INSERT INTO notes (content) VALUES (?)', (content,))
              conn.commit()
              conn.close()
              return redirect(url_for('index'))
      
          return render_template('create.html')
      

      Since you will use this route to insert new data to the database via a web form, you allow both GET and POST requests using methods=('GET', 'POST') in the app.route() decorator. In the create() view function, you open a database connection.

      If the user has submitted a form that means the condition request.method == 'POST' is true, so you extract the note’s content that the user has submitted using request.form['content'] and save it to a variable called content. If the content is empty, you flash a 'Content is required!' message and redirect the user to the index page. If the content was not empty, you use an INSERT SQL statement to add the note’s content to the notes table. You commit the changes and close the connection, then you redirect the user to the index page.

      If the request is a GET request, meaning the user has just visited the page, you render a template file called create.html.

      Save and close the file.

      Next, open the create.html template file:

      • nano templates/create.html

      Add the following code to it:

      flask_notes/templates/create.html

      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} Add a New Note {% endblock %}</h1>
          <form method="post">
          <div class="form-group">
              <label for="content">Note Content</label>
              <textarea type="text" name="content"
                     placeholder="Note content, you can use Markdown syntax" class="form-control"
                     value="{{ request.form['content'] }}" autofocus></textarea>
          </div>
      
          <div class="form-group">
              <button type="submit" class="btn btn-primary">Submit</button>
          </div>
          </form>
      
      {% endblock %}
      

      Here, you use a form with a text area for the note’s content. You use request.form to access the form data that is stored in case something goes wrong with your form submission (for example, if no content was provided). You add a submit button under the text area for the user to press to send data to the application in a POST request.

      Save and close the file.

      Next open the base.html file to add a New Note button to the navigation bar:

      Edit the file with the highlighted code as follows:

      flask_notes/templates/base.html

      <!doctype html>
      <html lang="en">
        <head>
          <!-- Required meta tags -->
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
      
          <!-- Bootstrap CSS -->
          <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
      
          <title>{% block title %} {% endblock %}</title>
        </head>
        <body>
          <nav class="navbar navbar-expand-md navbar-light bg-light">
              <a class="navbar-brand" href="https://www.digitalocean.com/community/tutorials/{{ url_for("index')}}">FlaskNotes</a>
              <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
                  <span class="navbar-toggler-icon"></span>
              </button>
              <div class="collapse navbar-collapse" id="navbarNav">
                  <ul class="navbar-nav">
                  <li class="nav-item active">
                      <a class="nav-link" href="#">About</a>
                  </li>
      
                  <li class="nav-item active">
                      <a class="nav-link" href="https://www.digitalocean.com/community/tutorials/{{ url_for("create') }}">New Note</a>
                 </li>
                  </ul>
              </div>
          </nav>
          <div class="container">
              {% for message in get_flashed_messages() %}
                  <div class="alert alert-danger">{{ message }}</div>
              {% endfor %}
              {% block content %} {% endblock %}
          </div>
      
          <!-- Optional JavaScript -->
          <!-- jQuery first, then Popper.js, then Bootstrap JS -->
          <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
          <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
          <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        </body>
      </html>
      

      You add a new <li> item to the navigation bar with the url_for() function to link to the create() view function. With this, you can access the page for creating a new note from the navigation bar.

      Run the development server if you haven’t already:

      Use your browser to go to http://127.0.0.1:5000/create and add the following markdown note:

      ### Flask
      Flask is a **web** framework for _Python_.
      
      Here is the Flask logo:
      
      ![Flask Logo](https://flask.palletsprojects.com/en/1.1.x/_images/flask-logo.png)
      

      This markdown contains an h3 heading, the word web in bold, the word Python in italic, and an image.

      Submit the note, you will find that your application formats it into HTML.

      Markdown Formatted Note

      You now have a new route that allows users to add new notes to the database. Users can take notes with Markdown formatting and the application will display the notes in HTML on the index page.

      You can access the full code for the application from this repository.

      Conclusion

      You created a Flask application for taking notes in Markdown format to allow users to use text formatting such as headings, bold and italic text, adding images and links, among other things. You connected your application to a SQLite database to store and retrieve your data. You’ve incorporated Markdown text into HTML conversion so that notes will render on the page. To learn more about using Markdown in Python, read How To Use Python-Markdown to Convert Markdown Text to HTML.

      For further information about Flask, check out the following tutorials:



      Source link

      How To Make a URL Shortener 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 Python and SQLite is a database engine that you can use with Python to store application data.

      In this tutorial, you will build a URL shortener, a service that takes any URL and generates a shorter, more readable version like bit.ly.

      Hashids is a library that generates a short unique ID from integers. For example, you can use it to convert a number like 12 to a unique string like 1XcId. You will use Hashids to generate unique strings for URL IDs.

      You can use unique strings to generate IDs for videos on a video-sharing site or IDs for images on a service to upload images. This unique string gives you unpredictable IDs; therefore, if a user can access an image at your_domain/image/J32Fr, they can’t predict the location of other images. This is not possible if you use integer IDs in a URL shortener—for example, your_domain/image/33 would allow users to predict the location of other images. Unpredictable URLs add a form of privacy to your service because they prevent users from working out different URLs shortened by other users.

      You will use Flask, SQLite, and the Hashids library to build your URL shortener. Your application will allow users to enter a URL and generate a shorter version, in addition to a statistics page where users can view the number of times a URL has been clicked. You’ll use the Bootstrap toolkit to style your application.

      Prerequisites

      Step 1 — Setting Up Dependencies

      In this step, you will activate your Python environment and install Flask and the Hashids library using the pip package installer. Then you’ll create the database you will use to store URLs.

      First, activate your programming environment if you haven’t already:

      Once you have activated your programming environment, install Flask and the Hashids library using the following command:

      • pip install flask hashids

      Then create a database schema file called schema.sql, containing SQL commands to create a urls table. Open a file called schema.sql inside your flask_shortener directory:

      Type the following SQL commands inside this file:

      flask_shortener/schema.sql

      DROP TABLE IF EXISTS urls;
      
      CREATE TABLE urls (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          original_url TEXT NOT NULL,
          clicks INTEGER NOT NULL DEFAULT 0
      );
      

      In the schema file, you first delete the urls table if it already exists. This avoids the possibility of another table named urls existing, which might result in confusing behavior; for example, if it has different columns. Note that this will delete all of the existing data whenever the schema file executes.

      You then create the table with the following columns:

      • id: The ID of the URL, this will be a unique integer value for each URL entry. You will use it to get the original URL from a hash string.
      • created: The date the URL was shortened.
      • original_url: The original long URL to which you will redirect users.
      • clicks: The number of times a URL has been clicked. The initial value will be 0, which will increment with each redirect.

      Save and close the file.

      To execute the schema.sql file to create the urls table, open a file named init_db.py inside your flask_shortener directory:

      Then add the following code:

      flask_shortener/init_db.py

      import sqlite3
      
      connection = sqlite3.connect('database.db')
      
      with open('schema.sql') as f:
          connection.executescript(f.read())
      
      connection.commit()
      connection.close()
      

      Here you connect to a file called database.db that your program will create once you execute this program. This file is the database that will hold all of your application’s data. You then open the schema.sql file and run it using the executescript() method that executes multiple SQL statements at once. This will create the urls table. Finally, you commit the changes and close the connection.

      Save and close the file.

      Run the program:

      After execution, a new file called database.db will appear in your flask_shortener directory.

      With this, you’ve installed Flask and the Hashids library, created the database schema, and created the SQLite database with a table called urls to store the URL shortener’s original URLs. Next, you’ll use Flask to create the index page where your users can enter a URL to generate a short URL.

      Step 2 — Creating the Index Page for Shortening URLs

      In this step, you will create a Flask route for the index page, which will allow users to enter a URL that you then save into the database. Your route will use the ID of the URL to generate a short string hash with the Hashids library, construct the short URL, and then render it as a result.

      First, open a file named app.py inside your flask_shortener directory. This is the main Flask application file:

      Add the following code to the file:

      flask_shortener/app.py

      import sqlite3
      from hashids import Hashids
      from flask import Flask, render_template, request, flash, redirect, url_for
      
      
      def get_db_connection():
          conn = sqlite3.connect('database.db')
          conn.row_factory = sqlite3.Row
          return conn
      

      In this code, you first import the sqlite3 module, the Hashids class from the hashids library, and Flask helpers.

      The get_db_connection() function opens a connection to the database.db database file and then sets the row_factory attribute to sqlite3.Row. As a result, you can have name-based access to columns; the database connection will return rows that behave like regular Python dictionaries. Lastly, the function returns the conn connection object you’ll be using to access the database.

      Next, add the following:

      flask_shortener/app.py

      . . .
      app = Flask(__name__)
      app.config['SECRET_KEY'] = 'this should be a secret random string'
      
      hashids = Hashids(min_length=4, salt=app.config['SECRET_KEY'])
      
      

      You create the Flask application object and set a secret key to secure sessions. Since the secret key is a secret random string, you’ll also use it to specify a salt for the Hashids library; this will ensure the hashes are unpredictable since every time the salt changes, the hashes also change.

      Note: A salt is a random string that is provided to the hashing function (that is, hashids.encode()) so that the resulting hash is shuffled based on the salt. This process ensures the hash you get is specific to your salt so that the hash is unique and unpredictable, like a secret password that only you can use to encode and decode hashes. Remember to keep it secret for security purposes (which is why you use the application’s secret key).

      You create a hashids object specifying that a hash should be at least 4 characters long by passing a value to the min_length parameter. You use the application’s secret key as a salt.

      Next, add the following code to the end of your file:

      flask_shortener/app.py

      . . .
      @app.route('/', methods=('GET', 'POST'))
      def index():
          conn = get_db_connection()
      
          if request.method == 'POST':
              url = request.form['url']
      
              if not url:
                  flash('The URL is required!')
                  return redirect(url_for('index'))
      
              url_data = conn.execute('INSERT INTO urls (original_url) VALUES (?)',
                                      (url,))
              conn.commit()
              conn.close()
      
              url_id = url_data.lastrowid
              hashid = hashids.encode(url_id)
              short_url = request.host_url + hashid
      
              return render_template('index.html', short_url=short_url)
      
          return render_template('index.html')
      

      The index() functions is a Flask view function, which is a function decorated using the special @app.route decorator. Its return value gets converted into an HTTP response that an HTTP client, such as a web browser, displays.

      Inside the index() view function, you accept both GET and POST requests by passing methods=('GET', 'POST') to the app.route() decorator. You open a database connection.

      Then if the request is a GET request, it skips the if request.method == 'POST' condition until the last line. This is where you render a template called index.html, which will contain a form for users to enter a URL to shorten.

      If the request is a POST request, the if request.method == 'POST' condition is true, which means a user has submitted a URL. You store the URL in the url variable; if the user has submitted an empty form, you flash the message The URL is required! and redirect to the index page.

      If the user has submitted a URL, you use the INSERT INTO SQL statement to store the submitted URL in the urls table. You include the ? placeholder in the execute() method and pass a tuple containing the submitted URL to insert data safely into the database. Then you commit the transaction and close the connection.

      In a variable called url_id, you store the ID of the URL you inserted into the database. You can access the ID of the URL using the lastrowid attribute, which provides the row ID of the last inserted row.

      You construct a hash using the hashids.encode() method, passing it the URL ID; you save the result in a variable called hashid. As an example, the call hashids.encode(1) might result in a unique hash like KJ34 depending on the salt you use.

      You then construct the short URL using request.host_url, which is an attribute that Flask’s request object provides to access the URL of the application’s host. This will be http://127.0.0.1:5000/ in a development environment and your_domain if you deploy your application. For example, the short_url variable will have a value like http://127.0.0.1:5000/KJ34, which is the short URL that will redirect your users to the original URL stored in the database with the ID that matches the hash KJ34.

      Lastly, you render the index.html template passing the short_url variable to it.

      After all the additions, the file will be as follows:

      flask_shortener/app.py

      import sqlite3
      from hashids import Hashids
      from flask import Flask, render_template, request, flash, redirect, url_for
      
      
      def get_db_connection():
          conn = sqlite3.connect('database.db')
          conn.row_factory = sqlite3.Row
          return conn
      
      
      app = Flask(__name__)
      app.config['SECRET_KEY'] = 'this should be a secret random string'
      
      hashids = Hashids(min_length=4, salt=app.config['SECRET_KEY'])
      
      
      @app.route('/', methods=('GET', 'POST'))
      def index():
          conn = get_db_connection()
      
          if request.method == 'POST':
              url = request.form['url']
      
              if not url:
                  flash('The URL is required!')
                  return redirect(url_for('index'))
      
              url_data = conn.execute('INSERT INTO urls (original_url) VALUES (?)',
                                      (url,))
              conn.commit()
              conn.close()
      
              url_id = url_data.lastrowid
              hashid = hashids.encode(url_id)
              short_url = request.host_url + hashid
      
              return render_template('index.html', short_url=short_url)
      
          return render_template('index.html')
      

      Save and close the file.

      Next, you’ll create a base template and the index.html template file.

      In your flask_shortener directory, create a templates directory and open a file called base.html inside it:

      • mkdir templates
      • nano templates/base.html

      Add the following code inside base.html. Note that, for styling, you’re using Bootstrap here too. If you are not familiar with HTML templates in Flask, see Step 3 of How To Make a Web Application Using Flask in Python 3:

      flask_shortener/templates/base.html

      <!doctype html>
      <html lang="en">
        <head>
          <!-- Required meta tags -->
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
      
          <!-- Bootstrap CSS -->
          <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
      
          <title>{% block title %} {% endblock %}</title>
        </head>
        <body>
          <nav class="navbar navbar-expand-md navbar-light bg-light">
              <a class="navbar-brand" href="https://www.digitalocean.com/community/tutorials/{{ url_for("index')}}">FlaskShortener</a>
              <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
                  <span class="navbar-toggler-icon"></span>
              </button>
              <div class="collapse navbar-collapse" id="navbarNav">
                  <ul class="navbar-nav">
                  <li class="nav-item active">
                      <a class="nav-link" href="#">About</a>
                  </li>
                  </ul>
              </div>
          </nav>
          <div class="container">
              {% for message in get_flashed_messages() %}
                  <div class="alert alert-danger">{{ message }}</div>
              {% endfor %}
              {% block content %} {% endblock %}
          </div>
      
          <!-- Optional JavaScript -->
          <!-- jQuery first, then Popper.js, then Bootstrap JS -->
          <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
          <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
          <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        </body>
      </html>
      

      Most of the code in the preceding block is standard HTML and code required for Bootstrap. The <meta> tags provide information for the web browser, the <link> tag links the Bootstrap CSS files, and the <script> tags are links to JavaScript code that allows some additional Bootstrap features. Check out the Bootstrap documentation for more information.

      The <title>{% block title %} {% endblock %}</title> tag allows the inheriting templates to define a custom title. You use the for message in get_flashed_messages() loop to display the flashed messages (warnings, alerts, and so on). The {% block content %} {% endblock %} placeholder is where inheriting templates place the content so that all templates have access to this base template, which avoids repetition.

      Save and close the file.

      Next, create the index.html file that will extend this base.html file:

      • nano templates/index.html

      Add the following code to it:

      flask_shortener/templates/index.html

      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} Welcome to FlaskShortener {% endblock %}</h1>
          <form method="post">
          <div class="form-group">
              <label for="url">URL</label>
              <input type="text" name="url"
                     placeholder="URL to shorten" class="form-control"
                     value="{{ request.form['url'] }}" autofocus></input>
          </div>
      
          <div class="form-group">
              <button type="submit" class="btn btn-primary">Submit</button>
          </div>
          </form>
      
          {% if short_url %}
          <hr>
          <span>{{ short_url }}</span>
          {% endif %}
      {% endblock %}
      

      Here you extend base.html, define a title, and create a form with an input named url. The url input will allow users to enter URLs to shorten. It has a value of request.form['url'], which stores data in cases of submission failure; that is if the user provides no URL. You also add a submit button.

      Then you check if the short_url variable has any value—this is true if the form submits and the short URL generates successfully. If the condition is true, you display the short URL under the form.

      Set the environment variables Flask needs and run the application using the following commands:

      • export FLASK_APP=app
      • export FLASK_ENV=development
      • flask run

      The FLASK_APP environment variable specifies the application you want to run (the app.py file). The FLASK_ENV environment variable specifies the mode. development means that the application will run in development mode with the debugger running. Remember to avoid using this mode in production. You run the application using the flask run command.

      Open a browser and type in the URL http://127.0.0.1:5000/. You will find a Welcome to FlaskShortener page.

      Flask Shortener Index page

      Submit a URL, and you will receive a short URL.

      Flask Shortened URL displayed beneath the URL input box

      You created a Flask application with a page that accepts URLs and generates shorter ones, but the URLs don’t do anything yet. In the next step, you’ll add a route that extracts the hash from the short URL, finds the original URL, and redirects users to it.

      Step 3 — Adding the Redirect Route

      In this step, you will add a new route that takes the short hash the application generates and decodes the hash into its integer value, which is the original URL’s ID. Your new route will also use the integer ID to fetch the original URL and increment the clicks value. Finally, you will redirect users to the original URL.

      First, open the app.py to add a new route:

      Add the following code to the end of the file:

      flask_shortener/app.py

      . . .
      
      @app.route('/<id>')
      def url_redirect(id):
          conn = get_db_connection()
      
          original_id = hashids.decode(id)
          if original_id:
              original_id = original_id[0]
              url_data = conn.execute('SELECT original_url, clicks FROM urls'
                                      ' WHERE id = (?)', (original_id,)
                                      ).fetchone()
              original_url = url_data['original_url']
              clicks = url_data['clicks']
      
              conn.execute('UPDATE urls SET clicks = ? WHERE id = ?',
                           (clicks+1, original_id))
      
              conn.commit()
              conn.close()
              return redirect(original_url)
          else:
              flash('Invalid URL')
              return redirect(url_for('index'))
      

      This new route accepts a value id through the URL and passes it to the url_redirect() view function. For example, visiting http://127.0.0.1:5000/KJ34 would pass the string 'KJ34' to the id parameter.

      Inside the view function, you first open a database connection. Then you use the decode() method of the hashids object to convert the hash to its original integer value and store it in the original_id variable. You check that the original_id has a value—meaning decoding the hash was successful. If it has a value, you extract the ID from it. As the decode() method returns a tuple, you fetch the first value in the tuple with original_id[0], which is the original ID.

      You then use the SELECT SQL statement to fetch the original URL and its number of clicks from the urls table, where the ID of the URL matches the original ID you extracted from the hash. You fetch the URL data with the fetchone() method. Next, you extract the data into the two original_url and clicks variables.

      You then increment the number of clicks of the URL with the UPDATE SQL statement.

      You commit the transaction and close the connection, and redirect to the original URL using the redirect() Flask helper function.

      If decoding the hash fails, you flash a message to inform the user that the URL is invalid, and redirect them to the index page.

      Save and close the file.

      Run your development server:

      Use your browser to go to http://127.0.0.1:5000/. Enter a URL and visit the resulting short URL; your application will redirect you to the original URL.

      You created a new route that redirects users from the short URL to the original URL. Next, you’ll add a page to show how many times each URL has been visited.

      Step 4 — Adding a Statistics Page

      In this step, you’ll add a new route for a statistics page that displays how many times each URL has been clicked. You’ll also add a button that links to the page on the navigation bar.

      Allowing users to see the number of visits each shortened link has received will provide visibility into each URL’s popularity, which is useful for projects, like marketing ad campaigns. You can also use this workflow as an example of adding a feature to an existing Flask application.

      Open app.py to add a new route for a statistics page:

      Add the following code to the end of the file:

      flask_shortener/app.py

      . . .
      
      @app.route('/stats')
      def stats():
          conn = get_db_connection()
          db_urls = conn.execute('SELECT id, created, original_url, clicks FROM urls'
                                 ).fetchall()
          conn.close()
      
          urls = []
          for url in db_urls:
              url = dict(url)
              url['short_url'] = request.host_url + hashids.encode(url['id'])
              urls.append(url)
      
          return render_template('stats.html', urls=urls)
      

      In this view function, you open a database connection. Then you fetch the ID, the creation date, the original URL, and the number of clicks for all of the entries in the urls table. You use the fetchall() method to get a list of all the rows. You then save this data in the db_urls variable and close the connection.

      To display the short URL for each entry, you will need to construct it and add it to each item in the list of the URLs you fetched from the database (db_urls). You create an empty list called urls and loop through the db_urls list with for url in db_urls.

      You use the dict() Python function to convert the sqlite3.Row object to a dictionary to allow assignment. You add a new key called short_url to the dictionary with the value request.host_url + hashids.encode(url['id']), which is what you used before to construct short URLs in the index view function. You append this dictionary to the urls list.

      Finally, you render a template file called stats.html, passing the urls list to it.

      Save and close the file.

      Next, create the new stats.html template file:

      • nano templates/stats.html

      Type the following code into it:

      flask_shortener/templates/stats.html

      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} FlaskShortener Statistics {% endblock %}</h1>
          <table class="table">
              <thead>
                  <tr>
                  <th scope="col">#</th>
                  <th scope="col">Short</th>
                  <th scope="col">Original</th>
                  <th scope="col">Clicks</th>
                  <th scope="col">Creation Date</th>
                  </tr>
              </thead>
              <tbody>
                  {% for url in urls %}
                      <tr>
                          <th scope="row">{{ url['id'] }}</th>
                          <td>{{ url['short_url'] }}</td>
                          <td>{{ url['original_url'] }}</td>
                          <td>{{ url['clicks'] }}</td>
                          <td>{{ url['created'] }}</td>
                      </tr>
                  {% endfor %}
              </tbody>
          </table>
      
      {% endblock %}
      

      Here you extend the base.html base template by specifying a title and defining a table with the following columns:

      • #: The ID of the URL.
      • Short: The short URL.
      • Original: The original URL.
      • Clicks: The number of times a short URL has been visited.
      • Creation Date: The creation date of the short URL.

      Each row is filled using a for loop that goes through the urls list and displays the value of each column for each URL.

      Run the development server with the following:

      Use your browser to go to http://127.0.0.1:5000/stats. You will find all the URLs in a table.

      Statistics page with list of URLs and number of clicks

      Next, add a Stats button to the navigation bar. Open the base.html file:

      Edit the file as per the following highlighted lines:

      flask_shortener/templates/base.html

      <!doctype html>
      <html lang="en">
        <head>
          <!-- Required meta tags -->
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
      
          <!-- Bootstrap CSS -->
          <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
      
          <title>{% block title %} {% endblock %}</title>
        </head>
        <body>
          <nav class="navbar navbar-expand-md navbar-light bg-light">
              <a class="navbar-brand" href="https://www.digitalocean.com/community/tutorials/{{ url_for("index')}}">FlaskTodo</a>
              <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
                  <span class="navbar-toggler-icon"></span>
              </button>
              <div class="collapse navbar-collapse" id="navbarNav">
                  <ul class="navbar-nav">
                  <li class="nav-item active">
                      <a class="nav-link" href="#">About</a>
                  </li>
      
                  <li class="nav-item active">
                      <a class="nav-link" href="https://www.digitalocean.com/community/tutorials/{{ url_for("stats')}}">Stats</a>
                  </li>
                  </ul>
              </div>
          </nav>
          <div class="container">
              {% for message in get_flashed_messages() %}
                  <div class="alert alert-danger">{{ message }}</div>
              {% endfor %}
              {% block content %} {% endblock %}
          </div>
      
          <!-- Optional JavaScript -->
          <!-- jQuery first, then Popper.js, then Bootstrap JS -->
          <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
          <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
          <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        </body>
      </html>
      

      Here you incorporate a new <li> item to the navigation bar. You use the url_for() function to link to the stats() view function. You can now access the statistics page from the navigation bar.

      Your statistics page shows information about each URL, including its shorter equivalent and how many times it has been visited.

      You can reuse this code for monitoring number of clicks in other contexts, such as keeping track of how many times a post has been liked or updated on a social media site or how many times a photo/video has been viewed.

      You can access the full code for the application from this repository.

      Conclusion

      You have created a Flask application that allows users to enter a long URL and generate a shorter version. You have transformed integers into short string hashes, redirected users from one link to another, and set up a page for statistics so you can monitor shortened URLs. For further projects and tutorials on working with Flask, check out the following tutorials:



      Source link

      How To Install and Use SQLite on Ubuntu 20.04


      How To Install and Use SQLite on Ubuntu 20.04

      The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      SQLite is a free, cross-platform database management system. It is popular for its efficiency and ability to interface with many different programming languages.

      In this tutorial you will install SQLite on Ubuntu 20.04. You will then create a database, read data from it, insert items, delete items, and join items from separate tables.

      Note: This tutorial includes practical instructions for installing and using SQLite. It does not cover larger conceptual issues and production concerns, such as when one should, or should not, consider using a SQLite database. For an excellent overview of popular relational databases and how they compare, check out our article, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.

      Additionally, many languages maintain integrations with SQLite. For instructions on using SQLite inside your Python code, check out our tutorial, How To Use the sqlite3 Module in Python 3.

      Prerequisites

      To complete this tutorial, you will need:

      Step 1 — Installing SQLite on Ubuntu 20.04

      To install the SQLite command-line interface on Ubuntu, first update your package list:

      Now install SQLite:

      • sudo apt-get install sqlite3

      To verify the installation, check the software’s version:

      You will receive an output like this:

      Output

      3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1

      With SQLite installed, you are now ready to create a new database.

      Step 2 — Creating a SQLite Database

      In this step you will create a database containing different sharks and their attributes. To create the database, open your terminal and run this sqlite3 command:

      sqlite3 sharks.db
      

      This will create a new database named sharks. If the file sharks.db already exists, SQLite will open a connection to it; if it does not exist, SQLite will create it.

      You will receive an output like this:

      SQLite version 3.31.1 2020-01-27 19:55:54
      Enter ".help" for usage hints.
      

      Following this, your prompt will change. A new prefix, sqlite>, now appears:

      With your Shark database created, you will now create a new table and populate it with data.

      Step 3 — Creating a SQLite Table

      SQLite databases are organized into tables. Tables store information. To better visualize a table, one can imagine rows and columns.

      The rest of this tutorial will follow a common convention for entering SQLite commands. SQLite commands are uppercase and user information is lowercase. Lines must end with a semi-colon.

      Now let’s create a table and some columns for various data:

      • An ID
      • The shark’s name
      • The shark’s type
      • The shark’s average length (in centimeters)

      Use the following command to create the table:

      • CREATE TABLE sharks(id integer NOT NULL, name text NOT NULL, sharktype text NOT NULL, length integer NOT NULL);

      Using NOT NULL makes that field required. We will discuss NOT NULL in greater detail in the next section.

      After creating the table, an empty prompt will return. Now let’s insert some values into it.

      Inserting Values into Tables

      In SQLite, the command for inserting values into a table follows this general form:

      INSERT INTO tablename VALUES(values go here);

      Where tablename is the name of your table, and values are within parentheses.

      Now insert three rows of VALUES into your sharks table:

      • INSERT INTO sharks VALUES (1, "Sammy", "Greenland Shark", 427);
      • INSERT INTO sharks VALUES (2, "Alyoshka", "Great White Shark", 600);
      • INSERT INTO sharks VALUES (3, "Himari", "Megaladon", 1800);

      Because you earlier specified NOT NULL for each of the variables in your table, you must enter a value for each.

      For example, try adding another shark without setting its length:

      • INSERT INTO sharks VALUES (4, "Faiza", "Hammerhead Shark");

      You will receive this error:

      Output

      Error: table sharks has 4 columns but 3 values were supplied

      In this step you created a table and inserted values into it. In the next step you will read from your database table.

      Step 4 — Reading Tables in SQLite

      In this step, we will focus on the most basic methods of reading data from a table. Recognize that SQLite provides more specific methods for viewing data in tables.

      To view your table with all of the inserted values, use SELECT:

      You will see the previously inserted entries:

      Output

      1|Sammy|Greenland Shark|427 2|Alyoshka|Great White Shark|600 3|Himari|Megaladon|1800

      To view an entry based on its id (The values we set manually), add the WHERE command to your query:

      • SELECT * FROM sharks WHERE id IS 1;

      This will return the shark whose id equals 1:

      Output

      1|Sammy|Greenland Shark|427

      Let’s take a closer look at this command.

      1. First, we SELECT all (*) values from our database, sharks.
      2. Then we look at all id values.
      3. Then we return all table entries where id is equal to 1.

      So far you have created a table, inserted data into it, and queried that saved data. Now you will update the existing table.

      Step 5 — Updating Tables in SQLite

      In the following two sections you will first add a new column into your existing table and then update existing values in the table.

      Adding Columns to SQLite Tables

      SQLite allows you to change your table using the ALTER TABLE command. This means that you can create new rows and columns, or modify existing rows and columns.

      Use ALTER TABLE to create a new column. This new column will track each shark’s age in years:

      • ALTER TABLE sharks ADD COLUMN age integer;

      You now have a fifth column, age.

      Updating Values in SQLite Tables

      Using the UPDATE command, add new age values for each of your sharks:

      • UPDATE sharks SET age = 272 WHERE id=1;
      • UPDATE sharks SET age = 70 WHERE id=2;
      • UPDATE sharks SET age = 40 WHERE id=3;

      Output

      1|Sammy|Greenland Shark|427|272 2|Alyoshka|Great White Shark|600|70 3|Himari|Megaladon|1800|40

      In this step you altered your table’s composition and then updated values inside the table. In the next step you will delete information from a table.

      Step 6 — Deleting Information in SQLite

      In this step you will delete entries in your table based on the evaluation of an argument.

      In the following command you are querying your database and requesting that that it delete all sharks in your sharks table whose age is less than 200:

      • DELETE FROM sharks WHERE age <= 200;

      Typing SELECT * FROM sharks; will verify that Alyoshka and Himari, who were each less than 200 years old, were deleted. Only Sammy the Greenland Shark remains:

      Output

      1|Sammy|Greenland Shark|427|272

      Step 7 — Joining Information in SQLite

      Let’s imagine that we had two tables: our current sharks table and an endangered table. Now what if the endangered table had an id value that mapped to the ids in your sharks table, and it also had a status value that indicated each shark’s conservation status?

      If you wanted to query data from both tables, you could use one of SQLite’s four join commands:

      • INNER JOIN
      • OUTER JOIN
      • LEFT JOIN
      • CROSS JOIN

      Let’s create that second table and then use INNER JOIN to join some data.

      First, create your endangered table:

      • CREATE TABLE endangered (id integer NOT NULL, status text NOT NULL);
      • INSERT INTO endangered VALUES (1, "near threatened");

      Now join your tables:

      SELECT * FROM sharks INNER JOIN endangered on sharks.id = endangered.id;

      Your output will look like this:

      Output

      1|Sammy|Greenland Shark|427|272|1|near threatened

      Note that the output also includes the id value from endangered. You can specify desired output with a more explicit command:

      • SELECT sharks.id, sharks.name, sharks.sharktype, sharks.length, sharks.age, endangered.status FROM sharks INNER JOIN endangered on sharks.id = endangered.id;

      This time the output excludes the second id value:

      Output

      1|Sammy|Greenland Shark|427|272|near threatened

      You have now successfully joined information from multiple tables.

      Conclusion

      SQLite is a useful tool for database management. One can quickly create a database and manipulate it with various commands. Following this tutorial, you now have a basic understanding of SQLite and you are prepared dive deeper into this database management system.

      For an excellent overview of Relational Databases systems and how they compare, check out our article, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.

      Additionally, many languages maintain integrations with SQLite. For instructions on using SQLite inside your Python code, check out our tutorial, How To Use the sqlite3 Module in Python 3.

      For specific help with SQLite’s syntax, the official documentation is another excellent resource.



      Source link