Title: Insert data into a MariaDB database in Python

In previous posts, I showed you how to create a MariaDB database and add tables to it. In this post, we'll add some data to the tables. This is pretty straightforward except for two requirements: satisfying database constraints and dealing with auto-incremented values.

Inserting Data

There are two common formats for SQL statements that insert data into a table. The first explicitly lists the names of the fields that you want to fill in and their values like this:

INSERT INTO Books (AuthorId, Title) VALUES (1, "Beginning Database Design Solutions, Second Edition")

If you don't list all of the fields, the omitted ones take their default values for that table. If a missing field has no default value, MariaDB raises an error.

If a field is auto-incrementing and you omit it from the INSERT command, the database automatically generates its value. That's normally what you want to do or why would you make it auto-generated?

In the second INSERT format, you don't list the field names. In that case, you must include a value for every field in the order in which they are given in the table's definition. Here's an example:

INSERT INTO Authors VALUES (4, "Terry", "Pratchett")

This is a bit less self-documenting. It also means you can't simply omit any auto-generated fields. Instead you should set those values to NULL and the database will generate them.

Satisfying Constraints

When you insert data into a table, you must satisfy any constraints. For example, the Books table has a foreign key constraint requiring its AuthorId field to match a value in the Authors table. In other words, you cannot set Books.AuthorId to a value that isn't in the Authors table.

To satisfy this kind of constraint, always insert the value in the foreign table first (Authors.AuthorId) before you insert the value in the constrained table (Books.AuthorId).

Python Code

The following insert_records1 function creates records for the authors Rod Stephens and Terry Pratchett.

def insert_records1(cur): '''Insert some records with known AuthorIds.''' # Insert records in order so the constraints are not violated. # Create Rod Stephens by explicitly listing the fields. command = ''' INSERT INTO Authors (AuthorId, FirstName, LastName) VALUES (1, "Rod", "Stephens") ''' print(clean(command)) cur.execute(command) # Create Terry Pratchett without listing the fields. command = ''' INSERT INTO Authors VALUES (4, "Terry", "Pratchett") ''' print(clean(command)) cur.execute(command) print() # Insert Books. books = [ [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"], [4, "Going Postal"], [4, "Making Money"], ] for author_id, title in books: command = f''' INSERT INTO Books (AuthorId, Title) VALUES ({author_id}, "{title}") ''' print(clean(command)) cur.execute(command) print()

This code first uses an INSERT statement that explicitly lists the fields that it will set to create the Authors table's Rod Stephens entry. The clean function removes newlines and compresses multiple spaces into single spaces so the program can display the SQL command in a nice format. The program prints the cleaned command and then executes it.

Next, the code does the same thing to create Terry Pratchett's record except this time it uses the command format that does not list the fields.

The function then defines a list holding values for a few books by those authors. It loops through the list, composes appropriate INSERT commands, and executes those commands to insert the data into the Books table.

Handling Auto-Incremented Values

The Authors table's AuthorId field has the AUTO_INCREMENT property so the database automatically creates values for it if you don't supply them. Meanwhile the Books table's AuthorId field is linked to the Authors values.

Suppose you want to create a new author with some books. You can create the Authors record and the database can generate an AuthorId for you, but you then need to use that value to create the Books record. So how do you get the auto-generated value?

The answer is to use the cursor's lastrowid property. That value contains the most recently auto-generated value.

The insert_records2 method shown in the following code demonstrates this.

def insert_records2(cur): '''Insert some records.''' # Insert records in order so the constraints are not violated. # Insert Authors first and get the new record's AuthorId. command = ''' INSERT INTO Authors (FirstName, LastName) VALUES ("Christopher", "Moore") ''' # command = ''' # INSERT INTO Authors # VALUES (NULL, "Christopher", "Moore") # ''' print(clean(command)) cur.execute(command) print() # Get the auto-incremented AuthorId. author_id = cur.lastrowid print(f'AuthorId is {author_id}') # Insert Books records. books = [ "Lamb: The Gospel According to Biff, Christ's Childhood Pal", "Fool", "The Serpent of Venice", "Shakespeare for Squirrels", ] for title in books: command = f''' INSERT INTO Books (AuthorId, Title) VALUES ({author_id}, "{title}") ''' print(clean(command)) cur.execute(command) print()

This code creates an SQL INSERT command to create an Authors record for Christopher Moore. That command specifies the authors first and last names, but does not include the AuthorId so that value is auto-generated. Alternatively you can use the value NULL for the auto-generated field.

The program prints and executes that statement. It then gets the cursor's lastrowid value, saves it in the variable author_id, and prints it.

Next, the code makes a list of some book titles by Christopher Moore. It loops through the list, builds INSERT statements that include the saved author_id value, and executes them to create the Books records.

Conclusion

This post explains how to use Python to insert data into a MariaDB database. The two most important takeaways are (1) insert values in the right order to satisfy constraints and (2) use lastrowid to get auto-generated values if you need them.

My next MariaDB post will show how you can select data from those tables.

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.