Python資料庫學習筆記(六)：SQLite3

建立資料庫

import sqlite3
con = sqlite3.connect('mydatabase.db')

建立資料庫Cursor變數

con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()

建立Table

employees (id, name, salary, department, position, hireDate)

使用 DB browser for sqlite 檢查資料是否建立成功

插入一筆資料到Table

cursorObj.execute("INSERT INTO employees VALUES(1, 'John', 700, 'HR', 'Manager', '2017-01-04')")
con.commit()
#VALUES裡面的值，可用?代替，表示缺失的空值

更新資料到Table

import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_update(con):
    cursorObj = con.cursor()
    cursorObj.execute('UPDATE employees SET name = "Rogers" where id = 2')
    con.commit()
sql_update(con)

選擇資料

cursorObj.execute('SELECT * FROM employees ')

取得所有資料

print(row) for row in cursorObj.fetchall()

取得指定資料

import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
    cursorObj = con.cursor()
    cursorObj.execute('SELECT id, name FROM employees WHERE salary > 800.0')
    rows = cursorObj.fetchall()
    for row in rows:
        print(row)
sql_fetch(con)

顯示有多少ROW在上一次操作時受到影響

rows = cursorObj.fetchall()
print len (rows)
print(cursorObj.execute('DELETE FROM employees').rowcount)

列出所有Tables

import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
    cursorObj = con.cursor()
 
    cursorObj.execute('SELECT name from sqlite_master where type= "table"')
 
    print(cursorObj.fetchall())
 
sql_fetch(con)

檢查表格是否存在

import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
    cursorObj = con.cursor()
    cursorObj.execute('create table if not exists projects(id integer, name text)')
    con.commit()
sql_fetch(con)

刪除Table

import sqlite3
 
con = sqlite3.connect('mydatabase.db')
 
def sql_fetch(con):
 
    cursorObj = con.cursor()
 
    cursorObj.execute('DROP table if exists employees')
 
    con.commit()
 
sql_fetch(con)

插入大量資料

import sqlite3
 
con = sqlite3.connect('mydatabase.db')
 
cursorObj = con.cursor()
 
cursorObj.execute('create table if not exists projects(id integer, name text)')
 
data = [(1, "Ridesharing"), (2, "Water Purifying"), (3, "Forensics"), (4, "Botany")]
 
cursorObj.executemany("INSERT INTO projects VALUES(?, ?)", data)
 
con.commit()

關閉與資料庫的連接

con = sqlite3.connect('mydatabase.db')
con.close()

建立時間資料

import sqlite3
import datetime
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute('create table if not exists assignments(id integer, name text, date date)')
data = [(1, "Ridesharing", datetime.date(2017, 1, 2)), (2, "Water Purifying", datetime.date(2018, 3, 4))]
cursorObj.executemany("INSERT INTO assignments VALUES(?, ?, ?)", data)
con.commit()

Python SQLite3 tutorial (Database programming) - Like Geeks