Home » Python » Databases » Python SQLite tutorial using sqlite3

Python SQLite tutorial using sqlite3

Updated on: March 9, 2021 | 20 Comments

This Python SQLite tutorial aims to demonstrate how to develop Python database applications with the SQLite database. You will learn how to perform SQLite database operations from Python.

As you all know, SQLite is a C-language library that implements a SQL database engine that is relatively quick, serverless, and self-contained, high-reliable. SQLite is the most commonly used database engine in the test environment (Refer to SQLite Home page).

SQLite comes built-in with most computers and mobile devices, and browsers. Python’s official sqlite3 module helps us to work with the SQLite database.

Python sqlite3 module adheres to Python Database API Specification v2.0 (PEP 249). PEP 249 provides a SQL interface designed to encourage and maintain the similarity between the Python modules used to access databases.

Let see each section now.

Table of contents

• Python SQLite Database Connection
  • Important points while connecting to SQLite
• Create SQLite table from Python
• SQLite Datatypes and it’s corresponding Python types
• Perform SQLite  CRUD Operations from Python
• Execute SQL File (scripts) from Python using cursor’s executescript
• Insert/Retrieve digital data in SQLite using Python
• Create Or Redefine SQLite Functions using Python
• Working with SQLite date and timestamp types in Python and vice-versa
• SQLite Database Exceptions
• Change SQLite connection timeout when connecting from Python
• Identify total changes since the SQLite database connection is open
• Take a backup of SQLite database from Python
• Python SQLite Exercise Project

Python Gui Tkinter With Sqlite

 Python GUI資料庫 SQLite & Tkinter

源自於

https://www.python4networkengineers.com/posts/python-intermediate/create_a_tkinter_gui_with_sqlite_backend/

main.py 

from tkinter import *

from tkinter.ttk import Treeview

from db import Database

db = Database("router.db")

#----------------------------------------------------------------------------

def select_router(event):

    try:

        global selected_item

        index = router_tree_view.selection()[0]

        selected_item = router_tree_view.item(index)['values']

        hostname_entry.delete(0, END)

        hostname_entry.insert(END, selected_item[1])

        brand_entry.delete(0, END)

        brand_entry.insert(END, selected_item[2])

        ram_entry.delete(0, END)

        ram_entry.insert(END, selected_item[3])

        flash_entry.delete(0, END)

        flash_entry.insert(END, selected_item[4])

    except IndexError:

        pass

#----------------------------------------------------------------------------

def add_router():

    if brand_text.get() == '' or hostname_text.get() == '' or ram_text.get() == '' or flash_text.get() == '':

        messagebox.showerror('Required Fields', 'Please include all fields')

        return

    db.insert(hostname_text.get(), brand_text.get(),

              ram_text.get(), flash_text.get())

    clear_text()

    populate_list()

    

#----------------------------------------------------------------------------    

def remove_router():

    db.remove(selected_item[0])

    clear_text()

    populate_list()

#----------------------------------------------------------------------------

def update_router():

    db.update(selected_item[0], hostname_text.get(), brand_text.get(),

              ram_text.get(), flash_text.get())

    populate_list()

#----------------------------------------------------------------------------

def clear_text():

    brand_entry.delete(0, END)

    hostname_entry.delete(0, END)

    ram_entry.delete(0, END)

    flash_entry.delete(0, END)

#----------------------------------------------------------------------------

def search_hostname():

    hostname = hostname_search.get()

    populate_list(hostname)

#----------------------------------------------------------------------------

def execute_query():

    query = query_search.get()

    populate_list2(query)

#----------------------------------------------------------------------------

def populate_list(hostname=''):

    for i in router_tree_view.get_children():

        router_tree_view.delete(i)

    for row in db.fetch(hostname):

        router_tree_view.insert('', 'end', values=row)

#----------------------------------------------------------------------------

def populate_list2(query='select * from routers'):

    for i in router_tree_view.get_children():

        router_tree_view.delete(i)

    for row in db.fetch2(query):

        router_tree_view.insert('', 'end', values=row)

#----------------------------------------------------------------------------

def add_router():

    if brand_text.get() == '' or hostname_text.get() == '' or ram_text.get() == '' or flash_text.get() == '':

        messagebox.showerror('Required Fields', 'Please include all fields')

        return

    db.insert(hostname_text.get(), brand_text.get(),

              ram_text.get(), flash_text.get())

    clear_text()

    populate_list()    

#----------------------------------------------------------------------------

#----------------------------------------------------------------------------

#----------------------------------------------------------------------------

app = Tk()

frame_search = Frame(app)

frame_search.grid(row=0, column=0)

lbl_search = Label(frame_search, text='Search by hostname',

                   font=('bold', 12), pady=20)

lbl_search.grid(row=0, column=0, sticky=W)

hostname_search = StringVar()

hostname_search_entry = Entry(frame_search, textvariable=hostname_search)

hostname_search_entry.grid(row=0, column=1)

lbl_search = Label(frame_search, text='Search by Query',

                   font=('bold', 12), pady=20)

lbl_search.grid(row=1, column=0, sticky=W)

query_search = StringVar()

query_search.set("Select * from routers where ram>1024")

query_search_entry = Entry(frame_search, textvariable=query_search, width=40)

query_search_entry.grid(row=1, column=1)

#----------------------------------------------------------------------------

frame_fields = Frame(app)

frame_fields.grid(row=1, column=0)

# hostname

hostname_text = StringVar()

hostname_label = Label(frame_fields, text='hostname', font=('bold', 12))

hostname_label.grid(row=0, column=0, sticky=E)

hostname_entry = Entry(frame_fields, textvariable=hostname_text)

hostname_entry.grid(row=0, column=1, sticky=W)

# BRAND

brand_text = StringVar()

brand_label = Label(frame_fields, text='Brand', font=('bold', 12))

brand_label.grid(row=0, column=2, sticky=E)

brand_entry = Entry(frame_fields, textvariable=brand_text)

brand_entry.grid(row=0, column=3, sticky=W)

# RAM

ram_text = StringVar()

ram_label = Label(frame_fields, text='RAM', font=('bold', 12))

ram_label.grid(row=1, column=0, sticky=E)

ram_entry = Entry(frame_fields, textvariable=ram_text)

ram_entry.grid(row=1, column=1, sticky=W)

# FLASH

flash_text = StringVar()

flash_label = Label(frame_fields, text='Flash', font=('bold', 12), pady=20)

flash_label.grid(row=1, column=2, sticky=E)

flash_entry = Entry(frame_fields, textvariable=flash_text)

flash_entry.grid(row=1, column=3, sticky=W)

#----------------------------------------------------------------------------

frame_router = Frame(app)

frame_router.grid(row=4, column=0, columnspan=4, rowspan=6, pady=20, padx=20)

columns = ['id', 'Hostname', 'Brand', 'Ram', 'Flash']

router_tree_view = Treeview(frame_router, columns=columns, show="headings")

router_tree_view.column("id", width=30)

for col in columns[1:]:

    router_tree_view.column(col, width=120)

    router_tree_view.heading(col, text=col)

    

router_tree_view.bind('<<TreeviewSelect>>', select_router)

router_tree_view.pack(side="left", fill="y")

scrollbar = Scrollbar(frame_router, orient='vertical')

scrollbar.configure(command=router_tree_view.yview)

scrollbar.pack(side="right", fill="y")

router_tree_view.config(yscrollcommand=scrollbar.set)

#----------------------------------------------------------------------------

frame_btns = Frame(app)

frame_btns.grid(row=3, column=0)

add_btn = Button(frame_btns, text='Add Router', width=12, command=add_router)

add_btn.grid(row=0, column=0, pady=20)

remove_btn = Button(frame_btns, text='Remove Router',

                    width=12, command=remove_router)

remove_btn.grid(row=0, column=1)

update_btn = Button(frame_btns, text='Update Router',

                    width=12, command=update_router)

update_btn.grid(row=0, column=2)

clear_btn = Button(frame_btns, text='Clear Input',

                   width=12, command=clear_text)

clear_btn.grid(row=0, column=3)

search_btn = Button(frame_search, text='Search',

                    width=12, command=search_hostname)

search_btn.grid(row=0, column=2)

search_query_btn = Button(frame_search, text='Search Query',

                          width=12, command=execute_query)

search_query_btn.grid(row=1, column=2)

app.title('Router Manager')

app.geometry('700x550')

# Populate data

populate_list()

# Start program

app.mainloop()

==============================

db.py

import sqlite3

class Database:

    def __init__(self, db):

        self.conn = sqlite3.connect(db)

        self.cur = self.conn.cursor()

        self.cur.execute(

            "CREATE TABLE IF NOT EXISTS routers (id INTEGER PRIMARY KEY, hostname text, brand text, ram integer, flash integer)")

        self.conn.commit()

    def fetch(self, hostname=''):

        self.cur.execute(

            "SELECT * FROM routers WHERE hostname LIKE ?", ('%'+hostname+'%',))

        rows = self.cur.fetchall()

        return rows

    def fetch2(self, query):

        self.cur.execute(query)

        rows = self.cur.fetchall()

        return rows

    def insert(self, hostname, brand, ram, flash):

        self.cur.execute("INSERT INTO routers VALUES (NULL, ?, ?, ?, ?)",

                         (hostname, brand, ram, flash))

        self.conn.commit()

    def remove(self, id):

        self.cur.execute("DELETE FROM routers WHERE id=?", (id,))

        self.conn.commit()

    def update(self, id, hostname, brand, ram, flash):

        self.cur.execute("UPDATE routers SET hostname = ?, brand = ?, ram = ?, flash = ? WHERE id = ?",

                         (hostname, brand, ram, flash, id))

        self.conn.commit()

    def __del__(self):

        self.conn.close()

==============================

Python SQLite資料庫操作簡介

 Python SQLite資料庫操作簡介

源自於 https://nkust.gitbook.io/python/sqlite-liao-cao-zuo-jie

Keyin_Data.py

import sqlite3

dbfile = "school.db"

conn = sqlite3.connect(dbfile)

print("=========學生成績表輸入============")

print("===============================")

num = int(input('要輸入學生成績的筆數-->')) 

while (num>0):

    stuno = input("學號：")

    chi = input("國文成績：")

    eng = input("英文成績：")

    mat = input("數學成績：")

    his = input("歷史成績：")

    geo = input("地理成績：")

    sql_str = "insert into score(stuno, chi, eng, mat, his, geo) values('{}',{},{},{},{},{});".format(stuno, chi, eng, mat, his, geo)

    conn.execute(sql_str)

    conn.commit()

    num=num-1

conn.close()

Show_score.py

#顯示學生成績表

import sqlite3

dbfile = "school.db"

conn = sqlite3.connect(dbfile)

rows = conn.execute("select * from score;")

for row in rows:

    for field in row:

        print("{}\t".format(field), end="")

    print()

conn.close()

>>> %Run show_score.py

1 A23001 89 98 34 55 67

2 A23002 89 85 78 65 96

3 A23003 98 95 92 93 85

4 A23004 56 65 85 75 64

5 A23005 96 86 75 74 84

6 A23006 56 54 52 85 90

7 A23007 85 76 96 91 90

8 A23008 63 65 67 68 64

9 A23009 45 78 89 92 62

10 A23010 86 87 84 85 80

11 A23011 65 96 68 67 60

>>> 

show2_score.py

import sqlite3

dbfile = "school.db"

conn = sqlite3.connect(dbfile)

conn.row_factory = sqlite3.Row

cur = conn.cursor()

cur.execute("select * from score;")

rows = cur.fetchall()

print(rows[0].keys())

print(type(rows))

print(type(rows[0]))

print("學號\t國文\t英文")

for row in rows:

    print("{}\t{}\t{}".format(row['stuno'], row['chi'], row['eng']))

>>> %Run show2_score.py

['id', 'stuno', 'chi', 'eng', 'mat', 'his', 'geo']

<class 'list'>

<class 'sqlite3.Row'>

學號 國文 英文

A23001 89 98

A23002 89 85

A23003 98 95

A23004 56 65

A23005 96 86

A23006 56 54

A23007 85 76

A23008 63 65

A23009 45 78

A23010 86 87

A23011 65 96

>>> 

不需要安裝額外的資料庫管理程式或伺服器，只要一個檔案就可以使用的資料庫系統SQLite，是初學者和小型資料庫應用程式的最佳選擇，儘管嚴格來說它其實還不算是一個資料庫管理系統。

SQLite是一個以檔案為基礎的非常精簡版的SQL資料庫管理系統，它的最主要特色是沒有外部的伺服器系統或是執行中的對應程式，所有的操作都內嵌在操作資料庫的應用程式中（也就是我們編寫的程式），對於Python來說，只要載入對應的模組即可立即上手使用。

建立資料庫可以在程式中以create table指令完成，也可以透過圖形化的介面操作，在此使用後者，請先到以下的網站下載並安裝DB Browser for SQLite：

Downloads - DB Browser for SQLite

執行的畫面如下：

請在該介面中建立所需要的資料庫和資料表以供後續使用。建立完成之後的畫面如下所示：

也可以在此介面新增或編輯資料：

接下來就可以使用以下的程式碼來存取資料庫內容：

#顯示學生成績表

import sqlite3

dbfile = "school.db"

conn = sqlite3.connect(dbfile)

rows = conn.execute("select * from score;")

for row in rows:

for field in row:

print("{}\t".format(field), end="")

print()

conn.close()

​

在Python中操作SQLite資料庫的標準步驟如下：

1.
import sqlite3
2.
使用sqlite3.connect("資料庫檔案路徑")，它會傳回一個指標，習慣上會使用conn這個變數來接收它。
3.
利用前面所傳回的變數conn呼叫execute函數，函數中的內容就是SQL指令的字串。在execute執行之後，會傳回一個叫做Cursor的物件，它可以用來存取每一筆紀錄，也就是資料表中查詢結果的指標，由於會以列的型式來存取，因此習慣上在程式中會用rows或是cursor或是c這個變數來接收，在此程式例中是以rows來接收。
4.
接著，可以使用迴圈的方式把每一列找出來使用。

在上述的程式中，取出的rows就是查詢結果的所有紀錄，以列的型式來存放，而由於每一列中有許多的欄位（field），所以在上面的程式中使用了2層的迴圈，外層迴圈用來找出每一列，內層迴圈用來找出列中的每一欄。

1 A23001 80 98 34 55 67

2 A23002 89 99 45 89 90

3 A23003 89 90 87 88 90

4 A23004 99 89 90 99 100

5 A23005 89 56 88 90 52

輸入成績並把它們儲存入資料庫的標準作法：

import sqlite3

dbfile = "school.db"

conn = sqlite3.connect(dbfile)

stuno = input("學號：")

chi = input("國文成績：")

eng = input("英文成績：")

mat = input("數學成績：")

his = input("歷史成績：")

geo = input("地理成績：")

sql_str = "insert into score(stuno, chi, eng, mat, his, geo) values('{}',{},{},{},{},{});".format(stuno, chi, eng, mat, his, geo)

conn.execute(sql_str)

conn.commit()

conn.close()

​

設定以欄位名稱操作資料庫的程式設計標準步驟：

import sqlite3

dbfile = "school.db"

conn = sqlite3.connect(dbfile)

conn.row_factory = sqlite3.Row

cur = conn.cursor()

cur.execute("select * from score;")

rows = cur.fetchall()

print(rows[0].keys())

print(type(rows))

print(type(rows[0]))

print("學號\t國文\t英文")

for row in rows:

print("{}\t{}\t{}".format(row['stuno'], row['chi'], row['eng'