添加数据
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)
</span><span class="com">#提交事务</span><span class="pln">
myconn</span><span class="pun">.</span><span class="pln">commit</span><span class="pun">()</span><span class="pln">
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)
</span><span class="com">#提交事务</span><span class="pln">
myconn</span><span class="pun">.</span><span class="pln">commit</span><span class="pun">()</span><span class="pln">
</span><span class="kwd">print</span><span class="pun">(</span><span class="pln">cur</span><span class="pun">.</span><span class="pln">rowcount</span><span class="pun">,</span><span class="str">"records inserted!"</span><span class="pun">)</span><span class="pln">
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)
</span><span class="com">#提交事务</span><span class="pln">
myconn</span><span class="pun">.</span><span class="pln">commit</span><span class="pun">()</span><span class="pln">
</span><span class="com">#获取 rowid</span><span class="pln">
</span><span class="kwd">print</span><span class="pun">(</span><span class="pln">cur</span><span class="pun">.</span><span class="pln">rowcount</span><span class="pun">,</span><span class="str">"record inserted! id:"</span><span class="pun">,</span><span class="pln">cur</span><span class="pun">.</span><span class="pln">lastrowid</span><span class="pun">)</span><span class="pln">
except:
myconn.rollback()
myconn.close()
输出:
1 record inserted! Id: 0
