本文概述
- 向表添加记录
- 插入多行
- 行编号
【Python MySQL插入操作如何实现()】我们在游标的execute()方法中以元组的形式提供实际值。
考虑以下示例。
例子
import mysql.connector#Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "google", database = "PythonDB")#creating the cursor objectcur = 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 tablecur.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 objectcur = 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 tablecur.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 objectcur = 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 tablecur.execute(sql, val)#commit the transaction myconn.commit()#getting rowidprint(cur.rowcount, "record inserted! id:", cur.lastrowid)except:myconn.rollback()myconn.close()
输出
1 record inserted! Id: 0
推荐阅读
- Python MySQL如何进行事务操作()
- Python-MySQL环境设置详解
- Python MySQL如何创建表()
- Python MySQL数据库连接如何操作()
- Python如何创建新数据库()
- Python如何使用继承()
- Python魔术方法介绍
- python|用Python制作可视化报表,这也太快了
- 可视化|50套可视化报表模板直接用,做报告不用愁了!快收藏