Python Data Access 简明教程

Python MySQL - Insert Data

可以使用 INSERT INTO 语句向现有的 MySQL 表中添加新行。在这里，你需要指定表名、列名和值（与列名顺序相同）。

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).

Syntax

以下是 MySQL 的 INSERT INTO 语句的语法。

Following is the syntax of the INSERT INTO statement of MySQL.

INSERT INTO TABLE_NAME (column1, column2,column3,...columnN)
VALUES (value1, value2, value3,...valueN);

Example

以下查询将记录插入名为 EMPLOYEE 的表中。

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
);

可以使用 SELECT 语句来验证插入操作之后的表记录，如下所示：

You can verify the records of the table after insert operation using the SELECT statement as −

mysql> select * from Employee;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Mac        | Mohan     | 20   | M    | 2000   |
+------------+-----------+------+------+--------+
1 row in set (0.00 sec)

指定列名并不是总是必须的，如果按照表列的相同顺序传递记录值，则可以不使用列名执行 SELECT 语句，如下所示：

It is not mandatory to specify the names of the columns always, if you pass values of a record in the same order of the columns of the table you can execute the SELECT statement without the column names as follows −

INSERT INTO EMPLOYEE VALUES ('Mac', 'Mohan', 20, 'M', 2000);

Inserting data in MySQL table using python

方法 execute() （在光标对象上调用）接受查询作为参数并执行给定的查询。要插入数据，需要将 MySQL INSERT 语句作为参数传递给它。

The execute() method (invoked on the cursor object) accepts a query as 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)""")

使用 Python 向 MySQL 中的表插入数据：

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

以下示例执行 SQL INSERT 语句以在 EMPLOYEE 表中插入记录 -

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='password', host='127.0.0.1', 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()

Inserting values dynamically

您还可以使用 MySQL INSERT 查询中的值代替 “%s”，并将值作为列表传递给它们，如下所示 -

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

以下示例动态地在 Employee 表中插入记录。

Following example inserts a record into the Employee table dynamically.

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 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()

Output

Data inserted