Using SQLs in Python – Tutorial for Beginners

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-devFor Fedora, use the following command -$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gccFor 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/pythonimport MySQLdb

If 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 MySQLdbImportError: 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_database

Example:

import mysql.connector#establishing the connectionconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “pwd”, database = “mydb”)#Creating a cursor object using the cursor() methodcursor = conn.cursor()#Doping database MYDATABASE if already exists.cursor.execute(“DROP database IF EXISTS MyDatabase”)#Preparing query to create a databasesql = “CREATE database MYDATABASE”;#Creating a databasecursor.execute(sql)#Retrieving the list of databasesprint(“List of databases: “)cursor.execute(“SHOW DATABASES”)print(cursor.fetchall())#Closing the connectionconn.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 connectionconn = mysql.connector.connect(host = “localhost”, user = “root”,passwd = “pwd”, database = “mydb”))#Creating a cursor object using the cursor() methodcursor = conn.cursor()#Dropping EMPLOYEE table if already exists.cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”)#Creating table as per requirementsql =”’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 connectionconn.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 mysqldb = 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 databasecursor.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 mysqldb = mysql.connect( host = “localhost”, user = “root”, passwd = “pwd”, database = “mydb”) cursor = db.cursor()## dropping the ‘id’ columncursor.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 mysqldb = 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 tablecursor.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 connectionconn = mysql.connector.connect( user=’root’, password=’pwd’, host=’localhost’, database=’mydb’)#Creating a cursor object using the cursor() methodcursor = 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 connectionconn.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 connectionconn = mysql.connector.connect( user=’root’, password=’pwd’, host=’localhost’, database=’mydb’)#Creating a cursor object using the cursor() methodcursor = 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 connectionconn.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 connectionconn = mysql.connector.connect( user=’root’, password=’pwd’, host=’localhost’, database=’mydb’)#Creating a cursor object using the cursor() methodcursor = conn.cursor()#Retrieving single rowsql = ”’SELECT * from EMPLOYEE”’#Executing the querycursor.execute(sql)#Fetching 1st row from the tableresult = cursor.fetchone();print(result)#Fetching 1st row from the tableresult = cursor.fetchall();print(result)#Closing the connectionconn.close()

The following example retrieves the first two rows of the EMPLOYEE table using the fetchmany() method.

import mysql.connector#establishing the connectionconn = mysql.connector.connect( user=’root’, password=’pwd’, host=’localhost’, database=’mydb’)#Creating a cursor object using the cursor() methodcursor = conn.cursor()#Retrieving single rowsql = ”’SELECT * from EMPLOYEE”’#Executing the querycursor.execute(sql)#Fetching 1st row from the tableresult = cursor.fetchmany(size =2);print(result)#Closing the connectionconn.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, columnNFROM table_nameWHERE [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 connectionconn = mysql.connector.connect( user=’root’, password=’pwd’, host=’localhost’, database=’mydb’)#Creating a cursor object using the cursor() methodcursor = 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 tableinsert_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 clausecursor.execute(“SELECT * from EMPLOYEE WHERE AGE <23”)print(cursor.fetchall())#Closing the connectionconn.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 connectionconn = mysql.connector.connect( user=’root’, password=’pwd’, host=’localhost’, database=’mydb’)#Creating a cursor object using the cursor() methodcursor = 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 tableinsert_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 clausecursor.execute(“SELECT * from EMPLOYEE ORDER BY AGE”)print(cursor.fetchall())#Closing the connectionconn.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 connectionconn = mysql.connector.connect( user=’root’, password=’pwd’, host=’localhost’, database=’mydb’)#Creating a cursor object using the cursor() methodcursor = conn.cursor()#Retrieving specific records using the ORDERBY clausecursor.execute(“SELECT * from EMPLOYEE ORDER BY INCOME DESC”)print(cursor.fetchall())#Closing the connectionconn.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 connectionconn = mysql.connector.connect( user=’root’, password=’pwd’, host=’localhost’, database=’mydb’)#Creating a cursor object using the cursor() methodcursor = conn.cursor()#Retrieving single rowprint(“Contents of the table: “)cursor.execute(“SELECT * from EMPLOYEE”)print(cursor.fetchall())#Preparing the query to delete recordssql = “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 dataprint(“Contents of the table after delete operation “)cursor.execute(“SELECT * from EMPLOYEE”)print(cursor.fetchall())#Closing the connectionconn.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_nameSET column1 = value1, column2 = value2…., columnN = valueNWHERE [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 connectionconn = mysql.connector.connect( user=’root’, password=’pwd’, host=’localhost’, database=’mydb’)#Creating a cursor object using the cursor() methodcursor = conn.cursor()#Preparing the query to update the recordssql = ”’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 datasql = ”’SELECT * from EMPLOYEE”’#Executing the querycursor.execute(sql)#Displaying the resultprint(cursor.fetchall())#Closing the connectionconn.close()

Output

[(‘Krishna’, ‘Sharma’, 22, ‘M’, 2000.0), (‘Raj’, ‘Kandukuri’, 23, ‘M’, 7000.0), (‘Ramya’, ‘Ramapriya’, 26, ‘F’, 5000.0)]