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

建立資料庫

import sqlite3con = 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 sqlite3con = 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 sqlite3con = 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 sqlite3con = 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()

Written by

Machine Learning / Deep Learning / Python / Flutter cakeresume.com/yanwei-liu

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store