For decades, MySQL, and its later fork MariaDB, have been a go-to solution for developers looking for an easy-to-use zero-cost relational database solution for their applications. Many pre-built database-driven applications come with built-in support for MySQL. It is only natural that Python can almost seamlessly work with this database server and this article will demonstrate how a beginner can get started doing so in this Python programming tutorial.
The code examples presented in this tutorial use Python 3 running on a server named allium-unus (Latin for “another one”, at least if Google Translate got it right) running Ubuntu Server 22.04 LTS.
Read: Key Differences Between T-SQL and SQL
What is MariaDB/MySQL
MariaDB is a widely-used fork of the wildly popular free and open-source MySQL database. It uses its own variation of SQL. MariaDB uses both a username and password system, as well as a trusted user authentication system (known as unix_socket authentication) for access management. MariaDB runs on both Windows and Linux, as well as many other operating systems.
Python communicates with MariaDB using a module named mysql.connector. MariaDB and MySQL include a command-line tool that allows for management of the database; however, there are zero-cost graphical tools, such as HeidiSQL, which can also be used for database management.
How to Configure MariaDB for Python Development
Below, we will detail how to configure MariaDB for development environments on both the Linux and Windows platforms.
Configure MariaDB on Linux
The easiest way to install MariaDB in Linux is via Package Management. The preferred package manager for Ubuntu is the apt suite. However, MariaDB can be installed by whatever the preferred package manager is on any given Linux version:
$ sudo apt-get update && sudo apt-get upgrade # Update Linux and all installed software $ sudo apt-get install mariadb-server # Install MariaDB Server $ sudo systemctl start mariadb.service # Start the MariaDB Server $ sudo systemctl enable mariadb.service # Enable on startup $ sudo systemctl status mariadb.service # Gets the current status of the MariaDB Server
If the installation was successful, then the output of the last command above would look similar to this:
Figure 1 – Successful MariaDB Installation in Linux
Upon a successful installation, there are Linux-specific post-install configuration steps, which will need to occur. Note that for this demonstration, unix_socket authentication will not be used, because the programming examples will not work with Windows. This is not to say that unix_socket authentication is a bad thing, but rather, that the goal of this Python tutorial is to present a relatively platform-neutral introduction to MariaDB and Python. The security needs of a specific installation and use case are a different topic entirely.
The command to perform this post-install configuration is:
$ sudo mysql_secure_installation
The image below shows the answers to the security questions that will pertain to the coding demonstrations shown further below in this tutorial:
Figure 2 – Answering the MySQL/MariaDB initial security questions
Read: Using the Command Line to Write SQL Data
Configure MariaDB on Windows
The MariaDB Installation for Windows can be accessed via https://mariadb.com/kb/en/installing-mariadb-msi-packages-on-windows/. This site includes links to an installer, which will configure all of the options, but as it is Windows, unix_socket authentication will not be available. This installer also presents an option to install HeidiSQL, which will provide a graphical database management interface for MariaDB.
Please note that the links posted were active at the time this article was written. If the links are no longer active, this information can be accessed via one’s preferred search engine.
How to Create a Database in MariaDB
Now that MariaDB is installed and configured, the provided mysql client will allow for the creation of a basic database with appropriate access restrictions, as was the case with previous database programming tutorials in this series. You can read those in the links below:
- Python Database Programming with SQL Express for Beginners
- Getting Started With Razor Pages Using C# | CodeGuru.com
A database named RazorDemo, with tables for Artists and Albums/b> will be created in MariaDB, as it was in the above-mentioned tutorials. Additionally, a MariaDB user account will also be created so that the demonstration Python scripts can connect to this database and modify its contents.
The MariaDB root user is required in order to create a database and its associated user account and password. Once the database and its user account are created, the user account should be used to create the tables and any other database objects, such as views, stored procedures, and the like. To begin the creation process, it is necessary to use the mysql client, as shown below:
$ mysql --user=root --password
The –password parameter tells the mysql client to prompt for the password. Use the password entered in the initialization process above. The following commands can then be used to create the RazorDemo database and its associated user account. Note that these commands are executed within the mysql client after successful authentication. The > in the command listing below represents the prompt used by the mysql client. This may vary in appearance.
> create database RazorDemo; -- Creates the database > create user 'rd_user'@'localhost' identified by 'myPW1234%'; -- Creates the user account with the password shown. > use RazorDemo; -- Switches the client to use this database. > grant all privileges on RazorDemo.* to 'rd_user'@'localhost'; -- Assigns all privileges for the database to the user account. > flush privileges; -- Applies all security changes.
Note that the password as shown in the create user… command is the only instance in which the password will appear in clear text. The password is hashed and then stored internally. Its original value cannot be recovered and should it be forgotten, it will need to be reset.
The @’localhost’ syntax next to the create user… and grant all privileges… commands restricts the scope of the rd_user account to connections from localhost. This means that even if a remote user has the password for that account, no connections can be made to the database from any network location. Below is the typical output of the commands above:
Figure 3 – Creating the RazorDemo database and user account
Note: as is the case with any administrative or superuser account for any software application, the MariaDB root account should be used sparingly, and it should never be used in place of a proper regular database user account for either database management or from any database-enabled application.
The success of these commands can the be tested by connecting to the RazorDemo database directly using the rd_user/b> account:
Figure 4 – Connecting directly to the RazorDemo database
Read: What Are Client Statistics in SQL Server?
How to Create Tables in MariaDB
This time, the name RazorDemo is entered as the last parameter to the mysql command, telling the mysql client to automatically use the RazorDemo database. Upon successfully connecting, the tables can be created using the SQL code below:
create table Artists (rcdid int not null auto_increment primary key, artist_name varchar(255) not null default ''); create table Albums (rcdid int not null auto_increment primary key, artist_id int not null references Artists(rcdid) on delete cascade, album_name varchar(255)); Listing 1 - MariaDB Table Creation SQL Code
The outputs of these commands will look similar to the image below:
Figure 5 – Successfully creating tables in MariaDB
Note: the –password parameter can also accept the actual password. However, there are two potential problems with this:
- Both Windows and Linux save their respective previously executed command histories, and the plaintext password will appear in these.
- Any characters in the password that are special to the operating system must be escaped.
As can be seen from the create table statements, the records in the Albums table must point to a record in the Artists table. The application code must respect this relationship as the database will be strictly enforcing it. MariaDB will reject any attempt to insert a record into the Albums table which does not correspond to a record in the Artists table. Furthermore, the on delete cascade clause in the artist_id column in the Albums table means that any deletion of an artist record will result in the automatic deletion of any Albums records associated with that artist. This is known as a cascading delete.
Final Thoughts on Python Programming with MySQL/MariaDB
Now that we have set up our test database, we can move on to the next part of this tutorial series, where we actually begin working with Python and MariaDB together. You can read that in our next tutorial, which you can find here: Database Programming with Python and MariaDB: Part Two.
