ฐานข้อมูล sql เป็นรูปแบบการเก็บข้อมูลที่ใช้กันอย่างกว้างขวาง การเก็บข้อมูลของ sql นั้นมีลักษณะเป็นตารางข้อมูลเป็นแถวๆ คล้ายกับ pandas

pandas มีคำสั่งที่ช่วยให้ติดต่อกับฐานข้อมูล sql ได้อย่างง่ายดายขึ้น โดยสามารถเอาตารางจาก pandas เขียนลงใน sql และอ่านตารางจาก sql เข้ามาเป็นตารางใน pandas

ความสามารถส่วนใหญ่ในส่วนนี้จะใช้กับมอดูล sqlalchemy เป็นหลัก ดังนั้นจำเป็นต้องติดตั้งมอดูล sqlalchemy ด้วย

เกี่ยวกับการใช้ sqlalchemy อ่านได้ใน https://phyblas.hinaboshi.com/20200529

เพียงแต่ว่าก็อาจไม่ต้องเรียกใช้ sqlalchemy โดยตรง แค่มีลงมอดูล sqlalchamy ไว้ก็สามารถใช้ความสามารถนี้ได้แล้ว

ฐานข้อมูล sql ยังแบ่งออกเป็นหลายแบบ เช่น sqlite, posgresql, mysql ซึ่ง sqlalchemy ก็รองรับฐานข้อมูลหลายชนิด ซึ่งก็ทำให้ใช้ใน pandas ได้ด้วยเช่นกัน ในที่นี้จะใช้ sqlite ซึ่งเป็นฐานข้อมูล sql แบบที่ง่ายที่สุด มีติดตัวอยู่ตั้งแต่แรกไม่ต้องติดตั้งเพิ่ม



เมื่อมีตารางข้อมูลเก็บอยู่ในเดตาเฟรมแล้วต้องการบันทึกลงฐานข้อมูล sql สามารถทำได้โดยใช้เมธอด .to_sql() จากตัวเดตาเฟรมนั้น

การใช้คำสั่งนี้มีการเขียนอยู่หลายวิธี ที่ง่ายที่สุดก็คือใส่ชื่อตารางและตามด้วยชื่อตารางที่เก็บข้อมูลนั้นอยู่

วิธีที่ง่ายที่สุดคือแค่ใส่ชื่อตาราง แล้วตามด้วยชื่อตัวฐานข้อมูลที่จะเก็บตารางข้อมูลนั้นไว้

df.to_sql(ชื่อตาราง,ชื่อฐานข้อมูล)

ตัวอย่างการใช้

import pandas as pd

p = {'สายพันธุ์':['ซันกูส','ฮาบุเนก','ลูนาโทน'],
     'ส่วนสูง':[1.3,2.7,1],
     'น้ำหนัก':[40.3,52.5,168]}
pokedf = pd.DataFrame(p,index=[335,336,337])
pokedf.to_sql('pokemon','sqlite:///pkdata.db')

ในที่นี้ใช้กับฐานข้อมูล sqlite ในส่วนของชื่อฐานข้อมูลจะเขียนเป็น 'sqlite:///ชื่อไฟล์' แบบนี้

อนึ่ง เดิมทีแล้ว .to_sql() ควรจะใช้กับตัวออบเจ็กต์เชื่อมต่อ ซึ่งใน sqlalchemy เรียกว่า engine

หากเขียนแบบเต็มๆตั้งแต่ขั้นตอนการสร้าง engine ก็อาจเขียนแบบนี้

import sqlalchemy

engine = sqlalchemy.create_engine('sqlite:///pkdata.db')
pokedf.to_sql('pokemon',engine)

เพียงแต่ว่าสามารถเขียนย่อเป็นแบบใส่แค่ชื่อฐานข้อมูลไปโดยตรงก็ได้ ดังนั้นจึงสะดวกกว่ามาก ไม่จำเป็นต้อง import sqlalchemy มาโดยตรงเลยด้วย

ส่วนการอ่านข้อมูลจากตารางใน sql ทำได้โดยฟังก์ชัน pd.read_sql_table() วิธีใช้ก็เช่นเดียวกับตอนเขียนข้อมูลลง sql นั่นคือใส่ชื่อตารางกับชื่อฐานข้อมูล

df = pd.read_sql_table(ชื่อตาราง,ชื่อฐานข้อมูล)

ตัวอย่างเช่นถ้าต้องการอ่านข้อมูลที่บันทึกลงไปในฐานข้อมูลในตัวอย่างที่แล้ว

df = pd.read_sql_table('pokemon','sqlite:///pkdata.db')
print(df)

ได้

จากในตัวอย่างที่แล้วทั้งตอนเขียนและอ่านล้วนไม่ได้ใส่ตัวเลือกเสริมอะไรลงไปเลย ทุกอย่างจึงเป็นไปตามค่าตั้งต้น

ซึ่งจะเห็นว่าตอนที่ใช้ .to_sql() นั้นตัวดัชนีก็ถูกเปลี่ยนเป็นคอลัมน์หนึ่งใน sql ไปด้วย โดยชื่อคอลัมน์ดัชนีก็จะกลายเป็นชื่อคอลัมน์ใน sql ด้วย

หากต้องการเปลี่ยนชื่อคอลัมน์ดัชนีใหม่อาจทำได้โดยใส่ในตัวเลือกเสริม index_label เช่น

pk = {'สายพันธุ์':['โซลร็อก','โดจ็อช'],
      'ส่วนสูง':[1.2,0.4],
      'น้ำหนัก':[154,1.9]}
index = pd.Series([338,339],name='หมายเลข')
pokedf = pd.DataFrame(pk,index=index)
pokedf.to_sql('pokemon','sqlite:///pkdt.db',index_label='id')
print(pd.read_sql_table('pokemon','sqlite:///pkdt.db'))

ได้



นอกจากนี้ถ้าหากไม่ได้ตั้งชื่อให้คอลัมน์ดัชนี แล้วก็ไม่ได้กำหนด index_label ก็จะถูกตั้งชื่อเป็น index โดยอัตโนมัติ

หากจะให้ทิ้งส่วนดัชนีไปเลยก็ใส่ตัวเลขเสริม index เป็น index=False

pk = {'สายพันธุ์':['นามาซึน','เฮย์งานิ'],
      'ส่วนสูง':[0.9,0.6],
      'น้ำหนัก':[23.6,11.5]}
pokedf = pd.DataFrame(pk,index=[340,341])
pokedf.to_sql('pokemon','sqlite:///pkdex.db',index=False)
print(pd.read_sql_table('pokemon','sqlite:///pkdex.db'))

ได้



หากชื่อตารางที่ใส่ไปนั้นซ้ำกับที่มีอยู่ในฐานข้อมูลนั้นแล้ว ปกติจะเกิดข้อผิดพลาดขึ้น

หากไม่ต้องการให้เป็นเช่นนั้นก็อาจกำหนดไปในตัวเลือกเสริม if_exists เพิ่มเติม

โดยถ้า id_exists='replace' ตารางเดิมจะหายไปแล้วเอาข้อมูลใหม่ใส่ลงไปแทน

เช่นลองใส่ตารางเดิมซ้ำในฐานข้อมูลเดียวกับตัวอย่างที่แล้ว

pk = {'สายพันธุ์':['เนนดอล','ลีลีลา'],
      'ส่วนสูง':[1.5,1],
      'น้ำหนัก':[108,23.8]}
pokedf = pd.DataFrame(pk,index=[344,345])
pokedf.to_sql('pokemon','sqlite:///pkdex.db',if_exists='replace')
print(pd.read_sql_table('pokemon','sqlite:///pkdex.db'))

ได้



จะเห็นว่าข้อมูลเก่าหายไปแล้วกลายเป็นข้อมูลใหม่

ตรงนี้ถ้าไม่ได้ใส่ หรือใส่ if_exists='fail' ก็จะขึ้นมาว่า

ValueError: Table 'pokemon' already exists.

นอกจากนี้ ถ้า id_exists='append' จะเป็นการเพิ่มข้อมูลเข้าไปในตารางที่มีอยู่แล้ว

เช่นลองใส่ตารางเดิมซ้ำในฐานข้อมูลเดียวกับตัวอย่างที่แล้ว

pk = {'สายพันธุ์':['ชิซาริเกอร์','ยาจิลอน'],
      'ส่วนสูง':[1.1,0.5]}
pokedf = pd.DataFrame(pk,index=[342,343])
pokedf.to_sql('pokemon','sqlite:///pkdex.db',if_exists='append')
print(pd.read_sql_table('pokemon','sqlite:///pkdex.db'))

ได้



ข้อมูลที่ใส่ต่อเข้าไปนั้นควรจะมีคอลัมน์ซ้ำกับตารางเดิม หรือจะขาดไปบางคอลัมน์ก็ได้ ค่าที่ขาดจะว่างไว้ แต่ถ้าหากมีคอลัมน์ที่ไม่มีอยู่เดิมก็จะเกิดข้อผิดพลาด



สำหรับชนิดของข้อมูลนั้น ถ้าไม่ได้กำหนดอะไรก็จะเป็นไปตามชนิดของข้อมูลที่สัมพันธ์กับที่อยู่ในเดตาเฟรม

เช่น ตัวอย่างนี้ เมื่อไม่ได้กำหนด dtype ก็จะเป็นแบบนี้

pk = {'สายพันธุ์':['มิโลคารอส','คาคุเรออน'],
      'ส่วนสูง':[6.2,1],
      'น้ำหนัก':[162.0,22.0]}
pokedf = pd.DataFrame(pk,index=pd.Series([350,352],name='เลข'))

pokedf.to_sql('pokemon','sqlite:///pkdex.db',if_exists='replace')
df = pd.read_sql_table('pokemon','sqlite:///pkdex.db',coerce_float=False)
print(df.values)

ได้เป็น int, str, float, float

[[350 'มิโลคารอส' 6.2 162.0]
 [352 'คาคุเรออน' 1.0 22.0]]

แต่หากต้องการให้เปลี่ยนชนิดข้อมูลเป็นแบบที่ต้องการก็สามารถกำหนดได้โดยตัวเลือกเสริม dtype

การใส่ชนิดข้อมูลนั้นให้ใส่ในรูปของชนิดข้อมูล sqlalchemy (ต้อง import มาใช้)

จากตัวอย่างที่แล้ว ลองกำหนด dtype เข้าไปได้ดังนี้

import sqlalchemy

dtype = {'ส่วนสูง':sqlalchemy.String,
         'น้ำหนัก':sqlalchemy.Integer,
         'เลข':sqlalchemy.Float}

pokedf.to_sql('pokemon','sqlite:///pkdex.db',dtype=dtype,if_exists='replace')
df = pd.read_sql_table('pokemon','sqlite:///pkdex.db')
print(df.values)

ได้เป็น float, str, str, int

[[350.0 'มิโลคารอส' '6.2' 162]
 [352.0 'คาคุเรออน' '1.0' 22]]

เวลาอ่านข้อมูลจาก sql ด้วย pd.read_sql_table() ก็มีตัวเลือกเสริมมากมายเพื่ออำนวยความสะดวกในการการกำหนดลักษณะการอ่าน

เช่น index_col ใช้กำหนดคอลัมน์ที่จะเป็นดัชนี ถ้าหากไม่กำหนดอะไรไปก็จะไม่มีคอลัมน์ไหนกลายเป็นดัชนี แล้วเป็นตัวเลข 0,1,2 ไป

ตัวอย่างการอ่านไฟล์โดยใส่ index_col ต่างๆกัน

pk = {'สายพันธุ์':['ฮินบาส','โปวาเลิน','คาเงะโบวซึ'],
      'ส่วนสูง':[0.6,0.3,0.6],
      'น้ำหนัก':[7.4,0.8,2.3]}
pokedf = pd.DataFrame(pk,index=pd.Series([349,351,353],name='เลข'))
pokedf.to_sql('pokemon','sqlite:///pkdex.db',if_exists='replace')

print(pd.read_sql_table('pokemon','sqlite:///pkdex.db'))
print(pd.read_sql_table('pokemon','sqlite:///pkdex.db',index_col='เลข'))
print(pd.read_sql_table('pokemon','sqlite:///pkdex.db',index_col='สายพันธุ์'))

ได้



ส่วนการเลือกเอาข้อมูลเฉพาะแค่บางคอลัมน์ก็ทำได้โดยใส่ตัวเลือกเสริม columns ระบุชื่อคอลัมน์ที่ต้องการลงไป

pk = {'สายพันธุ์':['ยูเรเดิล','อาโนปธ์','อาร์มัลโด'],
      'ส่วนสูง':[1.5,0.7,1.5],
      'น้ำหนัก':[60.4,12.5,68.2]}
pokedf = pd.DataFrame(pk,index=[346,347,348])
pokedf.to_sql('pokemon','sqlite:///pkdex.db',if_exists='replace')

print(pd.read_sql_table('pokemon','sqlite:///pkdex.db',columns=['index','สายพันธุ์']))
print(pd.read_sql_table('pokemon','sqlite:///pkdex.db',columns=['index','สายพันธุ์'],index_col='index'))

ได้



แต่ pd.read_sql_table() นั้นไม่สามารถกำหนดเงื่อนไขให้ข้อมูลออกมาเฉพาะบางแถวได้ จะอ่านข้อมูลออกมาทุกแถวเสมอ (เหมือนการใส่ where ในโค้ด sql) และกำหนดลำดับการเรียงข้อมูลไม่ได้ด้วย (เหมือนการใส่ order by ใน sql)



สำหรับการใช้งานที่ยืดหยุ่นกว่า อาจฟังก์ชันอีกตัวคือ pd.read_sql_query() แทน

ในขณะที่ pd.read_sql_table() นั้นแค่ระบุชื่อตารางก็ได้ข้อมูลมาแล้ว แต่ pd.read_sql_query() จะต้องเขียนโค้ด sql เพื่อสั่งฐานข้อมูลเโดยตรงเพื่อดึงเอาข้อมูลมา

การจะใช้ฟังก์ชันนี้ได้จึงต้องรู้โค้ด sql ด้วยทำให้อาจใช้ยากกว่า และเขียนยาวกว่า แต่ข้อดีคือเขียนได้ยืดหยุ่นกว่า สามารถเขียน where เพื่อกำหนดเงื่อนไขได้ หรือเขียน order by เพื่อเรียงลำดับข้อมูลได้

ส่วนการกำหนดคอลัมน์ที่จะใช้เป็นดัชนีก็ทำได้ด้วยการใส่ index_col เช่นกัน

ตัวอย่าง ลองใช้ select เพื่อดึงข้อมูลมา พร้อมทั้งใส่ where และ order by ดูเพื่อกำหนดเงื่อนไขและการเรียงลำดับ

pk = {'สายพันธุ์':['จูเพ็ตตา','โยมาวารุ','ซามาโยวรุ','โทรปิอุส'],
      'ส่วนสูง':[1.1,0.8,1.6,2],
      'น้ำหนัก':[12.5,35,30.6,100]}
pokedf = pd.DataFrame(pk,index=[354,355,356,357])
pokedf.to_sql('pokemon','sqlite:///pkdex.db',if_exists='replace')

sql = 'select * from pokemon where น้ำหนัก>32'
print(pd.read_sql_query(sql,'sqlite:///pkdex.db'))
sql = 'select สายพันธุ์,น้ำหนัก from pokemon order by น้ำหนัก desc'
print(pd.read_sql_query(sql,'sqlite:///pkdex.db'))
sql = 'select * from pokemon'
print(pd.read_sql_query(sql,'sqlite:///pkdex.db',index_col='index'))

ได้



หากในโค้ดมีการใช้เครื่องหมายคำถาม ? ซึ่งแทนตัวพารามิเตอร์ สามารถใส่ค่าลงไปได้โดยเติมลิสต์ของพารามิเตอร์ที่ต้องการแทนใส่ในคีย์เวิร์ด params เช่น

pk = {'สายพันธุ์':['ชิรีน','อับโซล','โซนาโน'],
      'ส่วนสูง':[0.6,1.2,0.6],
      'น้ำหนัก':[1,47,14]}
pokedf = pd.DataFrame(pk,index=pd.Series([358,359,360],name='เลข'))
pokedf.to_sql('pokemon','sqlite:///pkdex.db',if_exists='replace')

sql = 'select * from pokemon where สายพันธุ์==?'
print(pd.read_sql_query(sql,'sqlite:///pkdex.db',params=['ชิรีน']))

ได้



หรือใส่ในรูปของตัวแปรที่ชื่อขึ้นต้นด้วยโคลอน : ก็ได้ กรณีนี้ให้ใส่ params เป็นดิกชันนารี เช่น

sql = 'select * from pokemon where เลข=:lek'
print(pd.read_sql_query(sql,'sqlite:///pkdex.db',params={'lek': 359}))

ได้



นอกจากนี้มีฟังก์ชัน pd.read_sql() ซึ่งอาจใช้เพื่อแทน pd.read_sql_table() หรือ pd.read_sql_query() ได้

โดย pd.read_sql() จะดูจากค่าที่ใส่ไปเองว่าควรจะเรียก pd.read_sql_table() หรือ pd.read_sql_query() ถ้าใส่แค่ชื่อตารางก็จะเรียก pd.read_sql_table() แต่ถ้าใส่โค้ด sql ก็จะไปเรียก pd.read_sql_query()

ดังนั้นในทางปฏิบัติแล้วถ้าไม่อยากเขียนยาวจะใช้ pd.read_sql() อย่างเดียวตลอดก็ได้ ไม่ได้จำเป็นต้องใช้ pd.read_sql_table() หรือ pd.read_sql_query() โดยตรงเลย

ตัวอย่างการใช้ pd.read_sql()

pk = {'สายพันธุ์':['ยุกิวาราชิ','โอนิโกริ','ทามะซาราชิ'],
      'ส่วนสูง':[0.7,1.5,0.8],
      'น้ำหนัก':[16.8,256.5,39.5]}
pokedf = pd.DataFrame(pk,index=pd.Series([361,362,363],name='เลข'))
pokedf.to_sql('pokemon','sqlite:///pkdex.db',if_exists='replace')

print(pd.read_sql('pokemon','sqlite:///pkdex.db',columns=['เลข','สายพันธุ์']))
sql = 'select * from pokemon where น้ำหนัก<40'
print(pd.read_sql(sql,'sqlite:///pkdex.db'))

ได้



เช่นเดียวกับ .to_sql() ฟังก์ชัน pd.read_sql_table(), pd.read_sql_query() และ pd.read_sql() เองก็เดิมทีแล้วมีไว้ใช้กับตัวออบเจ็กต์เชื่อมต่อ (engine) ของ sqlalchemy เช่นกัน การเขียนแบบตัวอย่างที่ยกมาจึงเป็นแค่การเขียนย่อให้ง่าย

หากเขียนเต็มๆตั้งแต่สร้าง engine ของ sqlalchemy ก็อาจเขียนได้แบบนี้

import sqlalchemy

engine = sqlalchemy.create_engine('sqlite:///pkdex.db')
print(pd.read_sql('pokemon',engine))

นอกจากนี้ pd.read_sql_query() หรือ pd.read_sql() สามารถใช้กับมอดูล sqlite3 ได้ด้วย โดยใส่ตัวออบเจ็กต์เชื่อมต่อ (connect) ของ sqlite3 ลงไป โดยอาจเขียนแบบนี้

import sqlite3

conn = sqlite3.connect('pkdex.db')
print(pd.read_sql('select * from pokemon',conn))
conn.close()

เพียงแต่ .to_sql() กับ df.read_sql_table จะใช้ได้กับ sqlalchemy เท่านั้น ใช้กับ sqlite3 ไม่ได้



อ้างอิง

~ 自己紹介 ~

目次

日本による名言集

python モジュール -- numpy -- matplotlib -- pandas -- manim -- opencv -- pyqt -- pytorch 機械学習 -- ニューラル      ネットワーク

javascript

モンゴル語

言語学

maya

確率論

日本での日記

中国での日記 -- 北京での日記 -- 香港での日記 -- 澳門での日記

台灣での日記

北欧での日記

他の国での日記

qiita

その他の記事

記事の類別

==เลือกหมวด== ดาราศาสตร์ คณิตศาสตร์ -ความน่าจะเป็น คอมพิวเตอร์ -เขียนโปรแกรม --python ---numpy ---scipy ---matplotlib ---pandas ---manim ---pyqt ---sklearn ---pytorch ---mayapython --ruby --javascript --dart --MATLAB --SQL --regex --opencv -shell -3D --maya --MMD -microsoft_office -pdf -ปัญญาประดิษฐ์ --โครงข่ายประสาทเทียม --สเตเบิลดิฟฟิวชัน ---comfyui -การสุ่ม ภาษาศาสตร์ -ตัวอักษร -เรียนภาษา -หลักเกณฑ์การทับศัพท์ -ภาษาจีน --ภาษาจีนกลาง -ภาษาญี่ปุ่น -ภาษามองโกล -ภาษาลาว -ภาษาเขมร ประวัติศาสตร์ -ประวัติศาสตร์จีน -ประวัติศาสตร์ญี่ปุ่น ปรัชญา ประเทศจีน -จีนแผ่นดินใหญ่ --ปักกิ่ง --เทียนจิน --เหลียวหนิง --เหอเป่ย์ --เหอหนาน --ซานตง --ซานซี --อานฮุย --เจ้อเจียง --หูเป่ย์ --หูหนาน --ฝูเจี้ยน --กวางตุ้ง ---แต้จิ๋ว --ยูนนาน --ซินเจียง -ฮ่องกง -มาเก๊า -ไต้หวัน --ไทเป --จีหลง --เถาหยวน --ซินจู๋ --เหมียวลี่ --ไถจง --จางฮว่า --หยวินหลิน --เจียอี้ --ไถหนาน --เกาสยง --ผิงตง --อี๋หลาน ประเทศญี่ปุ่น -ฮกไกโด -อาโอโมริ -อิวาเตะ -มิยางิ -อากิตะ -ยามางาตะ -ฟุกุชิมะ -อิบารากิ -โทจิงิ -กุมมะ -ไซตามะ -จิบะ -โตเกียว -คานางาวะ -นีงาตะ -โทยามะ -อิชิกาวะ -ฟุกุอิ -ยามานาชิ -นางาโนะ -กิฟุ -ชิซึโอกะ -ไอจิ -มิเอะ -ชิงะ -เกียวโต -โอซากะ -เฮียวโงะ -นาระ -วากายามะ -โอกายามะ -ฮิโรชิมะ -ยามางุจิ -ฟุกุโอกะ -ซางะ -นางาซากิ -คุมาโมโตะ -โออิตะ ต่างแดน -อุษาคเนย์ --กัมพูชา --พม่า --สิงคโปร์ -ยุโรป --สวีเดน --เดนมาร์ก --ฟินแลนด์ -ออสเตรเลีย ท่องเที่ยว -มหาวิทยาลัย -พิพิธภัณฑ์ --พิพิธภัณฑ์วิทยาศาสตร์ --หอศิลป์ -สวนสัตว์ --พิพิธภัณฑ์สัตว์น้ำ -ท้องฟ้าจำลอง -ตึกระฟ้า -ปราสาท☑ --ปราสาทญี่ปุ่น --ปราสาทขอม --ปราสาทยุโรป -ศาสนสถาน --วัด --ศาลเจ้า --โบสถ์ --มัสยิด -สุสาน -มรดกโลก -ทะเล -ทะเลสาบ -ภูเขา -หิมะ -ดอกซากุระ -แมว -รถไฟ -เรือ -ตลาดกลางคืน -งานเทศกาล -ที่ระลึกภัยพิบัติ -ตามรอย บันเทิง -เกม --อาเตอลีเย --โปเกมอน --caligula --vn -อนิเมะ -มังงะ -นิยาย -เพลง --เพลงอนิเมะ --เพลงเกม เรื่องแต่ง บันทึก

　 記事を検索

　 最新記事

　 おすすめの記事

月別記事

もっと前の記事

ไทย

日本語

中文