SQL：sqlite读写数据示例(namedtuple)

# SQLite测试.py

import sqlite3
from collections import namedtuple

# 连接数据库，创建游标
conn=sqlite3.connect("test.db")
cursor=conn.cursor()

# 创建数据库
rows = cursor.execute("""create table if not exists user ( id integer primary key autoincrement, name text not null)""")
print(rows)  # <sqlite3.Cursor object at 0x0000000000AE6570>

data = [
("Tom",),
("Jack",),
("Jimi",)
]

# 删除数据
# cursor.execute("delete from user where id > 3")

# 插入多个数据
rows = cursor.executemany("insert into user(name) values(?)", data)

conn.commit()  # 提交事务

# 查询数据
cursor.execute("select * from user")

result = cursor.fetchall() # 获取所有查询结果
print(result)
# [(1, 'Tom'), (2, 'Jack'), (3, 'Jimi')]

# 使用具名元组可以很好的使用数据库中拿到的数据
user = namedtuple("user", ["id", "name"])
for u in map(user._make, result): print(u) print(u.id, u.name)
"""
user(id=1, name='Tom')
1 Tom
user(id=2, name='Jack')
2 Jack
user(id=3, name='Jimi')
3 Jimi
"""

# 关闭游标和连接
cursor.close()
conn.close()
  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
  
38
  
39
  
40
  
41
  
42
  
43
  
44
  
45
  
46
  
47
  
48
  
49
  
50
  
51
  
52
  
53
 

文章来源: pengshiyu.blog.csdn.net，作者：彭世瑜，版权归原作者所有，如需转载，请联系作者。

原文链接：pengshiyu.blog.csdn.net/article/details/79225885