A database that suffers from bloat and performance issues will slow down your site and hurt your search rankings. In addition, you could affect the overall User Experience (UX) too. In turn, this can impact your traffic figures, and trickle down back to your placings in search results (among other issues).

For this tutorial, we’ll look at WordPress database optimization from a number of angles. We’ll talk about the general structure you’ll see, along with lots of optimization techniques to utilize. We’ll also mention tools and services that can help you keep your databases ticking over.

How To Understand the WordPress Database Structure

In short, the WordPress database is the backbone of your website. It houses almost everything, such as content, user data, settings, and much more. Regarding WordPress, the database ‘tables’ are responsible for storing specific types of information.

The platform uses the MySQL or MariaDB database management systems, and you’ll access it with tools such as phpmyAdmin or Adminer:

For instance, the wp_posts table contains your posts and pages, while wp_users holds data about your site’s users. We’ll look at some of these tables in greater detail shortly.

Upon installation, WordPress creates a set of default tables to cover all the typical use cases you’d need. However, the plugins,themes, and install may also create tables to store specific and related data.

This modular approach is excellent in many aspects, as it allows for extensive functionality. On the flip side, though, too many superfluous tables (along with mismanagement) can also lead to bloat.

Understanding the database structure for WordPress is crucial for two reasons:

• Performance. Good database organization lets you retrieve data quicker, which will have a direct impact on load times and performance.
• Maintenance. Knowing which tables correspond to the different parts of your site will help during maintenance. For instance, if a particular plugin is causing issues, you might troubleshoot its associated tables.

Regular cleaning and optimization of your database’s tables can prevent them from becoming large and unwieldy. As we note, the performance impact could affect you in a negative way.

WordPress-Specific Tables

Sites of all types use databases, not just WordPress. The platform has its own database table types and roles, and if you want to optimize them, it’s important to understand them fully.

While we won’t cover them all here, you’ll jump into certain tables more than others:

• wp_options. This stores your site-wide settings and is arguably one of the most accessed tables within your database. You’ll want to keep this table lean through good optimization.
• wp_postmeta. Your post metadata lives here. As your site grows, this can become one of the largest tables in your database.
• wp_users and wp_usermeta. All of the information related to your site’s users and their metadata will sit in these two tables. In some rare cases, you may need to jump in here to change a password if you’re locked out of WordPress.

Remember that each additional plugin or theme you add to your site may modify this structure too. It would be rare to see a theme or plugin remove one of these tables, however.

Even so, you should regularly review and understand these potential changes. It’s key for maintaining an optimized database that supports, rather than hinders, your site’s performance.

Why WordPress Database Optimization is Necessary for Most Sites

Optimizing your WordPress database is not only good practice; it’s a necessity for most sites that want to provide a seamless experience. Your database is the central storage for all your site’s important data. Its health directly impacts your site’s performance, speed, and reliability.

As such, there are two general reasons why regular WordPress database optimization should be a workflow staple:

• Enhanced site speed and performance. Your WordPress database will include some unnecessary or redundant data over time, such as transient options, post revisions, spam comments, or outdated drafts. This bloat will make it slower to retrieve information.
• Improved user experience. Users also expect a smooth and fast browsing experience. A well-optimized database translates to quicker page loading and more efficient data processing. Simply put, every on-site interaction relies on database queries. With greater efficiency comes better UX.

There are also some more advanced reasons to make sure you optimize your database. For instance, an unoptimized database puts additional stress on your server as it works harder to find and serve data. This will have a critical effect if you use shared hosting with limited resources. It’s partly one reason why Kinsta doesn’t offer shared hosting at all.

What’s more, as your site grows, so does your database. While you may run a small, manageable system at first, this can grow in complexity fast. With regular optimization, you can ensure your database can handle the scaling your site undertakes without compromising performance.

In short, WordPress database optimization will boost response times. A loading delay of even a few seconds can lead to increased bounce rates and lost traffic, which doesn’t spell good news for your search rankings.

How to Carry Out Regular Maintenance and Cleaning

If you clean and regularly maintain your WordPress database, you have one of the best ways to keep your site running as smoothly as possible. However, databases can become bloated with unnecessary data over time, so a regular and consistent workflow is important.

What’s more, you should use all of the tools and services at your disposal in the most optimal way. The good news is that WordPress offers a number of approaches to maintain your database.

A plugin will have myriad ways to optimize a database. WP-Optimize is a popular solution for the job. There are others, but this one has great ratings and reviews on WordPress.org, is free, and gets regular updates.

We’ll showcase how to optimize your WordPress database using this plugin throughout the post, but you can transpose the instructions to your plugin of choice too. For Kinsta customers, make sure the plugin you choose meets our requirements and doesn’t appear on our banned list.

The next few sections will look at this in greater detail, and we’ll cover the manual approach and using WP-Optimize. We’ll cover Kinsta’s own tools later on. First, though, let’s cover some pre-optimization tasks.

What To Do Before You Tackle Optimization

Before you jump into your database, there are some simple tasks to tick off. For starters, you should always make a full backup of your site and database. If the worst scenario happens and you ruin your site, you can bring it back in minutes.

You will also want to delete any plugins or themes you don’t use on your site. This could solve a few problems, and not only with your database. It can help harden your site’s security too.

However, note that depending on the plugin or theme, this can leave unwanted tables behind. Of course, this is the exact reason we want to optimize the database, so understanding which plugins and themes leave transient data behind will help down the line.

The final task will be something you will find once you log into your database management tool of choice. Database errors can obviously be an indication of performance issues, so these should be something you resolve before you carry out further optimization.

In short, the process is to select all of your tables, and then use the Check table button to generate a report.

If you see OK or similar, this is optimal. However, any errors need resolving before you carry on. This is where a support request might be a good idea.

1. Optimize Your Database Tables

The first step is to optimize the tables within your database. With a manual approach, head to the Databases link within your management tool, then choose your database:

In many cases, you will already be in the database for your WordPress site. Regardless, you’ll see a list of tables within your database. Simply bulk select them all, then choose the Optimize table option from the drop-down menu before you click Go:

After some time, you’ll see a report outlining the status of each table within your database:

With WP-Optimize, head to WP-Optimize > Database > Optimizations. From here, click the Run Optimization button next to the Optimize database tables option:

The plugin will run through all of the tables, and then give you a success message. At this point, you can move on to post revisions.

2. Clean Up Post Revisions

WordPress’s post revision system means every save you make can add up over time. You can prune these from your database with ease though using SQL queries. The most simplistic way to do this is with one line:

DELETE FROM wp_posts WHERE post_type = 'revision’;

This will delete all types of revision posts from the table. However, there is also associated data in other tables too. To capture and remove all of this, you can use the following snippet:

DELETE FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_term_relationships WHERE object_id NOT IN (SELECT ID FROM wp_posts);
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);

Of course, you should use the right table prefix for your own database if you copy and paste this. With a plugin, this task takes seconds. Much like general WordPress database optimization, WP-Optimize gives you a one-click button from within WordPress:

In the future, you could limit the number of revisions WordPress uses through accessing your wp-config.php file. Here, add the following line to the file and save your changes:

define( 'WP_POST_REVISIONS', X );

Here, X is the number of revisions you’d like to keep. You could also specify false here, but we don’t recommend this. You always want to have at least one revision to fall back on if you need it.

3. Delete Spam Comments and Trashed Items

We can also use SQL queries to remove spam comments. Note that when you moderate comments, unwanted ones stay in your database for 30 days. This means comments marked as spam within that time frame will sit in your database.

You can clear these out completely with a few lines of SQL in your database management tool:

DELETE FROM wp_comments, wp_commentmeta
USING wp_comments
LEFT JOIN wp_commentmeta ON wp_comments.comment_ID = wp_commentmeta.comment_id
WHERE wp_comments.comment_approved = 'spam’;

It’s a similar case for items you send to the trash within WordPress. There may be a lot of content ‘in limbo,’ which you can delete with another SQL query:

DELETE p, pm, tr
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
WHERE p.post_status = 'trash’;

As with post revisions, you can set values for the time it takes to remove trash items within wp-config.php:

define( 'EMPTY_TRASH_DAYS', X )

Within WP-Optimize, there are three options to help you remove spam comments and the WordPress trash:

There is also the option to remove unapproved comments. This may be useful in some situations, but we wouldn’t recommend this. Instead, moderate these comments, then clear them if you need to.

4. Remove Unused Tags

Taxonomies are important to WordPress but they can become a huge collection over time. This is an excellent use case for optimizing your database, and as with other techniques, you can use an SQL query:

DELETE t, tt
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
LEFT JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag' AND tt.count = 0;

The nearest option to remove unused tags within WP-Optimize is Clean post meta data. This assesses whether you have any orphaned metadata and will remove it:

While this option may remove categories and other data, too, it’s a safe solution to use, especially if no other post or page uses them.

5. Get Rid of Pingbacks and Trackbacks

We’re not sure how many WordPress websites use pingbacks and trackbacks in the current era, but they can still clutter your database if you leave the setting on. The first task is to check whether you do have this option disabled. This is a simple job within the Settings > Discussion screen within WordPress:

In the Default Post Settings section, ensure you untick the “Attempt to notify any blogs…” and “Allow link notifications…” options and save your changes. Next, head back into your database management tool and run the following query:

DELETE c, cm
FROM wp_comments c
LEFT JOIN wp_commentmeta cm ON c.comment_ID = cm.comment_id
WHERE c.comment_type IN ('trackback', 'pingback’);

Both of these live in the same place as comments, although the type is different and is what the query focuses on. WP-Optimize offers two separate options for each of these:

Once you complete this job, you shouldn’t see trackbacks or pingbacks in your database again!

Using Kinsta’s Automatic WordPress Database Optimization

For Kinsta customers, you won’t need a WordPress database optimization plugin. We carry out continuous automatic optimizations around the clock for all sites. By extension, this means you won’t need to know anything about SQL queries, where data sits within your database, or anything else relevant to WordPress database optimization.

Continuous Database Optimization is part of our Application Performance Monitoring (APM). This keeps your WordPress database (and site) lean and efficient without the need for manual intervention.

APM cleans up your database by removing unnecessary data such as transients, orphaned metadata, and spam comments. As such, you can ensure your database remains optimized without input.

Using APM isn’t the focus of this post, but we do cover it within our documentation. When it comes to database monitoring, APM keeps a watch on the slowest SQL queries that run:

You access APM through the MyKinsta dashboard, specifically the APM screen. Note that you may need to turn this on first:

From here, you need to give APM some time to collate data. However, once it’s visible, you can check on those queries that may need further optimization.

How To Optimize Database Queries for Greater Efficiency

Optimizing the database queries you use is crucial to enhance the performance and speed of your WordPress site. Efficient queries mean faster retrieval of data, which in turn leads to improved load times and a better user experience.

Given that you may use queries to carry out WordPress database optimization, here are a couple of tips on how you can make them more efficient:

• Optimize your query structures. You’ll notice that we don’t use wildcards (or asterisks) within our example queries. Instead of using SELECT *, be specific with regard to the exact columns you need. Also, use JOIN instead of subqueries where possible. Subqueries can be less efficient, especially if they don’t have a good structure or involve large datasets.
• Use query caching. Tools such as Redis can store the results of queries in memory. This means the results of the query can be served from the cache rather than querying the database again next time.

These are vague tips, but there is plenty more you can do here. Let’s quickly look at this next.

Advanced WordPress Database Optimization and Troubleshooting Tips

‘Indexing’ can help you add a quick reference guide to your database. It helps the database server find data faster without scanning every row of a table.

To achieve this, identify the columns that see frequent use in your queries and consider adding indexes to them. You can do this from within phpMyAdmin (or similar). First, click on the table you’d like to index, and head to the Structure tab:

Next, select the columns you want to index, and choose the Index option at the bottom of the table:

Once you save your changes, this will index those columns.

The EXPLAIN statement can also help you to understand how MySQL executes your query. This can help you spot inefficiencies and understand how your query interacts with the indexes. To run this, add the statement to the front of an existing query. When you run it, SQL will break down how it will execute the query:

We can’t cover everything about the EXPLAIN statement here, although the MySQL documentation covers almost everything you need to know about it.

Monitoring Performance

Monitoring the performance of your WordPress database is an essential part of running a site. It will help to identify potential issues before they escalate and ensure that your site remains efficient and responsive.

Many of the techniques we cover in this post will go towards performance monitoring, such as using EXPLAIN on slow queries. However, there’s much more that’s possible. In fact, phpmyAdmin includes its own performance metrics within the Status tab for the server:

This can let you view query execution times and processes (on the Query statistics tab), which helps to identify those queries that need optimization. For advanced monitoring, MySQL Workbench will be invaluable:

This gives you advanced features for database design, development, and administration. It also provides performance reports and diagnostics that can help you fine-tune your database.

Within WordPress, the perennial Query Monitor plugin will let you monitor database queries, hooks, conditionals, HTTP requests, and more.

It’s particularly useful for detecting slow queries and identifying the plugins or themes causing them. In most cases, though, this is a development plugin, so may not be suitable for a production site.

How To Handle Large Databases in WordPress

As a WordPress site grows in content, users, and traffic, its database naturally expands. A large database will be something you will encounter (or worry about) a lot when running a site.

Most of the advice we give in this article can be suitable for a large database – and reducing it. However, there are plenty of other tips we can give for those databases that will inherently be larger than is typical:

• Archive older data. Instead of keeping all data live, consider archiving old posts or user data that you don’t access regularly.
• Use a Content Delivery Network (CDN). Offloading and serving static resources such as images, videos, and downloads from another server can reduce the load on your site and help to speed it up. What’s more, your site will load faster for users no matter where they are.
• Custom queries. If you’re a WordPress developer, write efficient queries for your themes and plugins. This will help you fetch only what you need and keep efficiency high.

You may even consider advanced techniques such as table partitioning and ‘database sharding.’ This is where data is split across multiple databases. It’s often complex and typically requires expert management.

Speaking of which, experts are a necessity when it comes to high performance from your database and attached website. Kinsta’s WordPress hosting is scalable, managed, and optimized for the platform.

Plans begin from $35 per month, and our architecture can support a simple blog, all the way up to enterprise-level networks. Drop us a line to find out how we can become your long-term hosting partner, regardless of your goals.

Summary

Your WordPress database is like a car’s engine: without tuning, it won’t perform as well as you need it to. In fact, a lack of WordPress database optimization could see you drop from first to last place when it comes to search rankings. Your users will also notice your site becoming an also-ran, so a tip-top database that runs fast is crucial for success.

There are plenty of ways you can do this, but a combination of manual techniques, plugins, and Kinsta’s own optimization tools will give you the best benefits. In many cases, automation can help you carry out many of these tasks without needing to log in or run any specific tool yourself.

We’d love to hear whether our WordPress database optimization tips have worked for you. Let us know which had the most impact in the comments section below!

The post How To Master WordPress Database Optimization on Kinsta appeared first on Kinsta®.

A host of frameworks and libraries make getting a Python application up and running easier. Those include Django, FastAPI, and Flask. The Flask framework attracts Python developers by supporting easy prototyping and customizability.

This hands-on article demonstrates how to develop a simple database-connected Python application using Flask.

Python Apps Made Easier With Flask

Developed in 2010, Flask is well-suited to developing Python web applications thanks to its ease of use and flexibility. Its lean architecture focuses on providing the basics while making it easy to add libraries for the functionality you need. This approach makes Flask ideal for many projects, from simple applications to complex systems.

Flask offers several tools and capabilities to support web app development, including:

• Libraries and tools to manage HTTP requests and responses
• The ability to route requests to designated functions
• Support for rendering templates
• Support for databases
• Authentication and authorization systems

How To Create Your Python Flask App

You can explore the benefits of using Flask for web app development by creating a Python web app using Flask. Then, you can build and deploy the application using Kinsta’s Web Application Hosting service and connect it to a Managed Database on the Kinsta platform.

Python Flask App Prerequisites

To follow this tutorial, you’ll need:

• Python version 3 or higher
• MySQL Workbench
• Python’s pip and virtualenv packages installed
• A code editor
• An account at Bitbucket, GitHub, or GitLab to host your Git repository
• A MyKinsta Account (free to sign up) to serve the application

Installing Flask for Python

Go to your terminal (Linux or macOS) or Command Prompt (Windows). Start by creating a directory called flask_demo.

Change to the new directory and create a Python virtual environment using the python3 -m venv venv command. In this case, we are also using venv as the name for the directory that will support the virtual environment.

Activate the virtual environment using one of these commands:

• venv\Scripts\activate in Windows
• source venv/bin/activate in Linux or macOS

Now, install Flask using pip by running pip install flask.

The work in your terminal so far should look something like this:

Building a Base Application

Next, create the base application and review its functionality by rendering content to the browser.

In the flask_demo directory, create a file called demo.py and add the following code:

from flask import Flask

app = Flask(__name__)

# Routes
@app.route('/')
def index():
    return "Happy Coding!"

if __name__ == '__main__':
    app.run(debug=True)

This code imports Flask from the flask module and creates an instance of it called app. The code then creates a route that returns text displaying “Happy Coding!” when users visit the app in a browser. Finally, it executes the development server once the script starts.

Start the application by running flask --app demo run in the terminal. The --app flag specifies the location of the application it’ll execute — here, the demo.py file.

Adding Templates to Your Python Flask App

Adding templates to your app will bolster your content. First, make a directory called templates in your application’s root. Next, move into the templates directory and create a file called index.html containing the following HTML code:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>HomePage</title>
</head>
<body>
    <h3>Flask Demo Application</h3>
    <p>My name is John Doe - learning about Application Deployment!</p>
</body>
</html>

In demo.py, import render_template from the flask module and render the index.html template in the route function like this:

from flask import Flask, render_template

app = Flask(__name__)

# Routes
@app.route('/')
def index():
    return render_template('index.html')

if __name__ == '__main__':
    app.run(debug=True)

Next, serve your application by running flask --app demo run in your local environment. Use the local address reported in the terminal to launch the app in your browser. You should see something like this:

Connecting Your Flask App to a Local Database

You will create a connection to a local database — MySQL — that stores application content.

To connect your Flask application to MySQL, install the following:

• flask_mysqldb, the MySQL connector for Flask, using pip install flask_mysqldb
• Python-dotenv, for reading environment variables, using pip install python-dotenv
• The Python MySQL connector, using pip install mysql-connector-python
• The MySQL dependency, using pip install mysqlclient

Head to MySQL Workbench to create a database. Make sure you add a database user with permissions to access the database and create tables.

Create a .env file in your application’s root directory to hold the database connection details. You would add your database user credentials and the database name to this template:

DB_HOST="localhost"
DB_USER="your-db-user"
DB_PASSWORD="your-db-password"
DB_NAME="your-db-name"

In a revised demo.py script, we will now import the MySQL connector and use Python-dotenv to read the environment variable keys in the .env file. This new demo.py script also checks for the existence of a table named persons in the database and will create and populate it if it does not exist.

import os
from flask import Flask, render_template
from flask_mysqldb import MySQL

from dotenv import load_dotenv
load_dotenv()

app = Flask(__name__)

app.config['MYSQL_HOST'] = os.getenv("DB_HOST")
app.config['MYSQL_USER'] = os.getenv("DB_USER")
app.config['MYSQL_PASSWORD'] = os.getenv("DB_PASSWORD")
app.config['MYSQL_DB'] = os.getenv("DB_NAME")

mysql = MySQL(app)

@app.route('/')
def index():
    cursor = mysql.connection.cursor()

    cursor.execute("SHOW TABLES LIKE 'persons'")
    result = cursor.fetchone()

    if not result:
        cursor.execute(''' CREATE TABLE persons (id INTEGER, firstname VARCHAR(20), lastname VARCHAR(20)) ''')
        cursor.execute(''' INSERT INTO persons VALUES(1, 'John', 'Doe') ''')
        cursor.execute(''' INSERT INTO persons VALUES(2, 'Milly', 'Winfrerey') ''')
        mysql.connection.commit()

    cursor.execute('SELECT * FROM persons')
    entry = cursor.fetchall()
    cursor.close()
    return render_template('index.html', entry=entry)

After instantiating Flask, the code above uses environment variables to capture the database attributes from the .env file in your application’s root.

Then, the code instantiates MySQL and associates it with Flask. It creates a cursor object in the index route. Next, the code checks for a table named persons in the database. If it is not found, it creates it with the attributes id, firstname, and lastname and inserts two rows of data.

The next three lines execute an SQL command to select all rows from the persons table and fetch the results. The cursor object is closed, and the results of the query are passed as the context variable entry for rendering with the template.

Here’s a revised index.html template file that can process the results of the database query:

<!DOCTYPE html><html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>HomePage</title>
</head>
<body>
    <h3>Flask Demo Application</h3>
    {% for e in entry %}
        <p>My name is {{e[1]}} {{e[2]}} - learning about Application Deployment!</p>
    {% endfor %}
</body>
</html>

Execute the application, then return to MySQL Workbench to check the data. It should look like the following:

When you query the table, the two entries generated by the app are returned. Your application now renders the following database-derived content in the browser:

How To Deploy Your Python Flask App to Kinsta

Now that your application is up and running locally, you can make it visible to the world by hosting it on Kinsta. You can pair Kinsta’s Web Application Hosting and Managed Database Hosting services to bring this app (and your future efforts) to life in the cloud. And you can try them both for free.

Preparing Your Python Project for Deployment

Kinsta’s Web Application Hosting platform deploys your code from your favorite Git host. Your next step is to configure your application environment to support that pathway and allow Kinsta to deploy your application with all its required dependencies.

Start by creating a new directory within your flask_demo project root. Let’s call it myapp. Then move the templates directory and the demo.py file into myapp.

Inside the myapp directory, create a wsgi.py file with the following content:

from myapp.demo import app as application

if __name__ == "__main__":
    application.run(debug=True)

The build process at Kinsta will also use pip to generate your application. You can pass a list of your app’s dependencies to pip on the production side using a requirements.txt file in the project’s root directory.

While still working in the venv virtual environment and within the flask_demo root directory, you can generate a requirements.txt file that is specific to your project with the following command:

pip freeze > requirements.txt

The contents of the resulting text file will look something like this:

blinker==1.7.0
click==8.1.7
Flask==3.0.0
Flask-MySQLdb==2.0.0
itsdangerous==2.1.2
Jinja2==3.1.2
MarkupSafe==2.1.3
mysql-connector-python==8.2.0
mysqlclient==2.2.1
protobuf==4.21.12
python-dotenv==1.0.0
Werkzeug==3.0.1

You won’t be sending the .env file with its database secrets to the production server. Since you won’t need the python-dotenv library to read .env in production, you can remove its reference from requirements.txt and remove (or comment out) these lines in demo.py:

from dotenv import load_dotenv
load_dotenv()

Adding a Python WSGI HTTP Server to the Project

One thing missing from the requirements above is a way to serve the application via HTTP in a production environment. The development server used on your local machine won’t do. For this project, you’ll use the Web Server Gateway Interface (WSGI) package Gunicorn between the app and Kinsta’s Nginx web servers.

You can add a Gunicorn requirement to your project by installing it within your virtual environment like this:

pip install gunicorn

After Gunicorn is installed, use pip to generate requirements.txt again.

An alternative to installing Gunicorn locally is to edit requirements.txt and simply add an entry like this:

gunicorn==21.2.0

To wrap up the groundwork for the WSGI server, create a file in the project’s root directory named Procfile and add the following line:

web: gunicorn myapp.wsgi

This will be the basis of the start command for your app in production.

Getting Your Project Ready for Git

The revised directory structure is ready for deployment at Kinsta, but you don’t want all of those files going to production. Create a .gitignore file in the project root with content like this:

/venv
.env

This will keep the files within the venv directory and the local database secrets in .env from uploading to your Git host.

You can now initiate your local Git environment and push the code to your Git host using your preferred tools.

Deploying Your Python Flask App to Kinsta

Login to your MyKinsta dashboard and make sure you have authorized Kinsta to access your Git service provider. Follow the steps to add an application, selecting the repository and branch on the Git host where Kinsta will find this Flask project code.

When configuring the build environment, select Use Buildpacks to set up container image, but leave all other settings at their defaults. (You will not provide a Start command because that is already defined in your Procfile.)

After reviewing billing information (you can still get started for free!), click the Build now button and watch the progress in the log viewer:

Adding a Database for Your Python Flask App

Kinsta has four managed database options to meet your needs and application requirements: Redis, PostgreSQL, MariaDB, and MySQL. For this tutorial, we have been building for the MySQL database service.

Follow the official instructions for adding a database, remembering to select the same data center you chose for your Flask application.

After creating the database, select it from the list of your available DB servers and scroll down to the Internal connections / Allowed applications section of the Overview tab. When you click the Add connection button, your Flask application service in the same data center will be available to select:

Click the Add environment variables to the application checkbox before creating the new connection. This displays the environment variables that will carry your database secrets — all handled securely without the need for the .env file.

At the bottom of the above dialog, the settings Available during runtime and Available during build process will be enabled by default — and that’s exactly what you want.

After finally clicking the Add connection button, the environment variables required for database access will be applied to your project Settings over in MyKinsta’s Applications dashboard:

Now, even when you rebuild your application after future enhancements, the database connection particulars will persist.

Congratulations! You’ve just created a Python Flask application and deployed it to the Kinsta platform.

Summary

Flask’s customizable framework makes creating a Python application dramatically simpler. Using Kinsta to deploy a Flask application makes things even easier, accelerating application development and deployment.

Here, we learned how to build a simple database-connected application within a local development environment and then make that available to the rest of the world on Kinsta’s Web Application Hosting and Database Hosting platforms.

Explore what else you can build on our platform by browsing our Quick Start Templates.

The post Build and Deploy a Python App in a Jiffy With Flask and Kinsta appeared first on Kinsta®.

Shortening a URL has several benefits, including making the address easier to share and less likely to be typed inaccurately by users. Even one missing character in a URL can make it completely useless, directing users to the wrong page or to a resource that doesn’t even exist.

Take the example of https://example.com/blog-url-shorteners/48bfefiahl9adik shortened to https://example.com/url-shorteners. It’s not hard to see which a user would be more prone to share, or which might be more likely to lead to mistakes while typing.

A URL shortener’s benefits go beyond tidying up long URLs. They can also help with the following:

• Improving ranking in search engines: Content creators, businesses, and start-ups all have content on their websites, blogs, or social media. Search engines prefer links with specific keywords so they can be ranked accordingly and generate good results. A short URL generated from a known platform can help rank your URL higher.
• Tracking traffic on your links: Paid URL shortener services like Bitly help you track the users clicking your links so you can analyze your incoming traffic and customize your content accordingly.

Two Approaches to a URL Shortener: A Python Library and an API

Following the instructions in this tutorial, you’ll build a URL shortener web app with Python using two different methods:

• The pyshorteners library
• The Bitly API

The pyshorteners module is used by developers to generate short URLs, while the Bitly API module generates short URLs and provides more robust functions like clicks per URL, locations of clicked URLs, customization of URLs, and more.

To complete the tutorial, you’ll need a basic knowledge of Python, and Python must be installed on your system.

Setting Up the Project Environment

Before creating your URL shortener web app, you need to set up the environment for the project, including the installation of Flask, a lightweight framework that makes developing Python web apps easier.

Begin with these steps:

• Create a project folder, perhaps with a name like url-shortener.
• Create an empty file named main.py within that folder.
• Create a virtual environment for this project so that any installation of Python libraries remains independent of your system. Use the command python -m venv myenv in your terminal to create that environment. (In this case, the environment files will be placed in the directory myenv.)
• Activate the virtual environment using the corresponding command for your operating system (and where <myenv> is the name of the directory you created in the previous step).
  • Windows: <myenv>\Scripts\activate.bat
  • Linux/macOS: source <myenv>/bin/activate
• Install Flask using the command pip install flask.
• Create a folder named templates in the project folder. (Flask will retrieve the HTML templates from this directory.)

Your work in the terminal so far will look something like this:

Using the pyshorteners Library to Build a URL Shortener Web App

With your project environment set up, you’ll now create your first URL shortener using the pyshorteners library.

Install the pyshorteners library with the following command:

pip install pyshorteners

Creating a Basic User Interface for the Web Application

Next, you’ll create a basic form in HTML with labels and input fields, where you enter a long URL and generate a shorter one.

Create a form.html file in the templates folder, then enter the following code in that file and save it:

<!DOCTYPE html>
<html lang="en">
<head>
  <title>URL Shortener</title>
</head>
<body>
  <h1>URL Shortener</h1>
  <form method="post" action="/">
    <label for="url">Enter an https:// URL:</label>
    <input type="url"
      name="url"
      id="url"
      placeholder="https://www.xyz.com"
      pattern="https://.*" size="50"
        value="{{old_url}}"
      required  
    >
    <button type="submit" value="submit">Submit</button>
    <br>
    <label>Generated URL: </label>
    <input name="generated_url" value="{{new_url}}" style="margin-top: 10px; margin-left:35px" size="50"></input>
  </form>
</body>
</html>

The above code creates a form with two labels, two input fields, and one button.

The first input field called url, is for writing the long URL, and the other field is for generating the short URL.

The url input field has the following attributes:

• name: To identify the element (e.g., URL)
• placeholder: To show a URL example
• pattern: To specify the pattern of a URL which is https://.*
• required: To give a URL input before submitting
• value: To view the old URL

The second input field has a value attribute set to new_url. The new_url is a short URL generated by the pyshorteners library from the main.py file (shown in the next section).

The entry form is depicted in the following screenshot:

URL Shortening Code Using pyshorteners

Now that you’ve created the form, you can add some functionality to it using Python and pyshorteners.

You’ll add code to process the long URL into a short one and run the web application. Navigate to the main.py file you created earlier, enter the following code, and save it:

from flask import Flask, render_template, request
import pyshorteners
app = Flask(__name__)
 
@app.route("/", methods=['POST', 'GET'])
def home():
  if request.method=="POST":
    url_received = request.form["url"]
    short_url = pyshorteners.Shortener().tinyurl.short(url_received)
    return render_template("form.html", new_url=short_url, old_url=url_received)
  else:
    return render_template('form.html')
 
if __name__ == "__main__":
 app.run() 

The code above imports the pyshorteners library and the following modules from the Flask framework, all of which you’ll need for shortening URLs:

• Flask: The Flask framework itself, which was previously introduced.
• render_template: A template rendering package used to generate the output of HTML files from the folder templates.
• request: An object from the Flask framework that contains all the data that a user sends from the frontend to the backend as a part of an HTTP request.

Next, it creates a function called home() that takes a URL submitted in the form and outputs a short URL. The app.route() decorator is used to bind the function to the specific URL route for running the app, and the POST/GET methods handle the requests.

In the home() function, there’s an if-else conditional statement.

For the if statement, if request.method=="POST", a variable called url_received is set to request.form["url"], which is the URL submitted in the form. Here, url is the name of the input field defined in the HTML form created earlier.

Then, a variable called short_url is set to pyshorteners.Shortener().tinyurl.short(url_received).
Here, two methods are used from the pyshorteners library: .Shortener() and .short(). The .Shortener() function creates a pyshorteners class instance and the .short() function takes in the URL as an argument and shortens it.

The short() function, tinyurl.short(), is one of the pyshorteners library’s many APIs. osdb.short() is another API that can also be used for the same purpose.

The render_template() function is used to render the HTML file template form.html and send URLs back to the form through arguments. The new_url argument is set to short_url and old_url is set to url_received. The if statement’s scope ends here.

For the else statement, if the request method is other than POST, only the form.html HTML template will be rendered.

Demonstration of the URL Shortener Web App Built with the pyshorteners Library

To demonstrate the pyshorteners URL shortener application, navigate to the default route for the application, http://127.0.0.1:5000/, after running the application.

Paste a link of your choice into the first field of the web form:

Click the Submit button to output a short URL with tinyurl as the domain in the Generated URL field:

Using the Bitly API Module to Build a URL Shortener Web App

In this section, you’ll develop a URL-shortening application using the Bitly API. As mentioned, the Bitly API module is another method for shortening URLs and also provides detailed analytics on clicks, location, and device type used (such as desktop or mobile).

Install the Bitly API using the following command:

pip install bitly-api-py3

You need an access token to use the Bitly API, which you can get by signing up with Bitly.

After completing the signup process, log in to Bitly to view your dashboard:

Click Settings on the left sidebar, then click the API section found under Developer settings.

Generate an access token by entering your password in the field above the Generate token button, as shown in the image below, and keep the token to use in the code for your app:

URL Shortening Code Using the Bitly API

Now that you have the token from Bitly, you can code the web app to shorten the URL using the Bitly API.

You’ll use the same form you created for the pyshorteners section but with some changes to the main.py file:

from flask import Flask, render_template, request
import bitly_api
app = Flask(__name__)
 
bitly_access_token = "37b1xxxxxxxxxxxxxxxxxxxxxxxxxx"
 
@app.route("/", methods=['POST', 'GET'])
def home():
  if request.method=="POST":
    url_received = request.form["url"]
    bitly = bitly_api.Connection(access_token=bitly_access_token)
    short_url = bitly.shorten(url_received)
    return render_template("form.html", new_url=short_url.get('url'), old_url=url_received)
  else:
    return render_template('form.html')
 
if __name__ == "__main__":
 app.run() 

As you can see from the code above, bitly_api is imported using import bitly_api. The access token is then saved in a variable called bity_access_token, as in bitly_access_token = "37b1xxxxxxxxxxxxxxxxxxxxxxxx".

The home() function shortens the URL and contains an if-else conditional statement.

For the if statement, if the method or request is POST, then the URL submitted in the form is set to the url_received variable.

The bitly_api.Connection(access_token=bitly_access_token) function connects to the Bitly API and passes it the access token you saved earlier as an argument.

To shorten the URL, the bitly.shorten() function is used by passing the url_received variable as an argument and saving it in a variable called short_url.

Finally, the created form is rendered, and URLs are sent back to be shown in the form using the render_template() function. The if statement completes here.

For the else statement, the form is rendered using the render_template() function.

Demonstration of the URL Shortener Web App Built with the Bitly API

To demonstrate the Bitly API URL shortener application, navigate to the default route for the application, http://127.0.0.1:5000/, after running the application.

Paste a link of your choice into the first field of the web form:

Click Submit to generate a short URL with bit.ly as the domain in the second field of the web application:

Using the Bitly API to shorten URLs in your Python application is a simple as that.

Summary

URL shorteners give you short URLs that are easy to share, look cleaner, and take up less space. In this article, you learned about URL shorteners and their benefits, as well as how to create a URL shortener web application with Python using pyshorteners and the Bitly API. The pyshorteners library provides short URLs, while the Bitly API provides detailed analytics as well as short URLs.

Since you’ve already made an awesome app, why not take it to the next level and host it on Kinsta’s Wed Application Hosting service? To help you get a Python app like this up and running on our platform, explore our Flask quick start tutorial.

The post Build a Simple URL Shortener With Python appeared first on Kinsta®.