SQLite เป็นระบบจัดการฐานข้อมูล (DBMS) ตัวหนึ่งที่เป็นที่นิยมใช้เพราะใช้ได้อย่างง่ายและมักมีลงไว้อยู่ในเครื่องแล้วโดยไม่จำเป็นต้องติดตั้งเพิ่มเข้าไปเหมือนอย่าง MySQL หรือ PostgreSQL

ในภาษาไพธอนมีมอดูลสำหรับจัดการกับฐานข้อมูล SQLite ชื่อมอดูล sqlite3 เป็นมอดูลมาตรฐานที่มีอยู่ในตัวไพธอนอยู่แล้ว ใช้งานได้ทันทีโดยไม่ต้องลงอะไรเพิ่ม

คำสั่งควบคุมใน SQLite ใช้ภาษา SQL (Structured Query Language, ภาษาสอบถามเชิงโครงสร้าง) โดยปกติแล้วเวลาที่เขียนเว็บมักจะควบคุมโดยทางอ้อมผ่านภาษาโปรแกรมอย่างไพธอน, จาวาสคริตป์, รูบี, php อีกที ทำให้ไม่จำเป็นต้องเขียนโค้ด sql จริงๆทั้งหมด

บทความนี้จะอธิบายตัวโค้ด sql อย่างคร่าวๆเน้นเฉพาะที่จำเป็นต้องรู้เมื่อใช้ผ่านไพธอน วัตถุประสงค์หลักคือแนะนำวิธีการใช้งานมอดูล sqlite3 ในไพธอน ดังนั้นจะไม่ได้ไปเน้นลงลึกในส่วนของ sql ตรงๆมาก




เริ่มต้นใช้งาน

ขั้นตอนการใช้งาน sqlite3 ในไพธอนเพื่อติดต่อกับฐานข้อมูลโดยทั่วไปมีดังนี้

• เชื่อมต่อ (connect):
  สร้างออบเจ็กต์เชื่อมต่อ sqlite3.connect() ซึ่งเป็นตัวติดต่อกับฐานข้อมูล


• ดำเนินการ (execute):
  เขียนโค้ด sql สั่งตัวออบเจ็กต์เชื่อมต่อด้วยเมธอด .execute() เพื่อสั่งให้ฐานข้อมูลทำตามคำสั่งที่ต้องการ


• ส่งมอบ (commit):
  บันทึกความเปลี่ยนแปลงที่ดำเนินการทำมาด้วยเมธอด .commit() ในตัวออบเจ็กต์เชื่อมต่อ


• ปิด (close):
  สั่งปิดตัวออบเจ็กต์เชื่อมต่อด้วยเมธอด .close()

เริ่มแรกขอยกตัวอย่างการเรียกใช้ตั้งแต่เชื่อมต่อฐานข้อมูล แล้วก็สั่งสร้างตาราง ใส่ข้อมูล ให้แสดงข้อมูล แล้วก็ปิด จบ

import sqlite3

# สร้างออบเจ็กต์ตัวเชื่อมต่อกับฐานข้อมูล
conn = sqlite3.connect('pkdata.db')

# สร้างตาราง
sql_create = '''
    create table pokemon (
        lek integer,
        chue text,
        nak real,
        sung real
    )
''' # ทำตารางโปเกมอน โดยมี ๔ สดมภ์ เลข, ชื่อ, หนัก, สูง
conn.execute(sql_create)

# ใส่ข้อมูลลงตาราง
sql_insert = '''
    insert into pokemon (lek,chue,nak,sung)
    values (1,"ฟุชิงิดาเนะ",6.9,0.7);
''' # ข้อมูลตัวแรก
conn.execute(sql_insert)
sql_insert = '''
    insert into pokemon (lek,chue,nak,sung)
    values (2,"ฟุชิงิโซว",13,1)
''' # ข้อมูลตัวที่ ๒
conn.execute(sql_insert)
conn.commit() # ส่งมอบ (บันทึกความเปลี่ยนแปลง)

# ดูข้อมูล
sql_select = '''
    select * from pokemon
''' # เลือกเอาข้อมูลโปเกมอนทุกตัวที่ใส่ไว้
for row in conn.execute(sql_select):
    print(row) # แสดงข้อมูลทีละแถว

# ท้ายสุดแล้วต้องปิดออบเจ็กต์ตัวเชื่อมต่อทิ้ง
conn.close() 

ผลที่ได้จะ print ข้อมูล ๒ แถวที่ใส่ลงไปออกมา ดังนี้

(1, 'ฟุชิงิดาเนะ', 6.9, 0.7)
(2, 'ฟุชิงิโซว', 13.0, 1.0)

จะเห็นว่าเป็นไปตามขั้นตอน connect > execute > commute > close

อนึ่ง ในที่นี้เพื่อความเป็นระเบียบและแยกชัดเจน โค้ดคำสั่ง sql ในบทความนี้จะใส่ไว้ในทริเปิลโควต ''' ''' แบบนี้เสมอ

โดยรวมแล้วทุกอย่างจะเริ่มต้นจากการสร้างออบเจ็กต์ตัวเชื่อมต่อ sqlite3.connect() โดยในวงเล็บให้ใส่ชื่อไฟล์ฐานข้อมูลลงไป ถ้าไฟล์นั้นไม่มีอยู่แต่แรกก็จะถูกสร้างขึ้นใหม่ ถ้ามีอยู่แล้วก็จะเชื่อมต่อเข้ากับฐานข้อมูลที่อยู่ในนั้น

ต่อมาคือสั่งเมธอด .execute() ที่ตัวออบเจ็กต์เชื่อมต่อ ในตัวอย่างนี้มีการเรียกใช้อยู่ ๓ คำสั่ง

• create table: สร้างตารางในฐานข้อมูลนี้
• insert into: ใส่ข้อมูลลงในตาราง
• select from: แสดงข้อมูลในตาราง

แต่ละคำสั่งไม่ว่าอันไหนก็ใช้เมธอด .execute() ในการดำเนินการเหมือนกัน แต่ว่ารายละเอียดจะต่างกันไป

คำสั่ง create table นั้นสั่งเสร็จก็จะมีการสร้างตารางขึ้นมาทันที ส่วนคำสั่ง insert into สั่งเสร็จแล้วสั่ง .commit() ต่อก็จะบันทึกข้อมูลลงไปในตาราง

ส่วน select from ซึ่งเป็นคำสั่งแสดงข้อมูลนั้น หลังจากเรียกใช้แล้วก็จะได้ออบเจ็กต์ข้อมูลออกมา ให้นำมาใช้ ซึ่งก็มีวิธีนำมาใช้อยู่หลายวิธี ในที่นี้ใช้ใส่ใน for เพื่อวนแสดงข้อมูลออกมาทีละตัว

หลังจากที่สั่งคำสั่งทั้งหมดเสร็จแล้วควรปิดท้ายด้วย .close() เพื่อปิดการเชื่อมต่อกับฐานข้อมูล เป็นอันเสร็จ

ต่อไปจะเป็นการอธิบายขยายความแต่ละขั้นตอนโดยละเอียด




ออบเจ็กต์ตัวเชื่อมต่อ sqlite3.connect

การใช้งาน sqlite3 เริ่มต้นจากการสร้างออบเจ็กต์ sqlite3.connect เพื่อเป็นตัวกลางในการทำการเชื่อมต่อกับฐานข้อมูล

วิธีการใช้

sqlite3.connect(ชื่อไฟล์ที่เก็บฐานข้อมูล)

เมื่อใช้ sqlite3.connect() หากไฟล์ที่เก็บฐานข้อมูลที่ใส่ไปนั้นยังไม่มีอยู่แต่แรกก็จะถูกสร้างขึ้นมาใหม่โดยอัตโนมัติแล้วก็เชื่อมต่อเข้าไปสู่ฐานข้อมูลนั้น แต่หากมีอยู่แล้วก็แค่เชื่อมต่อเข้าไปเฉยๆ

ออบเจ็กต์ที่สร้างขึ้นให้เก็บไว้ในตัวแปร แล้วใช้ตัวแปรนั้นเพื่อสั่ง .execute() หรือ .commute() ต่อไป และปิดท้ายด้วย .close() เพื่อปิดการเชื่อมต่อกับฐานข้อมูลด้วย

อีกวิธีหนึ่งในการใช้คือใช้กับ with และ contextlib.closing เพื่อจะได้ไม่ต้องมาสั่ง .close() อีกทีตอนท้ายสุด

รูปแบบการใช้

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(ชื่อไฟล์ฐานข้อมูล)) as ตัวแปร:
    # คำสั่งต่างๆที่จะสั่งให้ฐานข้อมูลทำ

ตัวอย่างเช่น ลองใช้วิธีนี้เปิดอ่านข้อมูลจากในไฟล์ฐานข้อมูลที่สร้างขึ้นมาจากตัวอย่างที่แล้วดูก็จะทำได้ดังนี้

import sqlite3
from contextlib import closing

with closing(sqlite3.connect('pkdata.db')) as conn:
    sql_select = '''
        select * from pokemon
    '''
    for row in conn.execute(sql_select):
        print(row)

เท่านี้ฐานข้อมูลก็จะถูกเชื่อมต่อเข้ามาแล้วก็อ่านข้อมูลข้างใน เสร็จแล้วก็ปิดการเชื่อมต่อลงหลังจากเสร็จ

การเขียนแบบนี้มีข้อดีตรงที่ไม่ต้องห่วงว่าจะต้องสั่ง .close() และต่อให้เกิดข้อผิดพลาดขึ้นมากลางคันการเชื่อมต่อกับฐานข้อมูลก็ถูกปิดลงไปเอง

ในขณะที่ถ้าไม่ใช้วิธีนี้ หากเกิดข้อผิดพลาดระหว่างทางแล้วไม่ได้อ่านไปถึง .close() ตอนท้ายก็จะทำให้ฐานข้อมูลไม่ถูกปิดลง

อนึ่ง เพื่อความเข้าใจง่าย ในตัวอย่างต่อๆไปในบทความนี้ก็จะไม่ได้เขียนแบบนี้ แต่ใช้ .close() ตามปกติ




สร้างตารางใหม่ด้วย execute CREATE TABLE

คำสั่ง create table ใน sql มีเอาไว้สำหรับสร้างตารางข้อมูลใหม่ขึ้นมา

รูปแบบการใช้สร้างตารางใหม่โดยผ่านไพธอนโดยเมธอด .execute() คือ

conn.execute('create table ชื่อตาราง (สดมภ์ที่มี)')

สดมภ์ หรือ คอลัมน์ (column) คือตัวที่บอกว่าข้อมูลนี้จะมีระบุคุณสมบัติอะไรบ้าง เช่น ชื่อ, น้ำหนัก, ส่วนสูง เป็นต้น เวลาสร้างให้ระบุชื่อและชนิดข้อมูล โดยเขียนไล่ไปแบบนี้ทีละตัว

(ชื่อ ชนิดข้อมูล, ชื่อ ชนิดข้อมูล, ชื่อ ชนิดข้อมูล, ...)

ชนิดข้อมูลประกอบด้วยจำนวนเต็ม, จำนวนจริง, สายอักขระ, ฯลฯ อาจเทียบกับไพธอนได้ดังนี้

SQLite	ไพธอน
integer	int
real	float
text	string
blob	bytes
null	None

ชนิดข้อมูลจะละไว้ไม่ระบุก็ได้

เมื่อสั่งแล้วจะสร้างตารางใหม่ทันที ไม่จำเป็นต้องสั่ง .commute() ต่อเหมือนอย่างเวลาใช้บางคำสั่งอย่างเช่น insert into

ตัวอย่าง เช่นสร้างตารางชื่อ digimon ในฐานข้อมูล dgdata.db โดยให้มีสดมภ์ ๓ ตัว คือ atk, def, spd โดยไม่ได้กำหนดชนิดข้อมูล

conn = sqlite3.connect('dgdata.db')
sql_create = '''
    create table digimon (atk, def, spd)
'''
conn.execute(sql_create)
conn.close()

ขณะที่สั่งสร้างตาราง ถ้ามีตารางชื่อนั้นอยู่ก่อนแล้วก็จะเกิดข้อผิดพลาดขึ้น ไม่มีการสร้างตารางใหม่เกิดขึ้น




ใส่ข้อมูลลงตารางด้วย execute INSERT INTO

คำสั่ง insert into ใน sql ใช้สำหรับใส่ข้อมูลเพิ่มเข้าไปในตาราง

conn.execute('insert into ชื่อตาราง (ชื่อสดมภ์, ) values (ค่าที่จะใส่, )')

หลังจากใส่ข้อมูลเพิ่มลงตารางไปแล้วต้องใช้เมธอด .commit() ข้อมูลจึงจะถูกบันทึกไว้

ตัวอย่าง เพิ่มข้อมูลโปเกมอนลงในตารางในฐานข้อมูลจากตัวอย่างแรกสุด

conn = sqlite3.connect('pkdata.db')
sql_insert = '''
    insert into pokemon (lek,chue,nak,sung)
    values (3,"ฟุชิงิบานะ",155.5,2.4);
'''
conn.execute(sql_insert)
conn.commit()
conn.close()

แต่ว่าในการใช้ .execute() แต่ละครั้งจะส่งได้แค่คำสั่งเดียว ปกติจึงสามารถใส่ได้แค่ทีละตัวเท่านั้น ถ้าจะสั่งหลายคำสั่งให้ใช้ .executemany() หรือ .executescript() ซึ่งจะกล่าวถึงต่อไป




การแทรกตัวแปรเข้าไปในคำสั่ง sql

.execute() นั้นสามารถแทรกตัวแปรเข้าไปได้ โดยค่าที่ต้องการให้แทนด้วยตัวแปรให้ใส่แทนด้วยเครื่องหมายคำถาม ? แล้วใส่ค่าที่ต้องการแทนลงไปเป็นอาร์กิวเมนต์ตัวถัดไป

conn.execute(คำสั่ง,ค่าที่ต้องการแทนลงไป)

ตัวอย่าง

conn = sqlite3.connect('pkdata.db')
data = (4,'ฮิโตคาเงะ',8.5,0.6)
sql_insert = '''
    insert into pokemon (lek,chue,nak,sung)
    values (?,?,?,?);
'''
conn.execute(sql_insert,data)
conn.commit()
conn.close()

ในที่นี้ค่าในทูเพิลจะไปแทนลงใน (?,?,?,?) ทีละตัวตามลำดับ

นอกจากนี้มีวิธีีเขียนอีกแบบคือใช้ตัวแปรระบุชื่อ โดยใช้ : นำหน้าชื่อตัวแปร แล้วใส่ข้อมูลในรูปดิกชันนารีแทน คือทำแบบนี้

conn = sqlite3.connect('pkdata.db')
data = {'lek': 5,'chue': 'ลิซาร์โด','nak':  19,'sung': 1.1}
sql_insert = '''
    insert into pokemon (lek,chue,nak,sung)
    values (:lek,:chue,:nak,:sung)
'''
conn.execute(sql_insert,data)
conn.commit()
conn.close()

วิธีการแทนค่าแบบนี้ถ้าหากใช้กับ for ก็สามารถใส่ข้อมูลต่างๆกันลงไปในแต่ละรอบได้โดยการแทนด้วยข้อมูลที่เปลี่ยนไปเรื่อยๆได้

อย่างไรก็ตาม ในกรณีที่ต้องการทำแบบนี้มีวิธีที่มีประสิทธิภาพกว่านั้น คือใช้เมธอด .executemany() แทน




สั่งคำสั่งหลายตัวไปด้วยกันด้วย executemany

เมธอด .executemany() ใช้เมื่อต้องการสั่งคำสั่งเหมือนๆกันหลายๆตัวแต่เปลี่ยนค่าไปเรื่อยๆในแต่ละรอบ

เหมาะที่จะใช้กับคำสั่งอย่าง insert เพื่อจะใส่ข้อมูลหลายๆตัวไปพร้อมๆกัน

วิธีใช้

conn.executemany(คำสั่ง,ลิสต์ของค่าแต่ละแถว)

ตัวที่ต้องการแทนให้ใส่เป็น ? ไว้ แล้วเตรียมลิสต์ของข้อมูลในแต่ละแถวไว้ ข้อมูลในแต่ละแถวจะเข้าไปแทนใน ? แต่ละตัวตามลำดับ

ตัวอย่าง

conn = sqlite3.connect('pkdata.db')
data = [(6,'ลิซาร์ดอน',110.5,1.7),
        (7,'เซนิงาเมะ',9,0.5)]

sql_insert = '''
    insert into pokemon (lek,chue,nak,sung)
    values (?,?,?,?);
'''
conn.executemany(sql_insert,data)
conn.commit()
conn.close()

หรือจะใช้แทนด้วยตัวแปรที่นำหน้าชื่อด้วย : แล้วใส่ลิสต์ของข้อมูลในรูปดิกชันนารีแทนก็ได้

ตัวอย่าง

conn = sqlite3.connect('pkdata.db')
data = [{'lek': 8,'chue': 'คาเมล','nak': 22.5,'sung': 1},
        {'lek': 9,'chue': 'คาเม็กซ์','nak': 101.1,'sung': 1.6}]

sql_insert = '''
    insert into pokemon (lek,chue,nak,sung)
    values (:lek,:chue,:nak,:sung);
'''
conn.executemany(sql_insert,data)
conn.commit()
conn.close()

ดึงข้อมูลออกมาดูโดย execute SELECT FROM

คำสั่ง select from ใน sql ใช้ดึงข้อมูลในตารางในตารางออกมาดู

รูปแบบการเขียน

conn.execute('select ชื่อสดมภ์ from ชื่อตาราง')

ชื่อสดมภ์หากมีหลายตัวให้คั่นด้วย , และหากต้องการทุกสดมภ์ก็ให้ใส่เป็น *

หากมีข้อมูลที่เติมเข้าไปแล้วแม้ยังไม่ได้สั่ง .commit() ก็จะแสดงออกมาด้วย แม้ว่าจะยังไม่ได้ถูกบันทึกลงในฐานข่้อมูลอย่างถาวรจริงๆ แต่ตราบใดที่ยังไม่ได้ปิดการเชื่อมต่อหรือสั่ง .rollback() ก็ไม่ได้หายไปไหน

วิธีการดูข้อมูลที่ดึงมาได้มีอยู่หลายวิธีด้วยกัน วิธีแรกคือเอามาวนใน for เลย เช่นแบบนี้

conn = sqlite3.connect('pkdata.db')
sql_select = '''
    select chue,sung from pokemon
'''
for row in conn.execute(sql_select):
    print(row)
conn.close()

ได้

('ฟุชิงิดาเนะ', 0.7)
('ฟุชิงิโซว', 1.0)
('ฟุชิงิบานะ', 2.4)
('ฮิโตคาเงะ', 0.6)
('ลิซาร์โด', 1.1)
('ลิซาร์ดอน', 1.7)
('เซนิงาเมะ', 0.5)
('คาเมล', 1.0)
('คาเม็กซ์', 1.6)

หรือจะแปลงเป็นลิสต์เพื่อแสดงข้อมูลออกมาทีเดียวเลยก็ได้

conn = sqlite3.connect('pkdata.db')
sql_select = '''
    select chue,nak from pokemon
'''
print(list(conn.execute(sql_select))) # ได้ [('ฟุชิงิดาเนะ', 6.9), ('ฟุชิงิโซว', 13.0), ('ฟุชิงิบานะ', 155.5), ('ฮิโตคาเงะ', 8.5), ('ลิซาร์โด', 19.0), ('ลิซาร์ดอน', 110.5), ('เซนิงาเมะ', 9.0), ('คาเมล', 22.5), ('คาเม็กซ์', 101.1)]
conn.close()

นอกจากนี้อีกวิธีคือใช้เมธอด .fetchone(), .fetchmany() หรือ .fetchall() ซึ่งใช้ดึงข้อมูลออกมาทีละตัวหรือทั้งหมด




fetchone, fetchmany และ fetchall

ออบเจ็กต์ที่ได้มาจากคำสั่ง select from นั้นจะมีเมธอด .fetchone(), .fetchmany() และ .fetchall() ใช้ในการดึงข้อมูลที่ได้มาไล่เรียง

.fetchone() จะให้ข้อมูลออกมาทีละตัวตามลำดับ และ .fetchmany() จะให้ออกมาตามจำนวนที่ระบุ

conn = sqlite3.connect('pkdata.db')
sql_select = '''
    select chue,sung,nak from pokemon
'''
data = conn.execute(sql_select)
print(data.fetchone()) # ได้ ('ฟุชิงิดาเนะ', 0.7, 6.9)
print(data.fetchone()) # ได้ ('ฟุชิงิโซว', 1.0, 13.0)
print(data.fetchone()) # ได้ ('ฟุชิงิบานะ', 2.4, 155.5)
print(data.fetchmany(3)) # ได้ [('ฮิโตคาเงะ', 0.6, 8.5), ('ลิซาร์โด', 1.1, 19.0), ('ลิซาร์ดอน', 1.7, 110.5)]
print(data.fetchmany(5)) # ได้ [('เซนิงาเมะ', 0.5, 9.0), ('คาเมล', 1.0, 22.5), ('คาเม็กซ์', 1.6, 101.1)]
print(data.fetchone()) # ได้ None
print(data.fetchmany()) # ได้ []
conn.close()

ถ้าจำนวนที่เหลือน้อยกว่าที่ระบุใน .fetchmany() ก็จะแสดงแค่เท่าที่เหลือ

ถ้าหมดแถวสุดท้ายแล้ว .fetchone() จะได้ None ส่วน .fetchmany() จะได้ลิสต์เปล่า

ส่วน .fetchall() ใช้ดึงข้อมูลทั้งหมดออกมาเป็นลิสต์ เหมือนการใช้ list()

conn = sqlite3.connect('pkdata.db')
sql_select = '''
    select chue,lek from pokemon
'''
data = conn.execute(sql_select)
print(data.fetchall()) # ได้ [('ฟุชิงิดาเนะ', 1), ('ฟุชิงิโซว', 2), ('ฟุชิงิบานะ', 3), ('ฮิโตคาเงะ', 4), ('ลิซาร์โด', 5), ('ลิซาร์ดอน', 6), ('เซนิงาเมะ', 7), ('คาเมล', 8), ('คาเม็กซ์', 9)]
conn.close()

กำหนดเงื่อนไขข้อมูลด้วย WHERE

เมื่อใช้ SELECT หากต้องการกำหนดเงื่อนไขข้อมูลให้เอามาแค่บางส่วนที่ต้องการให้เติม where ไปหลัง from

เช่นถ้าต้องการเอาตัวที่ nak มากกว่า 10

conn = sqlite3.connect('pkdata.db')
sql_select = '''
    select chue,nak from pokemon where nak<10
'''
data = conn.execute(sql_select)
print(data.fetchall()) # ได้ [('ฟุชิงิดาเนะ', 6.9), ('ฮิโตคาเงะ', 8.5), ('เซนิงาเมะ', 9.0)]
conn.close()

แต่คำสั่ง select ไม่อาจใช้กับเมธอด .executemany() เหมือนอย่างที่ทำกับ insert ได้

ถ้าต้องการหลายตัวที่มีเงื่อนไขต่างกันอาจใช้การวนซ้ำด้วย for แล้วแทนค่าตัวแปร

conn = sqlite3.connect('pkdata.db')
sql_select = '''
    select * from pokemon where lek==?
'''
for i in [2,5,8]:
    data = conn.execute(sql_select,[i])
    print(data.fetchall())
conn.close()

ได้

[(2, 'ฟุชิงิโซว', 13.0, 1.0)]
[(5, 'ลิซาร์โด', 19.0, 1.1)]
[(8, 'คาเมล', 22.5, 1.0)]

นอกจากนี้ก็ยังอาจจะใช้ and หรือ or เพื่อกำหนดเงื่อนไขเชื่อมหลายเงื่อนไขเข้าด้วยกัน

conn = sqlite3.connect('pkdata.db')
sql_select = '''
    select * from pokemon where (lek>=3 and lek<=4) or lek==7
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(3, 'ฟุชิงิบานะ', 155.5, 2.4), (4, 'ฮิโตคาเงะ', 8.5, 0.6), (7, 'เซนิงาเมะ', 9.0, 0.5)]
conn.close()

การกำหนดเงื่อนไขของสายอักขระด้วย LIKE

หากเงื่อนไขใน where เป็นสายอักขระอาจใช้ like ช่วยเพื่อกำหนดคัดเอาคำที่มีคำบางคำอยู่ในตำแหน่งที่ต้องการ

โดยจะใช้ % แทนอักษรใดๆกี่ตัวก็ได้ (รวมถึง 0 ตัว) และใช้ _ แทนอักษรตัวเดียว

ตัวอย่าง

conn = sqlite3.connect('pkdata.db')

sql_select = '''
    select * from pokemon where chue like 'ฟุชิ%'
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(1, 'ฟุชิงิดาเนะ', 6.9, 0.7), (2, 'ฟุชิงิโซว', 13.0, 1.0), (3, 'ฟุชิงิบานะ', 155.5, 2.4)]

sql_select = '''
    select * from pokemon where chue like '%ซา%'
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(5, 'ลิซาร์โด', 19.0, 1.1), (6, 'ลิซาร์ดอน', 110.5, 1.7)]

sql_select = '''
    select * from pokemon where chue like '%น_'
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(1, 'ฟุชิงิดาเนะ', 6.9, 0.7), (3, 'ฟุชิงิบานะ', 155.5, 2.4)]

sql_select = '''
    select * from pokemon where chue like '%เ_ะ'
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(1, 'ฟุชิงิดาเนะ', 6.9, 0.7), (4, 'ฮิโตคาเงะ', 8.5, 0.6), (7, 'เซนิงาเมะ', 9.0, 0.5)]

sql_select = '''
    select chue from pokemon where chue like '%า___'
'''
print(conn.execute(sql_select).fetchall())
# ได้ [('ฟุชิงิดาเนะ',), ('ฮิโตคาเงะ',), ('เซนิงาเมะ',), ('คาเมล',)]

conn.close()

จำกัดจำนวนด้วย LIMIT และ OFFSET

หากต้องการจำกัดจำนวนข้อมูลที่จะเอาก็สามารถเขียน limit ต่อท้าย ถ้าใช้พร้อมกับ where ให้ใส่ where ก่อน

conn = sqlite3.connect('pkdata.db')

sql_select = '''
    select * from pokemon limit 2
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(1, 'ฟุชิงิดาเนะ', 6.9, 0.7), (2, 'ฟุชิงิโซว', 13.0, 1.0)]

sql_select = '''
    select * from pokemon where lek>3 limit 3
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(4, 'ฮิโตคาเงะ', 8.5, 0.6), (5, 'ลิซาร์โด', 19.0, 1.1), (6, 'ลิซาร์ดอน', 110.5, 1.7)]

conn.close()

กำหนดจุดเริ่มต้นว่าจะเอาตั้งแต่ตัวที่เท่าไหร่ได้โดยใส่ offset ต่อท้ายไปอีก

conn = sqlite3.connect('pkdata.db')

sql_select = '''
    select * from pokemon limit 2 offset 2
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(3, 'ฟุชิงิบานะ', 155.5, 2.4), (4, 'ฮิโตคาเงะ', 8.5, 0.6)]

sql_select = '''
    select * from pokemon where lek>2 limit 3 offset 3
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(4, 'ฮิโตคาเงะ', 8.5, 0.6), (5, 'ลิซาร์โด', 19.0, 1.1), (6, 'ลิซาร์ดอน', 110.5, 1.7)]

conn.close()

เรียงลำดับผลการค้นด้วย ORDER BY

หากต้องการกำหนดวิธีการเรียงข้อมูลตามที่ต้องการอาจใช้ order by ต่อท้าย อาจใช้ร่วมกับ where และ limit

conn = sqlite3.connect('pkdata.db')

sql_select = '''
    select lek,chue from pokemon where lek>6 order by chue
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(8, 'คาเมล'), (9, 'คาเม็กซ์'), (7, 'เซนิงาเมะ')]

sql_select = '''
    select chue,nak from pokemon order by nak limit 3
'''
print(conn.execute(sql_select).fetchall())
# ได้ [('ฟุชิงิดาเนะ', 6.9), ('ฮิโตคาเงะ', 8.5), ('เซนิงาเมะ', 9.0)]

sql_select = '''
    select chue,nak from pokemon where nak>10 order by nak limit 3
'''
print(conn.execute(sql_select).fetchall())
# ได้ [('ฟุชิงิโซว', 13.0), ('ลิซาร์โด', 19.0), ('คาเมล', 22.5)]

conn.close()

สามารถใส่ desc หรือ asc ตามหลังเพื่อระบุว่าจะเรียงจากมากหรือจากน้อย ปกติถ้าไม่ได้ใส่ก็จะเรียงจากลำดับน้อยไปมากจึงไม่ได้จำเป็นต้องใส่ asc ก็ไม่ได้มีผล แต่หากต้องการให้เรียงจากมากไปน้อยก็เติม desc

ตัวอย่าง

conn = sqlite3.connect('pkdata.db')

sql_select = '''
    select chue,nak from pokemon order by nak desc limit 4
'''
print(conn.execute(sql_select).fetchall())
# ได้ [('ฟุชิงิบานะ', 155.5), ('ลิซาร์ดอน', 110.5), ('คาเม็กซ์', 101.1), ('คาเมล', 22.5)]

sql_select = '''
    select chue,nak from pokemon order by nak asc limit 4
'''
print(conn.execute(sql_select).fetchall())
# ได้ [('ฟุชิงิดาเนะ', 6.9), ('ฮิโตคาเงะ', 8.5), ('เซนิงาเมะ', 9.0), ('ฟุชิงิโซว', 13.0)]

conn.close()

ย้อนการกระทำกลับด้วย rollback

เมื่อใช้คำสั่ง insert into แล้วยังไม่ได้ทำการ commit สามารถย้อนให้ตารางกลับไปยังสถานะตอน commit ครั้งล่าสุด หรือตอนเริ่มแรกได้โดยใช้เมธอด .rollback()

ตัวอย่าง

conn = sqlite3.connect('pkdata.db')
data = (10,'คาเตอร์ปี',2.9,0.3)
sql_insert = '''
    insert into pokemon (lek,chue,nak,sung)
    values (?,?,?,?);
'''
conn.execute(sql_insert,data)

sql_select = '''
    select lek,chue from pokemon where lek>=8
'''
print(conn.execute(sql_select).fetchall())
# ได้ [(8, 'คาเมล'), (9, 'คาเม็กซ์'), (10, 'คาเตอร์ปี')]

conn.rollback() # ย้อนกลับ

print(conn.execute(sql_select).fetchall())
# ได้ [(8, 'คาเมล'), (9, 'คาเม็กซ์')]
conn.close()

แก้ไขข้อมูลด้วย execute UPDATE SET WHERE

คำสั่ง update ใน sql ใช้แก้ไขข้อมูลในตาราง

รูปแบบการใช้

conn.execute('update ชื่อตาราง set ชื่อสดมภ์ = ค่า, ... where เงื่อนไข')

ระวังว่าถ้าไม่ใส่ where จะกลายเป็นว่าแก้ข้อมูลในนั้นทั้งหมด และหลังจากสั่งเสร็จต้องใช้ .commit() จึงจะบันทึกความเปลี่ยนแปลงลงฐานข้อมูลจริงๆ

ตัวอย่าง

conn = sqlite3.connect('pkdata.db')

sql_select = '''
    select * from pokemon where lek==8
''' # แสดงช้อมูลก่อนแก้ไข
print(conn.execute(sql_select).fetchall()) # ได้ [(8, 'คาเมล', 22.5, 1.0)]

sql_update = '''
    update pokemon set chue='คาเมเลียน',nak=9,sung=3 where lek==8
''' # แก้ไข
conn.execute(sql_update)

sql_select = '''
    select * from pokemon where lek==8
''' # แสดงข้อมูลหลังแก้ไข
print(conn.execute(sql_select).fetchall()) # ได้ [(8, 'คาเมเลียน', 9.0, 3.0)]
conn.commit()
conn.close()

หลังจากใช้


ลบข้อมูลด้วย execute DELETE FROM WHERE

คำสั่ง delete from ใน sql ใช้สำหรับลบข้อมูลที่ต้องการทิ้ง

conn.execute('delete from ชื่อตาราง where เงื่อนไข')

หลังลบเสร็จก็ต้องสั่ง .commit() เพื่อบันทึกความเปลี่ยนแปลงเช่นกัน

ถ้าไม่ใส่ where จะเป็นการลบทิ้งทั้งหมด

ตัวอย่าง

conn = sqlite3.connect('pkdata.db')

sql_select = '''
    select * from pokemon where lek>=7
''' # แสดงข้อมูลก่อนลบ
print(conn.execute(sql_select).fetchall())
# ได้ [(7, 'เซนิงาเมะ', 9.0, 0.5), (8, 'คาเมเลียน', 9.0, 3.0), (9, 'คาเม็กซ์', 101.1, 1.6)]
sql_delete = '''
    delete from pokemon where lek>7
''' # ลบ
conn.execute(sql_delete)

sql_select = '''
    select * from pokemon where lek>=7
''' # แสดงข้อมูลหลังลบ
print(conn.execute(sql_select).fetchall())
# ได้ [(7, 'เซนิงาเมะ', 9.0, 0.5)]
conn.commit()
conn.close()

สั่งหลายคำสั่งไปในรวดเดียวด้วย executescript

.execute() ธรรมดาจะสั่งได้แค่ทีละคำสั่ง แต่หากต้องการป้อนคำสั่ง sql ลงไปหลายตัวพร้อมๆกัน นอกจากจะใช้เมธอด .executemany() แล้วก็ยังมีอีกวิธีคือใช้เมธอด .executescript() สามารถเขียนคำสั่งอะไรก็ได้ลงไปหลายคำสั่งได้โดยแต่ละคำสั่งให้เขียนคั่นด้วย ;

กรณีที่ใช้ .executescript() ถ้าใช้กับ insert ไม่จะเกิดการเปลี่ยนแปลงโดยไม่ต้อง .commit()

แต่ถ้าใช้กับคำสั่งที่ใช้เสร็จต้องมาเอาค่าอีกทีอย่าง select จะไม่ได้ค่าอะไรกลับมา

ตัวอย่าง

conn = sqlite3.connect('pkdata.db')

sql_insert = '''
    insert into pokemon (lek,chue,nak,sung)
    values (11,"ทรานเซล",9.9,0.7);
    insert into pokemon (lek,chue,nak,sung)
    values (12,"บัตเตอร์ฟรี",32,1.1)
'''
conn.executescript(sql_insert)

sql_select = '''
    select * from pokemon where lek>7
'''
print(conn.executescript(sql_select).fetchall()) # ได้ []
print(conn.execute(sql_select).fetchall()) # ได้ [(11, 'ทรานเซล', 9.9, 0.7), (12, 'บัตเตอร์ฟรี', 32.0, 1.1)]

conn.close()

เหมาะที่จะใช้เวลามีโค้ดยาวๆอยู่อย่างเช่นโค้ดที่ได้มาจากการสำรองข้อมูลแล้วต้องการรันทีเดียวเพื่อใส่ข้อมูลลงไปทั้งหมด




cursor เบื้องหลังการทำงานของ execute

ในตัวอย่างที่ผ่านมานั้นสั่ง .execute() ผ่านตัวออบเจ็กต์ตัวเชื่อมต่อ แต่จริงๆแล้วนี่เป็นแค่การเขียนย่อให้สะดวกขึ้น แต่เดิมทีจริงๆการสั่งคำสั่ง sql จะต้องสร้างออบเจ็กต์เคอร์เซอร์ (cursor) ขึ้นมาโดยใช้เมธอด .cursor() แล้วจึงค่อยสั่ง .execute() ผ่านตัวออบเจ็กต์เคอร์เซอร์นั้นอีกที

แต่ที่ตัวออบเจ็กต์ตัวเชื่อมต่อเองก็มีเมธอด .execute() ซึ่งเมื่อเรียก .execute() จากออบเจ็กต์ตัวเชื่อมต่อโดยตรงจะมีการสร้างออบเจ็กต์เคอร์เซอร์ขึ้นมาโดยอัตโนมัติแล้วก็สั่งจึงสั่ง .execute() จากเคอร์เซอร์นั้น

หากเขียนให้สมบูรณ์โดยสร้างเคอร์เซอร์มาก่อนแล้วค่อย .execute() ต้องเขียนแบบนี้

conn = sqlite3.connect('pkdata.db')
sql_select = '''
    select * from pokemon where lek<4
'''
cur = conn.cursor()
cur.execute(sql_select)
print(cur.fetchall()) # ได้ [(1, 'ฟุชิงิดาเนะ', 6.9, 0.7), (2, 'ฟุชิงิโซว', 13.0, 1.0), (3, 'ฟุชิงิบานะ', 155.5, 2.4)]
# หรือ
cur = conn.execute(sql_select)
print(cur.fetchall()) # ได้ผลเหมือนกัน

conn.close()

ในกรณีทั่วไปถ้าไม่จำเป็นก็สั่ง .execute() ผ่านออบเจ็กต์ตัวเชื่อมต่อโดยไม่ต้องสร้างเคอร์เซอร์ขึ้นมาก่อนก็ไม่มีปัญหา

conn.execute() จะคืนตัวออบเจ็กต์เคอร์เซอร์มาให้หลังจากใช้ กรณีใช้กับคำสั่ง select ก็ต้องรับเอาออบเจ็กต์ที่ได้จากเมธอดนี้มาใช้ด้วย

แต่ถ้าเรียก cur.execute() ตัวเคอร์เซอร์ cur นั้นจะเก็บข้อมูลที่ได้จาก select มาไว้อยู่แล้ว

แต่ค่าคืนกลับจาก cur.execute() ก็คือตัว cur นั้นเองเหมือนกัน ดังนั้นจะเขียนแบบนี้ก็ได้เช่นกัน

print(cur.execute(sql_select).fetchall())

สรุปเมธอดในเคอร์เซอร์

execute(sql)	ปัอนคำสั่ง sql
executemany(sql, ลิสต์ของตัวแปร)	ปัอนคำสั่ง sql ซ้ำๆโดยเปลี่ยนตัวแปรไปเรื่อยๆ
executescript(sql)	ปัอนคำสั่ง sql หลายคำสั่งต่อๆกัน
fetchone()	เอาผลที่ได้จาก select มาทีละตัว
fetchmany(n)	เอาผลที่ได้จาก select มาทีละ n ตัวใส่เป็นลิสต์
fetchall()	เอาผลที่ได้จาก select มาทั้งหมด
close()	ปิดการทำงาน

การโอนถ่ายข้อมูลไปยังอีกฐานข้อมูลด้วย backup

เมธอด .backup() ที่ออบเจ็กต์ตัวเชื่อมต่อใช้ถ่ายโอนข้อมูลจากฐานข้อมูลหนึ่งไปยังอีกฐานข้อมูล

ตัวอย่างการใช้

conn1 = sqlite3.connect('pkdata.db')
conn2 = sqlite3.connect('pkdata2.db')
conn1.backup(conn2)
conn1.close()

sql_select = '''
    select lek,chue from pokemon
'''
print(conn2.execute(sql_select).fetchall())
# ได้ [(1, 'ฟุชิงิดาเนะ'), (2, 'ฟุชิงิโซว'), (3, 'ฟุชิงิบานะ'), (4, 'ฮิโตคาเงะ'), (5, 'ลิซาร์โด'), (6, 'ลิซาร์ดอน'), (7, 'เซนิงาเมะ'), (11, 'ทรานเซล'), (12, 'บัตเตอร์ฟรี')]
conn2.close()

การใช้ iterdump เพื่อสำรองข้อมูลลงเป็นโค้ด sql เพื่อใช้สร้างใหม่

ออบเจ็กต์ตัวเชื่อมต่อมีเมธอด .iterdump() สำหรับใช้สำรองข้อมูลในฐานข้อมูลออกมาเป็นตัวหนังสือโค้ด sql ซึ่งเอาไว้สร้างตารางพร้อมทั้งฐานข้อมูลกลับมา โดยจะให้เป็นอิเทอเรเตอร์ เมื่อใช้ for จะได้โค้ดออกมาทีละบรรทัด

สามารถนำมาใช้เขียนลงไฟล์ได้ ดังนี้

conn = sqlite3.connect('pkdata.db')
with open('pkdatadump.sql','w') as f:
    for s in conn.iterdump():
        f.write(s+'\n')
conn.close()

แล้วจะได้ไฟล์ pkdatadump.sql ออกมา ซึ่งแสดงโค้ด sql ที่ถ้านำไปรันก็จะได้ตารางนี้กลับมา

BEGIN TRANSACTION;
CREATE TABLE pokemon (
        lek integer,
        chue text,
        nak real,
        sung real
    );
INSERT INTO "pokemon" VALUES(1,'ฟุชิงิดาเนะ',6.9,0.7);
INSERT INTO "pokemon" VALUES(2,'ฟุชิงิโซว',13.0,1.0);
INSERT INTO "pokemon" VALUES(3,'ฟุชิงิบานะ',155.5,2.4);
INSERT INTO "pokemon" VALUES(4,'ฮิโตคาเงะ',8.5,0.6);
INSERT INTO "pokemon" VALUES(5,'ลิซาร์โด',19.0,1.1);
INSERT INTO "pokemon" VALUES(6,'ลิซาร์ดอน',110.5,1.7);
INSERT INTO "pokemon" VALUES(7,'เซนิงาเมะ',9.0,0.5);
INSERT INTO "pokemon" VALUES(11,'ทรานเซล',9.9,0.7);
INSERT INTO "pokemon" VALUES(12,'บัตเตอร์ฟรี',32.0,1.1);
COMMIT;

ถ้าจะนำกลับมาใช้สร้างฐานข้อมูลใหม่ก็ทำได้โดยใช้ .executescript() เช่น

conn = sqlite3.connect('pkdata3.db')
with open('pkdatadump.sql') as f:
    conn.executescript(f.read())
conn.close()

แบบนี้ก็จะได้ฐานข้อมูลตัวเดิมที่มีข้อมูลเหมือนเดิมกลับมา



ทั้งหมดนี้เป็นการใช้ sqlite3 ในเบื้องต้น ยังมีรายละเอียดอีกมากมาย ศึกษาเพิ่มได้ในเว็บทางการของไพธอน >> https://docs.python.org/3/library/sqlite3.html