Going through the creation of various features in Python and SQLite

Sometimes a database based solution is the most efficient. SQL is very powerful in what it allows you to do and create. We will be looking at SQLite with Python and creating a database (table) and manipulating the data in it.

The context - A student database

The context we will be using is to store information about students. (the data will be very basic simply for illustration purposes). The students have an ID number, a name, a comment (this will be a short description of how they have done at school, whether badly or very well), and finally a field for POINTS. The points will reflect their overall performance in all areas of the school including both academic achievements and sporting excellence. This series will go through the following:

• -Database creation
• -Creating a Table
• -Adding records to a table
• -Fetching and displaying records
• -Updating records in a database table
• -Deleting records
• -Searching by condition using the WHILE clause
• -Searching for a key phrase or keyword
• -How to sort a table or column in SQLite
• -Search and return a selected field in SQLite
• -Counting rows in SQlite
• -Finding the max value in a column in Python and SQlite
• -Finding the average value in Python and SQLite
• -Calculate the SUM total of fields in Python and SQLite
• -Creating a login application in Python and SQLite

Video Demo

This particular series will give you the functions to create the above mentioned features. You could then adapt it to attempt the challenge at the end

What is SQlite

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects. You can visit this site to download a sqlite database viewer: Sqlite viewer download(simply download, unpack and open your table with this application. You will need to create a view, and view the database table). It's worth remembering however, that you can fetch and display everything from your python interface anyway! SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()

Do check out data.world for an amazing collection of all sorts of datasets