KVSPGTCS – Lets make things better



Computer Science (083)CLASS-XII 2020-21 DISTRIBUTION OF MARKS:UNITUNIT NAMEMARKSIComputational Thinking and Programming - 2 40IIComputer Networks 10IIIDatabase Management20TOTAL70Database Concepts: Introduction to database concepts and its need.Relational data model: Concept of domain, relation, tuple, attribute, degree, cardinality, key, primary key, candidate key, alternate key and foreign key;Structured Query Language:General Concepts: Advantages of using SQL, Data Definition Language and Data Manipulation Language;Data Types: number / decimal, character / varchar / varchar2, date;SQL commands: CREATE TABLE, DROP TABLE, ALTER TABLE, UPDATE ....SET...., INSERT, DELETE; SELECT, DISTINCT, FROM, WHERE, IN, BETWEEN, LIKE, NULL / ISNULL, ORDER BY,GROUP BY, HAVING;SQL functions: SUM ( ), AVG ( ), COUNT ( ), MAX ( ) and MIN ( );Joins: equi-join and natural join Interface of Python with an SQL database - Connecting SQL with Python - Creating Database connectivity Applications - Performing Insert, Update, Delete queries - Display data by using fetchone(),fetchall(),rowcountUnit III: Database ManagementWhat is Data?In simple words data can be facts related to any object in consideration. For example your name, age, height, weight, etc are some data related to you. A picture, image , file , pdf etc can also be considered data.What is a Database?Database is a systematic collection of data. Databases support storage and manipulation of data. Databases make data management easy. Let's discuss few examples.An online telephone directory would definitely use database to store data pertaining to people, phone numbers, other contact details, etc.WHY DO WE NEED DATABASETo manage large chunks of data: if size of data increases into thousands of records, it will simply create a problem to manage. Database can manage large amount of data.Accuracy: Through validation rule in database, data accuracy can be maintained.Ease of updating data: With the database, we can flexibly update the data according to our convenience. Moreover, multiple people can also edit data at same time.Security of data: With databases we have security groups and privileges to restrict access.Data integrity: In databases, we can be assured of accuracy and consistency of data due to the built in integrity checks and access controls.Advantages of Database SystemDatabases reduce Redundancy: It removes duplication of data because data are kept at one place and all the application refers to the centrally maintained database.Database controls Inconsistency: When two copies of the same data do not agree to each other, then it is called Inconsistency. By controlling redundancy, the inconsistency is also controlled.Database facilitates Sharing of Data; Data stored in the database can be shared among several users.Database ensures Security: Data are protected against accidental or intentional disclosure to unauthorized person or unauthorized modification.Database maintains Integrity: It enforces certain integrity rules to insure the validity or correctness of data. For ex. A date can’t be like 31/31/2000.Database enforce standards:What is a Database Management System (DBMS)?Database Management System (DBMS) is a collection of programs which enables its users to access database, manipulate data, reporting / representation of data.It also helps to control access to the database.Types of DBMSLet's see how the DBMS family got evolved with the time. Following diagram shows the evolution of DBMS categories.There are 4 major types of DBMS. Let's look into them in detail.Hierarchical DBMSIn a Hierarchical database, model data is organized in a tree-like structure. Data is Stored Hierarchically (top down or bottom up) format. Data is represented using a parent-child relationship. In Hierarchical DBMS parent may have many children, but children have only one work ModelThe network database model allows each child to have multiple parents. It helps you to address the need to model more complex relationships like as the orders/parts many-to-many relationship. In this model, entities are organized in a graph which can be accessed through several paths.Relational modelRelational DBMS is the most widely used DBMS model because it is one of the easiest. This model is based on normalizing data in the rows and columns of the tables. Relational model stored in fixed structures and manipulated using SQL.Object-Oriented ModelIn Object-oriented Model data stored in the form of objects. The structure which is called classes which display data within it. It defines a database as a collection of objects which stores both data members values and operations.What is Relational ModelThe relational model represents the database as a collection of relations. A relation is nothing but a table of values. Every row in the table represents a collection of related data values. These rows in the table denote a real-world entity or relationship.The table name and column names are helpful to interpret the meaning of values in each row. The data are represented as a set of relations. In the relational model, data are stored as tables. However, the physical storage of the data is independent of the way the data are logically organized.Relational Model ConceptsAttribute: Each column in a Table. Attributes are the properties which define a relation. e.g., Student_Rollno, NAME,etc.Tables – In the Relational model the, relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes.Tuple – It is nothing but a single row of a table, which contains a single record.Relation Schema: A relation schema represents the name of the relation with its attributes.Degree: The total number of attributes which in the relation is called the degree of the relation.Cardinality: Total number of rows present in the Table.Column: The column represents the set of values for a specific attribute.Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples.Relation key - Every row has one, two or multiple attributes, which is called relation key.Attribute domain – Every attribute has some pre-defined value and scope which is known as attribute domainDomain :It is a collection of values from which the value is derived for a column.What are Keys?A DBMS key is an attribute or set of an attribute which helps you to identify a row (tuple) in a relation(table). They allow you to find the relation between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table.Example:Employee IDFirstNameLastName11AndrewJohnson22TomWood33AlexHaleIn the above-given example, employee ID is a primary key because it uniquely identifies an employee record. In this table, no other employee can have the same employee ID.Why we need a Key?Here, are reasons for using Keys in the DBMS system.Keys help you to identify any row of data in a table. In a real-world application, a table could contain thousands of records. Moreover, the records could be duplicated. Keys ensure that you can uniquely identify a table record despite these challenges.Allows you to establish a relationship between and identify the relation between tablesHelp you to enforce identity and integrity in the relationship.What is a Primary Key?PRIMARY KEY is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key can't be a duplicate meaning the same value can't appear more than once in the table. A table cannot have more than one primary key.Rules for defining Primary key:Two rows can't have the same primary key valueIt must for every row to have a primary key value.The primary key field cannot be null.The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.Example:In the following example, StudID is a Primary Key.StudIDRoll NoFirst NameLastNameEmail111TomPriceabc@212NickWrightxyz@313DanaNatanmno@What is the Alternate key?ALTERNATE KEYS is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key.Example:In this table, StudID, Roll No, Email are qualified to become a primary key. But since StudID is the primary key, Roll No, Email becomes the alternative key.StudIDRoll NoFirst NameLastNameEmail111TomPriceabc@212NickWrightxyz@313DanaNatanmno@What is a Candidate Key?CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.Properties of Candidate key:It must contain unique valuesCandidate key may have multiple attributesMust not contain null valuesIt should contain minimum fields to ensure uniquenessUniquely identify each record in a tableExample: In the given table Stud ID, Roll No, and email are candidate keys which help us to uniquely identify the student record in the table.StudIDRoll NoFirst NameLastNameEmail111TomPriceabc@212NickWrightxyz@313DanaNatanmno@What is the Foreign key?FOREIGN KEY is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table.Example:DeptCodeDeptName001Science002English005ComputerTeacher IDFnameLnameB002DavidWarnerB017SaraJosephB009MikeBruntonIn this example, we have two table, teach and department in a school. However, there is no way to see which search work in which department.In this table, adding the foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables.Teacher IDDeptCodeFnameLnameB002002DavidWarnerB017002SaraJosephB009001MikeBruntonThis concept is also known as Referential Integrity.SQLSQL is an acronym of Structured Query Language. It is a standard language developed and used for accessing and modifying relational databases.The SQL language was originally developed at the IBM research laboratory in San José in the early 70s.SQL is being used by many database management systems. Some of them are:MySQL, PostgreSQL, Oracle, SQLite, Microsoft SQL ServerFeatures of SQL:Retrieve data from databaseInsert data in databaseUpdate data in databaseCreate new databaseCreate new tables in databaseCreate views in databaseAdvantages of using SQL, Easy to use: Commands are very easy to use & learnPortable: it is compatible with other databases.Handle large volume of dataReliable: Well defined commands always provide desirable results without ambiguity.Provide data abstraction (security of data); provides greater degree of abstraction as compared to other procedural language.Easily linked with HLL(High level languages)Case insensitive Data Definition Language DDL commands are used for creating databases and tables. It contains necessary statements for creating, manipulating, altering and deleting tables.CREATE (create database and table)ALTER (alter table)DROP (delete table)Data Manipulation Language:DML commands are used for manipulating Data.SELECT (view data from table)INSERT (insert data in table)UPDATE (update data in table)DELETE (delete data from table)Data Types(MySQL): We will use only bold datatypes TINYINT( )-128 to 127 normal0 to 255 UNSIGNED.SMALLINT( )-32768 to 32767 normal0 to 65535 UNSIGNED.MEDIUMINT( )-8388608 to 8388607 normal0 to 16777215 UNSIGNED.INT( )-2147483648 to 2147483647 normal0 to 4294967295 UNSIGNED.BIGINT( )-9223372036854775808 to 9223372036854775807 normal0 to 18446744073709551615 UNSIGNED.FLOATA small approximate number with a floating decimal point.DOUBLE( , )A large number with a floating decimal point.DECIMAL(x,y)Number stored in decimal format, allowing for a fixed decimal point. here ‘x’ is total number of digit and ‘y’ is number of decimal places.Decimal hold upto 19 significant digitNUMERIC(x,y)Number stored in decimal format, allowing for a fixed decimal point. here ‘x’ is total number of digit and ‘y’ is number of decimal placesDecimal hold upto 20 significant digitCHAR(x)A fixed ‘x’ number of characters upto 0 to 255 characters long.VARCHAR(x)A variable length ‘x’ characters upto 0 to 255 characters long. It will not leave unused space, it releases the unused memory spaceVARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.TEXTA string with a maximum length of 65535 characters.DATEYYYY-MM-DDSQL commands:Getting listings of databases; mysql> SHOW DATABASES;Creating a database-mysql> CREATE database <databasename>;mysql> CREATE database myschool;Deleting a database mysql> DROP database <databasename>;mysql> DROP database myschool;After we have created the database we use the USE statement to change the currentmysql> USE <database name>;mysql> USE myschool;Getting listings of tables in database (myschool)mysql> SHOW TABLES;The command DESCRIBE is used to view the structure of a table.mysql> DESCRIBE <tablename>;mysql> DESCRIBE student;To remove a table (DROP)mysql> drop table <tablename>;mysql> drop table student;Creating a table (CREATE)Creating a table in the database is achieved with a CREATE table command.mysql> CREATE TABLE student (lastname varchar(15),Firstname varchar(15), city varchar(20), class char(2));Insert data in Table (INSERT)To insert new rows into an existing table use the INSERT command:mysql>INSERT INTO student values(‘dwivedi’,’freya’,’Udaipur’,’4’);Similarly we can insert multiple records.Note 1: In INSERT command, only those columns can be omitted that have either default value or they allow NULL values.Note 2: To insert NULL values in a specific column, you can type NULL without quotes.Note 3: Dates are default entered in ‘YYYY-MM-DD’ format.INSERT command can also be used to take or derive values from one table and place them in another by using it with a query. To do this, simply replace the VALUES clause with an appropriate query as follows:mysql>INSERT INTO newstudent SELECT * FROM student WHERE class=4;Note: Both the tables must be existing tables of the database.View data from Table (SELECT)With the SELECT command we can retrieve (or see) previously inserted rows:mysql> SELECT * FROM student;A general form of SELECT is:SELECT what to select(field name) FROM table(s)WHERE condition that the data must satisfy;Conditions can be set with help of following operators:Comparison operators are: < ; <= ; = ; != or <> ; >= ; >Logical operators are: AND ; OR ; NOTComparison operator for special value NULL: ISSelecting rows by using the WHERE clause in the SELECT commandmysql> SELECT * FROM student WHERE class=“4";Selecting specific columns(Projection) by listing their namesmysql> SELECT first_name, class FROM student;Update data in Table (UPDATE)To modify or update entries in the table use the UPDATE commandmysql> UPDATE student SET class=“V" WHERE firstname=“freya";All columns will be updated with same valuemysql> UPDATE student SET class=“V"; Delete data from TableDeleting selected rows from a table using the DELETE commandmysql> DELETE FROM student WHERE firstname=“amar";Eliminating Redundant Data: (with Keyword DISTINCT)DISTINCT keyword eliminates duplicate rows from the result of a SELECT statement.mysql> SELECT DISTINCT city FROM Studentmysql> SELECT DISTINCT city FROM Student WHERE class=4BETWEEN- to access data in specified rangemysql> SELECT * FROM Student WHERE class between 4 and 6;IN- operator allows us to easily test if the expression is in the list of values.mysql> SELECT * FROM Student WHERE class in (4,5,6);Pattern Matching – LIKE OperatorA string pattern can be used in SQL using the following wild card% Represents a substring in any length_ Represents a single characterExample:‘A%’ represents any string starting /with ‘A’ character.‘_ _A’ represents any 3 character string ending with ‘A’.‘_B%’ represents any string having second character ‘B’‘_ _ _’ represents any 3 letter string.A pattern is case sensitive and can be used with LIKE operator.mysql> SELECT * FROM Student WHERE Name LIKE ‘A%’;mysql> SELECT * FROM Student WHERE Name LIKE%Singh%’;mysql> SELECT Name, City FROM Student WHERE Class>=8AND Name LIKE ‘%Kumar%’ ;Altering TableThe SQL ALTER TABLE command is used to add, delete or modify columns in an existing tableALTER TABLE command is also used to add and drop various constraints on an existing table.SyntaxALTER TABLE command to add a New Column in an existing table is as follows.ALTER TABLE table_name ADD column_name datatype;ALTER TABLE employeeADD (tel_number integer);ALTER TABLE command to DROP COLUMN in an existing table is as follows.ALTER TABLE table_name DROP COLUMN column_name;ALTER TABLE employeeDROP grade;ALTER TABLE command to change the DATA TYPE of a column in a table is as follows.ALTER TABLE table_name MODIFY COLUMN column_name datatype; ALTER TABLE employeeMODIFY( Job char(30) );ALTER TABLE command to change name of one column:ALTER TABLE table_name CHANGE old_column new_column datatype; ALTER TABLE employeeCHANGE First_Name FName varchar(30);Ordering Query Result – ORDER BY ClauseA query result can be orders in ascending (A-Z) or descending (Z-A) order as per any column. Default is Ascending order.mysql> SELECT * FROM Student ORDER BY class;mysql> SELECT * FROM Student ORDER BY City;To get descending order use DESC key word.mysql> SELECT * FROM Student ORDER BY class DESC;mysql> SELECT * FROM Student ORDER BY City DESC;mysql> SELECT Name, Fname, City FROM StudentWhere Name LIKE ‘R%’ ORDER BY Class;GROUP BY:Sometimes it is required to apply a Select query in a group of records instead of the whole table.The GROUP BY clause combines all those records that have identical values in a particular field or a group of fields. This grouping results into one summary record per group.We can group records by using GROUP BY <column> clause with Select command. A group column is chosen which has non-distinct (repeating) values like City, Job etc.LastnameFnameCityClassSharmaRajeshJaipur12KumarKamalKota12SaxenaRajeevKota10SinghRohitAjmer10VermaSachinJaipur11Example:SELECT COUNT(class)FROM student GROUP BY city;COUNT(class)22SELECT city, COUNT(*)FROM student GROUP BY city;Citycount(*)Jaipur2Kota2Ajmer1The GROUP BY Clause is used to group rows with the same values.The GROUP BY Clause is used together with the SQL SELECT statement.The SELECT statement used in the GROUP BY clause can only be used to contain column names, aggregate functions, constants and expressions.The HAVING clause is used to restrict the results returned by the GROUP BY clause.Aggregate Functions Name PurposeSUM() Returns the sum of the given column.MIN() Returns the minimum value in the given column.MAX() Returns the maximum value in the given column.AVG() Returns the Average value of the given column.COUNT() Returns the total number of values/ records as per given column.Aggregate Functions & NULLNull values are excluded while (avg) aggregate function is usedConsider a table Emp having following records as-Code Name SalE1 Mohak NULLE2 Anuj 4500E3 Vijay NULLE4 Vishal 3500E5 Anil 4000SQL QueriesResult of querymysql> Select Sum(Sal) from EMP;12000mysql> Select Min(Sal) from EMP;3500mysql> Select Max(Sal) from EMP;4500mysql> Select Count(Sal) from EMP;3mysql> Select Avg(Sal) from EMP;4000mysql> Select Count(*) from EMP;5Aggregate Functions & GroupAn Aggregate function may applied on a column with DISTINCT or ALL keyword. If nothing is given ALL is assumed.Using SUM (<Column>)This function returns the sum of values in a given column or expression.mysql> Select Sum(Sal) from EMP;mysql> Select Sum(DISTINCT Sal) from EMP;mysql> Select Sum (Sal) from EMP where City=‘Jaipur’;mysql> Select Sum (Sal) from EMP Group By City;mysql> Select Job, Sum(Sal) from EMP Group By Job;Using MIN (<column>)This function returns the Minimum value in the given column.mysql> Select Min(Sal) from EMP;mysql> Select Min(Sal) from EMP Group By City;mysql> Select Job, Min(Sal) from EMP Group By Job;Using MAX (<Column>)This function returns the Maximum value in given column.mysql> Select Max(Sal) from EMP;mysql> Select Max(Sal) from EMP where City=‘Jaipur’;mysql> Select Max(Sal) from EMP Group By City;Using AVG (<column>)This functions returns the Average value in the given column.mysql> Select AVG(Sal) from EMP;mysql> Select AVG(Sal) from EMP Group By City;Using COUNT (<*|column>)This function returns the number of rows in the given column.mysql> Select Count ( * ) from EMP;mysql> Select Count(Sal) from EMP Group By City;mysql> Select Count(*), Sum(Sal) from EMP Group By Job;Aggregate Functions & ConditionsYou may use any condition on group, if required. HAVING <condition> clause is used to apply a condition on a group.mysql> Select Job,Sum(Pay) from EMPGroup By Job HAVING Sum(Pay)>=8000;mysql> Select Job, Sum(Pay) from EMPGroup By Job HAVING Avg(Pay)>=7000;mysql> Select Job, Sum(Pay) from EMPGroup By Job HAVING Count(*)>=5;mysql> Select Job, Min(Pay),Max(Pay), Avg(Pay) from EMP GroupBy Job HAVING Sum(Pay)>=8000;mysql> Select Job, Sum(Pay) from EMP Where City=‘Jaipur’Note :- Where clause works in respect of the whole table but Having works on Group only. If Where and Having both are used then Where will be executed first.Joins: equi-join and natural joinA join is a query that combines rows from two or more tables. In a JOIN query more than one table are listed in the FROM clause. MySQL provides various type of Joining :CROSS JOIN or CARTESIAN PRODUCTEQUI-JOIN (in Syllabus)NATURAL JOIN (in Syllabus)Cross Join (Cartesian product) – (not in syllabus)It return all possible concatenation of all rows from both table i.e. one row of First table is joined with all the rows of second table.Cartesian product joins each row of one table with each row of another table. So if –First table have 6 rows and second table have 4 rows, then total number of rows in output will be 6 x 4 = 24. EQUI-JOINThe join, in which columns are compared for equality is called Equi-Join. A non-equi join specifies condition with non-equality operator. In equi-join we put (*) in the select list therefore the common column will appear twice in the output.To understand the output, let's take 2 table one for employee (contains employee detail with deptno) and another for department contains deptno and other department details.Now we want to fetch details of employee along with its corresponding matching department. Like for ‘alam’ deptno is 10 so from dept table it should show deptno 10 details and so onFrom the above query, we can observe that while doing equi-join we have to give equality condition on common column of both tables so that it picks related recordsOr we can give commands by giving tables a diff names for easy understanding:Natural JoinThe JOIN in which only one of the identical columns exists is called Natural Join. It is similar to Equi-join except that duplicate columns are eliminated in Natural join that would otherwise appear in Equi-Join.In natural join we specify the names of column to fetch in place of (*) which is responsible for appearing common column twice in output.A common error while giving command :The reason of this error is – the deptno exists in both the table, so in this case if we are selecting or using only deptno then it becomes ambiguous from which table this deptno will be selectedTo resolve this error, just qualify the common column by table name as TableName.column nameTill now we have performed joining using traditional SQL method which is common to most of the RDBMS software now we will learn MySQL style of joining using JOIN clause. MySQL support various options with JOINCartesian product using JOINSelect * from shades JOIN color;OrSelect * from shades CROSS JOIN color;Equi – Join using JOINSelect * from emp JOIN dept ON emp.deptno = dept.deptno;Select * from emp JOIN dept ON emp.deptno = dept.deptno where salary>50000;Natural – Join using JOINSelect * from emp NATURAL JOIN deptIn NATURAL JOIN condition the join condition is not required it automatically joins based on the common column valueInterface of Python with an SQL databaseA database is nothing but an organized collection of data. Data is organized into rows, columns and tables and it is indexed to make it easier to find relevant information. It becomes necessary to provide an interface between Python and Database through SQL.SQL is just a query language, it is not a database. To perform SQL queries, we need to install any database for example Oracle, MySQL, MongoDB, PostGres SQL, SQL Server, DB2 etc.Python Database API supports a wide range of database servers, like msql, postgressql, Informix, oracle, Sybase etc.Connecting SQL with PythonBefore we connect python program with any database like MySQL we need to build a bridge to connect Python and MySQL.Steps to use mysql-connectorDownload Mysql API ,exe file and install it.(click here to download)Or Install Mysql-Python Connector (Open command prompt and execute command) >pip install mysql-connectorWrite python statement in python shell import mysql.connectorIf no error message is shown means mysql connector is properly installedTo provide interface between database and programming language:1. Connection must be established. 2. mysql must be installed on the system 3. Database and Table also must be already created.import mysql.connector Or import mysql.connector as msHere “ms” is an alias, so every time we can use “ms” in place of “mysql.connector”To create connection, connect() function is usedIts syntax is:connect(host=<server_name>,user=<user_name>,passwd=<password>[,database=<database>])Here server_name means database servername, generally it is given as “localhost”User_name means user by which we connect with mysql generally it is given as “root”Password is the password of user “root”Database is the name of database whose data(table) we want to useExample:import mysql.connector mydb=mysql.connector.connect(host="localhost",user="root",passwd="root“, database=“school”) print(mydb)After successful execution of above statements in python following out will be displayed<mysql.connector.connection.MySQLConnection object at 0x022624F0>Example:is_connected() function returns true if connection is established otherwise false“mys” is an alias of package “mysql.connector”“mycon” is connection object which stores connection established with MySQLConnect() functions is used to establish connection with given parameters.Cursor object :The MySQLCursor class instantiates objects that can execute operations such as SQL statements. Cursor objects interact with the MySQL server using a MySQLConnection object.Cursor stores all the data as a temporary container of returned data and we can fetch data one row at a time from Cursor.TO CREATE CURSORCursor_name = connectionObject.cursor()For e.g.mycursor = mycon.cursor()TO EXECUTE QUERYWe use execute() function to send query to connectionCursor_name.execute(query)For e.g.mycursor.execute(?select * from emp?)Example:Output shows cursor is created and query is fired and stored, but no data is coming. To fetch data we have to use functions like fetchall(), fetchone(), fetchmany() etc.Example: (creating database)import mysql.connector mydb=mysql.connector.connect(host="localhost",user="root",passwd="") mycursor=mydb.cursor()mycursor.execute("create database if not exists school") mycursor.execute("show databases")for x in mycursor: print(x)Through line 4 we are creating a database named school (if it is already not created with the help of cursor object.)Line 5 executes the sql query show databases and store result in mycursor as collection, whose values are being fetched in x variable one by one.On execution of above program school database is created and a list of available databases is shown.Example: (creating table)import mysql.connectormydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student")mycursor=mydb.cursor()mycursor.execute("create table student(rollno int(3) primary key,name varchar(20),age int(2))")Example: change table structure (add, edit, remove column of a table)import mysql.connectormydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student") mycursor=mydb.cursor()mycursor.execute("alter table emp add (bonus int(3))") mycursor.execute("desc emp")for x in mycursor: print(x)Example: (insert record in a table)import mysql.connectormydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student")mycursor=mydb.cursor() while 1==1: ch=int(input("enter -1 to exit any other no to insert record into student table")) if ch==-1: break eno=int(input("Enter eno")) ename=input("Enter ename") edept=input("Enter dept") sal=int(input("Enter salary")) mycursor.execute("insert into emp values('"+str(eno)+"','"+ename+"','"+edept+"','"+str(sal)+"')") mit()Example:(search a record)import mysql.connectormydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student")mycursor=mydb.cursor()nm=input("enter name")mycursor.execute("select * from emp where ename='"+nm+"'") for x in mycursor: print (x)Example: (delete record of a table)import mysql.connectormydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student")mycursor=mydb.cursor()mycursor.execute("delete from emp where eno=100") mit()In above program delete query will delete a record with rollno=mit() method is necessary to call for database transaction.Example: (Update record) import mysql.connectormydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student")mycursor=mydb.cursor()mycursor.execute("update emp set sal=1000 where eno=101")mit()In above program update query update the marks with 99 of rollno=2To extract data from cursor following functions are used:fetchall() : it will return all the record in the form of tuple.fetchone() : it return one record from the result set. i.e. first time it will return first record, next time it will return second record and so on. If no more record it will return Nonefetchmany(n) : it will return n number of records. if no more record it will return an empty tuple.rowcount : it will return number of rows retrieved from the cursor so far.Example:import mysql.connector as mysmycon=mys.connect(host="localhost",user="root",passwd="",database="student")mycursor=mycon.cursor()mycursor.execute('select * from emp')mydata=mycursor.fetchall()nrec=mycursor.rowcountprint('Total records fetch:',nrec)for row in mydata:print(row)Example:import mysql.connector as mysmycon=mys.connect(host="localhost",user="root",passwd="",database="student")mycursor=mycon.cursor()mycursor.execute('select * from emp')mydata=mycursor.fetchall()nrec=mycursor.rowcountprint('Total records fetch:',nrec)for e,n,d,s in mydata: print (e,n,d,s)Example:import mysql.connector as mysmycon=mys.connect(host="localhost",user="root",passwd="",database="student")mycursor=mycon.cursor()mycursor.execute('select * from emp')mydata=mycursor.fetchall()nrec=mycursor.rowcountprint('Total records fetch:',nrec)for row in mydata: print(row[0],':',row[1],':',row[2],':',row[3])Example: (fetch all recrods)import mysql.connector mydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student")mycursor=mydb.cursor() mycursor.execute("select * from emp") myrecords=mycursor.fetchall()for x in myrecords: print (x)Example:import mysql.connector as mysmycon=mys.connect(host="localhost",user="root",passwd="",database="student")mycursor=mycon.cursor()mycursor.execute('select * from emp')mydata=mycursor.fetchone()nrec=mycursor.rowcountprint('Total records fetch:',nrec)print(mydata)mydata=mycursor.fetchone()nrec=mycursor.rowcountprint('Total records fetch:',nrec)print(mydata)Example:import mysql.connector mydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student")mycursor=mydb.cursor() mycursor.execute("select * from emp") row=mycursor.fetchone()while row is not None: print(row) row = mycursor.fetchone()Example:import mysql.connector as mysmycon=mys.connect(host="localhost",user="root",passwd="",database="student")mycursor=mycon.cursor()mycursor.execute('select * from emp')mydata=mycursor.fetchmany(3)nrec=mycursor.rowcountprint('Total records fetch:',nrec)for row in mydata: print(row)Example:import mysql.connector as mysmycon=mys.connect(host="localhost",user="root",passwd="",database="student")mycursor=mycon.cursor()e=int(input('Enter emp number to search'))query='select * from emp where eno='+str(e)mycursor.execute(query)data=mycursor.fetchone()if data!=None: print(data)else: print('No such employee')Example: (rowcount()): Rows affected by Query. We can get number of rows affected by the query byimport mysql.connector mydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student")mycursor=mydb.cursor()mycursor = mydb.cursor(buffered=True) mycursor.execute("select * from emp") noofrows=mycursor.rowcountprint("No of rows in student table are",noofrows)This type cursor fetches rows and buffers them after getting output from MySQL database. We can use such cursor as iterator. There is no point in using buffered cursor for single fetching of rows. If we don’t use buffered cursor then we will get -1 as output from ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download