Python MySQL插入操作如何实现？

本文概述

• 向表添加记录
• 插入多行
• 行编号

向表添加记录

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!

行编号

在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