- Python Data Access - Discussion
- Python Data Access - Useful Resources
- Python Data Access - Quick Guide
- Python MongoDB - Limit
- Python MongoDB - Update
- Python MongoDB - Drop Collection
- Python MongoDB - Delete Document
- Python MongoDB - Sort
- Python MongoDB - Query
- Python MongoDB - Find
- Python MongoDB - Insert Document
- Python MongoDB - Create Collection
- Python MongoDB - Create Database
- Python MongoDB - Introduction
- Python SQLite - Cursor Object
- Python SQLite - Join
- Python SQLite - Limit
- Python SQLite - Drop Table
- Python SQLite - Delete Data
- Python SQLite - Update Table
- Python SQLite - Order By
- Python SQLite - Where Clause
- Python SQLite - Select Data
- Python SQLite - Insert Data
- Python SQLite - Create Table
- Python SQLite - Establishing Connection
- Python SQLite - Introduction
- Python PostgreSQL - Cursor Object
- Python PostgreSQL - Join
- Python PostgreSQL - Limit
- Python PostgreSQL - Drop Table
- Python PostgreSQL - Delete Data
- Python PostgreSQL - Update Table
- Python PostgreSQL - Order By
- Python PostgreSQL - Where Clause
- Python PostgreSQL - Select Data
- Python PostgreSQL - Insert Data
- Python PostgreSQL - Create Table
- Python PostgreSQL - Create Database
- Python PostgreSQL - Database Connection
- Python PostgreSQL - Introduction
- Python MySQL - Cursor Object
- Python MySQL - Join
- Python MySQL - Limit
- Python MySQL - Drop Table
- Python MySQL - Delete Data
- Python MySQL - Update Table
- Python MySQL - Order By
- Python MySQL - Where Clause
- Python MySQL - Select Data
- Python MySQL - Insert Data
- Python MySQL - Create Table
- Python MySQL - Create Database
- Python MySQL - Database Connection
- Python MySQL - Introduction
- Python Data Access - Home
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
Python MySQL - Update 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.
Syntax
Following is the syntax of the UPDATE statement in MySQL −
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.
Example
Assume we have created a table in MySQL with name EMPLOYEES as −
mysql> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); Query OK, 0 rows affected (0.36 sec)
And if we have inserted 4 records in to it using INSERT statements as −
mysql> INSERT INTO EMPLOYEE VALUES ( Krishna , Sharma , 19, M , 2000), ( Raj , Kandukuri , 20, M , 7000), ( Ramya , Ramapriya , 25, F , 5000), ( Mac , Mohan , 26, M , 2000);
Following MySQL statement increases the age of all male employees by one year −
mysql> UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = M ; Query OK, 3 rows affected (0.06 sec) Rows matched: 3 Changed: 3 Warnings: 0
If you retrieve the contents of the table, you can see the updated values as −
mysql> 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 | +------------+-----------+------+------+--------+ 4 rows in set (0.00 sec)
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.
Example
The following example increases age of all the males by one year.
import mysql.connector #estabpshing the connection conn = mysql.connector.connect( user= root , password= password , host= 127.0.0.1 , 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) ]Advertisements