Python MySQL插入操作|极客笔记

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 MySQL插入操作

插入多行

我们还可以使用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!

Python MySQL插入操作

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

输出：