Create Rows using INSERT¶

Now that we have a database and a table, we can start adding data.

Here's a reminder of how the table would look like, this is the data we want to add:

id	root	nicodemus	english	originator
1	a	a	hello.	Lawrence Nicodemus
2	bc	buts	boots.	Lawrence Nicodemus
3	bs	tbilso	dragonfly.	null

You'll need a Database and Table¶

If you haven't yet done so, complete the steps to create your database and create your tables.

Create Data with SQL¶

You may want to add new rows to your table by just writing the information in SQL. Here's how!

Let's say we want to insert the record/row for hello into our database.

We can do this with the following SQL code:

INSERT INTO "roots" ("root", "nicodemus", "english", "originator")
VALUES ("a", "a", "hello.", "Lawrence Nicodemus");

It means, more or less:

Hey SQL database 👋, please INSERT something (create a record/row) INTO the table roots.

I want you to insert a row with some values in these specific columns:

"root"

"nicodemus"

"english"

"original"

And the values I want you to put in these columns are:

"a"

"a"

"hello."

"Lawrence Nicodemus"

Try it in DB Browser for SQLite¶

You can try that SQL statement in DB Browser for SQLite.

Make sure to open the same database we already created by clicking Open Database and selecting the same database.db file.

Tip

If you don't have that database.db file with the table roots, you can re-create it by running the SQL statement shown in the last chapter. 👆

To do all three, type this:

INSERT INTO "roots" ("root", "nicodemus", "english", "originator")
VALUES ("a", "a", "hello.", "Lawrence Nicodemus");
INSERT INTO "roots" ("root", "nicodemus", "english", "originator")
VALUES ("bc", "buts", "buts.", "Lawrence Nicodemus");
INSERT INTO "roots" ("root", "nicodemus", "english")
VALUES ("bs", "tbilso", "dragonfly.");

Notice that the entry for dragonfly has no originator, so we leave that out.

Then go to the Execute SQL tab and copy the SQL from above.

It would look like this:

Click the "Execute all" ▶ button.

Then you can go to the Browse Data tab, and you will see your newly created records/rows:

Create Roots as a Script¶

All databases have a method for running a file that contains a list of SQL statements as a script. The files are usually named with a .sql extension, although this is not required. Everything that can be typed manually or done in a GUI interface can be run as a script.

In fact, scripts are often convenient for backing up and restoring database entries. A nightly batch run could be set up to back up all of the data and the database structure (schema) by writing the commands to create the database and add all of the data into the database into a file. This file can be copied off-site and restored in case a hard drive fails or some other disaster befalls the server.

Create a Script File for the roots Table¶

Info

At this point, we need to use the terminal application to run SQL commands. Don't be afraid - the terminal (also called the command line) is just a super powerful application that lets you talk directly to your computer.

If you're working on a Windows machine, you'll want to use terminal rather than powershell. If you're working on a Mac, there's no such thing as powershell, just terminal. If you're working in Linux, you already know about this stuff, so we won't worry about you.

In your terminal application, aka at the command line, to create a backup of the roots table, run this command:

sqlite3 ../sqlmodel-tutorial/database.db ".dump 'roots'" > roots.sql

$ sqlite3 ../sqlmodel-tutorial/database.db ".dump 'roots'" > roots.sql

The command basically reads sqlite3 <database> "<command>" > (output to) <filename>. The particular command between double quotes reads .dump '<table name>'. The path to the <database> file will vary based on your system. The > character redirects output from the screen to <filename>. In our case, we dump the roots table creation and insertion commands from database.db into a file in the current directory named roots.sql. We could change the location of this file by adding a path. The .sql extension is just an identifier; it is not absolutely required.

Now we have a file, but what is in it?

This is the content of the file:

PRAGMA foreign_keys=OFF; --(1)
BEGIN TRANSACTION; --(2)
CREATE TABLE IF NOT EXISTS "roots" ( --(3)
        "id"    INTEGER NOT NULL,
        "root"  TEXT NOT NULL,
        "nicodemus"     TEXT NOT NULL,
        "english"       TEXT NOT NULL,
        "originator"    TEXT,
        PRIMARY KEY("id")
);
INSERT INTO roots VALUES(1,'a','a','hello.','Lawrence Nicodemus'); --(4)
INSERT INTO roots VALUES(2,'bc','buts','buts.','Lawrence Nicodemus');
INSERT INTO roots VALUES(3,'bs','tbilso','dragonfly.',NULL);
COMMIT; --(5)

Info

What the heck is PRAGMA??? PRAGMA is an SQL command that's used in SQLite specifically, in order to set various values that the database is going to use as part of its configuration. We don't know why it's called that, but it's explained here

Turn off FOREIGN KEY constraints during this command session. The reason to do this involves needing to possibly insert data that would violate constraints while tables are being rebuilt, For example, another table might not have a corresponding key because the table either hasn't been created or the data has not been inserted YET, but it will be later. The way to do this without setting this PRAGMA would be to create and insert tables that have foreign keys FIRST, and then create and insert tables that reference the foreign key tables LATER.
BEGIN a TRANSACTION. We haven't talked about transactions much, but this starts a transaction that wraps the creation of the table AND the addition of the data to the table. Either they all happen or nothing does.
Create a table with the name roots IF IT DOESN"T ALREADY EXIST. Also notice that the columns for this table are declared inside the parenthesis " (" that starts here. If the table exists, then we leave this transaction and DO NOT insert the rows of data. Since the roots table already exists, entering this data would either cause constraint violations or add duplicate data to the data.
These are the insertions of rows into the table. If any one of these fail, the transaction will be aborted, the table will not be created, and none of the data will be inserted.
If all went well, COMMIT ends the transaction and creates and populates the roots table. YEAH!

Run the Script¶

Now we can run our program as a script from DB Browser for SQLite.

Well. Almost. We still have a table sitting out there that will conflict with the transaction we are trying to run. We need to get rid of the existing table so that the transaction can do its thing.

In order to do that we have to type this SQL and execute it:

DROP TABLE "roots";

Because we created the engine with echo=True, it will print out all the SQL code that it is executing:

Now we get to turn around and add it back in again. So. Let's run that script from the DB Browser interface.

Choose the Execute SQL tab and then press the Open SQL File button.

Choose the SQL file with your commands in it.

Press the Open button.

We're almost ready to press the Execute All arrow shown in the image above. But not yet... first we've gotta remove these lines from our SQL statement:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;

The reason why is.. well.. because it turns out that in the background, SQLite has put us inside a transaction. That's why we have to keep pressing the Write Changes button. And inside a transaction, we can't have these lines, because we can't run that PRAGMA inside our transaction. And it doesn't matter if we run a transaction inside a transaction (although that is legal). So. After we remove those lines in the SQL panel we can run the code, and it will recreate the table as it existed before.

What's Next¶

Now you know how to add rows to the database. And backup. And restore. 🎉

Now is a good time to understand better how to query all of this data and get the information that you want from it.

I'll tell you about that in the next chapter. 🚀