UNIT-1 Introduction to SQLite.pdf

303: Database Handling Using Python SQLite handling using Python UNIT-1 Introduction to SQLite What is SQLite?  SQLite is embedded relational database management system. It is self-contained, serverless, zero con...

303: Database Handling Using Python SQLite handling using Python UNIT-1 Introduction to SQLite What is SQLite?  SQLite is embedded relational database management system. It is self-contained, serverless, zero configuration and transactional SQL database engine.  SQLite is free to use for any purpose commercial or private. In other words, "SQLite is an open source, zero-configuration, self-contained, stand alone, transaction relational database engine designed to be embedded into an application".  SQLite is different from other SQL databases because unlike most other SQL databases, SQLite does not have a separate server process. It 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. Difference between SQL and SQLite SQL SQLite SQL is a Structured Query Language used SQLite is an Embeddable Relational to query a Relational Database System. It is Database Management System which is written in C language. written in ANSI-C. SQL is Structured Query Language which is SQLite is a portable database resource. used with databases like MySQL, Oracle, You have to get an extension of SQLite in Microsoft SQL Server, IBM DB2, etc. It is whatever language you are programming not a database itself. in to access that database. You can access all of the desktop and mobile applications. A conventional SQL database needs to be SQLite database system doesn’t provide running as a service like OracleDB to such functionalities. connect to and provide a lot of functionalities. SQL is a query language which is used by SQLite is a database management system different SQL databases. It is not a itself which uses SQL. database itself. Page 1  303: Database Handling Using Python SQLite Features Following is a list of features which makes SQLite popular among other lightweight databases: o SQLite is totally free: SQLite is open-source. So, no license is required to work with it. o SQLite is serverless: SQLite doesn't require a different server process or system to operate. o SQLite is very flexible: It facilitates you to work on multiple databases on the same session on the same time. o Configuration Not Required: SQLite doesn't require configuration. No setup or administration required. o SQLite is a cross-platform DBMS: You don't need a large range of different platforms like Windows, Mac OS, Linux, and Unix. It can also be used on a lot of embedded operating systems like Symbian, and Windows CE. o Storing data is easy: SQLite provides an efficient way to store data. o Variable length of columns: The length of the columns is variable and is not fixed. It facilitates you to allocate only the space a field needs. For example, if you have a varchar(200) column, and you put a 10 characters' length value on it, then SQLite will allocate only 20 characters' space for that value not the whole 200 space. o Provide large number of API's: SQLite provides API for a large range of programming languages. For example:.Net languages (Visual Basic, C#), PHP, Java, Objective C, Python and a lot of other programming language. o SQLite is written in ANSI-C and provides simple and easy-to-use API. o SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT). Page 2  303: Database Handling Using Python SQLite Advantages and Disadvantages SQLite Advantages: SQLite is a very popular database which has been successfully used with on disk file format for desktop applications like version control systems, financial analysis tools, media cataloging and editing suites, record keeping programs etc. 1) Lightweight:  SQLite is a very light weighted database so, it is easy to use it as an embedded software with devices like televisions, Mobile phones, cameras, home electronic devices, etc. 2) Better Performance:  Reading and writing operations are very fast for SQLite database. It is almost 35% faster than File system.  It only loads the data which is needed, rather than reading the entire file and hold it in memory.  If you edit small parts, it only overwrites the parts of the file which was changed. 3) No installation needed:  SQLite is very easy to learn. You don’t need to install and configure it. Just download SQLite libraries in your computer and it is ready for creating the database. 4) Reliable:  It updates your content continuously so, little or no work is lost in a case of power failure or crash. 5) Portable:  SQLite is portable across all 32-bit and 64-bit operating systems and big- and little-endian architectures. Page 3  303: Database Handling Using Python  Multiple processes can be attached with same application file and can read and write without interfering each other.  It can be used with all programming languages without any compatibility issue. 6) Accessible:  SQLite database is accessible through a wide variety of third-party tools.  SQLite database's content is more likely to be recoverable if it has been lost. Data lives longer than code. 7) Reduce cost and complexity:  It reduces application cost because content can be accessed and updated using concise SQL queries instead of lengthy and error-prone procedural queries.  SQLite can be easily extended in in future releases just by adding new tables and/or columns. SQLite Disadvantages:  SQLite is used to handle low to medium traffic HTTP requests.  Database size is restricted to 2GB in most cases. Page 4  303: Database Handling Using Python How to Install SQLite? Steps for windows7 Step-1: Goto the official website www.sqlite.org. Step-2: Download sqlite-tools-win-x64-3460000.zip(4.80 MiB). (Note : First check your system configuration whether it is 32bit or 64bit. Given link is for 64bit) Step-3: Extract the zip file Step-4: Create one folder 'sqlite3' in C:/ Step-5: copy/cut all extract 3 files (1) sqldiff.exe, (2) sqlite3.exe, (3) sqlite3_analyzer.exe and put in folder c:/sqlite3/ Step-6: set path of this sqlite3.exe file :  Right click on MyComputer (This PC)  Goto properties  Click on Advance system settings  Click on Environment variables  Set variable : path value : c:/sqlite3 Step-7: To check path is set or not- Go to Command prompt Write Sqlite3. You may see sqlite> Page 5  303: Database Handling Using Python SQLite Limitations: There are few unsupported features of SQL92 in SQLite which are listed in the following table. SR. Feature Description NO 1 Right Outer Join Only LEFT OUTER JOIN is implemented. 2 Full Outer Join Only LEFT OUTER JOIN is implemented. 3 Alter Table The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported. 4 Trigger Support FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers. 5 Views VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. 6 Grant and Revoke The only access permissions that can be applied are the normal file access permissions of the underlying operating system. Page 6  303: Database Handling Using Python SQLite Fundamentals 1) SQLite Commands: SQLite commands are similar to SQL commands. There are three types of SQLite commands: DDL: Data Definition Language DML: Data Manipulation Language DQL: Data Query Language 1) Data Definition Language: There are three commands in this group: CREATE: This command is used to create a table, a view of a table or other object in the database. ALTER: It is used to modify an existing database object like a table. DROP: The DROP command is used to delete an entire table, a view of a table or other object in the database. 2) Data Manipulation Language: There are three commands in data manipulation language group: INSERT: This command is used to create a record. UPDATE: It is used to modify the records. DELETE: It is used to delete records. 3) Data Query Language: SELECT: This command is used to retrieve certain records from one or more table. Page 7  303: Database Handling Using Python 2) SQLite Dot command: Following is a list of SQLite dot commands. These commands are not terminated by a semicolon (;)..help Will gives help menu.show show the current values for various settings.exit /.quit Exits sqlite prompt.header ON|OFF Turn display of Header ON or OFF.Schema table_name Show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE..mode MODE Set output mode where MODE is one of −  csv − Comma-separated values  column − Left-aligned columns.  html − HTML code  insert − SQL insert statements for TABLE  line − One value per line  list − Values delimited by.separator string  tabs − Tab-separated values  tcl − TCL list elements.databases Show created database..tables To see whether table is created or not. Page 8  303: Database Handling Using Python 3) Comments:  Comments are used to add more readability in your SQLite code.  Comments cannot be nested.  Comments begin with two consecutive "--" characters.  Sometimes it also appears with "" character pair. SQLite Datatypes: SQLite data types are used to specify type of data of any object. Each column, variable and expression has related data type in SQLite. These data types are used while creating table. SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.  Other database management system like MYSQL datatypes, they are using static datatypes because when you declare a column with a specific data type, that column can store only data of the declared data type.  Different from other database systems, SQLite uses dynamic type system. In other words, a value stored in a column determines its data type, not the column’s data type.  In addition, you don’t have to declare a specific data type for a column when you create a table. In case you declare a column with the integer data type, you can store such as any kind of data types text and BLOB, SQLite will not complain about this.  SQLite provides five primitive data types which are referred to as storage classes.  Storage classes describe the formats that SQLite uses to store data on disk. A storage class is more general than a data type e.g., INTEGER storage class includes 6 different types of integers. Page 9  303: Database Handling Using Python Storage Class Meaning NULL NULL values mean missing information or unknown. INTEGER Integer values are whole numbers (either positive or negative). An integer can have variable sizes such as 1, 2,3, 4, or 8 bytes. REAL Real values are real numbers with decimal values that use 8- byte floats. TEXT TEXT is used to store character data. The maximum length of TEXT is unlimited. SQLite supports various character encodings. BLOB BLOB stands for a binary large object that can store any kind of data. The maximum size of BLOB is, theoretically, unlimited. Rules for datatypes: SQLite determines the data type of a value based on its data type according to the following rules:  If a literal has no enclosing quotes and decimal point or exponent, SQLite assigns the INTEGER storage class.  If a literal is enclosed by single or double quotes, SQLite assigns the TEXT storage class.  If a literal does not have quote nor decimal point nor exponent, SQLite assigns REAL storage class.  If a literal is NULL without quotes, it assigned NULL storage class.  If a literal has the X’ABCD’ or x ‘abcd’, SQLite assigned BLOB storage class. Note:  SQLite does not support built-in date and time storage classes. You can use the TEXT, INT, or REAL to store date and time values. Page 10  303: Database Handling Using Python SQLite Affinity Type: SQLite supports type affinity for columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Affinity Description TEXT This column is used to store all data using storage classes NULL, TEXT or BLOB. NUMERIC This column may contain values using all five storage classes. INTEGER It behaves the same as a column with numeric affinity with an exception in a cast expression. REAL It behaves like a column with numeric affinity except that it forces integer values into floating point representation. SQLite Affinity & its Type Name: Data Types Corresponding Affinity INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT INTEGER CHARACTER(20) VARCHAR(255) VARYING TEXT CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB BLOB no datatype specified NONE REAL DOUBLE DOUBLE PRECISION FLOAT REAL NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME NUMERIC Page 11  303: Database Handling Using Python Date & Time Data type: In SQLite, there is no separate class to store dates and times. But you can store date and times as TEXT, REAL or INTEGER values. Storage Class Date Format TEXT It specifies a date in a format like "yyyy-mm-dd hh:mm:ss.sss". REAL It specifies the number of days. INTEGER It specifies the number of seconds. Boolean Data Type: In SQLite, there is not a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). typeof() function: SQLites provides the typeof() function that allows you to check the storage class of a value based on its format. Output: Page 12  303: Database Handling Using Python Creating Databse: To create databse Syntax: Sqlite3 database_name.db Example: Sqlite3 HD.db To cheack whether databse is created type.databases.dump command: You can use.dump dot command to export complete database in a text file using the following SQLite command at the command prompt. sqlite3 testDB.db.dump > testDB.sql The above command will convert the entire contents of testDB.db database into SQLite statements and dump it into ASCII text file testDB.sql. Page 13  303: Database Handling Using Python Creating Table: In SQLite, CREATE TABLE statement is used to create a new table. While creating the table, we name that table and define its column and data types of each column. Syntax: CREATE TABLE database_name.table_name ( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype,..... columnN datatype ); Example: CREATE TABLE STUDENT ( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), FEES REAL ); To Check whether your table is created or not.tables command is used. Page 14  303: Database Handling Using Python Drop Table from the database: In SQLite, DROP TABLE statement is used to remove a table definition and all associated data, indexes, triggers, constraints and permission specifications associated with that table. Syntax: Drop Table database_name.table_name; Insert Records into Table: In SQLite, INSERT INTO statement is used to add new rows of data into a table. After creating the table, this command is used to insert data into the table. Syntax: INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN); Syntax: INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN); Example: Insert into student values (1,”Heta Desai”,27,”Bhatar”,28999.50); SELECT Statement: In SQLite database, SELECT statement is used to fetch data from a table. When we create a table and insert some data into that, we have to fetch the data whenever we require. That's why select query is used. Syntax: SELECT column1, column2, columnN FROM table_name; Example: Select * from student; Output: Page 15  303: Database Handling Using Python Mode Example.mode list.mode line.mode column.mode tab.mode html Page 16  303: Database Handling Using Python Update Statement: In SQLite, UPDATE query is used to modify the existing records in a table. It is used with WHERE clause to select the specific row otherwise all the rows would be updated. Syntax: UPDATE table_name SET column1 = value1, column2 = value2... , columnN = valueN WHERE [condition]; Example: Update student Set address=”Althan” Where id=1; Output: Delete Statement: In SQLite, DELETE query is used to delete the existing records from a table. You can use it with WHERE clause or without WHERE clause. WHERE clause is used to delete the specific records (selected rows), otherwise all the records would be deleted. Syntax: DELETE FROM table_name WHERE [conditions...................]; Page 17  303: Database Handling Using Python Where Clause: The SQLite WHERE clause is generally used with SELECT, UPDATE and DELETE statement to specify a condition while you fetch the data from one table or multiple tables. If the condition is satisfied or true, it returns specific value from the table. You would use WHERE clause to filter the records and fetching only necessary records. WHERE clause is also used to filter the records and fetch only specific data. Syntax: SELECT column1, column2, columnN FROM table_name WHERE [condition]; Note: You can use multiple comparison and logical operator with where clause. AND Operator: The SQLite AND Operator is generally used with SELECT, UPDATE and DELETE statement to combine multiple conditions. It is a conjunctive operator which makes multiple comparisons with different operators in the same SQLite statement. Syntax: SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN]; Page 18  303: Database Handling Using Python OR Operator: The SQLite OR Operator is generally used with SELECT, UPDATE and DELETE statement to combine multiple conditions. OR operator is always used with WHERE clause and the complete condition is assumed true if anyone of the both condition is true. Syntax: SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN]; Like Operator: The SQLite LIKE operator is used to match text values against a pattern using wildcards. In the case search expression is matched to the pattern expression, the LIKE operator will return true, which is 1. There are two wildcards used in conjunction with the LIKE operator: o The percent sign (%) o The underscore (_) The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. Example: Statement Description Where FEES like '200%' It will find any values that start with 200. Where FEES like It will find any values that have 200 in any position. '%200%' Page 19  303: Database Handling Using Python Where FEES like '_00%' It will find any values that have 00 in the second and third positions. Where FEES like It will find any values that start with 2 and are at least 3 characters in '2_%_%' length. Where FEES like '%2' It will find any values that end with 2 Where FEES like '_2%3' It will find any values that have a 2 in the second position and end with a 3 Where FEES like '2 3' It will find any values in a five-digit number that start with 2 and end with 3 LIMIT Clause: The SQLite LIMIT clause is used to limit the data amount fetched by SELECT command from a table. Syntax: SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] ; The LIMIT clause can also be used along with OFFSET clause. Syntax: SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]; NOTE: OFFSET is used to not retrieve the offset records from the table. It is used in some cases where we have to retrieve the records starting from a certain point: Page 20  303: Database Handling Using Python ORDER BY Clause: SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2,.. columnN] [ASC | DESC]; You can use one or more columns in ORDER BY clause. Your used column must be presented in column-list. Group By Clause: The SQLite GROUP BY clause is used with SELECT statement to collaborate the same identical elements into groups. The GROUP BY clause is used with WHERE clause in SELECT statement and precedes the ORDER BY clause. Syntax: SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2.... columnN ORDER BY column1, column2... columnN; Page 21  303: Database Handling Using Python HAVING Clause: The SQLite HAVING clause is used to specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. Syntax: SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2 DISTINCT Operator: The SQLite DISTINCT clause is used with SELECT statement to eliminate all the duplicate records and fetching only unique records. It is used when you have multiple duplicate records in the table. Syntax: SELECT DISTINCT column1, column2,..... columnN FROM table_name WHERE [condition]; IN Operator: The IN operator is used to compare a value to a list of literal values that have been specified. Syntax: SELECT * FROM TABLE_NAME WHERE Column_name IN (value1,value2..); Page 22  303: Database Handling Using Python BETWEEN Operator: The SQLite BETWEEN Condition is used to retrieve values within a range in a statement. Syntax: SELECT * FROM TABLE_NAME WHERE Column_name BETWEEN value1 AND value2; SQLite Between with Numerical Value: Example: SELECT * FROM EMPLOYEE WHERE EMP_ID BETWEEN 102 AND 105; SQLite Between using NOT Operator: The SQLite BETWEEN condition can also be combined with the NOT operator. Here is an example of how you would combine the BETWEEN condition with the NOT Operator. Example: SELECT * FROM EMPLOYEE WHERE EMP_ID NOT BETWEEN 102 AND 105; Page 23  303: Database Handling Using Python UNION Operator: SQLite UNION Operator is used to combine the result set of two or more tables using SELECT statement. The UNION operator shows only the unique rows and removes duplicate rows. While using UNION operator, each SELECT statement must have the same number of fields in the result set. Syntax SELECT expression1, expression2,... expression_n FROM tables [WHERE conditions] UNION SELECT expression1, expression2,... expression_n FROM tables [WHERE conditions]; Example: Table Stud: Table Emp: Output: Page 24  303: Database Handling Using Python INTERSECT Operator: SQLite INTERSECT Operator is used to give common records from two or more tables using SELECT statement. The INTERSECT operator shows only the common rows from the tables. Syntax: SELECT expression1, expression2,... expression_n FROM tables [WHERE conditions] INERSECT SELECT expression1, expression2,... expression_n FROM tables [WHERE conditions]; Table Stud: Table Emp; Output: Page 25  303: Database Handling Using Python EXCEPT Operator: The SQLite EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Syntax: SELECT expression1, expression2,... expression_n FROM tables [WHERE conditions] EXCEPT SELECT expression1, expression2,... expression_n FROM tables [WHERE conditions]; Table: Stud: Employee: Page 26  303: Database Handling Using Python Select EMP_ID FROM EMPLOYEE Except Select ID from STUD; Output: IS NULL Operator: The SQLite IS NULL Condition is used to test for a NULL value in a SELECT, INSERT, UPDATE, or DELETE statement. Syntax: The syntax for the IS NULL Condition in SQLite is: Expression IS NULL Here, The expression to test whether it is a NULL value. Note: o If expression is a NULL value, the condition evaluates to TRUE. o If expression is not a NULL value, the condition evaluates to FALSE. Page 27  303: Database Handling Using Python Department table: CREATE TABLE DEPARTMENT ( DEPT_ID INTEGER PRIMARY KEY AUTOINCREMENT, DEPT_NAME TEXT ); Employee table: CREATE TABLE EMPLOYEE (EMP_ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, DEPT_ID INTEGER, CONSTRAINT DEPT_FK FOREIGN KEY(DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID) ); Insert Records in Department table: INSERT INTO DEPARTMENT VALUES(1,’IT’), (2,’MARKETING’), (3,’FINANCE’), (4,’TESTING’); Page 28  303: Database Handling Using Python Insert Records in Employee table: Insert into EMPLOYEE values (101,’HETA’,4), (102,’AMIT’,2), (103,’PRIYA’,1), (104,’JAY’,4); 1) IS NULL With SELECT statement: Example: SELECT * FROM employees WHERE DEPT_ID IS NULL; This SQLite IS NULL example will return all records from the employees table where the department contains a NULL value. 2) IS NULL with UPDATE Statement: UPDATE employees SET DEPT_ID = 'Unknown' WHERE DEPT_ ID IS NULL; This SQLite IS NULL example will update records in the employees table and set the DEPT_ID to 'Unknown' where the DEPT_ID field contains a NULL value. Page 29  303: Database Handling Using Python 3) IS NULL with INSERT Statement: Create table temp ( T_EMP_ID INTEGER, T_NAME TEXT, T_DEPT_ID INTEGER ); INSERT INTO temp (T_EMP_ID, T_NAME, T_DEPT_ID) SELECT EMP_ID, NAME, DEPT_ID FROM EMPLOYEE WHERE DEPT_ID IS NULL; SQLite CASE Statement: In SQLite CASE statement is like an if...then...else condition in other programming languages or like C Language switch statements. Syntax: CASE test_expression WHEN [condition.1] THEN [expression.1] WHEN [condition.2] THEN [expression.2]... WHEN [condition.n] THEN [expression.n] ELSE [expression] END Here in SQLite Case statement each WHEN... THEN clauses evaluated in an orderly manner. First, it evaluated condition 1 in case if it satisfied then it returns expression 1 otherwise it will execute condition 2 and so on. If no condition is satisfied, then finally execution goes to ELSE block, and expression under ELSE is evaluated. Page 30  303: Database Handling Using Python Example: CREATE TABLE STUD ( ID INTEGER PRIMARY KEY, NAME TEXT, MARKS REAL ); INSERT INTO STUD VALUES(1,’HETA’,70),(2,’NIRMAL’,45),(3,’AJAY’,85),(4,’ISHA’,25); SELECT ID, NAME, MARKS, CASE WHEN MARKS>=80 THEN ‘A+’ WHEN MARKS>=70 THEN ‘A’ WHEN MARKS>=60 THEN ‘B’ WHEN MARKS>=50 THEN ‘C’ ELSE ‘SORRY FAILED!!!’ END AS ‘GRADE’ FROM STUD; Output: Page 31  303: Database Handling Using Python SQLite Transaction:  A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order.  For example, if you are creating, updating, or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.  SQL statements that will execute together as a unit like a single SQL statement. If all these T-SQL statements executed successfully without having any errors then the transaction will be committed and all the changes made by the transaction will be saved to the database permanently. In case, if any error occurred while executing these SQLite statements then the complete transaction will be rollbacked.  Properties of Transaction: (ACID Property) Transactions have the following four standard properties, usually referred to by the acronym ACID.  Atomicity − Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.  Consistency − Ensures that the database properly changes states upon a successfully committed transaction.  Isolation − Enables transactions to operate independently of and transparent to each other.  Durability − Ensures that the result or effect of a committed transaction persists in case of a system failure.  Transaction Control: Following are the following commands used to control transactions:  BEGIN TRANSACTION − To start a transaction.  COMMIT − To save the changes, alternatively you can use END TRANSACTION command.  ROLLBACK − To rollback the changes. Page 32  303: Database Handling Using Python Transactional control commands are only used with DML commands INSERT, UPDATE, and DELETE. It is not possible with DELETE and DROP command. SQLite BEGIN Command OR BEGIN TRANSACTION Command: The BEGIN command is used to start or open a transaction. Once an explicit transaction has been opened, it will remain open until it is committed or rolled back. Syntax: BEGIN; or BEGIN [TRANSACTION]; Here TRANSACTION keyword is optional. SQLite COMMIT Command: COMMIT command is the transactional command used to save changes invoked by a transaction to the database. COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command. Syntax: COMMIT [TRANSACTION]; or END [TRANSACTION]; Here TRANSACTION KEYWORD is optional. Once the COMMIT command executed successfully then all the changes are saved to the database and become visible to other clients. The data in the database will contain all the changes made during the transaction even if there are power problems or the system failure once COMMIT commands executed successfully. Page 33  303: Database Handling Using Python SQLite ROLLBACK Command: By using ROLLBACK command we can cancel the transaction and roll back all the proposed changes. ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued. Syntax: ROLLBACK; Example: Create table COMPANY ( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, AGE INTEGER, ADDRESS TEXT, SALARY REAL ); INSERT INTO COMPANY VALUES(1,’HETA’,27,’ALTHAN’,30500), (2,’JAY’,32,’BHATAR’,40000), (3,’DEEP’,34,’VESU’,40500), (4,’KINJAL’,37,’UDHANA’,20000), (5,’KARAN’,27,’ADAJAN’,15000); Now, start a transaction and delete records from the table having age = 27. Then, use ROLLBACK command to undo all the changes. Example: BEGIN; DELETE FROM COMPANY WHERE AGE=27; ROLLBACK; You can find records of the COMPANY table that will contain all the records like as shown below because we are rolled back current transaction without committing in a database table. Page 34  303: Database Handling Using Python Now, Let's start another transaction and delete records from the table having age = 27 and finally we use COMMIT command to commit all the changes. Example: BEGIN; DELETE FROM COMPANY WHERE AGE=27; COMMIT; Page 35  303: Database Handling Using Python SQLite Savepoint: In SQLite, savepoints are useful to mark specific points in the transaction. By using these savepoints we can rollback or accept the changes to particular save-points in transaction based on our requirements. We can create more than one save-point in transaction based on our requirements and sometimes we will call savepoints are the nested transactions because we will create savepoints within the transaction to rollback or commit changes to particular savepoint within the transaction. We can create a save-points in SQLite with SAVEPOINT command. Syntax of SQLite Save Points SAVEPOINT savepoint_name By using SQLite RELEASE command we can release a save-point and accept all the changes made since the savepoint was set. Following is the syntax to release savepoint. RELEASE [SAVEPOINT] savepoint_name By using the ROLLBACK command we can cancel the transaction and undo everything back to where save-point was set. Following is the syntax to rollback transactions using the ROLLBACK command. ROLLBACK [TRANSACTION] TO [SAVEPOINT] savepoint_name Example: Create table Savepoint_example ( Id integer ); Page 36  303: Database Handling Using Python BEGIN; INSERT INTO Savepoint_example VALUES(1); SAVEPOINT a; INSERT INTO Savepoint_example VALUES(2); SAVEPOINT b; INSERT INTO Savepoint_example VALUES(3); If you observe above statements we created different savepoints (a, b) in transaction while inserting records in the Savepoint_example table. Now we will try to rollback Savepoint_example table records to savepoint b using ROLLBACK command and see the records of table using the following queries. When we execute “ROLLBACK” command it will rollback table records to the point where we set savepoint. If you observe the above result it rolled back value “3” because we set the savepoint “b” in the transaction before the value “3” is inserted. Page 37  303: Database Handling Using Python SQLite JOINS: SQLite Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. In SQLite, Joins are the best way to combine and get records from multiple tables. Generally, in SQLite each JOIN operator will combine two tables into another table. In SQLite, we have different types of JOINS available those are:  Inner join  Outer join  Cross join  Self-Join Consider following table for JOINS example: Table: DEPARTMENT Table: EMPLOYEE Page 38  303: Database Handling Using Python 1) Inner join: INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, the column values for each matched pair of rows of A and B are combined into a result row. Syntax: SELECT t1.col1, t2.col2 FROM table1 t1 [INNER] JOIN table2 t2 ON t1.col3=t2.col5; Here INNER keyword is optional to use and it returns only the rows which are common or matched in both tables. In SQLite, INNER JOIN is used to combine and return only matching records from multiples tables based on the conditions defined in SQLite statements. Generally, the SQLite Inner Join will return intersection elements of multiple sets i.e, only the common matching elements from multiple sets. Example: SELECT d.DEPT_ID, d.DEPT_NAME FROM DEPARTMENT d JOIN EMPLOYEE e ON d. DEPT_ID = e. DEPT_ID; Output: Page 39  303: Database Handling Using Python Joining Three tables: There are 3 tables: 1) doctor(d_id,d_name,degree) 2) speciality(s_id,description,d_id) 3) visits(d_id,p_name,vdate) Page 40  303: Database Handling Using Python SELECT D.D_ID, D.D_NAME,S.DESCRIPTION,V.P_NAME,V.VDATE FROM DOCTOR D INNER JOIN SPECIALITY S ON D.D_ID=S.D_ID INNER JOIN VISITS V ON D.D_ID=V.D_ID WHERE D.D_ID=201 AND V.VDATE=’2019-10-15’; 2) Outer Join: OUTER JOIN is an extension of INNER JOIN. Though SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL, SQLite only supports the LEFT OUTER JOIN. Syntax: SELECT COLUMN1,COLUMN2,…. FROM TABLE1 LEFT OUTER JOIN TABLE2 ON condition; In the above SQLite Left Outer Join, table1 is a left-hand table, and table2 is a right-hand table. Here the left outer join tries to match every row of table1 table with every row in table2 table based on the join condition and it returns matching rows from both the tables and remaining rows of table1 table that doesn't match with table2 table are also included in the result. Page 41  303: Database Handling Using Python Example: SELECT d.DEPT_ID, d.DEPT_NAME, EMP_ID, NAME FROM DEPARTMENT d LEFT OUTER JOIN EMPLOYEE e ON d.DEPT_ID = e.DEPT_ID; Output: 3) Cross Join: In SQLite, CROSS JOIN is used to get the Cartesian product of rows by matching each row of the first table with every row of the second table. By using the CROSS JOIN keyword in SQLite statements we can get the result which contains a combination of all the rows from the first table with all the rows of the second table. In SQLite Cross join resultant table will contain multiplication number of rows in input tables. Suppose if table1 contains 10 rows and table2 contains 5 rows then if we apply Cross Join on these two tables we will get a resultant table that contains 50 rows. Syntax: SELECT Column1.colmun2,… FROM table1 CROSS JOIN table2; Example: SELECT DEPT_NAME,EMP_ID,NAME FROM DEPARTMENT CROSS JOIN EMPLOYEE; Page 42  303: Database Handling Using Python Output: 4) Self-Join: In SQLite Self Join is used to join the same table with itself. To use SQLite Self Join we need to create different alias names for the same table to perform operations based on our requirements. Syntax: SELECT x.column_name, y.column_name... FROM table1 x, table1 y WHERE x.column_name1 = y.column_name1; If you observe above SQLite Self join syntax we given alias name to table1 as x and y and used same field of table1 table for comparison. Example: TABLE EMPLOYEE: Page 43  303: Database Handling Using Python TABLE MANAGER: the manager_id column is a self-reference to emp_id in the emp_master table. Now write SQLite query like as shown following to use Self Join with a select statement to get employees manager details. EXAMPLE: SELECT x.emp_id, x.name as Employee, y.emp_id as 'Manager ID', y.name as 'Manager Name' FROM EMPLOYEE x, EMPLOYEE y WHERE x.manage_id = y.emp_id; Output: Page 44  303: Database Handling Using Python SQLite datetime function: In SQLite datetime() function is used to translate give string into date and time. Following is the syntax of SQLite datetime() function to get date and time from the given string. Datetime (datetimestring, [modifier1, modifier2…, modifierN]) The SQLite datetime() function takes datetimestring & one or more modifier values and returns date and time in YYYY-MM-DD HH:MM:SS format. It is important to note that this function will return the TEXT as an output. Format Example Now Current date YYYY-MM-DD 2021-06-29 YYYY-MM-DD HH:MM:SS 2021-06-29 07:25:10 HH:MM 10:50 HH:MM:SS 06:22:23 Modifiers Format Example Start of month Start of month Start of year Start of year Start of year Start of year XX years 02 years XX months 04 months XX minutes 15 minutes XX hours 10 hours XX days 02 days Example: 1) Compute the current date. Select date(‘now’) as ‘Current date’; Page 45  303: Database Handling Using Python 2) Compute current date and time. SELECT datetime(‘now’); 3) Compute the first day of the month. SELECT date('now','start of month'); 4) Compute the start of the year. SELECT date(‘now’, ‘start of year’); 5) You can add or subtract month from current month. SELECT date(‘now’, ‘-7 months’); Page 46  303: Database Handling Using Python SELECT date(‘now’,’+2 months’); SELECT date(‘2021-06-29’,’-7 days’); SELECT date(‘2021-06-29’,’+7 days’); SELECT datetime(‘2021-06-29 08:22:05’, ‘-2 hours’); SELECT datetime(‘2021-06-29 08:25:10’, ‘+10 minutes’); Page 47  303: Database Handling Using Python 6) Compute the last day of current month. SELECT date('now','start of month','+1 month','-1 day'); SQLite Time() Function: In SQLite time() function is used to get only time from the given datetime string. Syntax: time(datetimestring, [modifier1, modifier2…, modifierN]) The SQLite time() function will take datetimestring and one or more modifier values and returns time in a HH:MM:SS format. It is important to note that this function will return the TEXT as an output. Example: SELECT time() as ‘Current time’; OR SELECT time(‘now’) as ‘Current Time’; Page 48  303: Database Handling Using Python If you want to get time after 45 minutes from the current time, then we need to write the SQLite query like as shown following. Example: SELECT time(), time(‘now’,’+45 minutes’); If you want to get the time after 30 seconds from the current time the following sqlite query can be used. Example: SELECT time(),time(‘now’,’+30 seconds’); Page 49  303: Database Handling Using Python SQLite strftime() Function In SQLite strftime() function is used to convert given datetime string into a defined format. Syntax: strftime(format, datetimestring, [modifier1,modifier2…,modifier]) The SQLite strftime() function returns a formatted string by taking a datetimestring and formatting it according to format value. Format Description %d Day of the month, 01-31 %m month, 01-12 %Y Year %H Hour, 00-23 %M minute, 00-59 %S seconds, 00-59 %W week of the year, 00-53 %j day of the year, 001-366 %w day of the week, starting with Sunday as 0 Example: 1) If you want to extract Year, Month, and Day for the current date, then SELECT strftime(‘%Y/%m/%d’,’now’) as ‘Year/Month/Day’; 2) If you want to get current time in HH:MM format following SQLite query will return only current time in the required format. Page 50  303: Database Handling Using Python SELECT strftime(‘%H : %M’,’now’) as ‘Hour min’; 3) Now we will see the example of finding age. Consider birthdate as 28th December 1992. SELECT strftime(‘%Y’,’now’) – strftime(‘%Y’,’1992-12-28’) as age; Consider following Table : Table Faculty: Select F_id,name,strftime(‘%Y’,joining_date) From Faculty Where strftime(‘%Y’,joining_date) Between ‘2010’ AND ‘2015’; Page 51  303: Database Handling Using Python SQLite Trigger: SQLite Triggers are database functions, which are automatically performed/invoked when a specified database event occurs. In SQLite trigger is a database object and it will raise automatically whenever an insert, update and delete operations performed on a particular table or view in the database. Generally, in SQLite triggers are specific to the particular table so those triggers will raise automatically whenever we perform any operations like insert, update or delete on that particular table. The triggers in SQLite will help us to maintain the data integrity on the database and these triggers will raise automatically to perform defined rules to prevent invalid transactions BEFORE or AFTER INSERT, UPDATE, or DELETE operations. Note: In SQLite triggers are specific to a particular table so the triggers are dropped automatically when the associated table dropped. Benefits of Trigger in SQLite  It will help us to prevent invalid transactions by imposing business rules  Maintain data integrity  We can enable event logging for the changes in table data. Syntax: CREATE TRIGGER [IF NOT EXISTS] trigger_name [BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE] ON table_name [FOR EACH ROW | FOR EACH STATEMENT] [WHEN condition] BEGIN Trigger_action_body END; Here, o IF NOT EXISTS – It’s Optional and it will prevent throwing error in case if we try to create an existing trigger. Page 52  303: Database Handling Using Python o Trigger_name – Its name of the trigger which we are going to create. o [BEFORE|AFTER|INSTEAD OF] – It will determine when the trigger action can be performed is it BEFORE or AFTER or INSTEAD OF the event. o [INSERT|UPDATE|DELETE] – It will determine in which action triggers can be invoked such as INSERT, UPDATE or DELETE. o table_name – Its name of the table on which we are going to create trigger. o [FOR EACH ROW | FOR EACH STATEMENT] – Present SQLite will support only FOR EACH ROW so we don’t need to explicitly specify FOR EACH ROW and its Optional. o Trigger_action_body – It contains SQLite statements which will execute whenever the trigger action performed. Points about SQLite triggers –  SQLite trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular database table occurs or whenever an UPDATE occurs on one or more specified columns of a table.  Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted, or updated using references of the form NEW.column- name and OLD.column-name, where column-name is the name of a column from the table that the trigger is associated with.   If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.  The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification, or removal of the associated row.  The table to be modified must exist in the same database as the table or view to which the trigger is attached and one must use just tablename not database.tablename.  A special SQL function RAISE() may be used within a trigger-program to raise an exception.  Page 53  303: Database Handling Using Python 1) Before Insert: Example: CREATE TABLE Product ( P_id INTEGER PRIMARY KEY, P_name TEXT NOT NULL, amount REAL, quantity INTEGER ); Now we will create a trigger name trg_validate_products_before_insert on the “Product” table to raise before insert of any data using the following statements. CREATE TRIGGER trg_validate_products_before_insert BEFORE INSERT ON Product BEGIN SELECT CASE WHEN NEW.amount