Sqlite 3

Python 3 กับการใช้งาน Database Sqlite3

1. install sqlite 3 บน Raspbian (ลิงค์การติดตั้ง sqlite3 บน Raspbian)
2. ตัวอย่างการใช้งาน sqlite3 ร่วมกับ python version 3

 
#!/usr/local/bin/python3
# -*- coding: utf-8 -*-

import sqlite3

#path file database if not have database file. it will auto create new file data                                                                                                                                                             base
path = "/home/pi/test.db"

#function CONNECT
def create_connection():
    con = None
    con = sqlite3.connect(path)
    #auto commit
    con.isolation_level = None
    return con

#function CREATE
def create_table(con,sql):
    cur = con.cursor()
    cur.execute(sql)

#function INSERT
def insert_table(con,persons):
    cur = con.cursor()
    cur.executemany("INSERT INTO person(name, lastname) VALUES (?, ?)", persons)


#function UPDATE
def update_table(con,persons):
    cur = con.cursor()
    cur.execute("UPDATE person SET name=?,lastname=? WHERE id=? ", persons)


#function DELETE
def delete_table(con,persons):
    cur = con.cursor()
    cur.execute("DELETE FROM person WHERE id=? ", persons)


#function SELECT
def select_table(con,sql):
    cur = con.cursor()
    cur.execute(sql)
    rows = cur.fetchall()
    return rows

con = create_connection()
if con is not None:
    print ("Connect database success")


    #--------Start CREATE table person-----
    create_table(con,"CREATE TABLE IF NOT EXISTS person (id integer PRIMARY KEY                                                                                                                                                              AUTOINCREMENT,name text, lastname text)")
    #--------End CREATE table person-------


    #--------Start INSERT table person-----
    persons = [
        ("Lisa", "Meme"),
        ("Tiffany", "Klein")
    ]
    insert_table(con,persons)
    #--------End INSERT table person-------


    #--------Start Update table person-----
    #update person id 1 to name = Lisa_new AND lastname = Meme_new
    persons = ("Lisa_new","Meme_new","1")
    update_table(con,persons)
    #--------End Update table person-------


    #--------Start SELECT table person-----
    rows = select_table(con,"SELECT * FROM person")
    for row in rows:
        print(row)
    #--------End SELECT table person-------

    #--------Start DELETE table person-----
    #delete where id = 2
    persons = ("2")
    delete_table(con,persons)
    #--------End DELETE table person-------
else:
    print ("Error! cannot connect database.")



#disconnect database
if (con):
    con.close()

3.ผลลัพธ์

* หมายเหตุ -สามารถสั่ง run python 3 บน raspbian ด้วยคำสั่ง "sudo python3 ชื่อไฟล์.py"