Python Data Access 简明教程
Python MySQL - Update Table
任何数据库上的 UPDATE 操作都会更新一个或多个记录，这些记录已在数据库中可用。您可以使用 UPDATE 语句更新 MySQL 中现有记录的值。要更新特定行，您需要使用 WHERE 子句。
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
以下是 MySQL 中 UPDATE 语句的语法：
Following is the syntax of the UPDATE statement in MySQL −
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];您可以使用 AND 或 OR 运算符组合 N 个条件。
You can combine N number of conditions using the AND or the OR operators.
Example
假设我们在 MySQL 中使用 EMPLOYEES 作为名称创建了一个表格，如下所示：
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)如果使用 INSERT 语句向其中插入了 4 条记录，如下所示：
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);以下 MySQL 语句将所有男性员工的年龄增加一岁：
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
若要使用 Python 更新 MySQL 表中的记录：
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
以下示例将所有男性的年龄增加 1 岁。
The following example increases age of all the males by one year.
import mysql.connector
#establishing 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()