UNIT: III (DATABASE MANAGEMENT)
INTRODUCTION : A database is an organized collection of data that has been arranged and is kept in a computer system. A database management system often oversees a database (DBMS).
NEED OF A DATABASE:
Advantages of Database System
- Databases 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.
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 DBMS
There are 4 major types of
DBMS. Let's look into them in detail.
In 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 parent.
Network Model
The 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 model
Relational 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 Model
In 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 Model
The 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.
Relational Model Concepts
Attribute: 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 domain
Domain :It is a collection of values from which the value is derived for a column.
trick to remember to find out no of degree and Cardinality of a table:
DCCR >> (Degree-> Column Cardinality-> Row)
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 ID FirstName LastName
11 ANUJ KUMAWAT
22 GITIKA VERMA
33 TRAPTI SINGH
In 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 tables
Help 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 value
- It 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.
StudID Roll No First Name LastName Email
1 11 ANUJ KUMAR abc@gmail.com
2 12 SOMYA SINGH xyz@gmail.com
3 13 Dana Ram mno@yahoo.com
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.
StudID Roll No First
Name LastName Email
1 11 ANUJ KUMAR abc@gmail.com
2 12 SOMYA SINGH xyz@gmail.com
3 13 Dana Ram mno@yahoo.com
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 values
- Candidate key may have multiple attributes
- Must not contain null values
- It should contain minimum fields to ensure uniqueness
- Uniquely identify each record in a table
Example: 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.
StudID Roll No First Name LastName Email
1 11 ANUJ KUMAR abc@gmail.com
2 12 SOMYA SINGH xyz@gmail.com
3 13 Dana Ram mno@yahoo.com
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:
DeptCode DeptName
001 Science
002 English
005 Computer
Teacher ID Fname Lname
B002 Vinod Meena
B017 Birbal Jat
B009 Prabhu Singh
In 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 ID DeptCode Fname Lname
B002 002 Vinod Meena
B017 002 Birbal Jat
B009 001 Prabhu Singh
This concept is also known as Referential Integrity.
What is the Unique key?
Difference between Primary Key and Unique Key:
- The primary key will not accept NULL values whereas the Unique key can accept NULL values.
- A table can have only one primary key whereas there can be multiple unique keys on a table.
- A Primary Key can be a Unique Key, but a Unique Key cannot be a primary key.
SQL (Structured Query Language)
SQL 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 Server
Features of SQL:
1. Retrieve data from
database
2. Insert data in database
3. Update data in database
4. Create new database
5. Create new tables in
database
6. Create views in database
Advantages of using SQL,
1. Easy to use: Commands are
very easy to use & learn
2. Portable: it is compatible
with other databases.
3. Handle large volume of
data
4. Reliable: Well defined
commands always provide desirable results without ambiguity.
5. Provide data abstraction
(security of data); provides greater
degree of abstraction as compared to other procedural language.
6. Easily linked with
HLL(High level languages)
7. Case insensitive
Data Definition Language : (CAD)
DDL commands are used for creating databases and tables. It contains necessary statements for creating, manipulating, altering and deleting tables.
1. CREATE (create database
and table)
2. ALTER (alter table)
3. DROP (delete table)
trick to remember DDL Commands: CAD
Data Manipulation Language:
DML commands are used for manipulating Data.
1. SELECT (view data from
table)
2. INSERT (insert data in
table)
3. UPDATE (update data in
table)
4. DELETE (delete data from
table)
trick to remember DML Commands: SIDU
Data Types Mostly used in SQL:
|
Data type |
Meaning |
Example |
|
CHAR (n) |
Fixed length character string. ‘n’ is the number of characters. |
CHAR(5):“Ashok” “Vijay” |
|
VARCHAR(n) |
Variable length character string. ‘n’ is the maximum number of
characters in the string. |
VARCHAR(15): |
|
DATE |
Date in the form of YYYY-MM-DD |
DATE: ‘2014-03-20’ |
|
INTEGER |
Integer number |
23 |
|
DECIMAL (m, d) |
Fixed point number m represents the number of significant digits that
are stored for values and d represents the number of digits that can be
stored following the decimal point. If d is zero or not specified then the
value does not contains any decimal part. |
DECIMAL(5,2) : 999.99 DECIMAL (5) : 23456 |
.
SQL commands:
mysql> SHOW DATABASES;
Creating a database-
mysql> CREATE database
myschool;
Deleting a database
- mysql> DROP database <databasename>;
- mysql> DROP database myschool;
- mysql> USE <database name>;
- mysql> USE myschool;
- mysql> SHOW TABLES;
- 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 ; NOT
Comparison operator for
special value NULL: IS
Selecting rows by using the WHERE clause in the SELECT command
mysql> SELECT * FROM
student WHERE class=“4";
Selecting specific columns(Projection) by listing their names
mysql> SELECT
first_name, class FROM student;
Update data in Table (UPDATE)
To modify or update entries in the table use the UPDATE command
mysql> UPDATE student
SET class=“V" WHERE firstname=“freya";
All columns will be
updated with same value
mysql> UPDATE student
SET class=“V";
Delete data from Table
Deleting selected rows from a table using the DELETE command
mysql> 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 Student
mysql> SELECT DISTINCT
city FROM Student WHERE class=4
BETWEEN - to access data in
specified range
mysql> 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 Operator
A string pattern can be
used in SQL using the following wild card
% Represents a substring in any length
_
Represents a single character
Example:
‘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>=8
AND Name
LIKE ‘%Kumar%’ ;
Altering Table
The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table
ALTER TABLE command is
also used to add and drop various constraints on an existing table.
Syntax
ALTER TABLE command to add
a New Column in an existing table is as follows.
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE employee
ADD (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 employee
DROP 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 employee
MODIFY( Job char(30) );
ALTER TABLE command to change name of one column:
ALTER TABLE table_name CHANGE old_column new_column datatype;
ALTER TABLE employee
CHANGE First_Name FName
varchar(30);
Ordering
Query Result – ORDER BY Clause
A 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 Student
Where 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.
Lastname Fname City Class
Sharma Rajesh Jaipur 12
Kumar Kamal Kota 12
Saxena Rajeev Kota 10
Singh Rohit Ajmer 10
Verma Sachin Jaipur 11
Example:
SELECT COUNT(class) FROM student GROUP BY city;
COUNT(class)
2
2
SELECT city, COUNT(*) FROM student GROUP BY city;
City count(*)
Jaipur 2
Kota 2
Ajmer 1
The 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 Purpose
SUM() 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 & NULL
Null values are excluded while (avg) aggregate function is used
Consider a table Emp having following records as-
Code Name Sal
E1 Mohak NULL
E2 Anuj 4500
E3 Vijay NULL
E4 Vishal 3500
E5 Anil 4000
SQL Queries Result
of query
mysql> Select Sum(Sal)
from EMP; 12000
mysql> Select Min(Sal)
from EMP; 3500
mysql> Select Max(Sal)
from EMP; 4500
mysql> Select
Count(Sal) from EMP; 3
mysql> Select Avg(Sal)
from EMP; 4000
mysql> Select Count(*)
from EMP; 5
Aggregate Functions & Group
An 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 & Conditions
You 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 EMP
Group By Job
HAVING Sum(Pay)>=8000;
mysql> Select Job, Sum(Pay) from EMP
Group By Job
HAVING Avg(Pay)>=7000;
mysql> Select Job, Sum(Pay) from EMP
Group By Job
HAVING Count(*)>=5;
mysql> Select Job, Min(Pay),Max(Pay), Avg(Pay) from EMP Group
By 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 join
A 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 PRODUCT
EQUI-JOIN (in Syllabus)
NATURAL JOIN (in Syllabus)
Cross Join (Cartesian product) –
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-JOIN
The 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
on
From 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 records
Or we can give commands by
giving tables a diff names for easy understanding:
Natural Join
The 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 selected
To resolve this error, just qualify the common column by table
name as TableName.column name
Till 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 JOIN
Cartesian product using JOIN
Select * from shades JOIN
color;
Or
Select * from shades CROSS
JOIN color;
Equi – Join using JOIN
Select * 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 JOIN
Select * from emp NATURAL
JOIN dept
In NATURAL JOIN condition the join condition is not required
it automatically joins based on the common column value
Interface of Python with an SQL database
A
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 Python
Before we connect python program with any database like MySQL we need to build a bridge to connect Python and MySQL.
1.
Download Mysql API ,exe file and install it.(click
here
to
download)
Or
2.
Install Mysql-Python Connector
(Open command prompt and execute
command)
>pip install
mysql-connector
3.
Write python statement in python shell import mysql.connector
If
no error message is shown means mysql connector is properly installed
To 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 ms
Here
“ms” is an alias, so every time we can use “ms” in place of “mysql.connector”
To
create connection, connect() function is used
Its
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 use
Example:
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 MySQL
·
Connect() 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 CURSOR
Cursor_name
= connectionObject.cursor()
For
e.g.
mycursor
= mycon.cursor()
TO
EXECUTE QUERY
We
use execute() function to send query to connection
Cursor_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.connector
mydb=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.connector
mydb=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.connector
mydb=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)+"')")
mydb.commit()
Example:(search a record)
import mysql.connector
mydb=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.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student")
mycursor=mydb.cursor()
mycursor.execute("delete from emp where eno=100")
mydb.commit()
In
above program delete query will delete a record with rollno=1.commit() method
is necessary to call for database
transaction.
Example: (Update record)
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="",database="student")
mycursor=mydb.cursor()
mycursor.execute("update
emp set sal=1000 where eno=101")
mydb.commit()
In
above program update query update the marks with 99 of rollno=2
To 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
None
fetchmany(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 mys
mycon=mys.connect(host="localhost",user="root",passwd="",database="student")
mycursor=mycon.cursor()
mycursor.execute('select * from emp')
mydata=mycursor.fetchall()
nrec=mycursor.rowcount
print('Total records fetch:',nrec)
for row in mydata:
print(row)
Example:
import mysql.connector as mys
mycon=mys.connect(host="localhost",user="root",passwd="",database="student")
mycursor=mycon.cursor()
mycursor.execute('select * from emp')
mydata=mycursor.fetchall()
nrec=mycursor.rowcount
print('Total records fetch:',nrec)
for e,n,d,s in mydata:
print
(e,n,d,s)
Example:
import mysql.connector as mys
mycon=mys.connect(host="localhost",user="root",passwd="",database="student")
mycursor=mycon.cursor()
mycursor.execute('select * from emp')
mydata=mycursor.fetchall()
nrec=mycursor.rowcount
print('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 mys
mycon=mys.connect(host="localhost",user="root",passwd="",database="student")
mycursor=mycon.cursor()
mycursor.execute('select * from emp')
mydata=mycursor.fetchone()
nrec=mycursor.rowcount
print('Total records fetch:',nrec)
print(mydata)
mydata=mycursor.fetchone()
nrec=mycursor.rowcount
print('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 mys
mycon=mys.connect(host="localhost",user="root",passwd="",database="student")
mycursor=mycon.cursor()
mycursor.execute('select * from emp')
mydata=mycursor.fetchmany(3)
nrec=mycursor.rowcount
print('Total records fetch:',nrec)
for row in mydata:
print(row)
Example:
import mysql.connector as mys
mycon=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 by
import 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.rowcount
print("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
No comments:
Post a Comment