Python教程-插入操作

向表格添加记录

INSERT INTO 语句用于向表格添加记录。在 Python 中，我们可以在值的位置使用格式说明符（%s）。

我们通过游标的 execute() 方法以元组的形式提供实际值。

考虑以下示例。

示例

import mysql.connector  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
#creating the cursor object  
cur = myconn.cursor()  
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"  
  
#The row values are provided in the form of tuple   
val = ("John", 110, 25000.00, 201, "Newyork")  
  
try:  
    #inserting the values into the table  
    cur.execute(sql,val)  
  
    #commit the transaction   
    myconn.commit()  
      
except:  
    myconn.rollback()  
  
print(cur.rowcount,"record inserted!")  
myconn.close()  

输出:

1 record inserted!

插入多行

我们也可以使用 Python 脚本一次插入多行。多行被列为各种元组的列表。

列表的每个元素被视为一个特定的行，而元组的每个元素被视为一个特定的列值（属性）。

考虑以下示例。

示例

import mysql.connector  
      
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"  
val = [("John", 102, 25000.00, 201, "Newyork"),("David",103,25000.00,202,"Port of spain"),("Nick",104,90000.00,201,"Newyork")]  
      
try:  
    #inserting the values into the table  
    cur.executemany(sql,val)  
  
    #commit the transaction   
    myconn.commit()  
    print(cur.rowcount,"records inserted!")  
      
except:  
    myconn.rollback()  
  
myconn.close()  

输出:

3 records inserted!

行 ID

在 SQL 中，特定行由称为行 ID 的插入 ID 表示。我们可以使用游标对象的 lastrowid 属性获取最后插入的行 ID。

考虑以下示例。

示例

import mysql.connector  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
#creating the cursor object  
cur = myconn.cursor()  
      
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"  
      
val = ("Mike",105,28000,202,"Guyana")  
      
try:  
    #inserting the values into the table  
    cur.execute(sql,val)  
  
    #commit the transaction   
    myconn.commit()  
      
    #getting rowid  
    print(cur.rowcount,"record inserted! id:",cur.lastrowid)  
  
except:  
    myconn.rollback()  
  
myconn.close()  

输出:

1 record inserted! Id: 0