การอ่านและเขียนไฟล์ SQL ใน Pandas

เมื่อฉันเริ่มเรียนรู้การวิเคราะห์ข้อมูลเมื่อไม่กี่ปีที่ผ่านมา สิ่งแรกที่ฉันเรียนรู้คือ SQL และ Pandas ในฐานะนักวิเคราะห์ข้อมูล การมีพื้นฐานที่แข็งแกร่งในการทำงานกับ SQL และ Pandas เป็นสิ่งสำคัญอย่างยิ่ง ทั้งสองเป็นเครื่องมืออันทรงพลังที่ช่วยให้นักวิเคราะห์ข้อมูลวิเคราะห์และจัดการข้อมูลที่จัดเก็บไว้ในฐานข้อมูลได้อย่างมีประสิทธิภาพ

ภาพรวมของ SQL และ Pandas

SQL (Structured Query Language) เป็นภาษาโปรแกรมที่ใช้ในการจัดการและจัดการฐานข้อมูลเชิงสัมพันธ์ ในทางกลับกัน Pandas เป็นไลบรารี Python ที่ใช้สำหรับจัดการและวิเคราะห์ข้อมูล

การวิเคราะห์ข้อมูลเกี่ยวข้องกับการทำงานกับข้อมูลจำนวนมาก และมักใช้ฐานข้อมูลเพื่อจัดเก็บข้อมูลนี้ SQL และ Pandas มีเครื่องมือที่มีประสิทธิภาพสำหรับการทำงานกับฐานข้อมูล ช่วยให้นักวิเคราะห์ข้อมูลสามารถแยก จัดการ และวิเคราะห์ข้อมูลได้อย่างมีประสิทธิภาพ ด้วยการใช้ประโยชน์จากเครื่องมือเหล่านี้ นักวิเคราะห์ข้อมูลสามารถได้รับข้อมูลเชิงลึกอันมีค่าจากข้อมูลที่อาจจะได้มาได้ยาก

ในบทความนี้ เราจะสำรวจวิธีใช้ SQL และ Pandas เพื่ออ่านและเขียนลงในฐานข้อมูล

กำลังเชื่อมต่อกับฐานข้อมูล

การติดตั้งไลบรารี

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

เราสามารถติดตั้งไลบรารีทั้งสองได้โดยใช้ตัวจัดการแพ็คเกจ Python, pip โดยเรียกใช้คำสั่งต่อไปนี้ที่พรอมต์คำสั่ง

$ pip install pandas
$ pip install sqlalchemy

ทำการเชื่อมต่อ

เมื่อติดตั้งไลบรารีแล้ว ตอนนี้เราสามารถใช้ Pandas เพื่อเชื่อมต่อกับฐานข้อมูล SQL

ในการเริ่มต้น เราจะสร้างวัตถุเครื่องมือ SQLAlchemy ด้วย create_engine(). create_engine() ฟังก์ชั่นเชื่อมต่อรหัส Python กับฐานข้อมูล ใช้เป็นอาร์กิวเมนต์สตริงการเชื่อมต่อที่ระบุประเภทฐานข้อมูลและรายละเอียดการเชื่อมต่อ ในตัวอย่างนี้ เราจะใช้ประเภทฐานข้อมูล SQLite และเส้นทางของไฟล์ฐานข้อมูล

สร้างวัตถุเครื่องยนต์สำหรับฐานข้อมูล SQLite โดยใช้ตัวอย่างด้านล่าง:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db')

หากไฟล์ฐานข้อมูล SQLite, student.db ในกรณีของเรา, อยู่ในไดเร็กทอรีเดียวกันกับสคริปต์ Python เราสามารถใช้ชื่อไฟล์ได้โดยตรงดังที่แสดงด้านล่าง

engine = create_engine('sqlite:///student.db')

การอ่านไฟล์ SQL ด้วย Pandas

มาอ่านข้อมูลกันเถอะ เมื่อเราสร้างการเชื่อมต่อแล้ว ในส่วนนี้เราจะดูที่ read_sql, read_sql_tableและ read_sql_query ฟังก์ชั่นและวิธีการใช้งานเพื่อทำงานกับฐานข้อมูล

ดำเนินการค้นหา SQL โดยใช้ Panda's read_sql() ฟังก์ชัน

พื้นที่ read_sql() เป็นฟังก์ชันไลบรารีของ Pandas ที่ช่วยให้เราสามารถเรียกใช้แบบสอบถาม SQL และดึงผลลัพธ์ไปยังดาต้าเฟรมของ Pandas เดอะ read_sql() ฟังก์ชันเชื่อมต่อ SQL และ Python ทำให้เราสามารถใช้ประโยชน์จากพลังของทั้งสองภาษาได้ ฟังก์ชั่นล้อมรอบ read_sql_table() และ read_sql_query(). read_sql() ฟังก์ชันถูกกำหนดเส้นทางภายในตามอินพุตที่มีให้ ซึ่งหมายความว่าหากอินพุตต้องการดำเนินการสืบค้น SQL ฟังก์ชันจะถูกส่งไปยัง read_sql_query()และถ้าเป็นตารางฐานข้อมูลก็จะถูกกำหนดเส้นทางไปที่ read_sql_table().

พื้นที่ read_sql() ไวยากรณ์เป็นดังนี้:

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

จำเป็นต้องมีพารามิเตอร์ SQL และ con; ส่วนที่เหลือเป็นทางเลือก อย่างไรก็ตาม เราสามารถจัดการผลลัพธ์ได้โดยใช้พารามิเตอร์ทางเลือกเหล่านี้ มาดูพารามิเตอร์แต่ละตัวให้ละเอียดยิ่งขึ้น

• sql: แบบสอบถาม SQL หรือชื่อตารางฐานข้อมูล
• con: วัตถุการเชื่อมต่อหรือ URL การเชื่อมต่อ
• index_col: พารามิเตอร์นี้ช่วยให้เราสามารถใช้หนึ่งหรือหลายคอลัมน์จากผลการสืบค้น SQL เป็นดัชนีกรอบข้อมูล อาจใช้คอลัมน์เดียวหรือรายการของคอลัมน์ก็ได้
• coerce_float: พารามิเตอร์นี้ระบุว่าควรแปลงค่าที่ไม่ใช่ตัวเลขเป็นตัวเลขลอยตัวหรือปล่อยให้เป็นสตริง มันถูกตั้งค่าเป็นจริงตามค่าเริ่มต้น หากเป็นไปได้ ระบบจะแปลงค่าที่ไม่ใช่ตัวเลขเป็นประเภททศนิยม
• params: พารามิเตอร์จัดเตรียมวิธีการที่ปลอดภัยสำหรับการส่งค่าไดนามิกไปยังแบบสอบถาม SQL เราสามารถใช้พารามิเตอร์ params เพื่อส่งผ่านพจนานุกรม ทูเพิล หรือรายการ ไวยากรณ์ของพารามิเตอร์แตกต่างกันไปขึ้นอยู่กับฐานข้อมูล
• parse_dates: สิ่งนี้ช่วยให้เราสามารถระบุได้ว่าคอลัมน์ใดในดาต้าเฟรมผลลัพธ์จะถูกตีความเป็นวันที่ โดยจะยอมรับคอลัมน์เดียว รายการของคอลัมน์ หรือพจนานุกรมที่มีคีย์เป็นชื่อคอลัมน์และค่าเป็นรูปแบบคอลัมน์
• columns: สิ่งนี้ช่วยให้เราดึงเฉพาะคอลัมน์ที่เลือกจากรายการ
• chunksize: เมื่อทำงานกับชุดข้อมูลขนาดใหญ่ chunksize เป็นสิ่งสำคัญ เรียกผลลัพธ์คิวรีในส่วนที่เล็กลง ซึ่งช่วยเพิ่มประสิทธิภาพ

นี่คือตัวอย่างวิธีใช้ read_sql():

รหัส:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql("SELECT * FROM Student", engine, index_col='Roll Number', parse_dates='dateOfBirth')
print(df)
print("The Data type of dateOfBirth: ", df.dateOfBirth.dtype) engine.dispose()

Output:

 firstName lastName email dateOfBirth
rollNumber
1 Mark Simson  2000-02-23
2 Peter Griffen  2001-04-15
3 Meg Aniston  2001-09-20
Date type of dateOfBirth: datetime64[ns]

หลังจากเชื่อมต่อกับฐานข้อมูลแล้ว เราจะดำเนินการค้นหาที่ส่งคืนระเบียนทั้งหมดจาก Student ตารางและเก็บไว้ใน DataFrame df. คอลัมน์ “Roll Number” ถูกแปลงเป็นดัชนีโดยใช้ index_col พารามิเตอร์ และประเภทข้อมูล “dateOfBirth” คือ “datetime64[ns]” เนื่องจาก parse_dates. เราสามารถใช้ read_sql() ไม่เพียงดึงข้อมูล แต่ยังดำเนินการอื่นๆ เช่น แทรก ลบ และอัปเดต read_sql() เป็นฟังก์ชันทั่วไป

กำลังโหลดตารางหรือมุมมองเฉพาะจากฐานข้อมูล

กำลังโหลดตารางหรือมุมมองเฉพาะด้วย Pandas read_sql_table() เป็นอีกหนึ่งเทคนิคในการอ่านข้อมูลจากฐานข้อมูลเข้าสู่ Pandas dataframe

ความหมายของ read_sql_table?

ห้องสมุด Pandas ให้บริการ read_sql_table ฟังก์ชันซึ่งออกแบบมาโดยเฉพาะเพื่ออ่านตาราง SQL ทั้งหมดโดยไม่ต้องดำเนินการค้นหาใดๆ และส่งคืนผลลัพธ์เป็นดาต้าเฟรมของ Pandas

ไวยากรณ์ของ read_sql_table() เป็นดังต่อไปนี้:

pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

ยกเว้น table_name และสคีมาพารามิเตอร์จะอธิบายในลักษณะเดียวกับ read_sql().

• table_name: พารามิเตอร์ table_name คือชื่อของตาราง SQL ในฐานข้อมูล
• schema: พารามิเตอร์ทางเลือกนี้เป็นชื่อของสกีมาที่มีชื่อตาราง

หลังจากสร้างการเชื่อมต่อกับฐานข้อมูลแล้ว เราจะใช้ read_sql_table ฟังก์ชั่นโหลด Student ตารางลงใน Pandas DataFrame

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Student', engine)
print(df.head()) engine.dispose()

Output:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson  2000-02-23
1 2 Peter Griffen  2001-04-15
2 3 Meg Aniston  2001-09-20

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

รหัส:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df_iterator = pd.read_sql_table('Student', engine, chunksize = 1) for df in df_iterator: print(df.head()) engine.dispose()

Output:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson  2000-02-23
0 2 Peter Griffen  2001-04-15
0 3 Meg Aniston  2001-09-20

โปรดทราบว่า chunksize ฉันใช้นี่คือ 1 เพราะฉันมีเพียง 3 ระเบียนในตารางของฉัน

การสืบค้นฐานข้อมูลโดยตรงด้วยไวยากรณ์ SQL ของ Pandas

การดึงข้อมูลเชิงลึกจากฐานข้อมูลเป็นส่วนสำคัญสำหรับนักวิเคราะห์ข้อมูลและนักวิทยาศาสตร์ ในการทำเช่นนั้น เราจะใช้ประโยชน์จาก read_sql_query() ฟังก์ชัน

read_sql_query() คืออะไร

การใช้แพนด้า read_sql_query() เราสามารถเรียกใช้แบบสอบถาม SQL และรับผลลัพธ์โดยตรงไปยัง DataFrame เดอะ read_sql_query() ฟังก์ชั่นถูกสร้างขึ้นโดยเฉพาะสำหรับ SELECT งบ ไม่สามารถใช้สำหรับการดำเนินการอื่น ๆ เช่น DELETE or UPDATE.

ไวยากรณ์:

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None, dtype=None, dtype_backend=_NoDefault.no_default)

คำอธิบายพารามิเตอร์ทั้งหมดเหมือนกันกับ read_sql() การทำงาน. นี่คือตัวอย่างของ read_sql_query():

รหัส:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_query('Select firstName, lastName From Student Where rollNumber = 1', engine)
print(df) engine.dispose()

Output:

 firstName lastName
0 Mark Simson

การเขียนไฟล์ SQL ด้วย Pandas

ขณะวิเคราะห์ข้อมูล สมมติว่าเราพบว่าจำเป็นต้องแก้ไขรายการบางรายการ หรือจำเป็นต้องมีตารางหรือมุมมองใหม่ที่มีข้อมูลดังกล่าว หากต้องการอัปเดตหรือแทรกระเบียนใหม่ วิธีหนึ่งคือการใช้ read_sql() และเขียนแบบสอบถาม อย่างไรก็ตาม วิธีการดังกล่าวอาจใช้เวลานาน หมีแพนด้ามีวิธีการที่ยอดเยี่ยมที่เรียกว่า to_sql() สำหรับสถานการณ์เช่นนี้

ในส่วนนี้ เราจะสร้างตารางใหม่ในฐานข้อมูลก่อน แล้วจึงแก้ไขตารางที่มีอยู่

การสร้างตารางใหม่ในฐานข้อมูล SQL

ก่อนที่เราจะสร้างตารางใหม่ เรามาคุยกันก่อน to_sql() ในรายละเอียด.

ความหมายของ to_sql()?

พื้นที่ to_sql() ฟังก์ชันของไลบรารี Pandas ช่วยให้เราสามารถเขียนหรืออัปเดตฐานข้อมูลได้ เดอะ to_sql() ฟังก์ชันสามารถบันทึกข้อมูล DataFrame ลงในฐานข้อมูล SQL

ไวยากรณ์สำหรับ to_sql():

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

เหลือเพียง name และ con พารามิเตอร์จำเป็นต้องเรียกใช้ to_sql(); อย่างไรก็ตาม พารามิเตอร์อื่นๆ ให้ความยืดหยุ่นและตัวเลือกการปรับแต่งเพิ่มเติม เรามาพูดถึงรายละเอียดแต่ละพารามิเตอร์กัน:

• name: ชื่อของตาราง SQL ที่จะสร้างหรือแก้ไข
• con: วัตถุการเชื่อมต่อของฐานข้อมูล
• schema: สคีมาของตาราง (ไม่บังคับ)
• if_exists: ค่าเริ่มต้นของพารามิเตอร์นี้คือ "ล้มเหลว" พารามิเตอร์นี้ช่วยให้เราสามารถตัดสินใจดำเนินการหากมีตารางอยู่แล้ว ตัวเลือกประกอบด้วย "ล้มเหลว" "แทนที่" และ "ต่อท้าย"
• index: พารามิเตอร์ดัชนียอมรับค่าบูลีน ตามค่าเริ่มต้น จะถูกตั้งค่าเป็น True ซึ่งหมายความว่าดัชนีของ DataFrame จะถูกเขียนลงในตาราง SQL
• index_label: พารามิเตอร์ทางเลือกนี้ช่วยให้เราสามารถระบุป้ายชื่อคอลัมน์สำหรับคอลัมน์ดัชนี ตามค่าเริ่มต้น ดัชนีจะถูกเขียนลงในตาราง แต่สามารถกำหนดชื่อเฉพาะได้โดยใช้พารามิเตอร์นี้
• chunksize: จำนวนแถวที่จะเขียนต่อครั้งในฐานข้อมูล SQL
• dtype: พารามิเตอร์นี้ยอมรับพจนานุกรมที่มีคีย์เป็นชื่อคอลัมน์และค่าเป็นประเภทข้อมูล
• method: พารามิเตอร์ method อนุญาตให้ระบุเมธอดที่ใช้สำหรับการแทรกข้อมูลลงใน SQL โดยค่าเริ่มต้น จะถูกตั้งค่าเป็นไม่มี ซึ่งหมายความว่าแพนด้าจะค้นหาวิธีที่มีประสิทธิภาพสูงสุดตามฐานข้อมูล มีสองตัวเลือกหลักสำหรับพารามิเตอร์เมธอด:
  • multi: อนุญาตให้แทรกหลายแถวในแบบสอบถาม SQL เดียว อย่างไรก็ตาม ไม่ใช่ทุกฐานข้อมูลที่รองรับการแทรกแบบหลายแถว
  • ฟังก์ชันที่เรียกได้: ที่นี่ เราสามารถเขียนฟังก์ชันแบบกำหนดเองสำหรับการแทรกและเรียกใช้โดยใช้พารามิเตอร์เมธอด

นี่คือตัวอย่างการใช้ to_sql():

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') data = {'Name': ['Paul', 'Tom', 'Jerry'], 'Age': [9, 8, 7]}
df = pd.DataFrame(data) df.to_sql('Customer', con=engine, if_exists='fail') engine.dispose()

ตารางใหม่ชื่อลูกค้าจะถูกสร้างขึ้นในฐานข้อมูล โดยมีสองฟิลด์ชื่อ "ชื่อ" และ "อายุ"

ภาพรวมของฐานข้อมูล:

การอัปเดตตารางที่มีอยู่ด้วย Pandas Dataframes

การอัปเดตข้อมูลในฐานข้อมูลเป็นงานที่ซับซ้อน โดยเฉพาะอย่างยิ่งเมื่อต้องจัดการกับข้อมูลขนาดใหญ่ อย่างไรก็ตาม การใช้ to_sql() ฟังก์ชั่นใน Pandas สามารถทำให้งานนี้ง่ายขึ้นมาก หากต้องการอัปเดตตารางที่มีอยู่ในฐานข้อมูล ให้ to_sql() สามารถใช้ฟังก์ชั่นกับ if_exists ตั้งค่าพารามิเตอร์เป็น "แทนที่" สิ่งนี้จะเขียนทับตารางที่มีอยู่ด้วยข้อมูลใหม่

นี่คือตัวอย่างของ to_sql() ที่อัปเดตที่สร้างไว้ก่อนหน้านี้ Customer โต๊ะ. สมมติว่าใน Customer ตาราง เราต้องการอัปเดตอายุของลูกค้าชื่อ Paul จาก 9 เป็น 10 ในการดำเนินการนั้น ก่อนอื่น เราสามารถแก้ไขแถวที่เกี่ยวข้องใน DataFrame จากนั้นใช้ to_sql() ฟังก์ชั่นอัพเดทฐานข้อมูล

รหัส:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Customer', engine) df.loc[df['Name'] == 'Paul', 'Age'] = 10 df.to_sql('Customer', con=engine, if_exists='replace') engine.dispose()

ในฐานข้อมูล มีการอัพเดทอายุของ Paul:

สรุป

สรุปได้ว่า Pandas และ SQL เป็นเครื่องมือที่มีประสิทธิภาพสำหรับงานวิเคราะห์ข้อมูล เช่น การอ่านและเขียนข้อมูลไปยังฐานข้อมูล SQL Pandas มีวิธีง่ายๆ ในการเชื่อมต่อกับฐานข้อมูล SQL อ่านข้อมูลจากฐานข้อมูลไปยัง Pandas dataframe และเขียนข้อมูล dataframe กลับไปที่ฐานข้อมูล

ไลบรารี Pandas ช่วยให้จัดการข้อมูลใน dataframe ได้ง่าย ในขณะที่ SQL จัดเตรียมภาษาที่มีประสิทธิภาพสำหรับการสืบค้นข้อมูลในฐานข้อมูล การใช้ทั้ง Pandas และ SQL เพื่ออ่านและเขียนข้อมูลสามารถประหยัดเวลาและความพยายามในการวิเคราะห์ข้อมูล โดยเฉพาะอย่างยิ่งเมื่อข้อมูลมีขนาดใหญ่มาก โดยรวมแล้ว การใช้ประโยชน์จาก SQL และ Pandas ร่วมกันสามารถช่วยนักวิเคราะห์ข้อมูลและนักวิทยาศาสตร์ปรับปรุงเวิร์กโฟลว์ของพวกเขาได้

• เนื้อหาที่ขับเคลื่อนด้วย SEO และการเผยแพร่ประชาสัมพันธ์ รับการขยายวันนี้
• เพลโตไอสตรีม. ข้อมูลอัจฉริยะ Web3 ขยายความรู้ เข้าถึงได้ที่นี่.
• การสร้างอนาคตโดย Adryenn Ashley เข้าถึงได้ที่นี่.
• ซื้อและขายหุ้นในบริษัท PRE-IPO ด้วย PREIPO® เข้าถึงได้ที่นี่.
• ที่มา: https://stackabuse.com/reading-and-writing-sql-files-in-pandas/