Working with SQLite in Python Programming

Working with SQLite: The purpose of our this Python SQLite tutorial, to demonstrate how to we can develop Python database applications with the SQLite database. We will learn that how we can perform SQLite database operations from Python.

We will learn following operation in details

1. How to create database.
2. How to create table.
3. What is Not Null (NN).
4. What is Primary Key (PK).
5. What is Auto Increment (AI).
6. What is Unique Key.

(i). Create Database: If we are working with DB Browser SQLite, that in this case the database file will be created inside our system with .db extension. Open your DB Browser SQLite:

After opening DB browser, click on new database. After that a new popup will open & it will ask you to enter your database name (anything) and also to choose file location where you want to save your file (file stored will be in .db extension).

Next it will ask to create table etc. for now you may skip that step and close all files. Now move to the location where you have chosen to save your database file. You will see your file is there with .db extension. Which means its a database file.

Here is my file “abc” file with .db extension is present. Hence our database is created.

(ii). Create Table: We have successfully created our database. Now we will create table inside our database. Following are the steps to create table:

Step 1). Open your DB Browser SQLite.

Now select open database and move to the location where we have created our database file with .db extension. And select the .db file to open. After that you will see the below interface.

Here we can see that we have 0 tables available in our database. Let’s start creating table, click on create table highlighted in yellow. After clicking on create table below interface will open:

Here I have given my table as “user” and by clicking on “add field” we can add columns to our database. I have created two columns named as “id” and “name” datatype used is INTEGER and TEXT. There are four constraints are available that we can used on our columns as per our requirement. Not Null (NN), Primary Key (PK), AUTO INCREMENT (AI), UNIQUE (U) are available constraints. Click OK and our table is created.

We can add as many columns as we need. Hence we can create tables in our database.

(iii). Not Null: The NOT NULL constraint is a constraint o column and forces column not to accept NULL values. This always allows a field to always contain a value, which means that we cannot insert a new record or update any record without adding a value to this field.

(iv). Primary Key: The PRIMARY KEY constraint is unique to each record in a table. Primary keys should contain a UNIQUE value and it cannot contain NULL value. A table must have only ONE primary key in table. Primary key may consist of single or multiple columns in a table.

(v). Auto Increment: Auto–increment allows to insert a unique number which is to be generated automatically when we add a new record into our table. Mainly we use primary key and auto increment constraints together to a column, as we want our primary key to increment automatically without providing any value as input.

(vi). Unique Key: Unique key constraint allows column to always hold unique values. It should not contain any duplicate value.

So, this much information is enough for now. Let’s get started working with Python and SQLite.