7.2 Python执行DML语句（数据库增删改查）

前言：DDL与DML

（1）DDL(Data Definition Language)数据定义语言

适用范围：对数据库中的某些对象(例如，database、table)进行管理，如建库、建表等（create（创建），alter（修改） 和 drop（删除） 等）。

（2）DML(Data Manipulation Language)数据操纵语言

适用范围：对数据库中的数据进行一些简单操作，主要指数据的增删改查（ insert，delete，update，select）。

2、执行DML语句（数据库增删改查）

说明：

• 使用游标的 execute() 方法执行 DML 语句
• SQLite 数据 API 默认开启事务，因此必须提交事务，否则程序对数据所做的修改不会生效

1）插入数据（insert）

conn = sqlite3.connect('test.db')
c = conn.cursor()

# 插入数据
c.execute('insert into user_tb values(null, ?, ?, ?)', ('用户1', '123456', 18))
c.execute('insert into user_tb values(null, ?, ?, ?)', ('用户2', '234567', 19))

c.execute('insert into order_tb values(null, ?, ?, ?, ?)', ('鼠标', 30, 2, 1))

# 提交事务，使修改生效
conn.commit()

c.close()
conn.close()

2）更新数据（update）

• 更新单条数据：execute()

  conn = sqlite3.connect('test.db')
  c = conn.cursor()
  
  # 更新数据
  c.execute('update user_tb set pass=?', ('111111',))
  # 执行完 DML 语句之后，可通过游标的 rowcount 来获取被修改的记录条数
  print('受此次更新影响的记录条数：', c.rowcount)
  
  conn.commit()
  
  c.close()
  conn.close()
  

  受此次更新影响的记录条数： 2
  

  

• 更新多条数据：executemany()（重复执行多次DML语句）

  说明：

  • 使用 executemany() 方法可以将同一条 DML 语句重复执行多次
  • executemany() 方法的第二个参数包含几个元组，该 DML 语句就会被执行几次

  conn = sqlite3.connect('test.db')
  c = conn.cursor()
  
  # 重复执行 SQL 语句
  c.executemany('insert into user_tb values(null, ?, ?, ?)', 
               (('孙悟空', '123456', 20),         # 此处每个元组代表一行数据
                ('猪八戒', '234567', 21)
               ))
  
  conn.commit()    
  
  c.close()
  conn.close()
  

  

3）查询数据（select）

说明：

• 查询（select）结果通过游标获取
• 方法1：通过游标的 fetchone()、fetchmany(n)、fetchall() 方法获取查询结果
  • fetchone() 方法返回一个元组，该元组代表一行数据
  • fetchmany(n) 方法返回一个长度小于等于 n 的列表，列表的每个元素都是一个元组（每个元组代表一行数据）
  • fetchall() 方法返回的全部记录，可能导致内存开销过大，情况严重时导致系统崩溃，因此不推荐使用
• 方法2：将游标当成可迭代对象来获取查询结果

（1）查询列名

conn = sqlite3.connect('test.db')
c = conn.cursor()

# 查询 user_tb 表所有数据（_id > 0）
c.execute('select * from user_tb where _id > ?', (0,))

for col in c.description:       # description属性（元组）返回列信息
    print(col[0], end='\t')     # 打印列名
    
c.close()
conn.close()

_id	name	pass	age

（2）fetchone()查询数据

conn = sqlite3.connect('test.db')
c = conn.cursor()

# 查询 user_tb 表中_id > 2 的数据
c.execute('select * from user_tb where _id > ?', (2,))
    
while True:
    row = c.fetchone()    # fetchone() 每次获取一行数据
    if not row:
        break
    else:
        # 输出每个单元格的数据
        for d in row:
            print(d, end='\t')
        print()
           
c.close()
conn.close()

3	孙悟空	123456	20	
4	猪八戒	234567	21

（3）迭代游标查询数据

conn = sqlite3.connect('test.db')
c = conn.cursor()
        
c.execute('select * from user_tb where _id > ?', (2,))

# 迭代游标，迭代的记录指针代表一行数据
for row in c:
    for d in row:
        print(d, end='\t')
    print()
    
c.close()
conn.close()

3	孙悟空	123456	20	
4	猪八戒	234567	21

3）删除数据（delete）

conn = sqlite3.connect('test.db')
c = conn.cursor()

# 删除之前数据
print('删除之前：')
c.execute('select * from user_tb where _id > ?', (0,))
for row in c:
    for d in row:
        print(d, end='\t')
    print()
print('-'*40)

# 删除 _id = 4 的数据
c.execute('delete from user_tb where _id = ?', (4,))

# 删除之后数据
print('删除之后：')
c.execute('select * from user_tb where _id > ?', (0,))
for row in c:
    for d in row:
        print(d, end='\t')
    print() 
    
conn.commit()    
    
c.close()
conn.close()

删除之前：
1	用户1	111111	18	
2	用户2	111111	19	
3	孙悟空	123456	20	
4	猪八戒	234567	21	
----------------------------------------
删除之后：
1	用户1	111111	18	
2	用户2	111111	19	
3	孙悟空	123456	20