Inserting Values Into Database Table
1- INSERT INTO
You can insert values manually in DB Browser for SQLite as demonstrated in the previous tutorial but let’s start getting familiar with the SQL syntax.
INSERT INTO is command used to insert rows into existing database tables.
For example below code piece will enter “Michael” value to the Name column of table Fam. Let’s go ahead and execute this SQL in DB Browser for SQLite’s Execute SQL tab.
INSERT INTO Fam (Name)
You can see in the report that the SQL query executed successfully. If we go back to Browse Data tab to check out our entry we can see “Michael” entered to Name
Now you might be tempted to use INSERT INTO to fill the Age column for Michael but there is a little nuance here in SQL databases.
Let’s go ahead and try INSERT INTO and see what happens.
INSERT INTO Fam (Age)
If you run this SQL query, it will insert a new row where Age is 30 but it won’t touch the previously created row #6 where name equals.
This is because INSERT INTO is only used for creating new rows and for existing rows we will use UPDATE command.
Before we go on, you might want to remember INSERT INTO SQL query that’s used to insert rows into database tables or at least understand it and you can always look it up if you forget it in future.
INSERT INTO Table (Column) VALUES (...)
So, as we mentioned, INSERT INTO is used to enter rows. But, if you’ve already initiated a row and want to change its values you will need to utilize the UPDATE command. Check out UPDATE example in SQL below!
UPDATE statement can be used to update existing rows in an existing database table.
For example below code will SET Age column’s value to 35 in table Fam where Name column has value Michael. Let’s go ahead and execute this SQL in DB Browser for SQLite.
UPDATE Fam SET Age=35
New result after running the UPDATE SQL script above to update Michael’s age:
Let’s add a new column manually and then set its values to “Unknown“
To add a new column named Profession to the existing table, you will have to go to the Database Structure tab, click on the existing table’s name and then click Modify Table. Then you can click add and enter “Profession” as column name. Also, let’s make the data type TEXT.
UPDATE Fam SET Profession='Unknown'
Alternatively, you could also run an SQL command to add a new column. It can be done using the ALTER command. Check out the example below:
3- ALTER TABLE
ALTER TABLE is used to do operations that would alter the table such as adding or deleting columns. Let’s add a column named “Major” using SQL command section.
ALTER TABLE Fam
ADD COLUMN Major TEXT
Perfect, we’re making so much progress. I suggest you to repeat these steps by yourself so far to gain confidence with SQL language. Maybe create your own tables and you will be up to speed in no time with SQL and SQLite databases.
At first sight, SQL syntax might seem a bit weird if you are coming from Python, but it’s such a mini language and besides you have only so many commands to add drop change columns rows etc. In that sense it’s very easy to get used to.
Also, once you are comfortable using a few SQL commands it doesn’t matter if you forget them in future, you can always look them up as long as you’ve learned how to use them once.
4- SQL Python Syntax Differences
SQL and Python are not necessarily the two most comparable languages. One is a multipurpose and complete programming language while the other is a domain-specific mini language for database management. However, it can be useful for learning and cognition to realize the differences that exist between Python and SQL.
Let’s name a few differences: (Some of these can be environment specific)
- SQL is not case sensitive ALTER TABLE and alter table will both yield the same result.
- You can split SQL commands between multiple lines without much extra effort
- Indentation and whitespaces don’t matter in SQL syntax
In this compact SQL Tutorial, we have build upon the previous tutorial: DB Browser for SQLite and learned quite a few new things. We’ve learned how to use INSERT INTO to create new rows and UPDATE to update existing rows.
We’ve also learned how to add columns to an existing database table. We’ve learned doing these operations both manually from the user interface and using mini SQL scripts.
It may seem too simplistic, but these simple SQL codes will construct the backbone of more powerful Python scripts and SQLite database operations in the following tutorials of SQL tutorial series.
Thank you for visiting Holy Python!