Title: Create database tables and get table information from MariaDB in Python

[Viewing the Authors table's field definitions]

At this point, we've created a database and made a user with limited privileges. The next step in building a database is to make some tables and put data in them. This post will show how to perform four table-related tasks:

Drop tables
Create tables
List tables
Show CREATE TABLE statements

Drop Tables

You don't often need to drop tables. Usually you set up the database and then don't change it too much. Even if you do need to make changes, you can usually modify a table rather than dropping it.

However, when you're testing, it may be easier to write a script that drops the tables and recreates them so you don't need to worry about what they used to contain.

The following drop_tables function drops the Authors and Books tables.

def drop_tables(cur): '''Drop the Books and Authors tables.''' # Drop the Books and Authors tables. # Either do this in an order so table constraints are # not violated or empty the table first. cur.execute('DROP TABLE IF EXISTS Books') cur.execute('DROP TABLE IF EXISTS Authors')

This code looks simple and it is, mostly. It uses the DROP TABLE SQL command to drop the tables. It includes IF EXISTS so the database doesn't throw a tantrum if a table already doesn't exist.

There is a catch, however. Sometimes tables have relationships that restrict the values that a table can hold. You'll see shortly that this example's Books table requires that its AuthorId values must be present in the Authors table. That way you cannot create a Books entry that has no corresponding Authors entry.

If this code tried to drop the Authors table first, the database would have a conniption because that would mean the records in the Books table would have invalid AuthorId values (because that table would no longer exist).

There are a couple of ways to handle this. In this example, you can simply drop the Books table first and then drop the Authors table. That's the easiest solution, but what if you want to drop and recreate the Authors table without dropping the Books table?

Another approach would be to delete all of the Books records, but not the table itself, before dropping the Authors table.

A final, more difficult, solution would be to remove the constraint that restricts the Books table's AuthorId field. Then you could drop the Authors table and keep all of the Books records. Of course if you do that, the Books records' AuthorId values would then be meaningless, but at least you would still have the Title data.

Create Tables

The following create_tables function creates (or recreates) the Authors and Books tables.

def create_tables(cur): '''Create the tables.''' # Either create the tables in order so the constraints are not violated # or create the tables first and then alter them to add the constraints. # Create the Authors table. command = ''' CREATE OR REPLACE TABLE Authors ( # Make the Authors table. AuthorId INT NOT NULL AUTO_INCREMENT, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, PRIMARY KEY (AuthorId) )''' cur.execute(command) # Create the Books table. command = ''' CREATE OR REPLACE TABLE Books ( # Make the Books table. AuthorId INT NOT NULL, Title VARCHAR(100) NOT NULL, INDEX AuthorId (AuthorId), FOREIGN KEY author_fk (AuthorId) REFERENCES Authors(AuthorId) )''' cur.execute(command)

This code executes two SQL CREATE TABLE statements to make the tables. Note that SQL pretty much ignores whitespace so it's easy to make a nicely formatted SQL command in Python by putting it in a multi-line string. The database ignores any extra spaces, tables, and newlines.

Also note that MariaDB uses # as an end-of-line comment character so it ignores anything from the # to the end of the line within a command. It also allows -- as an end-of-line comment and it accepts /* ... */ as a multi-line comment.

Notice that the preceding statements include OR REPLACE. If a table already exists, the statement replaces it with the new definition. If you omit OR REPLACE and the table already exists, the database objects. Alternatively you can use IF NOT EXISTS as in CREATE TABLE IF NOT EXISTS Whatever (...) to only create the table if it isn't already there.

As was the case when dropping the tables, you need to create the tables in a particular order. Notice how the Books table's CREATE TABLE statement refers to the Author table's AuthorId field. Obviously that statement cannot refer to that field if the field does not yet exist.

Creating the Authors table first is an easy solution. An alternative would be to create both tables without the foreign key constraint and then later modify the Books table to add that constraint.

Foreign Key Constraints

Speaking of foreign key constraints, what the heck are they?

In general, constraints restrict the values allowed in a table's fields. A foreign key constraint requires that a value must be present in some other (foreign) table. In this example, any value in Books.AuthorId must be present in some Authors record's AuthorId field.

In this example, the words FOREIGN KEY are followed by the name author_fk so the command gives the foreign key that name. If you omit the name, the database will give it a name. Unless you need to alter the table, you usually don't need to refer to a key by its name so it doesn't matter too much.

Composing CREATE TABLE Statements

Many SQL commands (including CREATE TABLE) are very complicated and include dozens of variations, so it's reasonable to wonder how you would ever be able to build one from scratch. The basic commands actually aren't too hard once you get used to them. You can write the basics of a CREATE TABLE statement and then look up things like how to make primary keys and foreign key constraints.

But there's an even easier way.

Use a tool like HeidiSQL (shown at the top of this post) to build the table. Then look at the SQL code that the tool used to create the table.

The bottom part of HeidiSQL (shown in the picture at the top of this post) shows SQL statements that the tool has executed recently. That area only holds recent commands, however, so sometimes you may not see what you need. To find a table's CREATE statement, select the table and then click the "CREATE code" tab above to see the CREATE TABLE statement as shown in the picture below.

[Viewing the Authors table's CREATE TABLE statement]

Often the statement will include lots of values that you don't really need such as collation commands, index names, and mystical incantations like USING BTREE and ON UPDATE RESTRICT. Often you can omit those and accept the default behavior. If you're interested, you can learn more about those and decide if you want to use the values included by HeidiSQL.

List Tables

Relational databases are used mostly to work with the data, so working with metadata like table names is always a bit odd. The following list_tables function returns a list of the current database's table names.

def list_tables(cur): '''List the database's tables.''' cur.execute('SHOW TABLES') return [table[0] for table in cur]

This code executes the SHOW TABLES command. The cursor holds a list of tuples, each containing a single item: the table's name. This code uses a list comprehension to pull out those values and put them in a single list.

Show CREATE TABLE Statements

You can use HeidiSQL to interactively create a table and then look at the CREATE TABLE command that it used. You can also query the database to see how the table was created. To do that, execute a command like SHOW CREATE TABLE Authors. The result is returned as a single tuple containing the table's name and its CREATE TABLE command.

The command includes the text \n in places where a sane person would put a carriage return to make the command easier to read. You can make the result more reader-friendly by replacing the two-character string \n with a newline.

The example program uses the following code to see the database's CREATE TABLE statements.

# Get a list of table names. tables = list_tables(cur) # Show the tables' CREATE TABLE commands. for table_name in tables: command = f'SHOW CREATE TABLE {table_name}' cur.execute(command) name, cmd = cur.fetchone() # Get the single result row. print(f'*** {name} ***') # Show the table's name. print(cmd.replace('\\n', '\n')) # Show the command. print()

This code first calls list_tables to get a list of the tables' names. It then loops through those names and executes a SHOW CREATE TABLE command for each.

The program takes the returned commands, replaces \n with newlines, and displays the results. (The replace statement needs to double-up the backslash in \n so Python doesn't treat the backlash as an escape character.)

Here's the output produced for the Authors table.

*** authors *** CREATE TABLE `authors` ( `AuthorId` int(11) NOT NULL AUTO_INCREMENT, `FirstName` varchar(50) NOT NULL, `LastName` varchar(50) NOT NULL, PRIMARY KEY (`AuthorId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci

Conclusion

This post explains how to use Python and MariaDB to create database tables. In my next MariaDB post, I'll explain how you can insert data into those tables.

Meanwhile, download the example to experiment with it and to see additional details such as how the example logs in selects the current database.

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