One place for hosting & domains

      Database

      Connect to a MySQL Database Using the mysql Command


      Updated
      , by Linode

      Traducciones al Español

      Estamos traduciendo nuestros guías y tutoriales al Español. Es
      posible que usted esté viendo una traducción generada
      automáticamente. Estamos trabajando con traductores profesionales
      para verificar las traducciones de nuestro sitio web. Este proyecto
      es un trabajo en curso.

      Create a Linode account
      to try this guide with a $100 credit.

      This credit will be applied to any valid services used during your first 60 days.

      This guide shows you how to connect to a MySQL database using
      mysql, the MySQL command-line client. This opens up a simple SQL shell environment, allowing you to perform
      SQL queries and commands on your database. If you require more advanced capabilities, consider using the
      MySQL Shell.

      Note

      If you wish to connect to a Linode MySQL Managed Database, review the
      Connect to a MySQL Managed Database guide instead.

      Before You Begin

      • Obtain the connection details for the MySQL instance you wish to use. If you do not have a MySQL instance yet, you can
        create a Managed Database,
        deploy the MySQL Marketplace App, or
        install MySQL server (or MariaDB) on a Compute Instance. This instance must allow remote connections or you must run the mysql command from within same system.

      • Ensure mysql is installed and is compatible with the MySQL version on your database server. Run the following command on the system you intend on using to verify that mysql is installed.

        mysql --version
        

        This should inform you which version you are using. If the command is not found or you are not on a compatible version, see the
        Installing MySQL guide.

      Note

      The steps in this guide are written for a non-root user. Commands that require elevated privileges are prefixed with sudo. If you’re not familiar with the sudo command, see the
      Linux Users and Groups guide.

      General mysql Syntax

      The main purpose of the mysql utility is to connect to a MySQL database server and open a simple SQL shell environment. The mysql command can be used to connect to either a local or remote database server. In the commands provided below, see the
      Common Command Options for information on each of the available options.

      • Local database server: Use this command when connecting to a MySQL Server instance running on the same machine you are using.

        mysql -u [username] -p
        
      • Remote database server: In many cases, the database server is not on the same system you are using. In these cases, you can SSH in to the remote system (if permitted) and run the command above to connect to a local MySQL instance. Alternatively, you can use the mysql command to remotely connect to the database. If your MySQL server does not allow remote connections or your user cannot connect remotely, see
        Configure the Database Server to Allow Remote Connections.

        mysql -h [host] -p [port] -u [username] -p
        

        Note

        If you wish to connect to a Linode MySQL Managed Database, review the
        Connect to a MySQL Managed Database guide instead.

      Common Command Options

      The following list is a collection of common options used with the mysqldump command. At minimum, the username and password is required. When connecting to a remote database server, the host (and perhaps the port) should be provided. For a full list of available options, reference the
      Command Options for Connecting to the Server documentation.

      • Username (--user=[] or -u []): The username of your MySQL user. This user must have proper grants to access the database.

      • Password (--password=[] or -p[]): Specifies that the user’s password is required for the connection. The password can be entered directly in the command itself (though that is not recommended due to security concerns) or the password can be omitted (by just using the --password option with no value). In the password is omitted, mysql prompts you for the password before connecting to the database. For more details about password security, see MySQL’s
        End-User Guidelines for Password Security.

      • Host (--host=[] or -h []): The IP address or FQDN (fully qualified domain name) of the remote database server. You can omit this option from the command if you are connecting to a local MySQL instance on your same system.

      • Port (--port=[] or -P []): The port number of that the MySQL database instance uses. This can be omitted if your MySQL instance uses the default port of 3306.

      • SSL Settings (--ssl-mode): This controls if the connection should be encrypted. This can be set to DISABLED (unencrypted – not recommended), PREFERRED (tries an encrypted connection first before falling back to unencrypted), or REQUIRED (fails if an encrypted connection can’t be established. If omitted, this option is automatically set to PREFERRED. You can also set this to VERIFY_CA or VERIFY_IDENTITY to require an encrypted connection and either verify the CA certificate or both verify the CA certificate and the host name identity.

      If you are frequently connecting to the same database, you can securely store many of these options (including the password). See the
      Securely Storing Credentials guide. Other options can be stored in an
      option file.

      Configure the Database Server to Allow Remote Connections

      If you have installed the MySQL server yourself (not through a managed service) and wish to connect to a database remotely without first logging in to the database server through SSH, you may need to modify a few settings. This can be useful if you want to limit SSH access but still permit database access.

      Refer to our
      Create an SSH Tunnel for MySQL Remote Access to learn how to connect to your database using an SSH tunnel.

      1. Make sure your database has a user set up to allow connections from your local machine’s IP address.

        The example below displays a series of commands to create a new MySQL/MariaDB user named example_user. The user accepts connections from 192.0.2.0 and has SELECT, INSERT, UPDATE, and DELETE permissions on the example_db database:

        CREATE user 'example_user'@'192.0.2.0' IDENTIFIED BY 'password';
        GRANT SELECT,INSERT,UPDATE,DELETE ON example-db.* TO 'example_user' IDENTIFIED BY 'password';
        
      2. Locate you database’s configuration files using the command below. The following command lists the files’ default locations. The locations returned by the command may be different than those in the example shown below:

        sudo mysql --help
        
        ...
        Default options are read from the following files in the given order:
        /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
        ...
      3. Using your preferred text editor, locate the [mysqld] section and a bind-address parameter.

        If you see any !includedir parameters in the files, you may also need to check the files in the locations those parameters designate.

      4. Once you locate the bind-address parameter, change it from the default 127.0.0.1 to 0.0.0.0. This enables external connections on the database.

        Also, if the file contains a skip-networking parameter, comment it out with a #.

        File: /etc/mysql/mysql.conf.d/mysqld.conf
        1
        2
        3
        4
        5
        6
        7
        8
        
        ...
        [mysqld]
        
        ...
        # skip-networking
        
        bind-address = 0.0.0.0
        ...
      5. Restart the MySQL service.

        sudo systemctl restart mysqld
        

      Follow our
      Install MySQL Workbench for Database Administration guide for steps to install the MySQL Workbench tool on your local machine. This guide also shows you how to connect to a remote database via MySQL Workbench. These steps work whether your target database server is MySQL or MariaDB.

      For more information, take a look at the
      official MySQL Workbench manual. You may also refer to MariaDB’s documentation on
      using the MySQL Workbench with MariaDB.

      Conclusion

      Now that you have your remote database connection, you may want to learn more about using MySQL/MariaDB and working with more advanced database operations. You can refer to our extensive
      list of MySQL guides and specific
      MariaDB guides to build your database management skills.

      More Information

      You may wish to consult the following resources for additional information
      on this topic. While these are provided in the hope that they will be
      useful, please note that we cannot vouch for the accuracy or timeliness of
      externally hosted materials.

      This page was originally published on



      Join the conversation.
      Read other comments or post your own below. Comments must be respectful,
      constructive, and relevant to the topic of the guide. Do not post external
      links or advertisements. Before posting, consider if your comment would be
      better addressed by contacting our
      Support team or asking on
      our
      Community Site.



      Source link

      Database Sharding: Concepts, Examples, and Strategies


      Many software applications use a relational database management system (RDBMS) to store data. As the database grows, it becomes more time-and-storage intensive to store the data. One popular solution to this problem is
      database sharding
      . A sharded database distributes the records in a database’s tables across different databases on different computer systems. This guide explains how database sharding works and discusses some of the advantages and disadvantages of sharding. It also describes some of the main sharding strategies and provides some database sharding examples.

      What is Database Sharding?

      As databases grow larger, they can be scaled in one of two ways. Vertical scaling involves upgrading the server hosting the database with more RAM, CPU ability, or disk space. This allows it to store more data and process a query more quickly and effectively. Horizontal scaling, which is also known as “scaling out”, adds additional servers to distribute the workload.

      Data sharding is a common way of implementing horizontal scaling. Database sharding divides the table records in a database into smaller portions. Each section is a shard, and is stored on a different server. The database can be divided into shards based on different methods. In a simple implementation, the individual tables can be assigned to different shards. More often, the rows in a single table are divided between the shards.

      Vertical partitioning and horizontal partitioning are two different methods of partitioning tables into shards. Vertical partitioning assigns different columns within a table to different servers, but this technique is not widely used. In most cases, horizontal partitioning/sharding is used to implement sharding, and the two terms are often used interchangeably. Horizontal sharding divides the rows within a table amongst the different shards and keeps the individual table rows intact.

      Note

      Vertical partitioning and horizontal partitioning should not be confused with vertical and horizontal scaling.

      The shards are distributed across the different servers in the cluster. Each shard has the same database schema and table definitions. This maintains consistency across the shards. Sharding allocates each row to a shard based on a sharding key. This key is typically an index or primary key from the table. A good example is a user ID column. However, it is possible to generate a sharding key from any field, or from multiple table columns. The selection of the sharding key should be reasonable for the application and effectively distribute the rows among the shards. For example, a country code or zip code is a good choice to distribute the data to geographically dispersed shards. Sharding is particularly advantageous for databases that store large amounts of data in relatively few tables, and have a high volume of reads and writes.

      Each shard can be accessed independently and does not necessarily require access to the other shards. Different tables can use different sharding techniques and not all tables necessarily have to be sharded. As an ideal, sharding strives towards a shared-nothing architecture, in which the shards do not share data and there is no data duplication. In practice, it is often advantageous to replicate certain data to each shard. This avoids the need to access multiple servers for a single query and can result in better performance.

      The following example demonstrates how horizontal sharding works in practice. Before the database is sharded, the example store table is organized in the following way:

      store_IDcitystatezip_code
      1001DetroitMI48201
      1350ChicagoIL60601
      2101ClevelandOH44114
      2250PittsburghPA15222
      2455BostonMA02108
      2459New YorkNY10022

      After sharding, one shard has half the rows from the table.

      store_IDcitystatezip_code
      1001DetroitMI48201
      2101ClevelandOH44114
      2455BostonMA02108

      The second shard contains the remainder of the rows.

      store_IDcitystatezip_code
      1350ChicagoIL60601
      2250PittsburghPA15222
      2459New YorkNY10022

      Sharding does not necessarily make any backup copies of the data. Each record is still only stored on a single server. Replication is used to copy information to another server, resulting in primary and secondary copies of the data. Replication enhances reliability and robustness at the cost of additional complexity and resources. Sharded databases can be replicated, but the procedure for doing so can be very complex.

      Replication and caching are both potential alternatives to sharding, particular in applications which mainly read data from a database. Replication spreads out the queries to multiple servers, while caching speeds up the requests. See our guide
      How to Configure Source-Replica Replication in MySQL
      to learn more about data replication.

      Pros and Cons of a Sharded Database

      Generally, a horizontal scaling approach is more robust and effective than vertical scaling. Vertical scaling is much easier to implement, because it mainly consists of hardware upgrades. It might be the correct approach to take with a medium-sized database that is slowly reaching its limit. However, it is impossible to scale any system indefinitely, and ongoing growth rapidly becomes unmanageable. The limits of vertical scaling usually lead administrators to seek another alternative.

      Horizontal scaling allows systems to achieve a much higher scaling rate. Additional servers can be added as required, permitting the database system to organically grow and access additional resources. It provides administrators with much more flexibility.

      Database sharding is a horizontal scaling strategy, so it shares the advantages of this approach. However, it also offers several additional benefits, including the following:

      • It improves performance and speeds up data retrieval. Based on the sharding key, the database system immediately knows which shard contains the data. It can quickly route the query to the right server. Because each shard only contains a subset of the rows, it is easier for the database server to find the correct entry.
      • Additional computing capacity can be added with no downtime. Sharding increases the data storage capacity and the total resources available to the database.
      • It can be more cost efficient to run multiple servers than one mega-server.
      • Sharding can simplify upgrades, allowing one server to be upgraded at a time.
      • A sharded approach is more resilient. If one of the servers is offline, the remaining shards are still accessible. Sharding can be combined with high availability techniques for even higher reliability.
      • Many modern database systems provide some tools to assist with sharding, although they do not completely automate the process.

      Unfortunately, sharding also has drawbacks. Some of the downsides include:

      • Sharding greatly increases the complexity of a software development project. Additional logic is required to shard the database and properly direct queries to the correct shard. This increases development time and cost. A more elaborate network mesh is often necessary, which leads to an increase in lab and infrastructure costs.
      • Latency can be higher than with a standard database design.
      • SQL join operations
        affecting multiple shards are more difficult to execute and take longer to complete. Some operations might become too slow to be feasible. However, the right design can facilitate better performance on common queries.
      • Sharding requires a lot of tuning and tweaking as the database grows. This sometimes requires a reconsideration of the entire sharding strategy and database design. Uneven shard distribution can happen even with proper planning, causing the distribution to unexpectedly become lopsided.
      • It is not always obvious how many shards and servers to use, or how to choose the sharding key. Poor sharding keys can adversely affect performance or data distribution. This causes some shards to be overloaded while others are almost empty, leading to hotspots and inefficiencies.
      • It is more challenging to change the database schema after sharding is implemented. It is also difficult to convert the database back to its pre-sharded state.
      • Shard failures can cause cross-shard inconsistencies and other failures.
      • Backup and replication tasks are more difficult with a sharded database.
      • Although most RDBMS applications provide some sharding support, the tools are often not robust or complete. Most systems still do not fully support automatic sharding.

      Database Sharding Strategies: Common Architectures

      Any sharding implementation must first decide on a db sharding strategy. Database designers must consider how many shards to use and how to distribute the data to the various servers. They must decide what queries to optimize, and how to handle joins and bulk data retrieval. A system in which the data frequently changes requires a different architecture than one that mainly handles read requests. Replication, reliability and a maintenance strategy are also important considerations.

      The choice of a sharding architecture is a critical decision, because it affects many of the other considerations. Most sharded databases have one of the following four architectures:

      • Range Sharding.
      • Hashed Sharding.
      • Directory-Based Sharding.
      • Geographic-Based Sharding.

      Range Sharding

      Range sharding examines the value of the sharding key and determines what range it falls into. Each range directly maps to a different shard. The sharding key should ideally be immutable. If the key changes, the shard must be recalculated and the record copied to the new shard. Otherwise, the mapping is destroyed and the location could be lost. Range sharding is also known as dynamic sharding.

      As an example, if the userID field is the sharding key, then records having IDs between 1 to 10000 could be stored in one shard. IDs between 10001 and 20000 map to a second shard, and those between 20001 and 30000 to a third.

      This approach is fairly easy to design and implement, and requires less programming time. The database application only has to compare the value of the sharding key to the predefined ranges using a lookup table. This scheme is also easier to redesign and maintain. Range sharding is a good choice if records with similar keys are frequently viewed together.

      Range sharding works best if there are a large number of possible values that are fairly evenly distributed across the entire range. This design works poorly if most of the key values map to the same shard. Unfortunately, this architecture is prone to poor distribution of rows among the shards. A good design can still lead to an unbalanced distribution. For example, older accounts are more likely to have been deleted over the years, leaving the corresponding shard relatively empty. This leads to inefficiencies in the database. Choosing fairly large ranges can reduce, but not eliminate, this possibility.

      The database sharding examples below demonstrate how range sharding might work using the data from the store database. In this case, the records for stores with store IDs under 2000 are placed in one shard. Stores possessing IDs of 2001 and greater go in the other.

      The first shard contains the following rows:

      store_IDcitystatezip_code
      1001DetroitMI48201
      1350ChicagoIL60601

      The second shard has the following entries:

      store_IDcitystatezip_code
      2101ClevelandOH44114
      2250PittsburghPA15222
      2455BostonMA02108
      2459New YorkNY10022

      This results in a slightly imbalanced distribution of records. However, as new stores are added, they might be assigned larger store IDs. This leads to a greater imbalance as time goes on.

      To keep the database running efficiently, shards and ranges have to be regularly rebalanced. This might involve splitting the shards apart and reassigning the data, or merging several smaller shards. If the data is not regularly monitored, performance can steadily degrade.

      Hash Sharding (Key-Based)

      Hash-based sharding, also known as key-based or algorithmic sharding, also uses the shard key to determine which shard a record is assigned to. However, instead of mapping the key directly to a shard, it applies a hash function to the shard key. A hash function transforms one or more data points to a new value that lies within a fixed-size range. In this case, the size of the range is equal to the number of shards. The database uses the output from the hash function to allocate the record to a shard. This typically results in a more even distribution of the records to the different shards.

      This method allows multiple fields to be used as a compound shard key. This eliminates clumping and clustering, and is a better approach to use if several records can share the same key. Hash functions vary in complexity. A simple hash function calculates the remainder, or modulus, of the key divided by the number of shards. More complex hashing algorithms apply mathematically advanced equations to multiple inputs. However, it is important to use the same hash function on the same keys for each hashing operation. As with range sharding, the key value should be immutable. If it changes, the hash value must be recalculated and the database entry remapped.

      Hash sharding is more efficient than range sharding because a lookup table is not required. The hash is calculated in real time for each query. However, it is impossible to group related records together, and there is no logical connection between the records on a given shard. This requires most bulk queries to read records from multiple shards. Hash sharding is more advantageous for applications that read or write one record at a time.

      Hash sharding does not guarantee that the shards are destined to remain perfectly balanced. Patterns in the data still might lead to clustering, which can occur purely by chance. Hash sharding complicates the tasks of rebalancing and rebuilding the shards. To add more shards, it is usually necessary to re-merge all the data, recalculate the hashes, and reassign all the records.

      The following database sharding example demonstrates a simple hash sharing operation. It uses the simple hash function store_ID % 3 to assign the records in the store database to one of three shards. The first step is to calculate a hash result for each entry.

      Note

      The hash results are not actually stored inside the database. They are shown in the final column for clarity.

      store_IDcitystatezip_codehash result
      1001DetroitMI482012
      1350ChicagoIL606010
      2101ClevelandOH441141
      2250PittsburghPA152220
      2455BostonMA021081
      2459New YorkNY100222

      Rows having a hash result of 0 map to the first shard.

      store_IDcitystatezip_code
      1350ChicagoIL60601
      2250PittsburghPA15222

      Those that have a hash result of 1 are assigned to shard number two.

      store_IDcitystatezip_code
      2101ClevelandOH44114
      2459New YorkNY10022

      The remainder are stored in the third shard.

      store_IDcitystatezip_code
      1001DetroitMI48201
      2459New YorkNY10022

      In this case, although the data set is quite small, the hash function still distributes the entries evenly. This is not always the case with every database. However, as records are added, the distribution is likely to remain reasonably balanced.

      Directory-Based Sharding

      Directory-based sharding groups related items together on the same shard. This is also known as entity or relationship-based sharding. It typically uses the value contained in a certain field to decide what shard to use. Directory sharding is accomplished through the use of a static lookup table. The table contains a list of mappings between each possible value for the field and its designated shard. Each key can only map to one shard and must appear in the lookup table exactly once. However many keys can potentially be mapped to the same shard.

      As an example, the records in a table of customers can be mapped to shards based on the customer’s home state. The lookup table contains a list of all fifty states, which are the shard keys, and the shard it maps to. This allows for a system design where the records of all customers living in New England are stored on the first shard. Clients in the Mid-Atlantic are located on shard two. Clients residing in the Deep South are mapped to the third shard.

      Directory-based sharding provides a high level of control and flexibility in determining how the data is stored. When intelligently designed, it speeds up common table joins and the bulk retrieval of related data. This architecture is very helpful if the shard key can only be assigned a small number of possible values. Unfortunately, it is highly prone to clustering and imbalanced tables, and the overhead of accessing the lookup table degrades performance. However, the benefits of this architecture often outweighs its drawbacks.

      Directory-based sharding is a good choice for the stores database. The store entries can be distributed to the different shards based on their location. In this design, locations in New England and the mid-Atlantic are stored in the first shard, which serves as the North-East shard. Stores in the Midwest are written to the second shard.

      The first shard contains the entries displayed below.

      store_IDcitystatezip_code
      2250PittsburghPA15222
      2455BostonMA02108
      2459New YorkNY10022

      The second shard contains the remainder of the data.

      store_IDcitystatezip_code
      1001DetroitMI48201
      1350ChicagoIL60601
      2101ClevelandOH44114

      Although these two shards are perfectly balanced, this is not the main goal of directory sharding. It instead seeks to generate useful and relevant shards of closely-related information, which this example also accomplishes.

      Geographic-Based Sharding

      Geographic-based sharding, or Geo-sharding, is a specific type of directory-based sharding. Data is divided amongst the shards based on the location of the entry, which relates to the location of the server hosting the shard. The sharding key is typically a city, state, region, country, or continent. This groups geographically similar data on the same shard. It works the same way directory-based sharding does.

      A good example of geo-sharding relates to geographically dispersed customer data. The customer’s home state is used as a sharding key. The lookup table maps customers living in states in the same sales region to the same shard. Each shard is located on a server located in the same region as the customer data it contains. This makes it very quick and efficient for a regional sales team to access customer data.

      Is Sharding Right For Your Business?

      Because sharding has both advantages and drawbacks, it is important to consider which type of database benefits the most from sharding. The first part of any sharding strategy is to decide whether to shard at all. To generalize, sharding makes the most sense for a high-volume database that stores a large amount of data in a few simple tables. Sharding is especially compelling if a company expects a large increase in the size of its database. Sharding is also useful for organizations that want to access or co-locate their data on a regional basis. For instance, a large social media company would want its users to access database servers in the same country or on the same continent. This requires the company to shard its data based on user location.

      In other cases, the complexity and difficulty associated with sharding are greater than the benefits. A database with many small to medium-sized tables could use vertical scaling, increasing the storage and computing power on a single server. It could also use alternative strategies such as replication for greater resilience and read-only throughput.

      Conclusion

      This guide answers the question, “What is database sharding?”. Sharding is a method of distributing the data in a database table to several different shards based on the value of a sharding key. Each shard is stored on a different server. Ideally, the records in a sharded database are distributed amongst the shards in an equitable manner. The different shards share the same table definitions and schemas, but each record is only stored on a single shard.

      Sharding allows a database to scale horizontally, taking advantage of the increased storage, memory, and processing power that only multiple servers can offer. It also increases resiliency and performance. Each query only has to search through a portion of the total records, which is much faster. As a drawback, sharding increases the complexity of a database and increases the difficulty of joins and schema changes.

      Sharding can be accomplished using range sharding, hash sharding, or directory-based sharding. Range sharding is the easiest method, but is more likely to result in unequal shards. Hash sharding more effectively distributes the records, but is more difficult to implement. Directory-based sharding groups related items together on the same shard.

      A sharded database can be implemented using multiple Linode servers. Linode allows you to configure a full web application on a powerful Linux operating system running the industry-standard LAMP stack. Choose from a high-performance
      Dedicated CPU
      service, or a flexible and affordable
      Shared CPU
      alternative. Similarly, you can also use
      Linode’s Managed Database service
      to deploy a database cluster without the need to install and maintain the database infrastructure.

      More Information

      You may wish to consult the following resources for additional information
      on this topic. While these are provided in the hope that they will be
      useful, please note that we cannot vouch for the accuracy or timeliness of
      externally hosted materials.



      Source link

      How To Use a PostgreSQL Database in a Flask Application


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

      Introduction

      In web applications, you usually need a database, which is an organized collection of data. You use a database to store and maintain persistent data that can be retrieved and manipulated efficiently. For example, in a social media application, you have a database where user data (personal information, posts, comments, followers) is stored in a way that can be efficiently manipulated. You can add data to a database, retrieve it, modify it, or delete it, depending on different requirements and conditions. In a web application, these requirements might be a user adding a new post, deleting a post, or deleting their account, which might or might not delete their posts. The actions you perform to manipulate data will depend on specific features in your application. For example, you might not want users to add posts with no titles.

      Flask is a lightweight Python web framework that provides useful tools and features for creating web applications in the Python Language. PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It’s standards-compliant and has many advanced features such as reliable transactions and concurrency without read locks.

      In this tutorial, you’ll build a small book review web application that demonstrates how to use the psycopg2 library, a PostgreSQL database adapter that allows you to interact with your PostgreSQL database in Python. You’ll use it with Flask to perform basic tasks, such as connecting to a database server, creating tables, inserting data to a table, and retrieving data from a table.

      Prerequisites

      Step 1 — Creating the PostgreSQL Database and User

      In this step, you’ll create a database called flask_db and a database user called sammy for your Flask application.

      During the Postgres installation, an operating system user named postgres was created to correspond to the postgres PostgreSQL administrative user. You need to use this user to perform administrative tasks. You can use sudo and pass in the username with the -iu option.

      Log in to an interactive Postgres session using the following command:

      You will be given a PostgreSQL prompt where you can set up your requirements.

      First, create a database for your project:

      • CREATE DATABASE flask_db;

      Note: Every Postgres statement must end with a semi-colon, so make sure that your command ends with one if you are experiencing issues.

      Next, create a database user for our project. Make sure to select a secure password:

      • CREATE USER sammy WITH PASSWORD "https://www.digitalocean.com/community/tutorials/password';

      Then give this new user access to administer your new database:

      • GRANT ALL PRIVILEGES ON DATABASE flask_db TO sammy;

      To confirm the database was created, get the list of databases by typing the following command:

      You’ll see flask_db in the list of databases.

      When you are finished, exit out of the PostgreSQL prompt by typing:

      Postgres is now set up so that you can connect to and manage its database information via Python using the psycopg2 library. Next, you’ll install this library alongside the Flask package.

      Step 2 — Installing Flask and psycopg2

      In this step, you will install Flask and the psycopg2 library so that you can interact with your database using Python.

      With your virtual environment activated, use pip to install Flask and the psycopg2 library:

      • pip install Flask psycopg2-binary

      Once the installation is successfully finished, you’ll see a line similar to the following at the end of the output:

      Output

      Successfully installed Flask-2.0.2 Jinja2-3.0.3 MarkupSafe-2.0.1 Werkzeug-2.0.2 click-8.0.3 itsdangerous-2.0.1 psycopg2-binary-2.9.2

      You now have the required packages installed on your virtual environment. Next, you’ll connect to and set up your database.

      Step 3 — Setting up a Database

      In this step, you’ll create a Python file in your flask_app project directory to connect to the flask_db database, create a table for storing books, and insert some books with reviews into it.

      First with your programming environment activated, open a new file called init_db.py in your flask_app directory.

      This file will open a connection to the flask_db database, create a table called books, and populate the table using sample data. Add the following code to it:

      flask_app/init_db.py

      import os
      import psycopg2
      
      conn = psycopg2.connect(
              host="localhost",
              database="flask_db",
              user=os.environ['DB_USERNAME'],
              password=os.environ['DB_PASSWORD'])
      
      # Open a cursor to perform database operations
      cur = conn.cursor()
      
      # Execute a command: this creates a new table
      cur.execute('DROP TABLE IF EXISTS books;')
      cur.execute('CREATE TABLE books (id serial PRIMARY KEY,'
                                       'title varchar (150) NOT NULL,'
                                       'author varchar (50) NOT NULL,'
                                       'pages_num integer NOT NULL,'
                                       'review text,'
                                       'date_added date DEFAULT CURRENT_TIMESTAMP);'
                                       )
      
      # Insert data into the table
      
      cur.execute('INSERT INTO books (title, author, pages_num, review)'
                  'VALUES (%s, %s, %s, %s)',
                  ('A Tale of Two Cities',
                   'Charles Dickens',
                   489,
                   'A great classic!')
                  )
      
      
      cur.execute('INSERT INTO books (title, author, pages_num, review)'
                  'VALUES (%s, %s, %s, %s)',
                  ('Anna Karenina',
                   'Leo Tolstoy',
                   864,
                   'Another great classic!')
                  )
      
      conn.commit()
      
      cur.close()
      conn.close()
      

      Save and close the file.

      In this file, you first import the os module you’ll use to access environment variables where you’ll store your database username and password so that they are not visible in your source code.

      You import the psycopg2 library. Then you open a connection to the flask_db database using the psycopg2.connect() function. You specify the host, which is the localhost in this case. You pass the database name to the database parameter.

      You provide your username and password via the os.environ object, which gives you access to environment variables you set in your programming environment. You will store the database username in an environment variable called DB_USERNAME and the password in an environment variable called DB_PASSWORD. This allows you to store your username and password outside your source code, so that your sensitive information is not leaked when the source code is saved in source control or uploaded to a server on the internet. Even if an attacker gains access to your source code, they will not gain access to the database.

      You create a cursor called cur using the connection.cursor() method, which allows Python code to execute PostgreSQL commands in a database session.

      You use the cursor’s execute() method to delete the books table if it already exists. This avoids the possibility of another table named books existing, which might result in confusing behavior (for example, if it has different columns). This isn’t the case here, because you haven’t created the table yet, so the SQL command won’t be executed. Note that this will delete all of the existing data whenever you execute this init_db.py file. For our purposes, you will only execute this file once to initiate the database, but you might want to execute it again to delete whatever data you inserted and start with the initial sample data again.

      Then you use CREATE TABLE books to create a table named books with the following columns:

      • id: An ID of the serial type, which is an autoincrementing integer. This column represents a primary key you specify using the PRIMARY KEY keywords. The database will assign a unique value to this key for each entry.
      • title: The book’s title of the varchar type, which is a character type of variable length with a limit. varchar (150) means that the title can be up to 150 characters long. NOT NULL signifies that this column can’t be empty.
      • author: The book’s author, with a limit of 50 characters. NOT NULL signifies that this column can’t be empty.
      • pages_num: An integer representing the number of pages the book has. NOT NULL signifies that this column can’t be empty.
      • review: The book review. The text type signifies that the review can be text of any length.
      • date_added: The date the book was added to the table. DEFAULT sets the default value of the column to CURRENT_TIMESTAMP, which is the time at which the book was added to the database. Just like id, you don’t need to specify a value for this column, as it will be automatically filled in.

      After creating the table, you use the cursor’s execute() method to insert two books into the table, A Tale of Two Cities by Charles Dickens, and Anna Karenina by Leo Tolstoy. You use the %s placeholder to pass the values to the SQL statement. psycopg2 handles the insertion in the background in a way that prevents SQL Injection attacks.

      Once you finish inserting book data into your table, you use the connection.commit() method to commit the transaction and apply the changes to the database. Then you clean things up by closing the cursor with cur.close(), and the connection with conn.close().

      For the database connection to be established, set the DB_USERNAME and DB_PASSWORD environment variables by running the following commands. Remember to use your own username and password:

      • export DB_USERNAME="https://www.digitalocean.com/community/tutorials/sammy"
      • export DB_PASSWORD="https://www.digitalocean.com/community/tutorials/password"

      Now, run your init_db.py file in the terminal using the python command:

      Once the file finishes execution with no errors, a new books table will be added to your flask_db database.

      Log in to an interactive Postgres session to check out the new books table.

      Connect to the flask_db database using the c command:

      Then use a SELECT statement to get the titles and authors of books from the books table:

      • SELECT title, author FROM books;

      You’ll see an output like the following:

              title         |      author
      ----------------------+------------------
       A Tale of Two Cities | Charles Dickens
       Anna Karenina        | Leo Tolstoy
      

      Quit the interactive session with q.

      Next, you’ll create a small Flask application, connect to the database, retrieve the two book reviews you inserted into the database, and display them on the index page.

      Step 4 — Displaying Books

      In this step, you’ll create a Flask application with an index page that retrieves the books that are in the database, and display them.

      With your programming environment activated and Flask installed, open a file called app.py for editing inside your flask_app directory:

      This file will set up your database connection and create a single Flask route to use that connection. Add the following code to the file:

      flask_app/app.py

      import os
      import psycopg2
      from flask import Flask, render_template
      
      app = Flask(__name__)
      
      def get_db_connection():
          conn = psycopg2.connect(host="localhost",
                                  database="https://www.digitalocean.com/community/tutorials/flask_db",
                                  user=os.environ['DB_USERNAME'],
                                  password=os.environ['DB_PASSWORD'])
          return conn
      
      
      @app.route('/')
      def index():
          conn = get_db_connection()
          cur = conn.cursor()
          cur.execute('SELECT * FROM books;')
          books = cur.fetchall()
          cur.close()
          conn.close()
          return render_template('index.html', books=books)
      

      Save and close the file.

      Here, you import the os module, the psycopg2 library, and the Flask class and the render_template() from the flask package. You make a Flask application instance called app.

      You define a function called get_db_connection(), which opens a connection to the flask_db database using the user and password you store in your DB_USERNAME and DB_PASSWORD environment variables. The function returns the conn connection object you’ll be using to access the database.

      Then you create a main / route and an index() view function using the app.route() decorator. In the index() view function, you open a database connection using the get_db_connection() function, you create a cursor, and execute the SELECT * FROM books; SQL statement to get all the books that are in the database. You use the fetchall() method to save the data in a variable called books. Then you close the cursor and the connection. Lastly, you return a call to the render_template() function to render a template file called index.html passing it the list of books you fetched from the database in the books variable.

      To display the books you have in your database on the index page, you will first create a base template, which will have all the basic HTML code other templates will also use to avoid code repetition. Then you’ll create the index.html template file you rendered in your index() function. To learn more about templates, see How to Use Templates in a Flask Application.

      Create a templates directory, then open a new template called base.html:

      • mkdir templates
      • nano templates/base.html

      Add the following code inside the base.html file:

      flask_app/templates/base.html

      <!DOCTYPE html>
      <html lang="en">
      <head>
          <meta charset="UTF-8">
          <title>{% block title %} {% endblock %}- FlaskApp</title>
          <style>
              nav a {
                  color: #d64161;
                  font-size: 3em;
                  margin-left: 50px;
                  text-decoration: none;
              }
      
              .book {
                  padding: 20px;
                  margin: 10px;
                  background-color: #f7f4f4;
              }
      
              .review {
                      margin-left: 50px;
                      font-size: 20px;
              }
      
          </style>
      </head>
      <body>
          <nav>
              <a href="https://www.digitalocean.com/community/tutorials/{{ url_for('index') }}">FlaskApp</a>
              <a href="#">About</a>
          </nav>
          <hr>
          <div class="content">
              {% block content %} {% endblock %}
          </div>
      </body>
      </html>
      

      Save and close the file.

      This base template has all the HTML boilerplate you’ll need to reuse in your other templates. The title block will be replaced to set a title for each page, and the content block will be replaced with the content of each page. The navigation bar has two links, one for the index page where you use the url_for() helper function to link to the index() view function, and the other for an About page if you choose to include one in your application.

      Next, open a template called index.html. This is the template you referenced in the app.py file:

      • nano templates/index.html

      Add the following code to it:

      flask_app/templates/index.html

      
      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} Books {% endblock %}</h1>
          {% for book in books %}
              <div class="book">
                  <h3>#{{ book[0] }} - {{ book[1] }} BY {{ book[2] }}</h3>
                  <i><p>({{ book[3] }} pages)</p></i>
                  <p class="review">{{ book[4] }}</p>
                  <i><p>Added {{ book[5] }}</p></i>
              </div>
          {% endfor %}
      {% endblock %}
      

      Save and close the file.

      In this file, you extend the base template, and replace the contents of the content block. You use an <h1> heading that also serves as a title.

      You use a Jinja for loop in the line {% for book in books %} to go through each book in the books list. You display the book ID, which is the first item using book[0]. You then display the book title, author, number of pages, review, and the date the book was added.

      While in your flask_app directory with your virtual environment activated, tell Flask about the application (app.py in this case) using the FLASK_APP environment variable. Then set the FLASK_ENV environment variable to development to run the application in development mode and get access to the debugger. For more information about the Flask debugger, see How To Handle Errors in a Flask Application. Use the following commands to do this:

      • export FLASK_APP=app
      • export FLASK_ENV=development

      Make sure you set the DB_USERNAME and DB_PASSWORD environment variables if you haven’t already:

      • export DB_USERNAME="https://www.digitalocean.com/community/tutorials/sammy"
      • export DB_PASSWORD="https://www.digitalocean.com/community/tutorials/password"

      Next, run the application:

      With the development server running, visit the following URL using your browser:

      http://127.0.0.1:5000/
      

      You’ll see the books you added to the database on the first initiation.

      Index Page

      You’ve displayed the books in your database on the index page. You now need to allow users to add new books. You’ll add a new route for adding books in the next step.

      Step 5 — Adding New Books

      In this step, you’ll create a new route for adding new books and reviews to the database.

      You’ll add a page with a web form where users enter the book title, book author, the number of pages, and the book review.

      Leave the development server running and open a new terminal window.

      First, open your app.py file:

      For handling the web form, you’ll need to import a few things from the flask package:

      • The global request object to access submitted data.
      • The url_for() function to generate URLs.
      • The redirect() function to redirect users to the index page after adding a book to the database.

      Add these imports to the first line in the file:

      flask_app/app.py

      
      from flask import Flask, render_template, request, url_for, redirect
      
      # ...
      

      Then add the following route at the end of the app.py file:

      flask_app/app.py

      
      # ...
      
      
      @app.route('/create/', methods=('GET', 'POST'))
      def create():
          return render_template('create.html')
      

      Save and close the file.

      In this route, you pass the tuple ('GET', 'POST') to the methods parameter to allow both GET and POST requests. GET requests are used to retrieve data from the server. POST requests are used to post data to a specific route. By default, only GET requests are allowed. When the user first requests the /create route using a GET request, a template file called create.html will be rendered. You will later edit this route to handle POST requests for when users fill and submit the web form for adding new books.

      Open the new create.html template:

      • nano templates/create.html

      Add the following code to it:

      flask_app/templates/create.html

      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} Add a New Book {% endblock %}</h1>
          <form method="post">
              <p>
                  <label for="title">Title</label>
                  <input type="text" name="title"
                         placeholder="Book title">
                  </input>
              </p>
      
              <p>
                  <label for="author">Author</label>
                  <input type="text" name="author"
                         placeholder="Book author">
                  </input>
              </p>
      
              <p>
                  <label for="pages_num">Number of pages</label>
                  <input type="number" name="pages_num"
                         placeholder="Number of pages">
                  </input>
              </p>
              <p>
              <label for="review">Review</label>
              <br>
              <textarea name="review"
                        placeholder="Review"
                        rows="15"
                        cols="60"
                        ></textarea>
              </p>
              <p>
                  <button type="submit">Submit</button>
              </p>
          </form>
      {% endblock %}
      

      Save and close the file.

      You extend the base template, set a heading as a title, and use a <form> tag with the attribute method set to post to indicate that the form will submit a POST request.

      You have a text field with the name title, which you’ll use to access the title data in your /create route.

      You have a text field for the author, a number field for the number of pages, and a text area for the book review.

      Last, you have a Submit button at the end of the form.

      Now, with the development server running, use your browser to navigate to the /create route:

      http://127.0.0.1:5000/create
      

      You will see an Add a New Book page with an input field for a book title, one for its author, and one for the number of pages the book has, a text area for the book’s review, and a Submit button.

      Add a New Book

      If you fill in the form and submit it, sending a POST request to the server, nothing happens because you did not handle POST requests on the /create route.

      Open app.py to handle the POST request the user submits:

      Edit the /create route to look as follows:

      flask_app/app.py

      
      # ...
      
      @app.route('/create/', methods=('GET', 'POST'))
      def create():
          if request.method == 'POST':
              title = request.form['title']
              author = request.form['author']
              pages_num = int(request.form['pages_num'])
              review = request.form['review']
      
              conn = get_db_connection()
              cur = conn.cursor()
              cur.execute('INSERT INTO books (title, author, pages_num, review)'
                          'VALUES (%s, %s, %s, %s)',
                          (title, author, pages_num, review))
              conn.commit()
              cur.close()
              conn.close()
              return redirect(url_for('index'))
      
          return render_template('create.html')
      

      Save and close the file.

      You handle POST requests inside the if request.method == 'POST' condition. You extract the title, author, number of pages, and the review the user submits from the request.form object.

      You open a database using the get_db_connection() function, and create a cursor. Then you execute an INSERT INTO SQL statement to insert the title, author, number of pages, and review the user submitted into the books table.

      You commit the transaction and close the cursor and connection.

      Lastly, you redirect the user to the index page where they can see the newly added book below the existing books.

      With the development server running, use your browser to navigate to the /create route:

      http://127.0.0.1:5000/create
      

      Fill in the form with some data and submit it.

      You’ll be redirected to the index page where you’ll see your new book review.

      Next, you’ll add a link to the Create page in the navigation bar. Open base.html:

      Edit the file to look as follows:

      flask_app/templates/base.html

      
      <!DOCTYPE html>
      <html lang="en">
      <head>
          <meta charset="UTF-8">
          <title>{% block title %} {% endblock %} - FlaskApp</title>
          <style>
              nav a {
                  color: #d64161;
                  font-size: 3em;
                  margin-left: 50px;
                  text-decoration: none;
              }
      
              .book {
                  padding: 20px;
                  margin: 10px;
                  background-color: #f7f4f4;
              }
      
              .review {
                      margin-left: 50px;
                      font-size: 20px;
              }
      
          </style>
      </head>
      <body>
          <nav>
              <a href="https://www.digitalocean.com/community/tutorials/{{ url_for("index') }}">FlaskApp</a>
              <a href="https://www.digitalocean.com/community/tutorials/{{ url_for("create') }}">Create</a>
              <a href="#">About</a>
          </nav>
          <hr>
          <div class="content">
              {% block content %} {% endblock %}
          </div>
      </body>
      </html>
      

      Save and close the file.

      Here, you add a new <a> link to the navigation bar that points to the Create page.

      Refresh your index page and you’ll see the new link in the navigation bar.

      You now have a page with a web form for adding new book reviews. For more on web forms, see How To Use Web Forms in a Flask Application. For a more advanced and more secure method of managing web forms, see How To Use and Validate Web Forms with Flask-WTF.

      Conclusion

      You built a small web application for book reviews that communicates with a PostgreSQL database. You have basic database functionality in your Flask application, such as adding new data to the database, retrieving data, and displaying it on a page.

      If you would like to read more about Flask, check out the other tutorials in the Flask series.



      Source link