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()