sqlite3

Posted on by

สร้างดาต้าเบส หรือเปิดดาต้าเบส

import sqlite3
con = sqlite3.connect("tutorial.db")

เตรียม cursor

cur = con.cursor()

สร้างตาราง

cur.execute("CREATE TABLE movie(title, year, score)")

ตรวจสอบตารางที่มีจาก sqlite_master

res = cur.execute("SELECT name FROM sqlite_master")
print(res.fetchone())
# ('movie',)

insert ข้อมูล

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")
con.commit()

select ข้อมูล

res = cur.execute("SELECT score FROM movie")
print(res.fetchall())

insert ข้อมูลด้วย executemany()

data = [
    ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
    ("Monty Python's The Meaning of Life", 1983, 7.5),
    ("Monty Python's Life of Brian", 1979, 8.0),
]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit()  # Remember to commit the transaction after executing INSERT.
for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
    print(row)

Verify that the database has been written to disk by calling con.close() to close the existing connection

con.close()

Tool