Short Notes: Interface Python with SQL Database | Computer Science for Grade 12 PDF Download

Revision Notes

• Python’s standard for database interfaces is the python DB-API
• Python Database API supports a wide range of database servers such as Gad Fly, m SQL, My SQL, Oracle, Sybase etc.
• You need to download separate DB API module for each database you need to access.
• DB-API provides a minimal standard for working with databases.
• MySQLdb is an interface for connecting to a MySQL database servers from Python.
• Connect method of MySQLdb  interface is used to create a connection object using MySQLdb module.
• A cursor is a Python object that enables you to work with the database. In database terms, the cursor is positioned at a particular location within a table or tables in a database.
• To get a cursor  you need to call the cursor method on the database object.
• To save your changes to the database, you must commit the transaction. using commit()  
• When you are done with the script close the cursor and then the connection to free up the resources
• The DB-API’s include a defined set of exceptions.  
• Database object Methods
  
• Cursor object Attributes and Methods
  Note: C is the cursor object.

SQL Commands

Revision Note

• Structured Query Language (SQL)
  • When a user wants to get some information from a database file, he can issue a query. 
  • A query is a user-request to retrieve data or information with a certain condition.
  • SQL is a query language that allows user to specify the conditions, (instead of algorithms). 
• Types of SQL commands
  • Data Definition Language Commands (DDL Command): All the commands used to create, modify or delete physical structure of an object like table. e.g., Create, Alter, Drop.
  • Data Manipulation Language Command (DML Command): All the commands used to modify contents of a table comes under this category. e.g., Insert, Delete, Update commands. 
  • TCL command: These commands are used to control transaction of DML commands.  e.g., Commit, Rollback.
• Basic structure of an SQL query
  • General structure SELECT, ALL/DISTINCT, *, AS, FROM, WHERE
  • Comparison IN, BETWEEN, LIKE “% _”
  • Grouping GROUP BY, HAVING, COUNT(), SUM(), AVG(), MAX(), MIN()
  • Display order ORDER BY, ASC/DESC
  • Logical operators AND, OR, NOT
• Constraint
  •  Constraint is a condition applicable on a field or group of fields.
  • Two types of constraint:
    • Column constraint: Apply only to individual column.
    • Table constraint: Apply to group of columns.
    • Different constraints:
      Unique Constraint-Primary Key constraint.
      Default constraint-Check constraint. 

Applying Constraint
Example: Create a student table with filled student id, student name, father’s name, age, class, address.
CREATE TABLE student
sid char (4) PRIMARY KEY,
sname char (20) NOT NULL,
fname char (20),
age number (2) CHECK (age<20),
class char (5) NOT NULL,
address char (50));

• SELECT COMMAND
  The SELECT command is a query that is given to produce certain specified information from the database table.
  Syntax:
  SELECT <column-name>,[,<column-name>,......]
  FROM <table-name>;
  Example: Write a query to display the name and salary of the employee in emp table.
  SELECT ename, sal
  FROM emp;
  Variations of SELECT command:
  • Selecting specific Rows........WHERE clause
    Syntax:
    SELECT <column-name> [,<column-name>.......]
    FROM <table-name>
    WHERE <condition>;
    Example: Disp lay the codes, names and salary of employees who belong to ‘Manager ’ category.
    SELECT empno, ename, sal
    FROM emp
    WHERE job=“MANAGER”; 
  • Searching for NULL (IS NULL command)
    The Null value in a column can be searched for in a table using IS NULL in the WHERE Clause
    Syntax:
    SELECT......<column-name>[,<column-name> y;......]
    FROM <table-name>
    WHERE <column-name> IS NULL;
    Example: Disp lay the codes, names and jobs of employees whose DeptNo is Null.
    SELECT empno,empname, job
    FROM emp
    WHERE DeptNo IS NULL;
  • IS NOT NULL Command
    Example: Display the names and jobs of those employees; whose deptNo is not Null.
    SELECT ename, job FROM emp
    WHERE deptno IS NOT NULL;
  • Sorting Result-ORDER BY Clause
    The resulting column can be sorted in ascending and descending order using the ORDER BY clause.
    Syntax:
    SELECT <column-name>[,<column-name>.......]
    FROM <table-name>
    WHERE <condition>
    ORDER BY <column-name>
    Example:
    Display the list of employees in the descending order of employee code, who is manger.
    SELECT * FROM emp
    WHERE job=“MANAGER”
    ORDER BY ecode;
  • Conditions based on a range
    SQL provides a BETWEEN operator that defines a range of values that the column value must fall for the condition to become true.
    Example:
    Select Roll_no, name From
    student WH ERE Roll_no BETWEEN 100 AND 103;
    The given command displays Roll_no and name of those students whose Roll_no lies in the range 100 to 103 (both 100 and 103 are included in the range):
  • Conditions based on a list
    To specify a list of values, IN operator is used. This operator selects values that match any value in the given list.
    Example:
    SELECT * FROM student WHERE city IN (‘Delhi’, ‘Agra’, ‘Gwalior’).
    The above command displays all those records whose city is either Delhi or Agra or Gwalior.
  • Conditions based on Pattern
    SQL provides two wild card characters that are used while comparing the strings with LIKE operator.
    (a)  percent (%)   matches any string.
    (b)  Underscore(_) matches any one character.
    Example:
    SELECT Roll_no, name, city FROM student WHERE Roll_no LIKE “%3”; displays those records where last digit of Roll_no is 3 and may have any number of characters in front.
    Example:
    Select Roll-no, name, city FROM student WHERE Roll_no LIKE “1_3”;
    displays those records whose Roll_no starts with 1 and second letter may be any letter but ends with digit 3.
  • The INSERT command
    The tuples are added to relation using INSERT command of SQL.
    Syntax:
    INSERT INTO <table-name>[<column list>]
    VALUES (<value>,<value>,<value>,.....);
    Example: Ent er a new record in student table.
    INSERT INTO student (sid,sname,fname,age, class,address);
    VALUES("101",“Mohan”,“Pawan”,15,“8”,“Jaipur”);
    Output:
    sid sname fname age class address
    101 Mohan Pawan 15 8 Jaipur
  • The DELETE command
    The delete command removes the tuples from the tables. This command remove the entire row from the table and not the individual field. So, no field argument is needed.
    Syntax:
    DELETE FROM <table-name>
    WHERE <condition>;
    Example: Delete all the records of employee whose salary is less than 3000.
    DELETE FROM emp
    WHERE sal<3000;
    • To delete all the record from the table.
      Syntax:
      DELETE FROM<table-name>;
  • The UPDATE command
    The UPDATE command is used to change some values in existing rows. The UPDATE command specifies the rows to be changed using the WHERE clause, and new data using the SET keyword.
    Example: Upd ate the salary of employee to 5000 whose employee code is 1011.
    UPDATE emp
    SET sal=5000
    WHERE empno=1011;
  • The ALTER TABLE command
    The ALTER command is used to change the definition of existing table.
    (a) It can be used to add columns to a table.
    Syntax (to add a column to a table):
    ALTER TABLE<table-name> ADD <column name> <data type> <size>;
    (b) To modify existing columns of a table:
    Syntax:
    ALTER TABLE <table-name>
    MODIFY (Column-name newdatatype (newsize));
    Example: To m odify column job of table emp to have new width of 30 character.
    ALTER TABLE emp
    MODIFY (job char (30));
  • The DROP Command:
    The DROP command is used to drop the table from the database. For dropping a table all the tuples should be deleted first i.e., the table should be empty.
    Syntax:
    DROP TABLE <table-name>
    Example: Drop the student table from the database.
    DROP TABLE student;
    Some Example:  
    Ex 1. Write a query on the customers table whose output will exclude all customers with a rating <=100, unless they are located in Shimla.
    SELECT * FROM customers WHERE rating >100 OR city =“Shimla”;
    Ex 2. Write a query that selects all orders except those zeros or NULLs in the amount field.
    SELECT * FROM Orders WHERE amt <>0 AND (amt IS NOT NULL);
    Ex 3. Write a query that lists customers in descending order of rating. Output the rating field first, followed by the customers name and number.
    SELECT rating, cust-name, cust-num FROM customers ORDER BY rating DESC;
    Ex 4. Write a command that puts the following values, in their given order, into the salesman table:
    cust-name-Manisha, city-Manali, comm.-NULL, cust-num-1901.
    INSERT INTO salesman (city, cust-name, comm., cust-num VALUES (“Manisha”, NULL, 1901);
    Operators in SQL:
    The following are the commonly used operators in SQL:
    (i) Arithmetic Operators +, –, *, /
    (ii) Relational Operators =, <, >, <=, >=, < >
    (iii) Logical Operators OR, AND, NOT
    • Arithmetic operators are used to perform simple arithmetic operators. 
    • Relational operators are used when two values are to be compared and logical operators are used to connect search conditions in the WHERE Clause in SQL.
• Other Operators:
  Range check – between low and high
  List check – in
  Pattern check – like, not like (%and _ (under score) is used).
• SQL Functions:
  SQL supports functions which can be used to compute and select numeric, character and date columns of a relations. These functions can be applied on a group of rows. The rows are grouped on a common value of a column in the table. These functions return only one value for a group and therefore, they are called aggregate or group functions.
  • SUM (  ): It returns the sum of values of a column of numeric type.
    E.g., Select sum (salary) from employee;
  • AVG (  ): It returns the average of values of a column of numeric type.
    E.g., Select avg (salary) from employee;
  • MIN (  ): It returns the minimum of the values of a column or a given relation.
    E.g., Select min (salary) from employee;
  • MAX (  ): It returns the maximum of the values of a column or a given relation.
    E.g., Select max (salary) from employee;
  • Count (  ): It returns the number of rows in a relation.
    E.g., Select count (*) from employee;

 Know the Terms

• Attribute: A set of properties (name, datatype, size, ...) used to characterise the data items of entities. A group of attributes constructs an entity-type (or table), i.e.: all values of a certain column must confirm to the same attributes. Attributes are optionally complemented by constraints. 
• Column: A set of values of a single table which resides on the same position within its rows. 
• Constraint: Similar to attributes constraints define rules at a higher level, data items must confirm to. E.g.: null, primary and foreign key, uniqueness, default value, user-defined-criterias like STATUS < 10. 
• Database: A set of tables. Those tables contain user data and the data dictionary. 
• Data Control Language (DCL): A class of statements which defines the access rights to data, e.g: GRANT , REVOKE .
• Data Definition Language (DDL): A class of statements which defines logical and physical design of a database, e.g. : CREATE TABLE.
• Data Manipulation Language (DML):  A class of statements which retrieves and manipulates data, e.g.: SELECT ..., INSERT ..., UPDATE ..., DELETE ..., COMMIT, ROLLBACK.
• Query: An often used statement which retrieves data from the database. It is introduced by the keyword SELECT and usually contains a predicate.
• Relational Model: A database in which inter-table relationships are primarily organized through common data columns which define a one-to-many relationship between a row of the primary key table and one or more rows of the matching foreign key table. Equi-joins relate tables that have matching primary/foreign key values, but other comparisons (relationships) may be defined. Besides describing how the database tables are related, the relational model also defines how the related data can be accessed and manipulated. SQL is the most commonly used relational model database language.
• Relationship: A reference between two different or the same entity. References are not implemented as links. They base upon the values of the entities.
• Row: One record in a table containing information about one single entity. A row has exactly one value for each of its columns - in accordance with First Normal Form. This value may be NULL.
• Statement: A single command which is executed by the DBMS. There are 3 main classes of statements: DML, DDL and DCL.
  Table (=Relation): A set of rows of a certain entity-type, i.e. all rows of a certain table have the same structure.
• Transaction: A logical unit of work consisting of one or more modifications to the database. The ACID criterium must be achieved. A transaction is either saved by the COMMIT statement or completely cancelled by the ROLLBACK statement.
• Value: Implementation of a single data item within a certain column of a certain row.
• View: A virtual table containing only its definition and no real data. The definition consists of a query to one or more real tables or views. Queries to the view are processed as queries to the underlying real tables.