Python — How to use SQLITE3 database (SQLITE3 implementation with Python)

Learning database (Target is only Relational Database a.k.a. RDBMS) is the most important attribute to become successful software engineer. However, most of us start learning programming language first then go for learning database. Even I did the same when I started my career 15 years ago. Fortunately & unfortunately, I have always involved in the project where we used to have one or more than one DBAs (Database Administrator) with very sound knowledge on database concepts. Whenever we want to do something, we always take their help & make the logic work. In that way, I have been able to make projects successful but always remain dependent on DBAs or someone with good knowledge on Databases. I found bit boring to just learn databases concepts & hit some SQL queries. My experience says If you are very good with database fundamentals along with writing complicated SQL queries then you can reduce hundreds to thousands lines of programming logic. Making application layer lighter is always preferable as it not only increased the readability of code but also make application code easy to maintain. I always think to have training content available that provide easy to understand database concepts with programming language so that I can enjoy learning database concept with same interest & curiosity I have for learning any new programming language. I was planning to have training content for learning database concepts with one of the powerful & easiest programming language to make database learning enjoyable & interesting with programming rather just asking to write queries . To achieve that I started tutorial series on youtube with subject How to implement SQLITE3 database with Python programming. Here are the links to each part published till date along with what each part covers -

Python — How to use Sqlite3 database -Part1 : This part covers -
1. How to create sqlite3 database
2. How to create Cursor
3. How to create Table
4. How to verify with sqlite_master table whether table is created or not

Python — How to use Sqlite3 database -Part2 : This part covers -
1. How to select records
2. How to insert records
3. How to update records
4. How to update records

Python — How to use Sqlite3 database (executemany, fetchall, fecthone, fetchmany) -Part3 : This part covers -
1. How to parameterized query with Question Mark (List of tuples)
2. How to parameterized query with Name (List of Dictionary)
3. How to use fetchall, fetchmany & fetchone
4. Fix the mistakes made in previous part

Python — How to use Sqlite3 database (executescript, execute queries from file) -Part4 : This part covers -
1. How to execute multiple SQL queries with executescript
2. How to exeucte SQL queires from file with executescript
3. How to iterate through the records extracting from database

Python — How to use Sqlite3 database (Constraints — PRIMARYKEY, UNIQUE, NOT NULL, CHECK, DEFAULT) -Part5 : This part covers -
1. How to create table with constraints like PRIMARYKEY, UNIQUE, CHECK, DEFAULT, NOT NULL
2. How application respond when new data does not ensure those constraints
3. How to resolve those constraints
4. How to use IF NOT EXISTS with CREATE TABLE statement to overcome with Table Exist issue

Python — How to use Sqlite3 database (Constraints — Foreign Key to have Referential Integrity) -Part6 : This part covers -
1. Create parent table
2. Use FOREIGN KEY to create child table to establish relationship between parent & child tables
3. How to enable Foreign Key effect in SQLITE3 with PRAGMA foreign_keys = 1
4. Verification of data correctly being stored in child table
5. Verification of IntegirtyError to have FOREIGN KEY constraint failed with not present data in parent table

Python — How to use Sqlite3 database (Referential Integrity — How to delete records from parent table with CASCADE, RESTRICT, SET NULL, SET DEFAULT, NO ACTION) -Part7 : This part covers -
1. Delete records from parent table
2. Default behavior explanation for Parent Child table with ON DELETE RESTRICT ON UPDATE RESTRICT,
3. Manual intervention required to delete records from parent table in case child tables are having dependent records,
4. How to control deletion of records from parent table with options like
— CASCADE,
— SET NULL,
— SET DEFAULT,
— NO ACTION
— RESTRICT

Stay tune for upcoming parts to cover all database concepts with programming logic.

Stay healthy & Keep learning new concepts in innovative ways & keep sharing with others