[Python] sqlite3 教學

程式語言：Python
Package：sqlite3
官方文件
SQL 基本指令

功能：處理分析資料庫

import sqlite3


conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create table
cursor.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()

取得 columns name

# Cursor Objects
# tuple 的第一個值，其餘皆為 None，為了相容 Python DB API
# 需執行 SELECT， description 才會被設定
colNames = [colname[0] for colname in cursor.description]

在 RAM 中建立資料庫

con = sqlite3.connect(":memory:")

安全性，絕不要用 python 處理字串的方式查詢值或給值

# 絕不要這麼做，不安全
symbol = 'RHAT'
cursor.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# 請用以下方式
t = ('RHAT',)
cursor.execute('SELECT * FROM stocks WHERE symbol=?', (t,))

# 但可以用來取代 SQL Object，因 ? 無法用在 SQL Object 上
cursor.execute("SELECT COUNT(Name) FROM {} WHERE Name=?".format(group), (food,))

cursor.execute("INSERT INTO {} VALUES(?, ?)".format(group), (food, 1))

cursor.execute("UPDATE {} SET Times=? WHERE Name=?".format(group),
            (times_before + 1, food))

得到資料

for row in cursor.execute('SELECT * FROM stocks ORDER BY price'):
    print(row)

重覆建立 table

可用來判斷 table 是否已存在

cursor.execute('''CREATE TABLE ts( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTERGER)''')

# Traceback (most recent call last):
#   File "", line 1, in 
# sqlite3.OperationalError: table ts already exists