Python - ฐานข้อมูลเชิงสัมพันธ์

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

การติดตั้ง SQLAlchemy

การติดตั้งเป็นอย่างตรงไปตรงมาโดยใช้งูที่เราได้กล่าวถึงในบทข้อมูลวิทยาศาสตร์สิ่งแวดล้อม สมมติว่าคุณได้ติดตั้ง Anaconda ตามที่อธิบายไว้ในบทนี้ให้รันคำสั่งต่อไปนี้ใน Anaconda Prompt Window เพื่อติดตั้งแพ็คเกจ SQLAlchemy

conda install sqlalchemy

การอ่านตารางเชิงสัมพันธ์

เราจะใช้ Sqlite3 เป็นฐานข้อมูลเชิงสัมพันธ์ของเราเนื่องจากมีน้ำหนักเบาและใช้งานง่าย แม้ว่าไลบรารี SQLAlchemy สามารถเชื่อมต่อกับแหล่งข้อมูลเชิงสัมพันธ์ที่หลากหลายรวมถึง MySql, Oracle และ Postgresql และ Mssql ก่อนอื่นเราสร้างเอ็นจิ้นฐานข้อมูลจากนั้นเชื่อมต่อกับเอ็นจินฐานข้อมูลโดยใช้ไฟล์to_sql ฟังก์ชันของไลบรารี SQLAlchemy

ในตัวอย่างด้านล่างเราสร้างตารางเชิงสัมพันธ์โดยใช้ to_sqlฟังก์ชันจาก dataframe ที่สร้างขึ้นแล้วโดยการอ่านไฟล์ csv จากนั้นเราใช้ไฟล์read_sql_query ฟังก์ชันจากแพนด้าเพื่อดำเนินการและจับผลลัพธ์จากการสืบค้น SQL ต่างๆ

from sqlalchemy import create_engine
import pandas as pd

data = pd.read_csv('/path/input.csv')

# Create the db engine
engine = create_engine('sqlite:///:memory:')

# Store the dataframe as a table
data.to_sql('data_table', engine)

# Query 1 on the relational table
res1 = pd.read_sql_query('SELECT * FROM data_table', engine)
print('Result 1')
print(res1)
print('')

# Query 2 on the relational table
res2 = pd.read_sql_query('SELECT dept,sum(salary) FROM data_table group by dept', engine)
print('Result 2')
print(res2)

เมื่อเรารันโค้ดด้านบนจะให้ผลลัพธ์ดังต่อไปนี้

Result 1
   index  id    name  salary  start_date        dept
0      0   1    Rick  623.30  2012-01-01          IT
1      1   2     Dan  515.20  2013-09-23  Operations
2      2   3   Tusar  611.00  2014-11-15          IT
3      3   4    Ryan  729.00  2014-05-11          HR
4      4   5    Gary  843.25  2015-03-27     Finance
5      5   6   Rasmi  578.00  2013-05-21          IT
6      6   7  Pranab  632.80  2013-07-30  Operations
7      7   8    Guru  722.50  2014-06-17     Finance

Result 2
         dept  sum(salary)
0     Finance      1565.75
1          HR       729.00
2          IT      1812.30
3  Operations      1148.00

การแทรกข้อมูลลงในตารางเชิงสัมพันธ์

นอกจากนี้เรายังสามารถแทรกข้อมูลลงในตารางเชิงสัมพันธ์โดยใช้ฟังก์ชัน sql.execute ที่มีอยู่ในแพนด้า ในโค้ดด้านล่างเราใช้ไฟล์ csv ก่อนหน้านี้เป็นชุดข้อมูลอินพุตเก็บไว้ในตารางเชิงสัมพันธ์จากนั้นแทรกระเบียนอื่นโดยใช้ sql.execute

from sqlalchemy import create_engine
from pandas.io import sql

import pandas as pd

data = pd.read_csv('C:/Users/Rasmi/Documents/pydatasci/input.csv')
engine = create_engine('sqlite:///:memory:')

# Store the Data in a relational table
data.to_sql('data_table', engine)

# Insert another row
sql.execute('INSERT INTO data_table VALUES(?,?,?,?,?,?)', engine, params=[('id',9,'Ruby',711.20,'2015-03-27','IT')])

# Read from the relational table
res = pd.read_sql_query('SELECT ID,Dept,Name,Salary,start_date FROM data_table', engine)
print(res)

เมื่อเรารันโค้ดด้านบนจะให้ผลลัพธ์ดังต่อไปนี้

id        dept    name  salary  start_date
0   1          IT    Rick  623.30  2012-01-01
1   2  Operations     Dan  515.20  2013-09-23
2   3          IT   Tusar  611.00  2014-11-15
3   4          HR    Ryan  729.00  2014-05-11
4   5     Finance    Gary  843.25  2015-03-27
5   6          IT   Rasmi  578.00  2013-05-21
6   7  Operations  Pranab  632.80  2013-07-30
7   8     Finance    Guru  722.50  2014-06-17
8   9          IT    Ruby  711.20  2015-03-27

การลบข้อมูลจากตารางเชิงสัมพันธ์

นอกจากนี้เรายังสามารถลบข้อมูลลงในตารางเชิงสัมพันธ์โดยใช้ฟังก์ชัน sql.execute ที่มีอยู่ในแพนด้า โค้ดด้านล่างจะลบแถวตามเงื่อนไขการป้อนข้อมูลที่กำหนด

from sqlalchemy import create_engine
from pandas.io import sql

import pandas as pd

data = pd.read_csv('C:/Users/Rasmi/Documents/pydatasci/input.csv')
engine = create_engine('sqlite:///:memory:')
data.to_sql('data_table', engine)

sql.execute('Delete from data_table where name = (?) ', engine,  params=[('Gary')])

res = pd.read_sql_query('SELECT ID,Dept,Name,Salary,start_date FROM data_table', engine)
print(res)

เมื่อเรารันโค้ดด้านบนจะให้ผลลัพธ์ดังต่อไปนี้

id        dept    name  salary  start_date
0   1          IT    Rick   623.3  2012-01-01
1   2  Operations     Dan   515.2  2013-09-23
2   3          IT   Tusar   611.0  2014-11-15
3   4          HR    Ryan   729.0  2014-05-11
4   6          IT   Rasmi   578.0  2013-05-21
5   7  Operations  Pranab   632.8  2013-07-30
6   8     Finance    Guru   722.5  2014-06-17