This post is also available in: العربية (Arabic)
Python Database API ( Application Program Interface ) is the Database interface for the standard Python. This standard is adhered to by most Python Database interfaces. There are various Database servers supported by Python Database such as MySQL, GadFly, mSQL, PostgreSQL, Microsoft SQL Server 2000, Informix, Interbase, Oracle, Sybase, etc.
What is an SQL?
SQL (pronounced as “sequel”) is a language to operate databases; it includes database creation, deletion, fetching rows, modifying rows, etc. SQL is an ANSI (American National Standards Institute) standard language, but there are many different versions of the SQL language.
SQL is a Structured Query Language, which is a computer language for storing, manipulating, and retrieving data stored in a relational database.
SQL is the standard language for Relational Database Systems. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres, and SQL Server use SQL as their standard database language.
SQL is widely popular because it offers the following advantages −
- Allows users to access data in the relational database management systems.
- Allows users to describe the data.
- Allows users to define the data in a database and manipulate that data.
- Allows embedding within other languages using SQL modules, libraries & pre-compilers.
- Allows users to create and drop databases and tables.
- Allows users to create views, stored procedures, functions in a database.
- Allows users to set permissions on tables, procedures, and views.
Using SQLs in Python
MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and is built on top of the MySQL C API.
To install MySQLdb module, use the following command:
For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python
Before proceeding, you make sure you have included MySQLdb in your code. Just type the following in your Python script and execute it −
#!/usr/bin/python
import MySQLdbIf MySQLdb is not installed in your machine, it generates the following error:
Traceback (most recent call last):
File "test.py", line 3, in <module>
import MySQLdb
ImportError: No module named MySQLdb
Making a Database Connection
There are the following steps to connect a python application to our database.
- Create the connection object
- Create the cursor object
- Execute the query
Creating the Connection
To create a connection between the MySQL database and the python application, the connect() method of mysql.connector module is used.
Pass the database details like HostName, username, and the database password in the method call. The method returns the connection object.
The syntax to use the connect() is given below.
Connection-Object= mysql.connector.connect(host = <host-name> , user = <username> , passwd = <password> )Example:
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "pwd")
#printing the connection object
print(myconn)Creating a Cursor Object
The cursor object can be defined as an abstraction specified in the Python DB-API 2.0. It facilitates us to have multiple separate working environments through the same connection to the database. We can create the cursor object by calling the ‘cursor’ function of the connection object. The cursor object is an important aspect of executing queries to the databases.
The syntax to create the cursor object is given below.
<my_cur> = conn.cursor()Example:
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "pwd", database = "mydb")
#printing the connection object
print(myconn)
#creating the cursor object
cur = myconn.cursor()
print(cur)Executing the Query
This process involves executing the SQL query to perform the desired operation.
Creating a Database
A database is a collection of tables which in turn store the related records. You can create a database in MYSQL using the CREATE DATABASE query.
CREATE DATABASE name_of_the_databaseExample:
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(host = "localhost", user = "root",passwd = "pwd", database = "mydb")
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Doping database MYDATABASE if already exists.
cursor.execute("DROP database IF EXISTS MyDatabase")
#Preparing query to create a database
sql = "CREATE database MYDATABASE";
#Creating a database
cursor.execute(sql)
#Retrieving the list of databases
print("List of databases: ")
cursor.execute("SHOW DATABASES")
print(cursor.fetchall())
#Closing the connection
conn.close()Output:
List of databases:
[('information_schema',), ('dbbug61332',), ('details',), ('exampledatabase',), ('mydatabase',), ('mydb',), ('mysql',), ('performance_schema',)]Creating a Table
The CREATE TABLE statement is used to create tables in MYSQL database. Here, you need to specify the name of the table and, definition (name and datatype) of each column.
Syntax:
Following is the syntax to create a table in MySQL −
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
Example:
The following query creates a table named EMPLOYEE in MySQL with five columns namely, FIRST_NAME, LAST_NAME, AGE, SEX and, INCOME.
CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
);
The DESC statement gives you the description of the specified table. Using this you can verify if the table has been created or not as shown below −
Desc Employee;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| FIRST_NAME | char(20) | NO | | NULL | |
| LAST_NAME | char(20) | YES | | NULL | |
| AGE | int(11) | YES | | NULL | |
| SEX | char(1) | YES | | NULL | |
| INCOME | float | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
Creating a Table in MySQL Using Python
The method named execute() (invoked on the cursor object) accepts two variables −
- A String value representing the query to be executed.
- An optional args parameter which can be a tuple or, list or, dictionary, representing the parameters of the query (values of the placeholders).
It returns an integer value representing the number of rows affected by the query.
Once a database connection is established, you can create tables by passing the CREATE TABLE query to the execute() method.
In short, to create a table using python 7minus;
- Import mysql.connector package.
- Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
- Create a cursor object by invoking the cursor() method on the connection object created above.
- Then, execute the CREATE TABLE statement by passing it as a parameter to the execute() method.
Example:
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(host = "localhost", user = "root",passwd = "pwd", database = "mydb")
)
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Dropping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
#Creating table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
)'''
cursor.execute(sql)
#Closing the connection
conn.close()Primary Key
It is a unique value in the table. It helps to find each row uniquely in the table. To create a Primary Key, we use the PRIMARY KEY statement while creating the table.
The statement INT AUTO_INCREMENT PRIMARY KEY is used to identify each row uniquely with a number starting from 1.
Example:
import mysql.connector as mysql
db = mysql.connect(
host = "localhost",
user = "root",
passwd = "pwd",
database = "mydb"
)
cursor = db.cursor()
## first we have to 'drop' the table which has already created to create it again with the 'PRIMARY KEY'
## 'DROP TABLE table_name' statement will drop the table from a database
cursor.execute("DROP TABLE users")
## creating the 'users' table again with the 'PRIMARY KEY'
cursor.execute("CREATE TABLE users (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255), last_name VARCHAR(255))")Dropping the Primary Key
We use ALTER TABLE table_name DROP column_name statement to drop the column with Primary Key.
import mysql.connector as mysql
db = mysql.connect(
host = "localhost",
user = "root",
passwd = "pwd",
database = "mydb"
)
cursor = db.cursor()
## dropping the 'id' column
cursor.execute("ALTER TABLE users DROP id")
cursor.execute("DESC users")
print(cursor.fetchall())
Adding a Primary Key
Adding Primary Key to the existing table. We use ALTER TABLE table_name ADD PRIMARY KEY(column_name) statement to add a Primary Key to a table.
import mysql.connector as mysql
db = mysql.connect(
host = "localhost",
user = "root",
passwd = "pwd",
database = "mydb"
)
cursor = db.cursor()
## adding 'id' column to the 'users' table
## 'FIRST' keyword in the statement will add a column in the starting of the table
cursor.execute("ALTER TABLE users ADD COLUMN id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST")
cursor.execute("DESC users")
print(cursor.fetchall())
Inserting Data
You can add new rows to an existing table of MySQL using the INSERT INTO statement. In this, you need to specify the name of the table, column names, and values (in the same order as column names).
INSERT INTO TABLE_NAME (column1, column2,column3,...columnN)
VALUES (value1, value2, value3,...valueN);
The following query inserts a record into the table named EMPLOYEE.
INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('
Mac', 'Mohan', 20, 'M', 2000
);
Inserting Data in MySQL Using Python
The execute() method (invoked on the cursor object) accepts a query as a parameter and executes the given query. To insert data, you need to pass the MySQL INSERT statement as a parameter to it.
cursor.execute("""INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)""")
To insert data into a table in MySQL using Python −
- import mysql.connector package.
- Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
- Create a cursor object by invoking the cursor() method on the connection object created above
- Then, execute the INSERT statement by passing it as a parameter to the execute() method.
Example:
The following example executes SQL INSERT statement to insert a record into the EMPLOYEE table −
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='pwd', host='localhost', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
# Preparing SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(
FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
# Executing the SQL command
cursor.execute(sql)
# Commit your changes in the database
conn.commit()
except:
# Rolling back in case of error
conn.rollback()
# Closing the connection
conn.close()You can also use “%s” instead of values in the INSERT query of MySQL and pass values to them as lists as shown below −
cursor.execute("""INSERT INTO EMPLOYEE VALUES ('Mac', 'Mohan', 20, 'M', 2000)""",
('Ramya', 'Ramapriya', 25, 'F', 5000))
Example:
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='pwd', host='localhost', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
# Preparing SQL query to INSERT a record into the database.
insert_stmt = (
"INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)"
"VALUES (%s, %s, %s, %s, %s)"
)
data = ('Ramya', 'Ramapriya', 25, 'F', 5000)
try:
# Executing the SQL command
cursor.execute(insert_stmt, data)
# Commit your changes in the database
conn.commit()
except:
# Rolling back in case of error
conn.rollback()
print("Data inserted")
# Closing the connection
conn.close()Retrieving/Fetching Data from a Table
You can retrieve/fetch data from a table in MySQL using the SELECT query. This query/statement returns contents of the specified table in tabular form and it is called a result-set.
SELECT column1, column2, columnN FROM table_name;Assume we have created a table in MySQL with the name cricketers_data as −
CREATE TABLE cricketers_data(
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Date_Of_Birth date,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255)
);
And if we have inserted 5 records into it using INSERT statements as −
insert into cricketers_data values(
'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into cricketers_data values(
'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into cricketers_data values(
'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into cricketers_data values(
'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into cricketers_data values(
'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');The following query retrieves the FIRST_NAME and Country values from the table.
select FIRST_NAME, Country from cricketers_data;
+------------+-------------+
| FIRST_NAME | Country |
+------------+-------------+
| Shikhar | India |
| Jonathan | SouthAfrica |
| Kumara | Srilanka |
| Virat | India |
| Rohit | India |
+------------+-------------+
You can also retrieve all the values of each record using * instated of the name of the columns as −
SELECT * from cricketers_data;
+------------+------------+---------------+----------------+-------------+
| First_Name | Last_Name | Date_Of_Birth | Place_Of_Birth | Country |
+------------+------------+---------------+----------------+-------------+
| Shikhar | Dhawan | 1981-12-05 | Delhi | India |
| Jonathan | Trott | 1981-04-22 | CapeTown | SouthAfrica |
| Kumara | Sangakkara | 1977-10-27 | Matale | Srilanka |
| Virat | Kohli | 1988-11-05 | Delhi | India |
| Rohit | Sharma | 1987-04-30 | Nagpur | India |
+------------+------------+---------------+----------------+-------------+
Retrieving/Fetching Data from a MySQL Table Using Python
READ Operation on any database means to fetch some useful information from the database. You can fetch data from MYSQL using the fetch() method provided by the mysql-connector-python.
The cursor.MySQLCursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where,
- The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones).
- The fetchone() method fetches the next row in the result of a query and returns it as a tuple.
- The fetchmany() method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row.
Note − A result set is an object that is returned when a cursor object is used to query a table.
rowcount − This is a read-only attribute and returns the number of rows that were affected by an execute() method.
Example:
Following example fetches all the rows of the EMPLOYEE table using the SELECT query and from the obtained result set initially, we are retrieving the first row using the fetchone() method and then fetching the remaining rows using the fetchall() method.
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='pwd', host='localhost', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving single row
sql = '''SELECT * from EMPLOYEE'''
#Executing the query
cursor.execute(sql)
#Fetching 1st row from the table
result = cursor.fetchone();
print(result)
#Fetching 1st row from the table
result = cursor.fetchall();
print(result)
#Closing the connection
conn.close()The following example retrieves the first two rows of the EMPLOYEE table using the fetchmany() method.
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='pwd', host='localhost', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving single row
sql = '''SELECT * from EMPLOYEE'''
#Executing the query
cursor.execute(sql)
#Fetching 1st row from the table
result = cursor.fetchmany(size =2);
print(result)
#Closing the connection
conn.close()The Where Clause
If you want to fetch, delete or, update particular rows of a table in MySQL, you need to use the where clause to specify conditions to filter the rows of the table for the operation.
For example, if you have a SELECT statement with a where clause, only the rows which satisfy the specified condition will be retrieved.
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]
Assume we have created a table in MySQL with the name EMPLOYEES as −
mysql> CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
);
And if we have inserted 4 records into it using INSERT statements as −
INSERT INTO EMPLOYEE VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000);The following MySQL statement retrieves the records of the employees whose income is greater than 4000.
SELECT * FROM EMPLOYEE WHERE INCOME > 4000;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
+------------+-----------+------+------+--------+
| Raj | Kandukuri | 20 | M | 7000 |
| Ramya | Ramapriya | 25 | F | 5000 |
+------------+-----------+------+------+--------+
WHERE Clause Using Python
To fetch specific records from a table using the python program −
- import mysql.connector package.
- Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
- Create a cursor object by invoking the cursor() method on the connection object created above.
- Then, execute the SELECT statement with WHERE clause, by passing it as a parameter to the execute() method.
Example
The following example creates a table named Employee and populates it. Then using the where clause it retrieves the records with an age value less than 23.
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='pwd', host='localhost', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql = '''CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
)'''
cursor.execute(sql)
#Populating the table
insert_stmt = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
VALUES (%s, %s, %s, %s, %s)"
data = [('Krishna', 'Sharma', 19, 'M', 2000), ('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),('Mac', 'Mohan', 26, 'M', 2000)]
cursor.executemany(insert_stmt, data)
conn.commit()
#Retrieving specific records using the where clause
cursor.execute("SELECT * from EMPLOYEE WHERE AGE <23")
print(cursor.fetchall())
#Closing the connection
conn.close()Output:
[('Krishna', 'Sharma', 19, 'M', 2000.0), ('Raj', 'Kandukuri', 20, 'M', 7000.0)]The Order By Clause
While fetching data using the SELECT query, you can sort the results in the desired order (ascending or descending) using the OrderBy clause. By default, this clause sorts results in ascending order, if you need to arrange them in descending order you need to use “DESC” explicitly.
FROM table_name
[WHERE condition]
[ORDER BY column1, column2,.. columnN] [ASC | DESC]; of the ORDER BY clause:
Assume we have created a table in MySQL with the name EMPLOYEES as −
CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
);
And if we have inserted 4 records into it using INSERT statements as −
INSERT INTO EMPLOYEE VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000);
The following statement retrieves the contents of the EMPLOYEE table in ascending order of age.
SELECT * FROM EMPLOYEE ORDER BY AGE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
+------------+-----------+------+------+--------+
| Krishna | Sharma | 19 | M | 2000 |
| Raj | Kandukuri | 20 | M | 7000 |
| Ramya | Ramapriya | 25 | F | 5000 |
| Mac | Mohan | 26 | M | 2000 |
+------------+-----------+------+------+--------+
You can also retrieve data in descending order using DESC as −
SELECT * FROM EMPLOYEE ORDER BY FIRST_NAME, INCOME DESC;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
+------------+-----------+------+------+--------+
| Krishna | Sharma | 19 | M | 2000 |
| Mac | Mohan | 26 | M | 2000 |
| Raj | Kandukuri | 20 | M | 7000 |
| Ramya | Ramapriya | 25 | F | 5000 |
+------------+-----------+------+------+--------+
Order By Clause Using Python
To retrieve the contents of a table in a specific order, invoke the execute() method on the cursor object and, pass the SELECT statement along with the ORDER BY clause, as a parameter to it.
In the following example, we are creating a table with name and Employee, populating it, and retrieving its records back in the (ascending) order of their age, using the ORDER BY clause.
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='pwd', host='localhost', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql = '''CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
)'''
cursor.execute(sql)
#Populating the table
insert_stmt = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
VALUES (%s, %s, %s, %s, %s)"
data = [('Krishna', 'Sharma', 26, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 29, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000)]
cursor.executemany(insert_stmt, data)
conn.commit()
#Retrieving specific records using the ORDER BY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY AGE")
print(cursor.fetchall())
#Closing the connection
conn.close()Output
[('Raj', 'Kandukuri', 20, 'M', 7000.0),
('Krishna', 'Sharma', 26, 'M', 2000.0),
('Mac', 'Mohan', 26, 'M', 2000.0),
('Ramya', 'Ramapriya', 29, 'F', 5000.0)
]
In the same way, you can retrieve data from a table in descending order using the ORDER BY clause.
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='pwd', host='localhost', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving specific records using the ORDERBY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY INCOME DESC")
print(cursor.fetchall())
#Closing the connection
conn.close()Output:
[('Raj', 'Kandukuri', 20, 'M', 7000.0),
('Ramya', 'Ramapriya', 29, 'F', 5000.0),
('Krishna', 'Sharma', 26, 'M', 2000.0),
('Mac', 'Mohan', 26, 'M', 2000.0)
]
Removing a Record from the Table
To remove/delete records from a MySQL table, you need to use the DELETE FROM statement. To remove specific records, you need to use the WHERE clause along with it.
DELETE FROM table_name [WHERE Clause]Assume we have created a table in MySQL with the name EMPLOYEES as −
CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
);
And if we have inserted 4 records into it using INSERT statements as −
INSERT INTO EMPLOYEE VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000);
The following MySQL statement deletes the record of the employee with FIRST_NAME ”Mac”.
DELETE FROM EMPLOYEE WHERE FIRST_NAME = 'Mac';If you retrieve the contents of the table, you can see only 3 records since we have deleted one.
select * from EMPLOYEE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
+------------+-----------+------+------+--------+
| Krishna | Sharma | 20 | M | 2000 |
| Raj | Kandukuri | 21 | M | 7000 |
| Ramya | Ramapriya | 25 | F | 5000 |
+------------+-----------+------+------+--------+
If you execute the DELETE statement without the WHERE clause all the records from the specified table will be deleted.
DELETE FROM EMPLOYEE;If you retrieve the contents of the table, you will get an empty set as shown below −
select * from EMPLOYEE;Removing Data from a MySQL Table Using Python
DELETE operation is required when you want to delete some records from your database.
To delete the records in a table −
- import mysql.connector package.
- Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
- Create a cursor object by invoking the cursor() method on the connection object created above.
- Then, execute the DELETE statement by passing it as a parameter to the execute() method.
Following program deletes all the records from EMPLOYEE whose AGE is more than 20 −
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='pwd', host='localhost', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving single row
print("Contents of the table: ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())
#Preparing the query to delete records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (25)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
conn.commit()
except:
# Roll back in case there is any error
conn.rollback()
#Retrieving data
print("Contents of the table after delete operation ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())
#Closing the connection
conn.close()Output:
Contents of the table:
[('Krishna', 'Sharma', 22, 'M', 2000.0),
('Raj', 'Kandukuri', 23, 'M', 7000.0),
('Ramya', 'Ramapriya', 26, 'F', 5000.0),
('Mac', 'Mohan', 20, 'M', 2000.0),
('Ramya', 'Rama priya', 27, 'F', 9000.0)]
Contents of the table after delete operation:
[('Krishna', 'Sharma', 22, 'M', 2000.0),
('Raj', 'Kandukuri', 23, 'M', 7000.0),
('Mac', 'Mohan', 20, 'M', 2000.0)]
Updating/Modifying a Table
UPDATE Operation on any database updates one or more records, which are already available in the database. You can update the values of existing records in MySQL using the UPDATE statement. To update specific rows, you need to use the WHERE clause along with it.
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
You can combine N number of conditions using the AND or the OR operators.
Assume we have created a table in MySQL with the name EMPLOYEES as −
CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
);
And if we have inserted 4 records into it using INSERT statements as −
INSERT INTO EMPLOYEE VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000);
The following MySQL statement increases the age of all male employees by one year −
UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M';If you retrieve the contents of the table, you can see the updated values as −
select * from EMPLOYEE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
+------------+-----------+------+------+--------+
| Krishna | Sharma | 20 | M | 2000 |
| Raj | Kandukuri | 21 | M | 7000 |
| Ramya | Ramapriya | 25 | F | 5000 |
| Mac | Mohan | 27 | M | 2000 |
+------------+-----------+------+------+--------+
Updating the Contents of a Table Using Python
To update the records in a table in MySQL using Python −
- import mysql.connector package.
- Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
- Create a cursor object by invoking the cursor() method on the connection object created above.
- Then, execute the UPDATE statement by passing it as a parameter to the execute() method.
The following example increases the age of all the males by one year.
import mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='pwd', host='localhost', database='mydb')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Preparing the query to update the records
sql = '''UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M' '''
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
conn.commit()
except:
# Rollback in case there is any error
conn.rollback()
#Retrieving data
sql = '''SELECT * from EMPLOYEE'''
#Executing the query
cursor.execute(sql)
#Displaying the result
print(cursor.fetchall())
#Closing the connection
conn.close()Output
[('Krishna', 'Sharma', 22, 'M', 2000.0),
('Raj', 'Kandukuri', 23, 'M', 7000.0),
('Ramya', 'Ramapriya', 26, 'F', 5000.0)
]
