Title: Get started using MariaDB in Python

This post explains how to get started using the free MariaDB database in Python. It's pretty long but the steps are fairly easy.

MariaDB is a relational database management system (RDMBS). It isn't a database itself. Instead you can use it to host relational databases.

The first step is installing it.

Install MariaDB

1. Go to https://mariadb.com/products/community-server/ and click Download Community Server.
2. Select the MariaDB version you want and your platform. (I'm using MS Windows 64-bit x86. The following details may be somewhat different for other platforms.)
3. Click Download.
4. Install. As you do, be sure to save the root password and TCP port number. That installs the MariaDB database engine and the HeidiSQL database administration tool.

Create a Database

You can use HeidiSQL to define database users, create databases, insert data into the database, perform queries, and do just about everything else you might want to do to an RDBMS.

You can also do all of those things from a Python program, too, but that's harder and not as interactive.

When you're working on large projects, it's often useful to be able to recreate a database as needed so you can do things like test your code and then restore the database to some initial state. In that case, you may want to use a Python program or SQL scripts to do that. I'll explain how to do at least some of this in Python in later posts.

In this post, we're going to use HeidiSQL to define the database interactively. To do that, follow these steps.

1. Start HeidiSQL. The MariaDB installation may have created a desktop shortcut that you can double click, or you can use the Start menu to launch it. (In non-Windows operating systems, you'll have to use their methods for finding and starting programs.)
2. To connect to MariaDB, enter the root password as shown in the picture on the left below and then click Open. (If the picture's too small, right click it and select Open Image In New Tab to see it full scale.) After you open the MariaDB, you should see the result on the right below.) The highlighted label "Unnamed" on the top of the treeview on the left is the name of your current HeidiSQL session. You can give it a name and save it if you want to reopen the same session later.
3. Create a new MariaDB database. (MariaDB is a database engine, not a database. It can hold multiple databases.)
   1. Right-click on the Unnamed entry at the top of the treeview on the left, open the Create New menu, and pick Database.
   2. Enter the database name LibraryDB as shown in the following picture and click OK.

Create Tables

1. Create the Author table.
   1. In the treeview on the left, right-click on the librarydb entry, open the Create New menu, and pick Table.
   2. Enter the table's name: Authors.
   3. Add the FirstName column.
      1. In the Columns area below, click the Add button.
      2. Set the following column properties.
         1. Name = FirstName
         2. Datatype = VARCHAR
         3. Length = 20
         4. Uncheck the Allow NULL property
   4. Repeat step C to add the LastName column: VARCHAR 20 NOT NULL
   5. Repeat step C to add the AuthorId column: INT 20 NOT NULL
   6. Set the Default value for AuthorId to AUTO_INCREMENT

      Because the AuthorId field has AUTO_INCREMENT turned on, the database will automatically create new AuthorId values for any new records that you create. Just don't specify a value for AuthorId and the database will set it to the next integer value: 1, 2, 3, etc.

   7. Drag AuthorId to the top of the column list. (This isn't really necessary but I wanted to show that you can do it.)
   8. Right-click the AuthorId property, open the Create New Index menu, and select PRIMARY.
   9. Click Save! If you don't do this, the table won't be created.

      The result should look like the following. Notice the little yellow key to the left of the AuthorId column indicating that it is the table's primary key.

      
2. Repeat the previous steps to create the Books table giving it the following columns.
   1. AuthorId: INT NOT NULL
   2. Title: VARCHAR 100 NOT NULL NO DEFAULT
3. Right-click AuthorId, open the Create New Index menu, and select KEY.
4. Click Save!

   The result should look like the following. Notice the little green key to the left of the AuthorId column indicating that it is a key (but not the primary key) in this table.

   

CREATE TABLE `Authors` ( `AuthorId` INT NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR(20) NOT NULL, `LastName` VARCHAR(20) NOT NULL, PRIMARY KEY (`AuthorId`) ) COLLATE='utf32_general_ci' ;

Add Data

1. Add Authors data.
   1. Select the Authors table in the treeview on the left.
   2. Click the Data tab on the right.
   3. Right-click the data area and select Insert Row insert row data.
      1. Leave the AuthorId blank.
      2. Enter FirstName = Rod
      3. Enter LastName=Stephens
      4. Click below the data to add the record. Notice that AuthorId is auto-populated as 1.
   4. Repeat step 3 to add Terry Pratchett. (The AuthorId is 2.)
   5. Repeat step 3 to add Christopher Moore. (The AuthorId is 3.)

   The following picture shows a close-up of the data tab with those three records.

   
2. Add Books data.
   1. Select the Books table in the treeview on the left.
   2. If the Data tab isn't selected, click it.
   3. Repeat the earlier steps to add these records.

   AuthorId	Title
   1	Beginning Database Design Solutions, Second Edition
   1	Build Your Own Ray Tracer With Python
   1	Beginning Software Engineering, Second Edition
   1	Interview Puzzles Dissected
   2	Going Postal
   2	Making Money
   3	Lamb: The Gospel According to Biff, Christ's Childhood Pal
   3	Fool
   3	The Serpent of Venice
   3	Shakespeare for Squirrels

   The following picture shows the result.

   

Install the MariaDB Connector

Installing the connector is easy. Simply execute the command pip install mariadb at your favorite command prompt. I use an Anaconda prompt, but you can use whatever tool you use for pip.

Python Code (Finally!)

There are several standard steps for interacting with a database. Here are the steps for querying the data.

1. Open the database to get a connection to it
2. Create a cursor associated with the connection
3. Use the cursor to execute a query
4. Iterate through the cursor and display the results
5. Close the cursor
6. Close the database connection

(When you're modifying the data (for example, adding, modifying, or deleting records), you must also be sure to commit any changes or they will be discarded. I'll talk about that in a later post.)

Here's this example's Python code.

import mariadb # Connect to the database. try: conn = mariadb.connect( user='Rod', password='my_super_secret_password', host='localhost', port=3306, # Default MariaDB port database='librarydb' ) except mariadb.Error as e: print(f'Error connecting to MariaDB: {e}') else: # Create a cursor. cur = conn.cursor() # Select and display data. cur.execute(''' SELECT Title, FirstName, LastName FROM Authors, Books WHERE Authors.AuthorId = Books.AuthorId ''') for title, first_name, last_name in cur: author = f'{first_name} {last_name}:' print(f'{author:20} {title}') # Close the cursor and connection. cur.close() conn.close()

If the connection succeeds, the program creates a cursor and uses its execute method to execute an SQL query. That query selects data from the Authors and Books tables where the records in the two tables have the same AuthorId values.

Next, the program uses a for loop to iterate through the cursor's results. It defines variables inside the for statement to hold the cursor's selected values.

For each cursor result, the program formats the author's name and then prints out the name and the current result's book title.

The code finishes by closing the cursor and the database connection.

The following text shows the result.

Rod Stephens: Beginning Database Design Solutions, Second Edition Rod Stephens: Build Your Own Ray Tracer With Python Rod Stephens: Beginning Software Engineering, Second Edition Rod Stephens: Interview Puzzles Dissected Terry Pratchett: Going Postal Terry Pratchett: Making Money Christopher Moore: Lamb: The Gospel According to Biff, Christ's Childhood Pal Christopher Moore: Fool Christopher Moore: The Serpent of Venice Christopher Moore: Shakespeare for Squirrels

Conclusion

1. Install MariaDB
2. Create a Database
3. Create Tables
4. Add Data
5. Install the MariaDB Connector
6. Write Python code

Fortunately those steps are pretty easy. In later posts, I'll explain how you can do things like modifying the database and performing CRUD operations (Create, Read, Update, and Delete) in Python. Until then, download the example to experiment with it and to see additional details.

For a lot more information about database design and implementation, see my book Beginning Database Design Solutions, Second Edition.