DEV Community

loading...

Python3 Programming - Exercise 24

otumianempire profile image Otu Michael ・3 min read

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
);
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

Add multiple rows

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

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`;
Enter fullscreen mode Exit fullscreen mode

Read all rows and a particular column

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

SELECT `name` FROM `test_tb`;
Enter fullscreen mode Exit fullscreen mode

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

SELECT `id` FROM `test_tb`;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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

SELECT * FROM `test_tb` WHERE `id` = 3;
Enter fullscreen mode Exit fullscreen mode

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

SELECT * FROM `test_tb` WHERE `id` > 3;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Delete

Delete the row with id = 1

DELETE FROM `test_tb` WHERE `id` = 1;
Enter fullscreen mode Exit fullscreen mode

Delete the row with name = 'kirito'

DELETE FROM `test_tb` WHERE `name` = 'kirito';
Enter fullscreen mode Exit fullscreen mode

Delete all data

Be careful when we do this.

DELETE FROM `test_tb;
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)

pic
Editor guide