INSERT INTO 语句用于将记录添加到表中。在python中,无涯教程可以使用格式说明符(%s)代替值。
在游标的execute()方法中以元组形式提供实际值。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)" #行值以元组的形式提供 val = ("John", 110, 25000.00, 201, "Newyork") try: #将值插入表中 cur.execute(sql,val) #提交事务 myconn.commit() except: myconn.rollback() print(cur.rowcount,"record inserted!") myconn.close()
输出:
1 record inserted!
无涯教程还可以使用python脚本一次插入多行。提及多行作为各种元组的列表。
列表的每个元素都被视为一个特定的行,而元组的每个元素都被视为一个特定的列值(属性)。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 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: #将值插入表中 cur.executemany(sql,val) #提交事务 myconn.commit() print(cur.rowcount,"records inserted!") except: myconn.rollback() myconn.close()
输出:
3 records inserted!
在SQL中,特定行由插入ID(称为行ID)表示。无涯教程可以使用游标对象的属性lastrowid来获取最后插入的行ID。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 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: #将值插入表中 cur.execute(sql,val) #提交事务 myconn.commit() #获取 rowid print(cur.rowcount,"record inserted! id:",cur.lastrowid) except: myconn.rollback() myconn.close()
输出:
1 record inserted! Id: 0
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)