Python資料庫：建立以及基本操作

使用sqlite3建立資料庫以及基礎操作

kuan_min

9 min readAug 17, 2020

前言

今天將會分享如何使用python的sqlite3套件做資料庫的基礎操作，並不會針對資料庫語法做解說。

將學習到

使用sqlite3建立資料庫
使用sqlite3匯入資料
使用sqlite3資料搜尋
使用sqlite3 資料更新、刪除

環境設定

win10 Anaconda的jupyter(6.0.3)

DB Browser(SQLite)：官網

使用套件

import sqlite3

開始工作

建立資料庫

import sqlite3
#drop table 
conn = sqlite3.connect('D.db')#連結資料庫，如果沒有則會建立cursor = conn.cursor()#啟用游標
cursor.execute("DROP TABLE  IF EXISTS  away;")#查看是否存在away
cursor.execute("DROP TABLE  IF EXISTS  home;")#查看是否存在homeprint ("D.db created successfully")
conn.commit()#執行sql語法，每次執行都要提交sql語法
cursor.close()#每次執行都要關閉游標
conn.close()#每次執行都要關閉連結

設定資料庫格式

#creat table
conn = sqlite3.connect(‘D.db’)#連結資料庫
print (“Opened database successfully”)
cursor = conn.cursor()#啟用游標#設定away資料表
cursor.execute('''CREATE TABLE IF NOT EXISTS away 
 (id INT PRIMARY KEY NOT NULL,
 name varchar(8) NOT NULL,
 car varchar(30) NOT NULL,
 color varchar(10) NOT NULL);''')
print (“Table away created successfully”)#設定home資料表
cursor.execute('''CREATE TABLE IF NOT EXISTS home 
 (id INT PRIMARY KEY NOT NULL,
 name varchar(8) NOT NULL,
 car varchar(30) NOT NULL,
 color varchar(10) NOT NULL);''')print (“Table home created successfully”)
conn.commit()#執行sql語法
cursor.close()#關閉游標
conn.close()#關閉連結

匯入資料

#insert
conn = sqlite3.connect(‘D.db’)#連結資料庫
cursor = conn.cursor()#啟用游標
print(“Opened database successfully”)#匯入資料至away資料表
cursor.execute(“INSERT INTO away (id,name,car,color) \
 VALUES (001, ‘藤原拓海’, ‘TOYOTA AE86’, ‘熊貓’ )”)cursor.execute(“INSERT INTO away (id,name,car,color) \
 VALUES (002, ‘高橋涼介’,’MAZDA RX-7',’白’ )”)cursor.execute(“INSERT INTO away (id,name,car,color) \
 VALUES (003, ‘高橋啟介’,’MAZDA RX-7',’黃’ )”)#匯入資料至home資料表
cursor.execute(“INSERT INTO home (id,name,car,color) \
 VALUES (004, ‘二宮大輝’,’Honda K9',’黃’ )”)cursor.execute(“INSERT INTO home (id,name,car,color) \
 VALUES (005, ‘星野好造’,’Nissan GT-R R32',’深黃’ )”)cursor.execute(“INSERT INTO home (id,name,car,color) \
 VALUES (006, ‘乾信司’,’TOYOTA AE86',’熊貓’ )”)conn.commit()#執行sql語法
print(“Records created successfully”)
cursor.close()#關閉游標
conn.close()#關閉連結

搜尋資料

#search
conn = sqlite3.connect(‘D.db’)#連結資料庫
cursor = conn.cursor()#啟用游標
print(“Opened database successfully”)#搜尋所有away的欄位資料
cursor = cursor.execute(“SELECT * from away”)
for row in cursor:
 print(row)#搜尋所有home的欄位資料
cursor = cursor.execute(“SELECT * from home”)#顯示
for row in cursor:
 print(row)conn.commit()#執行sql語法
print(“Operation done successfully”)
cursor.close()#關閉游標
conn.close()#關閉連結

#search
conn = sqlite3.connect(‘D.db’)#連結資料庫
cursor = conn.cursor()#啟用游標
print(“Opened database successfully”,’\n’)#搜尋id, name, car, color欄位且在color是"黃"的
cursor = cursor.execute(“SELECT id, name, car, color from home where color=’黃’”)#顯示
for row in cursor:
 print(“id = “, row[0])
 print(“name = “, row[1])
 print(“car = “, row[2])
 print(“color = “, row[3], “\n”)conn.commit()#執行sql語法
print(‘\n’,”Operation done successfully”)
cursor.close()#關閉游標
conn.close()#關閉連結

更新資料

#update
conn = sqlite3.connect(‘D.db’)#連結資料庫
cursor = conn.cursor()#啟用游標
print(“Opened database successfully”)#更新home欄位裡id是4的color成"白"
cursor.execute(“UPDATE home set color = ‘白’ where id = ‘4’ “)#顯示更新幾筆
print(“Total number of rows updated :”, conn.total_changes)#顯示
cursor = conn.execute(“SELECT id, name, car, color from home”)
for row in cursor:
 print(“id = “, row[0])
 print(“name = “, row[1])
 print(“car = “, row[2])
 print(“color = “, row[3], “\n”)conn.commit()#執行sql語法
print( “Operation done successfully”)
cursor.close()#關閉游標
conn.close()#關閉連結

刪除資料

#DELETE
conn = sqlite3.connect(‘D.db’)#連結資料庫
cursor = conn.cursor()#啟用游標
print(“Opened database successfully”)#刪除name=’星野好造’
cursor.execute(“DELETE from home where name = ‘星野好造’;”)conn.commit()#執行sql語法
print( “Operation done successfully”)
cursor.close()#關閉游標
conn.close()#關閉連結

匯出資料到pandas和.csv

conn = sqlite3.connect(‘D.db’)#連結資料庫
cursor = conn.cursor()#啟用游標
print(“Opened database successfully”)import pandas as pd
#匯入至pandas
df = pd.DataFrame(cursor, columns= [‘id’, ‘name’, ‘car’, ‘color’])
print(df,’\n’)#匯入至 .csv
df.to_csv (r’drift_home.csv’, index = False, header=True)print( “Operation done successfully”)
cursor.close()#關閉游標
conn.close()#關閉連結