目录
- 起步
- 增
- 增一条
- 增多条
- 查看自增id
- 提交
- 删
- 改
- 查
- 示例代码
起步
#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""pymysql增删改"""
import pymysql
conn = pymysql.connect(
user='root',
password='',
host='localhost',
port=3306,
charset='utf8mb4',
database='hardy2_db',
)
cursor = conn.cursor(cursor=None)
增
增一条
# 增一条:
# 直接写值(注意要加引号):
# sql = "insert pymysql_tb values(null, 21, 'hardy9sap');"
# args传值:
# ==========================================================
# 说明:
# 1. mysql中的null值在python中是没有的, 对应的是None
# 2. cursors.execute(sql, [(1, 'alex1'), (2, 'alex2')])
# 没有这种操作,要不然后面的executemany()就白设计了。
# 3. 如果只有一个占位符,写成序列形式(例如:元祖、列表等)或者单个都可以
# ==========================================================
sql = 'insert pymysql_tb value(%s, %s, %s);'
affected = cursor.execute(query=sql, args=(None, 21, 'hardy9sap'))
print(affected) # int, 返回受影响的行数数目
增多条
# 增多条:
# =============================================================
# 写法:
# 1.
# affected = cursor.executemany(query=sql, args=[
# (None, 22, 'Alex'),
# (0, 23, 'Egon'),
# (None, 24, 'TaiBai'),
# ])
#
# 2.
# affected = cursor.executemany(query=sql, args=(
# (None, 22, 'Alex'),
# (0, 23, 'Egon'),
# (None, 24, 'TaiBai'),
# ))
#
# 3.
# cursors.executemany(
# sql,
# [
# {'id': 0, 'age': 22, 'name': 'Alex'},
# {'id': 0, 'age': 23, 'name': 'Egon'},
# {'id': 0, 'age': 24, 'name': 'TaiBai'},
# ]
#
# )
#
#
# ==============================================================
sql = 'insert pymysql_tb value(%s, %s, %s);'
affected = cursor.executemany(query=sql, args=[
(None, 22, 'Alex'),
(0, 23, 'Egon'),
(None, 24, 'TaiBai'),
])
print(affected)
查看自增id
cursor.lastrowid
,获取上一次插入数据之后自增id值变成多少了,也可以看成这个值就是本次插入数据的第一条数据的自增id值。
print(cursor.lastrowid)
提交
执行了增加insert
命令,然而数据表中并没有发生变化,为什么呢?
对于增删改操作(像创建库/表、删库/表不用加commit)一定要加上conn.commit()操作
注意
- 对于增, 虽然没有进行
commit()
, 但是表中的auto_increment
已经发生了变化。
conn.commit()
删
sql = 'delete from pymysql_tb where id=%s;'
affected = cursor.execute(query=sql, args=(4, ))
print(affected) # int, 受影响的行数
conn.commit()
删表
# 删表
sql = 'drop table if exists pymysql_tb;'
affected = cursor.execute(query=sql)
print(affected)
改
sql = 'update pymysql_tb set name=%s where id=%s;'
affected = cursor.execute(query=sql, args=('林海峰老师', 5))
print(affected)
conn.commit()
查
# 查
sql = 'select id, age, name as nickname from pymysql_tb;'
affected = cursor.execute(query=sql)
# 拿到查询到的记录数
# print(affected) # int, 返回受影响的行数数目
示例代码
#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""pymysql增删改
增:
增一条:
cursor.execute(query=, args=)
增多条:
cursor.executemany(query=, args=)
获取上一次插入数据之后自增id值变成多少了
cursor.lastrowid
删:
cursor.execute(query=, args=)
改:
cursor.execute(query=, args=)
(增删改)提交:
conn.commit()
增删改execute之后, 都可以拿到结果, int, 表示受影响的行数数目
"""
import pymysql
conn = pymysql.connect(
user='root',
password='',
host='localhost',
port=3306,
charset='utf8mb4',
database='hardy2_db',
)
cursor = conn.cursor(cursor=None)
# 增
# 增一条:
# 直接写值(注意要加引号):
# sql = "insert pymysql_tb values(null, 21, 'hardy9sap');"
# args传值:
# ==========================================================
# 说明:
# 1. mysql中的null值在python中是没有的, 对应的是None
# 2. cursors.execute(sql, [(1, 'alex1'), (2, 'alex2')])
# 没有这种操作,要不然后面的executemany()就白设计了。
# 3. 如果只有一个占位符,写成序列形式(例如:元祖、列表等)或者单个都可以
# ==========================================================
# sql = 'insert pymysql_tb value(%s, %s, %s);'
# affected = cursor.execute(query=sql, args=(None, 22, 'hardy9sap'))
# print(affected) # int, 返回受影响的行数数目
# 增多条:
# =============================================================
# 写法:
# 1.
# affected = cursor.executemany(query=sql, args=[
# (None, 22, 'Alex'),
# (0, 23, 'Egon'),
# (None, 24, 'TaiBai'),
# ])
#
# 2.
# affected = cursor.executemany(query=sql, args=(
# (None, 22, 'Alex'),
# (0, 23, 'Egon'),
# (None, 24, 'TaiBai'),
# ))
#
# 3.
# cursors.executemany(
# sql,
# [
# {'id': 0, 'age': 22, 'name': 'Alex'},
# {'id': 0, 'age': 23, 'name': 'Egon'},
# {'id': 0, 'age': 24, 'name': 'TaiBai'},
# ]
#
# )
#
#
# ==============================================================
# sql = 'insert pymysql_tb value(%s, %s, %s);'
# affected = cursor.executemany(query=sql, args=[
# (None, 22, 'Alex'),
# (0, 23, 'Egon'),
# (None, 24, 'TaiBai'),
# ])
# print(affected)
# 获取上一次插入数据之后自增id值变成多少了
# 也可以看成这个值就是本次插入数据的第一条数据的自增id值
# 从0开始计算
# print(cursor.lastrowid)
# 然而数据表中并没有发生变化,为什么呢?
# 对于增删改操作(像创建库/表、删库/表不用加commit)一定要加上conn.commit()操作
# ============================================================
# 注意:
# 1. 对于增, 虽然没有进行commit(), 但是表中的auto_increment
# 已经发生了变化.
#
# ============================================================
# conn.commit()
# 删
# sql = 'delete from pymysql_tb where id=%s;'
# affected = cursor.execute(query=sql, args=(4, ))
# print(affected) # int, 受影响的行数
#
# conn.commit()
# 删表
# sql = 'drop table if exists pymysql_tb;'
# affected = cursor.execute(query=sql)
# print(affected)
# 改
# sql = 'update pymysql_tb set name=%s where id=%s;'
# affected = cursor.execute(query=sql, args=('林海峰老师', 5))
# print(affected)
#
# conn.commit()
# 查
sql = 'select id, age, name as nickname from pymysql_tb;'
affected = cursor.execute(query=sql)
# 拿到查询到的记录数
# print(affected) # int, 返回受影响的行数数目
# 关闭游标
cursor.close()
# 关闭连接
conn.close()