One place for hosting & domains

      February 2021

      What is Docker?


      Docker is a popular open source containerization tool used to provide a portable and consistent runtime environment for software applications, while consuming less resources than a traditional server or virtual machine. Docker uses containers, isolated user-space environments that run at the operating system level and share system resources such as the kernel and the filesystem.

      While other container systems exist, Docker became popular for providing a more accessible and comprehensive interface around the technology, and for creating a public repository of base container images that users can build upon when creating containerized environments to run their applications.

      To learn more about Docker, you can refer to the following resources:



      Source link

      How To Use WP-CLI v2 to Manage Your WordPress Site from the Command Line


      The author selected the Free Software Foundation to receive a donation as part of the Write for DOnations program.

      Introduction

      WP-CLI is a command-line tool for WordPress development and administrative tasks. It provides several commands that you can use to manage your WordPress website without needing to log in to the dashboard and navigate through the pages.

      Using WP-CLI to manage your WordPress installation over the conventional interface process helps to speed up your workflow. For many aspects of your website, you can also use WP-CLI in Bash scripts to automate tasks that are tedious or take a long time to perform.

      In this tutorial, you’ll use many of the features of WP-CLI and discover how it can fit into your workflow. You’ll cover common operations such as managing plugins and themes, creating content, working with the database, and updating WordPress. The capabilities of WP-CLI go beyond this tutorial; however, you’ll be able to transfer the skills from this tutorial to work with the more common options of other WP-CLI features.

      Prerequisites

      To follow this tutorial, you’ll need a secure WordPress installation. If you need to set up WordPress, you can follow these tutorials for your chosen server distribution:

      Note: It’s also possible to install WordPress with WP-CLI if you don’t have an existing setup, but we won’t be covering that aspect in this article.

      Step 1 — Installing WP-CLI

      In this step, you’ll install the latest version of the WP-CLI tool on your server. The tool is packaged in a Phar file, which is a packaging format for PHP applications that makes app deployment and distribution convenient.

      You can download the Phar file for WP-CLI through curl:

      • curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar

      Once you have downloaded the file, run the following command to verify that it is working:

      You will receive the following output:

      Output

      OS: Linux 5.4.0-51-generic #56-Ubuntu SMP Mon Oct 5 14:28:49 UTC 2020 x86_64 Shell: /bin/bash PHP binary: /usr/bin/php7.4 PHP version: 7.4.3 php.ini used: /etc/php/7.4/cli/php.ini WP-CLI root dir: phar://wp-cli.phar/vendor/wp-cli/wp-cli WP-CLI vendor dir: phar://wp-cli.phar/vendor WP_CLI phar path: /home/ayo WP-CLI packages dir: WP-CLI global config: WP-CLI project config: WP-CLI version: 2.4.0

      Next, make the file executable with the following command:

      At this point, you can execute the wp-cli.phar file directly to access the WP-CLI tool. To make it available globally on the system, move it to your /usr/local/bin/ directory and rename it to wp. This ensures that you can access WP-CLI from any directory by entering the wp command at the start of a prompt:

      • sudo mv wp-cli.phar /usr/local/bin/wp

      Now, you will be able to issue the following command to check the installed version of WP-CLI:

      Output

      WP-CLI 2.4.0

      In this step, you installed WP-CLI on your server. You can check out alternative installation methods in the documentation. In subsequent sections, you’ll explore the tasks you can accomplish through the WP-CLI interface.

      Step 2 — Configuring WordPress Plugins

      It can be tedious to install and manage WordPress plugins through the admin user interface. It’s possible to offload such tasks to WP-CLI to make the process much faster. In this section you will learn to install, update, and delete plugins on a WordPress site through the command line.

      Before you proceed, make sure you are in the directory of your WordPress installation:

      Remember to change the highlighted directory name to the directory that contains your WordPress installation. This might be your domain name, if you followed the prerequisite tutorials.

      Listing Current Plugins

      You can list the currently installed plugins on a WordPress site with the following command:

      It displays a list of plugin names along with their status, version, and an indication of an available update.

      Output

      +---------+----------+-----------+---------+ | name | status | update | version | +---------+----------+-----------+---------+ | akismet | inactive | available | 4.1.7 | | hello | inactive | none | 1.7.2 | +---------+----------+-----------+---------+

      Searching for Plugins

      You can search for plugins through the search bar on the WordPress plugin repository page or you can use the following command for quicker access:

      Once you run this command, you will receive a list of the top 10 plugins that match the search term (as of early 2021). The expected output for the seo query is:

      Output

      Success: Showing 10 of 4278 plugins. +------------------------------------------------------------+---------------------+--------+ | name | slug | rating | +------------------------------------------------------------+---------------------+--------+ | Yoast SEO | wordpress-seo | 98 | | All in One SEO | all-in-one-seo-pack | 92 | | Rank Math – SEO Plugin for WordPress | seo-by-rank-math | 98 | | The SEO Framework | autodescription | 98 | | SEOPress, on-site SEO | wp-seopress | 98 | | Slim SEO – Fast & Automated WordPress SEO Plugin | slim-seo | 92 | | W3 Total Cache | w3-total-cache | 88 | | LiteSpeed Cache | litespeed-cache | 98 | | SEO 2021 by Squirrly (Smart Strategy) | squirrly-seo | 92 | | WP-Optimize – Clean, Compress, Cache. | wp-optimize | 96 | +------------------------------------------------------------+---------------------+--------+

      You can go to the next page by using the --page flag:

      • wp plugin search seo --page=2

      Take note of the value in the slug column. You’ll use this value to install or update the plugin on the command line.

      Installing Plugins

      You can install one or more plugins by using the wp plugin install command. You find the name of the plugin you want to install (in the slug column) and pass it as an argument to wp plugin install. You can also find the name of the plugin in the URL of the plugin page.

      Plugin name in URL

      • wp plugin install jetpack wordpress-seo gutenberg

      The output indicates the progress and completion of the installation of each of the plugins:

      Output

      Installing Jetpack – WP Security, Backup, Speed, & Growth (9.3.1) Downloading installation package from https://downloads.wordpress.org/plugin/jetpack.9.3.1.zip... Unpacking the package... Installing the plugin... Plugin installed successfully. Installing Yoast SEO (15.6.2) Downloading installation package from https://downloads.wordpress.org/plugin/wordpress-seo.15.6.2.zip... Unpacking the package... Installing the plugin... Plugin installed successfully. Installing Gutenberg (9.8.1) Downloading installation package from https://downloads.wordpress.org/plugin/gutenberg.9.8.1.zip... Unpacking the package... Installing the plugin... Plugin installed successfully. Success: Installed 3 of 3 plugins.

      You can run the wp plugin list command again to confirm that you’ve installed the plugins successfully:

      Output

      +---------------+----------+-----------+---------+ | name | status | update | version | +---------------+----------+-----------+---------+ | akismet | inactive | available | 4.1.7 | | gutenberg | inactive | none | 9.8.1 | | hello | inactive | none | 1.7.2 | | jetpack | inactive | none | 9.3.1 | | wordpress-seo | inactive | none | 15.6.2 | +---------------+----------+-----------+---------+

      If you want to install a plugin from a remote source other than the WordPress plugin repository, you can pass the zip file’s URL as an argument to wp plugin install. This can be helpful for installing custom or premium plugins. For example, the following command will install the myplugin.zip file hosted on example.com. Make sure to replace the highlighted URL with a link to the plugin zip file before running the command:

      • wp plugin install https://example.com/wp-content/uploads/myplugin.zip

      To install an older version of a plugin in the WordPress repository, specify the desired plugin version through the --version flag:

      • wp plugin install jetpack --version=8.0

      Activating and Deactivating Plugins

      You can install and activate plugins in one go by appending the --activate flag to wp plugin install:

      • wp plugin install redirection --activate

      Output

      Installing Redirection (5.0) Downloading installation package from https://downloads.wordpress.org/plugin/redirection.zip... Using cached file '/home/ayo/.wp-cli/cache/plugin/redirection-5.0.zip'... Unpacking the package... Installing the plugin... Plugin installed successfully. Activating 'redirection'... Warning: Plugin 'redirection' is already active. Success: Installed 1 of 1 plugins.

      To activate or deactivate one or more plugins, use the wp plugin activate and wp plugin deactivate commands respectively:

      • wp plugin activate jetpack gutenberg
      • wp plugin deactivate jetpack gutenberg

      Or you can use the --all flag to activate or deactivate all plugins at once. This is useful if you want to debug a problem in your WordPress installation:

      • wp plugin activate --all
      • wp plugin deactivate --all

      Updating Plugins

      You can update plugins through the wp plugin update command. You can choose to update a set of plugins or all of them at once by appending the --all flag. For example, to update the akismet plugin, you can run the following command:

      You would receive output similar to:

      Output

      Enabling Maintenance mode... Downloading update from https://downloads.wordpress.org/plugin/akismet.4.1.8.zip... Unpacking the update... Installing the latest version... Removing the old version of the plugin... Plugin updated successfully. Disabling Maintenance mode... +---------+-------------+-------------+---------+ | name | old_version | new_version | status | +---------+-------------+-------------+---------+ | akismet | 4.1.7 | 4.1.8 | Updated | +---------+-------------+-------------+---------+ Success: Updated 1 of 1 plugins.

      Deleting plugins

      To delete WordPress plugins, you can use the wp plugin delete command. You can specify one or more plugins to delete like the following:

      • wp plugin delete redirection

      Your output will confirm the deletion:

      Output

      Deleted 'redirection' plugin. Success: Deleted 1 of 1 plugins.

      You can also delete all the installed plugins in one go by appending the --all flag instead of specifying the plugin names one after the other:

      In this step, you’ve used WP-CLI to manage the plugins on your WordPress website. It’s much faster to perform actions compared to clicking through the admin dashboard. In the next section, you’ll leverage WP-CLI for installing and managing WordPress themes.

      Step 3 — Configuring Themes

      The process of managing themes through WP-CLI is almost identical to the way you can use it to manage plugins. In this section, you’ll source and apply new themes to a WordPress website through the wp theme subcommand.

      First, check what themes you currently have installed on the website:

      You’ll receive a list of the installed themes:

      Output

      +-----------------+----------+-----------+---------+ | name | status | update | version | +-----------------+----------+-----------+---------+ | twentynineteen | inactive | available | 1.8 | | twentytwenty | inactive | none | 1.6 | | twentytwentyone | active | available | 1.0 | +-----------------+----------+-----------+---------+

      There are three themes currently installed and the active one is twentytwentyone. If you want to find something with more features, you can try a search like the following:

      The output shows there are 832 themes that match the color search term:

      Output

      Success: Showing 10 of 832 themes. +---------------------+---------------------+--------+ | name | slug | rating | +---------------------+---------------------+--------+ | Color | color | 0 | | All Colors | all-colors | 100 | | Color Blog | color-blog | 98 | | Color Block | color-block | 0 | | X Blog color | x-blog-color | 0 | | Multicolor Business | multicolor-business | 0 | | ColorNews | colornews | 100 | | Colorist | colorist | 100 | | ColorMag | colormag | 98 | | MultiColors | multicolors | 74 | +---------------------+---------------------+--------+

      You can page through the results using the --page flag. For this example, just go ahead and install the ColorMag theme since it has a pretty good rating. The --activate flag activates the theme immediately:

      • wp theme install colormag --activate

      The output will confirm the installation:

      Output

      Installing ColorMag (2.0.4) Downloading installation package from https://downloads.wordpress.org/theme/colormag.2.0.4.zip... Unpacking the package... Installing the theme... Theme installed successfully. Activating 'colormag'... Success: Switched to 'ColorMag' theme. Success: Installed 1 of 1 themes.

      If you visit your website, you’ll find that the ColorMag theme was applied successfully.

      ColorMag theme

      The output from the wp theme list command notes that there is an update available for both the twentynineteen and twentytwentyone themes. You can update them both using the following command:

      You’ll receive output similar to the following:

      Output

      Downloading update from https://downloads.wordpress.org/theme/twentynineteen.1.9.zip... Unpacking the update... Installing the latest version... Removing the old version of the theme... Theme updated successfully. Downloading update from https://downloads.wordpress.org/theme/twentytwentyone.1.1.zip... Unpacking the update... Installing the latest version... Removing the old version of the theme... Theme updated successfully. +-----------------+-------------+-------------+---------+ | name | old_version | new_version | status | +-----------------+-------------+-------------+---------+ | twentynineteen | 1.8 | 1.9 | Updated | | twentytwentyone | 1.0 | 1.1 | Updated | +-----------------+-------------+-------------+---------+ Success: Updated 2 of 2 themes.

      The wp theme command offers many subcommands that can help you achieve tasks like getting the details of a theme, checking if a particular theme is installed, or even deleting one or more themes. You can explore all of the options by prepending help before the subcommand, as in wp help theme or wp help theme install.

      Now that you can manage themes through WP-CLI, you’ll review the options that the tool provides for managing WordPress content.

      Step 4 — Creating Posts and Pages

      WP-CLI provides several ways to manage content through the command line. It can be more comfortable to write posts in the terminal if you’re familiar with a command-line editor like nano or vim.

      You can browse the list of posts on the site with:

      You’ll receive a list of posts:

      Output

      +----+--------------+-------------+---------------------+-------------+ | ID | post_title | post_name | post_date | post_status | +----+--------------+-------------+---------------------+-------------+ | 1 | Hello world! | hello-world | 2021-01-24 12:32:06 | publish | +----+--------------+-------------+---------------------+-------------+

      The output shows one published post with the title of Hello world! and an ID of 1. To delete this post, use the wp post delete command and pass it the post ID:

      Your output will confirm the post’s deletion:

      Output

      Success: Trashed post 1.

      To create a new post, run the following command:

      • wp post create --post_status=publish --post_title="Sample post created with WP-CLI" --edit

      This command uses the --post_status flag to set the status of the post. Setting it to publish ensures that the post is published immediately after running the command. If you want to create a draft instead, set the --post_status flag to draft. The --post_title flag is how you can specify the title of the post, while --edit causes the post body to be opened in the default system editor (vim). You can find out the other flags that you can use in conjunction with the create subcommand by typing wp help post create in your terminal.

      Once the vim editor is open, press the i key to enter INSERT mode then enter the content of the post into the editor. Once you’re done editing the post, exit the vim editor by pressing the ESC button then type :wq and press ENTER. You will receive the following output after exiting vim:

      Output

      Success: Created post 6.

      If you enter the wp post list command again, you will find the post you just created. You can also check the frontend of the website.

      WP-CLI post

      Instead of writing the post on the command line, it’s also possible to import the post content from a text file. First, you need to create the file. For example:

      Next, open the file in a command-line editor to add or edit your content:

      Once you’re through with the edits, save and close the file by pressing CTRL-X followed by Y to save. You can import the contents of that file as a WordPress post by using the following command. All you need to do is specify the path to the file after the create subcommand. For the example file here, you would run:

      • wp post create ./content.txt --post_title="Sample Post Created with WP-CLI" --post_status=publish

      If you want to create a page instead of a post, append the --post_type flag and set it to page:

      • wp post create --post_title="A simple page" --post_status=draft --post_type=page

      Generating Posts or Pages

      WP-CLI also provides an option to cleanly generate posts and pages with dummy data. This is useful if you need custom data to quickly test a theme or plugin that you are developing. The following command is to generate posts. If you don’t include additional flags, it will generate 100 posts by default.

      You can change the number of posts generated by using the --count flag:

      • wp post generate --count=20

      If you want to generate pages instead of posts, append the --post_type flag and set it to page:

      • wp post generate --count=20 --post_type=page

      You can also use the wp help post generate to see other available options that can help you get your desired result.

      WordPress Revisions

      It is not uncommon for older sites to have tens or hundreds of revisions on their main pages due to years of editing and updating content. Revisions can be helpful in case you need to revert back to a previous version of your content, but they can also hurt the performance if there are too many. You can clean up all the post revisions in the WordPress database by executing the following command:

      • wp post delete $(wp post list --post_type="revision" --format=ids) --force

      The command enclosed in the parenthesis is evaluated first and it will produce the ids of all the post revisions that are present passing them to the delete subcommand. The --force flag is necessary because posts of type 'revision' do not support being sent to trash.

      Step 5 — Managing Your Database

      One of the most useful features of WP-CLI is its ability to interact with the MySQL database. For example, if you need an interactive session, you can enter a MySQL prompt with the following command:

      You can then use the MySQL shell as you normally would and, once you are through with your tasks, exit the shell by typing exit.

      For one-off queries, you can use the wp db query command by passing a valid SQL query as an argument to the command. For example, to list all the registered users in the WordPress database, you could run:

      • wp db query "SELECT user_login,ID FROM wp_users;"

      You will be presented with an output similar to the following:

      Output

      +------------+----+ | user_login | ID | +------------+----+ | admin | 1 | +------------+----+

      With wp db query you can run any one-off SQL query for the WordPress database.

      Backing Up and Restoring

      WP-CLI also allows you to back up your WordPress database. Running this following command will place a SQL dump file in the current directory. This file contains your entire WordPress database including your posts, pages, user accounts, menus, and so on:

      Once the file is produced, you can move it to a different location for safekeeping:

      Output

      Success: Exported to 'wordpress-2021-01-25-25618e7.sql'.

      You can also import a SQL dump file into your database through the wp db import command. This is useful when you are migrating a WordPress website from one location to another.

      Searching and Replacing

      Another common operation you can perform with WP-CLI is a find-and-replace operation. You can make a dry run first to find out how many instances it would modify. The first string is the search component while the second is the replacement:

      • wp search-replace --dry-run 'example.com' 'example.net'

      After running this, your output would be similar to the following:

      Output

      Success: 10 replacements to be made.

      Once you are sure you want to proceed, remove the --dry-run flag from the previous command:

      • wp search-replace 'example.com' 'example.net'

      In this step, you’ve reviewed several database operations that you can perform using WP-CLI. You can also complete other operations, such as optimizing the database, viewing database tables, deleting a database, or resetting one. You can explore the other options under the wp db subcommand by typing wp help db in your terminal.

      Step 6 — Updating WordPress

      You can update the core WordPress file with WP-CLI. You can examine the current version of WordPress that you have installed by running:

      Output

      5.6

      You can check for updates through the wp core check-update command. If your version is not the latest, this will produce an output similar to the following:

      Output

      +---------+-------------+-----------------------------------------------------------------------+ | version | update_type | package_url | +---------+-------------+-----------------------------------------------------------------------+ | 5.6.1 | minor | https://downloads.wordpress.org/release/wordpress-5.6.1-partial-0.zip | +---------+-------------+-----------------------------------------------------------------------+

      If an update is available, you can install it with:

      Output

      Updating to version 5.6.1 (en_US)... PHP Warning: Declaration of WP_CLICoreCoreUpgrader::download_package($package, $check_signatures = true) should be compatible with WP_Upgrader::download_package($package, $check_signatures = false, $hook_extra = Array) in phar:///usr/local/bin/wp/vendor/wp-cli/core-command/src/WP_CLI/Core/CoreUpgrader.php on line 30 Warning: Declaration of WP_CLICoreCoreUpgrader::download_package($package, $check_signatures = true) should be compatible with WP_Upgrader::download_package($package, $check_signatures = false, $hook_extra = Array) in phar:///usr/local/bin/wp/vendor/wp-cli/core-command/src/WP_CLI/Core/CoreUpgrader.php on line 30 Downloading update from https://downloads.wordpress.org/release/wordpress-5.6.1-partial-0.zip... Unpacking the update... Success: WordPress updated successfully.

      You can also update to a specific version by setting the --version flag to the version number. If you want to revert to an older version, you also need to add the --force flag, but this isn’t recommended:

      • wp core update --version=5.6
      • wp core update --version=5.0 --force

      In this final step, you updated your version of WordPress with WP-CLI.

      Conclusion

      For WordPress developers and adminstrator’s working on the command line, WP-CLI is a great addition to the toolbox. In this tutorial, we covered several of the more common tasks that you can perform through the command line.

      WP-CLI has many more commands and options that you can familiarize yourself with to achieve even more on the command line without the web interface. Use wp help <command> to find out all the things you can do with a specific subcommand. There are also many community tools that extend WP-CLI with even more features.

      For more tutorials on WordPress, check out our WordPress topic page.



      Source link

      How To Store and Retrieve Data in MariaDB Using Python on Ubuntu 18.04


      The author selected the Tech Education Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      MariaDB is an open source version of the popular MySQL relational database management system (DBMS) with a SQL interface for accessing and managing data. It is highly reliable and easy to administer, which are essential qualities of a DBMS capable of serving modern applications. With Python’s growing popularity in technologies like artificial intelligence and machine learning, MariaDB makes a good option for a database server for Python.

      In this tutorial, you will connect a Python application to a database server using the MySQL connector. This module allows you to make queries on the database server from within your application. You’ll set up MariaDB for a Python environment on Ubuntu 18.04 and write a Python script that connects to and executes queries on MariaDB.

      Prerequisites

      Before you begin this guide, you will need the following:

      Step 1 — Preparing and Installing

      In this step, you’ll create a database and a table in MariaDB.

      First, open your terminal and enter the MariaDB shell from the terminal with the following command:

      Once you’re in the MariaDB shell, your terminal prompt will change. In this tutorial, you’ll write Python to connect to an example employee database named workplace and a table named employees.

      Start by creating the workplace database:

      • CREATE DATABASE workplace;

      Next, tell MariaDB to use workplace as your current database:

      You will receive the following output, which means that every query you run after this will take effect in the workplace database:

      Output

      Database changed

      Next, create the employees table:

      • CREATE TABLE employees (first_name CHAR(35), last_name CHAR(35));

      In the table schema, the parameters first_name and a last_name are specified as character strings (CHAR) with a maximum length of 35.

      Following this, exit the MariaDB shell:

      Back in the terminal, export your MariaDB authorization credentials as environment variables:

      • export username="username"
      • export password="password"

      This technique allows you to avoid adding credentials in plain text within your script.

      You’ve set up your environment for the project. Next, you’ll begin writing your script and connect to your database.

      Step 2 — Connecting to Your Database

      In this step, you will install the MySQL Connector and set up the database.

      In your terminal, run the following command to install the Connector:

      • pip3 install mysql-connector-python

      pip is the standard package manager for Python. mysql-connector-python is the database connector Python module.

      Once you’ve successfully installed the connector, create and open a new file Python file:

      In the opened file, import the os module and the mysql.connector module using the import keyword:

      database.py

      import os
      import mysql.connector as database
      

      The as keyword here means that mysql.connector will be referenced as database in the rest of the code.

      Next, initialize the authorization credentials you exported as Python variables:

      database.py

      . . .
      username = os.environ.get("username")
      password = os.environ.get("password")
      

      Follow up and establish a database connection using the connect() method provided by database. The method takes a series of named arguments specifying your client credentials:

      database.py

      . . .
      connection = database.connect(
          user=username,
          password=password,
          host=localhost,
          database="workplace")
      

      You declare a variable named connection that holds the call to the database.connect() method. Inside the method, you assign values to the user, password, host, and database arguments. For user and password, you will reference your MariaDB authorization credentials. The host will be localhost by default if you are running the database on the same system.

      Lastly, call the cursor() method on the connection to obtain the database cursor:

      database.py

      . . .
      cursor = connection.cursor()
      

      A cursor is a database object that retrieves and also updates data, one row at a time, from a set of data.

      Leave your file open for the next step.

      Now you can connect to MariaDB with your credentials; next, you will add entries to your database using your script.

      Step 3 — Adding Data

      Using the execute() method on the database cursor, you will add entries to your database in this step.

      Define a function add_data() to accept the first and last names of an employee as arguments. Inside the function, create a try/except block. Add the following code following your cursor object:

      database.py

      . . .
      def add_data(first_name, last_name):
          try:
              statement = "INSERT INTO employees (first_name,last_name) VALUES (%s, %s)"
              data = (first_name, last_name)
              cursor.execute(statement, data)
              connection.commit()
              print("Successfully added entry to database")
          except database.Error as e:
              print(f"Error adding entry to database: {e}")
      

      You use the try and except block to catch and handle exceptions (events or errors) that disrupt the normal flow of program execution.

      Under the try block, you declare statement as a variable holding your INSERT SQL statement. The statement tells MariaDB to add to the columns first_name and last_name.

      The code syntax accepts data as parameters that reduce the chances of SQL injection. Prepared statements with parameters ensure that only given parameters are securely passed to the database as intended. Parameters are generally not injectable.

      Next you declare data as a tuple with the arguments received from the add_data function. Proceed to run the execute() method on your cursor object by passing the SQL statement and the data. After calling the execute() method, you call the commit() method on the connection to permanently save the inserted data.

      Finally, you print out a success message if this succeeds.

      In the except block, which only executes when there’s an exception, you declare database.Error as e. This variable will hold information about the type of exception or what event happened when the script breaks. You then proceed to print out an error message formatted with e to end the block using an f-string.

      After adding data to the database, you’ll next want to retrieve it. The next step will take you through the process of retrieving data.

      Step 4 — Retrieving Data

      In this step, you will write a SQL query within your Python code to retrieve data from your database.

      Using the same execute() method on the database cursor, you can retrieve a database entry.

      Define a function get_data() to accept the last name of an employee as an argument, which you will call with the execute() method with the SELECT SQL query to locate the exact row:

      database.py

      . . .
      def get_data(last_name):
          try:
            statement = "SELECT first_name, last_name FROM employees WHERE last_name=%s"
            data = (last_name,)
            cursor.execute(statement, data)
            for (first_name, last_name) in cursor:
              print(f"Successfully retrieved {first_name}, {last_name}")
          except database.Error as e:
            print(f"Error retrieving entry from database: {e}")
      

      Under the try block, you declare statement as a variable holding your SELECT SQL statement. The statement tells MariaDB to retrieve the columns first_name and last_name from the employees table when a specific last name is matched.

      Again, you use parameters to reduce the chances of SQL injection.

      Smilarly to the last function, you declare data as a tuple with last_name followed by a comma. Proceed to run the execute() method on the cursor object by passing the SQL statement and the data. Using a for loop, you iterate through the returned elements in the cursor and then print out if there are any successful matches.

      In the except block, which only executes when there is an exception, declare database.Error as e. This variable will hold information about the type of exception that occurs. You then proceed to print out an error message formatted with e to end the block.

      In the final step, you will execute your script by calling the defined functions.

      Step 5 — Running Your Script

      In this step, you will write the final piece of code to make your script executable and run it from your terminal.

      Complete your script by calling add_data() and get_data() with sample data (strings) to verify that your code is working as expected.

      If you would like to add multiple entries, you can call add_data() with further sample names of your choice.

      Once you finish working with the database make sure that you close the connection to avoid wasting resources:
      connection.close():

      database.py

      import os
      import mysql.connector as database
      
      username = os.environ.get("username")
      password = os.environ.get("password")
      
      connection = database.connect(
          user=username,
          password=password,
          host=localhost,
          database="workplace")
      
      cursor = connection.cursor()
      
      def add_data(first_name, last_name):
          try:
          statement = "INSERT INTO employees (first_name,last_name) VALUES (%s, %s)"
          data = (first_name, last_name)
            cursor.execute(statement, data)
          cursor.commit()
          print("Successfully added entry to database")
          except database.Error as e:
          print(f"Error adding entry to database: {e}")
      
      def get_data(last_name):
          try:
            statement = "SELECT first_name, last_name FROM employees WHERE last_name=%s"
            data = (last_name,)
            cursor.execute(statement, data)
            for (first_name, last_name) in cursor:
              print(f"Successfully retrieved {first_name}, {last_name}")
          except database.Error as e:
            print(f"Error retrieving entry from database: {e}")
      
      add_data("Kofi", "Doe")
      get_data("Doe")
      
      connection.close()
      

      Make sure you have indented your code correctly to avoid errors.

      In the same directory, you created the database.py file, run your script with:

      You will receive the following output:

      Output

      Successfully added entry to database Successfully retrieved Kofi, Doe

      Finally, return to MariaDB to confirm you have successfully added your entries.

      Open up the MariaDB prompt from your terminal:

      Next, tell MariaDB to switch to and use the workplace database:

      After you get the success message Database changed, proceed to query for all entries in the employees table:

      You output will be similar to the following:

      Output

      +------------+-----------+ | first_name | last_name | +------------+-----------+ | Kofi | Doe | +------------+-----------+ 1 row in set (0.00 sec)

      Putting it all together, you’ve written a script that saves and retrieves information from a MariaDB database.

      You started by importing the necessary libraries. You used mysql-connector to connect to the database and os to retrieve authorization credentials from the environment. On the database connection, you retrieved the cursor to carry out queries and structured your code into add_data and get_data functions. With your functions, you inserted data into and retrieved data from the database.

      If you wish to implement deletion, you can build a similar function with the necessary declarations, statements, and calls.

      Conclusion

      You have successfully set up a database connection to MariaDB using a Python script on Ubuntu 18.04. From here, you could use similar code in any of your Python projects in which you need to store data in a database. This guide may also be helpful for other relational databases that were developed out of MySQL.

      For more on how to accomplish your projects with Python, check out other community tutorials on Python.



      Source link