Unit 3: Data Management

A database is a collection of related data.

Ex:-student marks data(Ex:- cbse exam data),rail/flight ticket reservation data,

bank account data,library books data,social media data,e-commerce(amazon
,flipkart) products data

A database management system (DBMS) is a collection of programs(SOFTWARE)

that enables users to create and maintain a database.

Some examples of open source and commercial DBMS include MySQL,

PostgreSQL, Oracle, Teradata, Microsoft SQL Server, Microsoft Access, MongoDB.

Relational Database:

It uses the concept of a mathematical relation—which looks somewhat like a

table of values—as its basic building block.

Attributes : the columns of a relation are the attributes which are also referred as
fields

Tuple:Each row of data in a relation (table) is called a tuple. The tuples within a
relation must be distinct.

Degree of a table: The Number of Attributes in a table is called it’s degree

Cardinality of a table: The Number of Tuples in a table is called it’scardinality

DOMAIN: It is a set of values from which an attribute can take a value in each
row. Usually, a data type is used to specify domain for an attribute. For example,
in STUDENT relation, the attribute RollNo takes integer values and hence its
domain is a set of integer values. Similarly, the set of character strings constitutes
the domain of the attribute Name.

RDBMS : Relational database management system

STUDENT

GuardianI
RollNo Name AdmnNo AadharNo Subject1 Subject2 Subject3 D
101 A 13122 981010100701 80 76 76 901
102 B 13456 981220093381 70 90 90 901
103 C 12231 781010100623 76 90 90 902
104 D 10781 682237451706 70 90 90 902
105 A 11133 766611112222 70 95 95 902

GUARDIAN

GuardianI GuardianNam
D e Occupation Address PhoneNo
88899911
901 X Banker Adyar 10
Software Velache 88877722
902 Y Engineer ry 22

KEY: A KEY is an attribute or set of an attribute which helps you to identify a

row(tuple) in a relation(table).

CANDIDATE KEY : A relation can have one or more keys. Each key is called a
CANDIDATE KEY

PRIMARY KEY: Out ofone or more candidatekeys in a relation, one key is selected
as PRIMARY KEY. A primary KEY should be unique in a given table.
 ALTERNATE KEYS: All the other candidate keys which are not selected as primary
key are called ALTERNATE KEYS

FOREIGN KEY:

A foreign key is used to represent the relationship between two relations. A

foreign key is an attribute whose value is derived from the primary key of another
relation.

SQL

SQL stands for structured query language.

It is a standard language for creating, accessing and manipulating databases.

DDL
 DDL stands for Data Definition Language.
 It is a language used for defining and modifying the data and its structure.
CREATE, ALTER , DROP

DML

 DML stands for Data Manipulation Language.

 It is a language used for inserting, deleting and updating data in a database.
 It is used to manipulate data in a relational database.
DML commands are as follows
1. INSERT 2. UPDATE 3. DELETE

DQL- DATA QUERY LANGUAGE

Used to retrieve data from database

SELECT
MYSQL
My is the daughter’s name of the MySQL’s co-founder, Monty [Link] name of MySQL
is the combination of My and SQL, [Link] is a database management
system that allows you to manage relational databases. It is open source software
backed by Oracle.
 It means you can use MySQL without paying a dime. Also, if you want, you can
change its source code to suit your needs.

Data Types

Data type Description

CHAR(n) Specifies character type data of length n where n could be any

value from 0 to 255. CHAR is of fixed length, means, declaring
CHAR (10) implies to reserve spaces for 10 characters. If data
does not have 10 characters (for example, ‘city’ has four
characters), MySQL fills the remaining 6 characters with spaces
padded on the right.

VARCHAR(n) Specifies character type data of length ‘n’ where n could be any
value from 0 to 65535. But unlike CHAR, VARCHAR is a variable-
length data type. That is, declaring VARCHAR (30) means a
maximum of 30 characters can be stored but the actual allocated
bytes will depend on the length of entered string. So ‘city’ in
VARCHAR (30) will occupy the space needed to store 4 characters
only.

INT INT specifies an integer value. Each INT value occupies 4 bytes of
storage. The range of values allowed in integer type are -
2147483648 to 2147483647. For values larger than that, we have
to use BIGINT, which occupies 8 bytes.

FLOAT Holds numbers with decimal points. Each FLOAT value occupies 4
bytes.

DATE The DATE type is used for dates in 'YYYY-MM-DD' format. YYYY is
the 4 digit year, MM is the 2 digit month and DD is the 2 digit
date. The supported range is '1000-01-01' to '9999-12-31'.
 MySQL Numeric Types

MySQL supports all standard SQL numeric data types which include INTEGER,
DECIMAL, and NUMERIC. It also supports the approximate numeric data types
(FLOAT, REAL, and DOUBLE PRECISION).

INTEGER

Type Length Minimum Value Maximum Value Minimum Value Maximum Value
in Bytes (Signed) (Signed) (Unsigned) (Unsigned)

TINYINT 1 -128 127 0 255

SMALLINT 2 -32768 32767 0 65535

MEDIUMINT 3 -8388608 8388607 to 0 16777215

INT 4 -2147483648 2147483647 0 4294967295

BIGINT 8 -9223372036854775808 92233720368 0 184467440737

54775807 09551615

Floating-Point Types
Type Length in Bytes
The FLOAT and DOUBLE types represent
approximate numeric data. FLOAT 4
MySQL allows a nonstandard syntax:
FLOAT(M,D) or REAL(M,D) or DOUBLE DOUBLE 8
PRECISION(M,D).

Here values can be stored up to M digits in total where D represents the decimal
point. For example, a column defined as FLOAT(8,5) will look like -999.99999.
MySQL performs rounding when storing values, so if you insert 999.00009 into a
FLOAT(7,4) column, the approximate result is 999.0001
Financial data such as account balances must be stored as NUMERIC or DECIMAL
data types as they are accurate.

Constraints

Constraints are certain types of restrictions on the data values that an attribute
can have. They are used to ensure the accuracy and reliability of data. However, it
is not mandatory to define constraint for each attribute of a table.

Constraint Description

NOT NULL Ensures that a column cannot have NULL values where NULL
means missing/ unknown/not applicable value.

UNIQUE Ensures that all the values in a column are distinct/unique.

PRIMARY KEY The column which can uniquely identify each row or record in a
table.

FOREIGN KEY The column which refers to value of an attribute defined as

primary key in another table.

DEFAULT A default value specified for the column if no value is provided.

 CREATE COMMAND
Database creation:

Syntax :

CREATE DATABASE [IF NOT EXISTS] db_name;

CREATE DATABASE student;

Note that [IF NOT EXISTS] is optional.

LIST ALL DATABASES:

mysql> show databases;

Selecting a Database:

SYNTAX1: USE db_name

SYNTAX2: USE db_name ;

USE student;

Creating a table

CREATE TABLE tablename(

attributename1 datatype constraint,

attributename2 datatype constraint,

attributenameN datatype constraint);

MYSQL> CREATE TABLE STUDENT(RNO INT,NAME
VARCHAR(50),ADMNO INT,AADHAR BIGINT,PER FLOAT,GID INT);
LIST ALL TABLES IN A DATABASE:

mysql> show tables;

DESCRIBE STRUCTURE OF A TABLE

mysql>desc student;

mysql> describe student;

ALTER COMMAND
After creating a table we may realize that we need to add/remove an attribute or
to modify the datatype of an existing attribute or to add constraint in attribute.
In all such cases, we need to change or alter the structure of the table by using
the alter statement.
Syntax:
ALTER TABLE tablename ADD/Modify/DROP attribute1 DATATYPE constraint
default value, ....

(A) Add primary key to a relation

Syntax: ALTER TABLE tablename ADD PRIMARY KEY ( attribute name);
ALTER TABLE student ADD PRIMARY KEY (rno);
(B)Remove primary key from the table
Syntax: ALTER TABLE table_name DROP PRIMARY KEY;

ALTER TABLE STUDENT DROP PRIMARY KEY;

(C) Add an attribute to an existing table
ALTER TABLE table_name ADD attribute_name DATATYPE;
ALTER TABLE STUDENT ADD GENDER CHAR(1);
(D) Remove an attribute
ALTER TABLE table_name DROP attribute;

ALTER TABLE STUDENT DROP ADMNO;

 DROP COMMAND
Sometimes a table in a database or the database itself needs to be removed. We
can use DROP statement to remove a database or a table permanently from the
system. However, one should be very cautious while using this statement as it
cannot be undone. Using the Drop command to remove a database will ultimately
remove all the tables within it.
Syntax to drop a database:

SYNTAX: DROP DATABASE [IF EXISTS] db_name;

DROP DATABASE student ;

Syntax to drop a table:
DROP TABLE [IF EXISTS] table_name;

Note that [IF EXISTS] is optional.

INSERT COMMAND

INSERT INTO statement is used to insert new records in a table. Its syntax is:

INSERT INTO tablename VALUES(value 1, value 2,....);

mysql> insert into student values(101,'a',13101,444555666777,80.5,901);

mysql> insert into student values(102,'b',13102,333555666777,70.5,901);

insert into student values(103,'c',15102,444555666777,80.5,902);

insert into student values(105,'a',12354,75,903);
If we want to provide values only for some of the attributes in a table (supposing
other attributes having NULL or any other default value), then we shall specify
the attribute name alongside each data value as shown in the following syntax
of INSERT INTO statement.
Syntax:
INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2,
...);

INSERT INTO STUDENT(RNO,NAME,ADMNO,AADHAR,PER,GID)

VALUES(104,'D',16104,444555666777,80.5,901);
 UPDATE COMMAND
We may need to make changes in the value(s) of one or more columns of existing
records in a table. For example, we may require some changes in address, phone
number or spelling of name, etc. The UPDATE statement is used to make such
modifications in the existing data.
Syntax:
SYNTAX:

UPDATE table_name
SET attribute1 = value1, attribute2 = value2, ...
WHERE condition;

UPDATE STUDENT
SET PER = 95
WHERE RNO = 103;

Note: If we miss the where clause in the UPDATE statement then the PER of all
the records will be changed to 95.

DELETE COMMAND
DELETE statement is used to delete one or more record(s) from a table.
Syntax:
DELETE FROM table_name WHERE condition;

DELETE FROM STUDENT WHERE rno = 102;

Like UPDATE statement, we need to be careful to include WHERE clause
while using DELETE statement to delete records in atable. Otherwise,
all the records in the table will get deleted.

SELECT COMMAND
The SQL statement SELECT is used to retrieve data from the tables in a database
and the output is also displayed in tabular form.

SELECT attribute1, attribute2, ...

FROM table_name

WHERE condition ;
*(star) – can be used to retrieve all columns
Syntax: SELECT * FROM table_name;

mysql>SELECT * FROM student

Retrieve selected columns

mysql> SELECT rNo FROM student;
Renaming of columns
mysql> SELECT rNo as ROLLNO FROM student;
mysql> SELECT rNo ROLLNO FROM student;

WHERE Clause
The WHERE clause is used to retrieve data that meet some specified conditions.

SELECT *FROM student WHERE gid='901';

OPERATORS:
ARITHMETIC(MATHEMATICAL) OPERATORS:
+,-,*,/,%
mysql> select rno, name,sub1+sub2+sub3 as Total,(sub1+sub2+sub3)/3 as
Percentage from student;
+------+------+-------+------------+
| rno | name | Total | Percentage |
+------+------+-------+------------+
| 1 | A | 245 | 81.6667 |
| 2 | B | 275 | 91.6667 |
| 3 | C | NULL | NULL |
+------+------+-------+------------+
RELATIONAL OPERATORS:
> GREATER THAN >= GREATER THAN OR EQUALTO
< LESS THAN <= LESS THAN OR EQUALTO
= EQUAL TO
!= NOT EQUAL TO
<> NOT EQUAL TO
mysql> select rno, name,sub1+sub2+sub3 as Total,(sub1+sub2+sub3)/3 as
Percentage from student where (sub1+sub2+sub3)/3 > 90;
+------+------+-------+------------+
| rno | name | Total | Percentage |
+------+------+-------+------------+
| 2 | B | 275 | 91.6667 |
+------+------+-------+------------+
1 row in set (0.01 sec)

Logical Operators AND ,OR, NOT

In SQL, all logical operators evaluate to TRUE, FALSE, or NULL (UNKNOWN). In
MySQL, these are implemented as 1 (TRUE), 0 (FALSE), and NULL.
We have used = operator in WHERE clause. We can also use other relational
operators (<, <=, >, >=, !=) to specify conditions.
 The logical operators AND, OR, and NOT are used with WHERE clause to
combine multiple conditions.

SELECT * FROM student WHERE NOT name=’a’;

SELECT * FROM student WHERE per>70 and per<80;
SELECT * FROM student WHERE per>70 or name=’a’;

BETWEEN:
SELECT * FROM student WHERE per BETWEEN 70 and 80;
IN:
SELECT * FROM student WHERE RNO IN (104,102);

DISTINCT Clause
By default, SQL shows all the data retrieved through query as output. However,
there can be duplicate values. The SELECT statement when combined with
DISTINCT clause, returns records without repetition

SELECT distinct name FROM student;

SELECT

distinct name,per FROM student;

LIKE: USED FOR PATTERN MATCHING

The LIKE operator makes use of the following two wild card characters:
1 % (percent)— used to represent zero, one, or multiple characters
2 _ (underscore)— used to represent a single character

SYNTAX:
SELECT *FROM TABLE_NAME WHERE ATTRIBUTE_NAME LIKE ‘pattern’

display details of all those employees whose name starts with 'a'.

mysql> SELECT * FROM EMPLOYEE WHERE name LIKE 'a%';

Display details of all those employees whose name ends with 'a'.
 mysql> SELECT * FROM EMPLOYEE WHERE name LIKE '%a';

Display details of all those employees whose name contains 'a'.

mysql> SELECT * FROM EMPLOYEE WHERE name LIKE '%a%';

Displays names of all employees containing 'a' as the second character.

mysql> SELECT name FROM EMPLOYEE WHERE name LIKE '_a%';

Display details of all those employees whose name consists of exactly 5 letters
and starts with any letter but has ‘ANYA’ after that.

mysql> SELECT * FROM EMPLOYEE WHERE name LIKE '_ANYA';

Display names of all the employees containing 'se' as a substring in name.

mysql> SELECT name FROM EMPLOYEE WHERE name LIKE '%se%';

Handling NULL Values

SQL supports a special value called NULL to represent a missing or unknown

value.

SELECT *
FROM EMPLOYEE
WHERE CITY IS NULL;
SELECT *
FROM EMPLOYEE
WHERE CITY IS NOT NULL;

Aggregate functions – MIN,MAX,AVG,COUNT,SUM

An aggregate function performs a calculation on multiple values and returns a
single value.

1) COUNT2) SUM3) AVG4) MIN5) MAX

The following illustrates the syntax of an aggregate function:

function_name(DISTINCT | ALL )
note: by default ALL is used
Employee Table :

COUNT Function
The COUNT function returns the total number of values in the specified field. It
works on both numeric and non-numeric data types. All aggregate functions by
default exclude nulls values before working on the data.
COUNT (*) is a special implementation of the COUNT function that returns the
count of all the rows in a specified table. COUNT (*) also considers Nulls and
duplicates.
mysql> select count(city) from employee;
+-------------+
| count(city) |
+-------------+
| 6|
+-------------+
Using DISTINCT
Distinct includes only distinct values in the column
mysql> select count(distinct city) from employee;
| count(distinct city) |
--------------------------
3
------------------------
mysql> select count(*) from employee;
count(*)
--------------------------
7
2) SUM:-returns the sum of all the values in the specified column. SUM works on
numeric fields only. Null values are excluded from the result returned.
mysql> select sum(salary ) from employee;
sum(salary)
-----------------------
67000
3) AVG:-returns the average of the non NULL values in a specified [Link]
works only on numeric data types.
mysql> select avg(salary) from employee;
avg(salary)
-----------------------
11166.6667
---------------
4) MIN:-Return the lowest value (minimum) in a set of non-NULL values.
mysql> select min(salary) from employee;
---------------
min(salary)
---------------
8000
5) MAX :Return the highest value (maximum) in a set of non-NULL values.
mysql> select max(salary) from employee;
---------------
max(salary)
---------------
15000

ORDER BY Clause
ORDER BY clause is used to display data in an ordered (arranged) form with respect to a
specified column. By default, ORDER BY displays records in ascending order of the specified
column’s values. To display the records in descending order, the DESC (means descending)
keyword needs to be written with that column.

SELECT *FROM employee WHERE salary BETWEEN 8000 and 12000 order by salary;

SELECT *FROM employee WHERE salary BETWEEN 8000 and 12000 order by salary ASC;

SELECT *FROM employee WHERE salary BETWEEN 8000 and 12000 order by salary DESC;

`
 GROUP BY
The GROUP BY statement groups rows that have the same values into summary
rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT, MAX,
MIN, SUM, AVG) to group the result-set by one or more columns.

GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

mysql> SELECT CITY,SUM(SALARY) FROM employee GROUP BY CITY

ORDER BY SUM(SALARY);

HAVING
The HAVING clause was added to SQL because the WHERE keyword could not
be used with aggregate functions.

HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
mysql> SELECT CITY,SUM(SALARY)
FROM employee
GROUP BY CITY
HAVING SUM(SALARY) > 20000
ORDER BY SUM(SALARY);
-

MySQL JOINS
MySQL JOINS are used with SELECT statement. It is used to retrieve data from
multiple tables. It is performed whenever you need to fetch records from two or
more tables.

NATURAL JOIN: Used when the tables have one or more column
with same name and datatype .

SELECT COL1,COL2,… FROM table1 NATURAL JOIN table2;

 The associated tables have one or more pairs of identically named columns.

 The columns must be the same data type.

 Don’t use ON clause in a NATURAL JOIN.

Equi join:
SELECT column_list
FROM table1, table2
WHERE table1.column_name = table2.column_name;

SELECT column_list
FROM table1 JOIN table2
ON table1.column_name = table2.column_name;

If department id is having same name in both tables we can use below:

Without aliasing(renaming) table names

mysql> select * from employee ,department where

employee.dept_id=department.dept_id;

mysql> select * from employee join department on

employee .dept_id=department.dept_id;

With aliasing(renaming) table names

mysql> select * from employee e , department d where e.dept_id=d.dept_id;

mysql> select * from employee e join department d on e.dept_id=d.dept_id;

If department id is having different name in both tables we can use below:

mysql> select * from employee ,department where dept_id=did;

mysql> select * from employee join department on dept_id=did;

Note: join is same inner join .Even if we replace join with inner join in above
statement we will get same output.

mysql> select * from employee inner join depart on dept_id=did;

CARTESIAN PRODUCT
The CROSS JOIN keyword returns all records from both tables (table1 and table2).
Syntax
SELECT COL1,COL2,… FROM table1 CROSS JOIN table2;
SELECT COL1,COL2,… FROM table1,table2;
If table 1 has ‘m’ rows and table2 has ‘n’ rows ,total number
of rows of cross join will be ‘m X n’

Need for Database:

Before databases were introduced the data was stored in files. People used to
write specific application programs to manipulate data. Databases have following
advantages over files:

[Link] huge Volume of Data:

Database management systems have specific programs to store and retrieve

huge data very fast when compared to files.

[Link] Integrity:

refers to the accuracy and consistency of data.

For example, a user could accidentally try to enter a phone number into a date field. If the system
enforces data integrity, it will prevent the user from making these mistakes.

3. Security of data

Databases have various methods to ensure security of data. There are user logins
required before accessing a database and various access specifiers. These allow
only authorised users to access the database.

4. ELIMINATES Data Redundancy:

The duplication of data can be possible using text files or spreadsheets. Database
removes this problem by using keys.
 Interface of Python with an SQL database
Connecting SQL with Python

MySQL Connector/Python is a driver that enables Python programs to access

MySQL databases.

INSTALLATION OF MYSQL-CONNECTOR
Goto highlighted path in command prompt and type as below:

C:\Users\dwht\AppData\Local\Programs\Python\Python36-32>

pip install mysql-connector

VERIFYING THE INSTALLATION:

GOTO PYTHON SHELL AND TYPE

import [Link]

CONNECTING FROM PYTHON TO MYSQL:

import [Link] as myc

con = [Link](host="localhost", user="root", passwd="1234")

[Link]()

connect() takes below parameters and returns connection object:

Argument Name Default Description

user (username) The user name used to authenticate with the MySQL server.

password (passwd) The password to authenticate the user with the MySQL server.

database (db) optional The database name to use when connecting with the MySQL server.

[Link]

host Or localhost The host name or IP address of the MySQL server.

close() : used to close the connection to mysql

import [Link] as myc

con = [Link](host="localhost", user="root",

password="1234")

print(con.is_connected()) #prints true if connected to Mysql server.

[Link]()

CURSOR OBJECT:

SQL QUERIES are executed through cursor object’s execute method.

To get cusor object we have to call cursor function from connection object.

mycursor = [Link]()

[Link]("CREATE DATABASE student")

Creating a Database

import [Link] as myc

con = [Link](host="localhost", user="root", passwd="1234")

mycursor = [Link]()

[Link]("DROP DATABASE IF EXISTS student")

[Link]("CREATE DATABASE student")

[Link]("USE student")

[Link]()
A COMMIT means that the changes made in the current transaction are made permanent and become
visible to other sessions.

A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction.
Introduction to Database Programming in Python
Interacting with a database is an important feature in many programming languages including python. In comparision to storing data in flat
files, its much easier to store, retrive and modify data in a database. We are going to learn the following concepts and programming skills.

Creating a Database connection

Creating a Database
Create a Table
Inserting into the table
Retrieving data from Table
Updating Records in a table
Deleting Data in a table

Before you can start working with MySQL database, you need to start the database server. I am using WAMP server for this tutorial. You
also need to install the latest mysql-connetor for this purpose. use pip install mysql-connector in the command window to download
and install it.

Connecting to the database server

In [23]: import [Link]

con = [Link](host="localhost", user="root", passwd="")
mycursor = [Link]()
[Link]()

Creating a Database

In [3]: import [Link]

con = [Link](host="localhost", user="root", passwd="")
mycursor = [Link]()
[Link]("DROP DATABASE IF EXISTS student")
[Link]("CREATE DATABASE student")
[Link]("USE student")

Creating the Table

In [11]: [Link]("DROP TABLE IF EXISTS studentinfo")

[Link]("CREATE TABLE studentinfo (name VARCHAR(30), age INT(3), gender CHAR(1))")

Inserting data into the table

In [12]: sql = """INSERT INTO studentinfo(name, age, gender)

VALUES('Ashok',17,'M')"""
[Link](sql)
[Link]()

Inserting multiple rows simultaniously

Here we are going to use the executemany() function that accept two parameters as shpown below.

In [15]: sql = """INSERT INTO studentinfo(name, age, gender)

VALUES(%s, %s, %s)"""
rows = [('Amit', 18,'M'),('Sudha', 17, 'F')]
[Link](sql, rows)
[Link]()
[Link]()
Reading from Database Table
fetchone() − It fetches the next row of a query result set. A result set is an object that is returned when a cursor object is used to
query a table.
fetchall() − It fetches all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves the
remaining rows from the result set.

In [17]: import [Link]

con = [Link](host="localhost", user="root", passwd="", database="student")
mycursor = [Link]()

sql = "SELECT * FROM studentinfo"

[Link](sql)

result = [Link]()

for row in result:

name = row[0]
age = row[1]
gender = row[2]
print("Name=%s, Age=%d, Gender=%c" % (name,age,gender))
[Link]()

Name=Ashok, Age=17, Gender=M

Name=Amit, Age=18, Gender=M
Name=Sudha, Age=17, Gender=F
Name=Amit, Age=18, Gender=M
Name=Sudha, Age=17, Gender=F

Updating records in a Table

In [1]: import [Link]

con = [Link](host="localhost", user="root", passwd="", database="student")
mycursor = [Link]()

sql = "UPDATE studentinfo SET age=age-3 WHERE age='%d'" % (21)

[Link](sql)

sql = "SELECT * FROM studentinfo"

[Link](sql)

result = [Link]()

for row in result:

name = row[0]
age = row[1]
gender = row[2]
print("Name=%s, Age=%d, Gender=%c" % (name,age,gender))
[Link]()

Name=Ashok, Age=17, Gender=M

Name=Amit, Age=18, Gender=M
Name=Sudha, Age=17, Gender=F
Name=Amit, Age=18, Gender=M
Name=Sudha, Age=17, Gender=F

Deleting Records from a Table

In [ ]: import [Link]
con = [Link](host="localhost", user="root", passwd="", database="student")
mycursor = [Link]()

sql = "DELETE FROM studentinfo WHERE name='%s'" % ('Ashok')

[Link](sql)

sql = "SELECT * FROM studentinfo"

[Link](sql)

result = [Link]()

for row in result:

name = row[0]
age = row[1]
gender = row[2]
print("Name=%s, Age=%d, Gender=%c" % (name,age,gender))
[Link]()
rowcount :
It gives the number of rows returned for SELECT statements, or the number of rows
affected by DML statements such as INSERT or UPDATE or DELETE

use of %s format specifier or format() to perform queries

Using %s Format Specifier
Example:
Program
name = "John"
age = 25
# Using %s format specifier
output = "My name is %s and I am %s years old." % (name, age)
# In above first %s is replaced by name and second %s by age.
print(output)
Output
My name is John and I am 25 years old.
Using format Method
Example 1:
name = "John"
age = 25
# Using format() method
output = "My name is {} and I am {} years old.".format(name,
age)
print(output)
Output
My name is John and I am 25 years old.
Example 2:
output = "My name is {0} and I like {1}. {0} loves
Python!".format("Alice", "coding")
print(output)
Output
My name is Alice and I like coding. Alice loves Python!

Consider employee table:

Dynamic select
import [Link] as myc
con=[Link](host='localhost',user='root',password='1234')
cur=[Link]()
eid=int(input('Enter employee id to see his details:'))
[Link]('use hybridcs')
[Link]('select * from employee where id=%s' % eid)
data=[Link]()
print([Link])
for row in data:
print('Employee id:',row[0])
print('Employee Name:',row[1])
print('City:',row[2])
print('Salary:',row[3])
[Link]()
[Link]()

Dynamic delete

import [Link] as myc

con=[Link](host='localhost',user='root',password='1234')
cur=[Link]()
eid=int(input('Enter employee id:'))
[Link]('use hybridcs')
s=""" delete from employee where id=%s"""%(eid)
[Link](s)
[Link]()
[Link]()
Dynamic insert
import [Link] as myc
con=[Link](host='localhost',user='root',password='1234')
cur=[Link]()
eid=int(input('Enter employee id:'))
name=input('Enter Employee Name:')
city=input('Enter City Name:')
sal=int(input('Enter employee salary:'))
[Link]('use hybridcs')
s=""" insert into employee values(%s,'%s','%s',%s)
"""%(eid,name,city,sal)
[Link](s)
[Link]()
[Link]()
Dynamic Update
import [Link] as myc
con=[Link](host='localhost',user='root',password='1234')
cur=[Link]()
eid=int(input('Enter employee id:'))
sal=int(input('Enter changed salary of employee:'))
[Link]('use hybridcs')
s=""" update employee set salary=%s where id=%s """%(sal,eid)
[Link](s)
[Link]()
[Link]()