Python3 Programming - Exercise 24 - SQL

SQL

SQL = Structured Query Language.

This, we may say, is the language we shall use to talk to the database. Read more on about databases here and also learn SQL from Sololearn.

All we are interested in is CRUD. We want to learn how to create (insert), read (select), update and delete data. To continue any further, Download the SQLite Browser. It makes the work here easier.

Create a sample table

Let's create a database, sample.db, and save it into a folder of any choice. We recommend the folder in which you have done the practicals.

Copy and paste this SQL code into windows ( text area) when we click on the Execute SQL tab.

CREATE TABLE `test_tb` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `name` TEXT
);

The code above

• This code creates a table with the name, test_tb
• There are two fields in the table, id and name
• The id field has some properties
  • INTEGER - the type of data to store
  • NOT NULL - the column value must not be empty or null
  • PRIMARY KEY - makes every row unique
  • AUTOINCREMENT - increase the PRIMARY KEY sequentially. Thus we ignore the values for the id field because it is PRIMARY KEY and AUTOINCREMENT
• The name field has only one property, ie. the data type is a TEXT

We shall use this table in this discussion.

Insert

Add a row

We may add data (a row) to the table by inserting.

INSERT INTO `test_tb` (`name`) VALUES('John Doe');

Add multiple rows

INSERT INTO `test_tb` (`name`) VALUES ('Swift Python'), ('kirito'), ('kevin'), ('spit fire');

Read

Reading is done by selecting.

Read all rows and columns

This will read all the data and with all the field displaying.

SELECT * FROM `test_tb`;

Read all rows and a particular column

This will read all the data but display only the name field.

SELECT `name` FROM `test_tb`;

And this will read all the data but display only the id field.

SELECT `id` FROM `test_tb`;

Read rows WHERE some column's value is given (condition)

This will read all the data where the name field is equal to John Doe

SELECT * FROM `test_tb` WHERE `name` = 'John Doe';

This will read a row whose column (id) value equals 3

SELECT * FROM `test_tb` WHERE `id` = 3;

This will read a row whose column (id) value greater than 3

SELECT * FROM `test_tb` WHERE `id` > 3;

Update

Let us update a row, with id = 1 and change the name value to Terry

UPDATE `test_tb` SET `name` = 'Terry' WHERE `id` = 1;

Delete

Delete the row with id = 1

DELETE FROM `test_tb` WHERE `id` = 1;

Delete the row with name = 'kirito'

DELETE FROM `test_tb` WHERE `name` = 'kirito';

Delete all data

Be careful when we do this.

DELETE FROM `test_tb;

Note

SQL is case insensitive

Practical

use the DB Browser to create some tables and experiment with them.

Summary

• SQL is the language of the databases.
• Inserting, reading, updating and deleting data is feasible using SQL on SQLite.

Resources

• Download SQLite Browser
• Corey Schafer SQLite - Youtube
• wiki Databases
• SQLite
• w3schools SQL