Database – Page 8 – CoP PSU IT Blog

วิธีเชื่อมต่อ PostgreSQL จาก python

January 25, 2019 09:41

ในการเชื่อมต่อกับ PostgreSQL จาก Python นั้น จะต้องใช้ Package ‘psycopg2’ คำสั่งในการติดตั้งคือ วิธีการเรียกใช้จาก python import numpy as np import pandas as pd import matplotlib.pyplot as plt import psycopg2 from IPython.display import display, HTML # used to print out pretty pandas dataframes import matplotlib.dates as dates import matplotlib.lines as mlines %matplotlib inline plt.style.use(‘ggplot’) # specify user/password/where the database is sqluser = ‘username’ sqlpass = ‘userpasswordgohere’ dbname = ‘dbname’ schema_name = ‘someschema’ host = ‘host.postgresql.server’ query_schema = ‘SET search_path to ‘ + schema_name + ‘;’ # connect to the database con = psycopg2.connect(dbname=dbname, user=sqluser, password=sqlpass, host=host) query = query_schema + ‘select * from sometable’ df = pd.read_sql_query(query,con) ก็จะได้ df เป็น Dataframe เอาไปใช้งานต่อได้ จบ

วิธีสร้าง User บน PostgreSQL ให้สามารถเข้าถึงฐานข้อมูลได้แบบ Read-Only

January 25, 2019 09:11

บันทึกเอาไว้สั้น ๆ ในที่นี้ ต้องการสร้างผู้ใช้ ‘username’ ให้มีรหัสผ่านเป็น ‘userpasswordgohere’ ให้สามารถใช้งาน ‘someschema’ โดยให้ใช้เฉพาะ ‘SELECT’ ได้กับทุก Tables ใน someschema บน PostgreSQL ใช้คำสั่งนี้ ในการสร้างและ Grant สิทธิ์ จบ

Oracle Label Security

August 6, 2018 15:41

Oracle Label Security (OLS) เป็นส่วนขยายของเทคโนโลยี Virtual Private Database (VPD) ซึ่งเริ่มนำมาใช้ตั้งแต่ Oracle 8i OLS อนุญาตให้มีการควบคุมการเข้าถึงลงในแถวต่างๆ ตามป้ายกำกับที่ระบุ ฟังก์ชันการทำงานที่คล้ายคลึงกันสามารถทำซ้ำโดยใช้ Fine Grained Access Control (FGAC) แต่ OLS ให้โซลูชันที่ง่ายกว่าสำหรับการรักษาความปลอดภัยระดับแถว (row-level security) ในบทความนี้ฉันจะนำเสนอตัวอย่างง่ายๆของการกำหนดค่า OLS ตั้งค่าฐานข้อมูล หากไม่ได้เลือกตัวเลือก Label Security เมื่อครั้งติดตั้ง Oracle Database สามารถเรียกตัวติดตั้งและเลือกตัวเลือก Label Security เพิ่มเติมได้ภายหลัง เข้าระบบด้วยบัญชีผู้ใช้ Oracle เปิด terminal พิมพ์ dbca (Database Server เป็น Linux) เมื่อได้ Welcome Screen คลิก Next ที่หน้า Operation คลิก Configure Database Options แล้วคลิก Next หน้า Database เลือกฐานข้อมูลที่ต้องการ คลิก Next ที่หน้า Database Features คลิกเลือก Label Security คลิก Next คลิก Finish เมื่อจบการตั้งค่าให้ restart database สร้างผู้ใช้ทดสอบ คำสั่งทั้งหมดทำภายใน sqlplus สร้างผู้ใช้ ols_test มีรหัสผ่านว่า password โดยให้สิทธิ์ CONNECT, RESOURCE, SELECT_CATALOG_ROLE sqlplus, / as sysdba CONN / AS SYSDBA CREATE USER ols_test IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO ols_test; ต่อไปเป็นการให้สิทธิ์ในแพ็คเกจ OLS แก่ผู้ใช้ ols_test จำเป็นต้อง unlock ผู้ใช้ lbacsys และตั้งรหัสผ่านว่า lbacsys เพื่อใช้เป็นคนกำหนดสิทธิ์ต่างๆ ให้ ols_test ได้แก่สิทธิ์ execute บน sa_components, sa_user_admin, sa_label_admin, sa_policy_admin, sa_audit_admin, sa_sysdba, to_lbac_data_label และให้ ols_test เป็น lbac_dba sqlplus, / as sysdba, lbacsys/lbacsys ALTER USER lbacsys IDENTIFIED BY lbacsys ACCOUNT UNLOCK; CONN lbacsys/lbacsys GRANT EXECUTE ON sa_components TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_user_admin TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_user_admin TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_label_admin TO ols_test WITH GRANT OPTION; GRANT EXECUTE ON sa_policy_admin TO

Transpose rows into columns using the Oracle PIVOT operator

June 26, 2018 13:47

ก่อนหน้านี้เราเคยนำเสนอบทความวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ ด้วยการใช้ฟังก์ชัน SUM และ DECODE กันมาแล้ววันนี้ลองมาใช้อีกวิธีในการแสดงผลดังกล่าวด้วย PIVOT ก่อนอื่นมาดูในส่วน syntax ของ PIVOT กันก่อน PIVOT Syntax SELECT * FROM ( SELECT column1, column2 FROM tables WHERE conditions ) PIVOT ( aggregate_function(column2) FOR column2 IN ( expr1, expr2, … expr_n) | subquery ) ORDER BY expression [ ASC | DESC ]; โดยที่ aggregate_function คือ aggregate function เช่น SUM, COUNT, MIN, MAX, หรือ AVG IN ( expr1, expr2, … expr_n ) คือ รายการค่าของ column2 ที่ต้องการจะแสดงในส่วนคอลัมภ์ คราวนี้ลองมาดูตัวอย่างกัน สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ โจทย์ เราต้องการจะแสดงจำนวนของนักศึกษาแยกตามคณะที่สังกัด โดยให้แสดงข้อมูลอยู่ในรูปแบบคอลัมภ์ ดังนั้นจึงเราสามารถเขียนคิวรีโดยใช้ PIVOT เพื่อแก้ปัญหาข้างต้นได้ดังนี้ SELECT * FROM (SELECT ‘TotalStudent’ fac_summary, fac_id FROM test_new_student) PIVOT (COUNT (fac_id) FOR fac_id IN (’06’ Engineering, ’08’ Science, ’12’ Natural_Resources)) ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์

Oracle : Transpose rows into columns using SUM and DECODE

June 26, 2018 13:17

วันนี้เราจะนำเสนอวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ โดยการประยุกต์ใช้ฟังก์ชัน SUM และ ฟังก์ชัน DECODE ก่อนอื่นจะขออธิบายในส่วนของฟังก์ชัน SUM และ DECODE กันก่อน ฟังก์ชัน SUM SUMจัดเป็นฟังก์ชันประเภท Aggregate Function ทำหน้าที่ในการคำนวณผลรวมของค่าในคอลัมภ์ มีรูปแบบการใช้งานดังตัวอย่างข้างล่างนี้ SELECT SUM(aggregate_expression) FROM tables [WHERE conditions]; โดยที่ aggregate_expression คือ คอลัมภ์ที่ต้องการค่าผลรวม โดยที่ข้อมูลในคอลัมภ์จะต้องเป็นชนิดตัวเลขเท่านั้น มาดูตัวอย่างการใช้งานกัน SELECT fac_id, SUM (eng_score) sum_eng_score FROM test_new_student GROUP BY fac_id; ผลลัพธ์ที่ได้ : แสดงผลรวมของคะแนนภาษาอังกฤษของแต่ละคณะ ฟังก์ชัน DECODE DECODE เป็นการเขียนเงื่อนไขบนชุดคำสั่ง select โดยมีลักษณะการทำงานเหมือน IF-THEN-ELSE โดยถ้าเข้าเงื่อนไขที่กำหนด ต้องการให้ทำอะไรก็ให้ระบุลงไป Syntax: DECODE( expression , search , result [, search , result]… [, default] ) โดยที่ expression คือ ค่าที่จะเอามาใช้เปรียบเทียบเงื่อนไข compare_value คือ ค่าที่สามารถจะมาเปรียบเทียบได้กับ expression return_value คือค่าที่จะแสดงออกมาเมื่อ ค่าของ expression = compare_value default_return_value คือค่าที่จะแสดง เมื่อ expression <> compare_value ลองมาดูตัวอย่างการใช้งานกัน สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ ตัวอย่างคิวรีในการใช้ฟังก์ชัน DECODE SELECT fac_id, DECODE ( fac_id, ’06’, ‘คณะวิศวกรรมศาสตร์’, ’08’, ‘คณะวิทยาศาสตร์’, ’12’, ‘คณะทรัพยากรธรรมชาติ’,’null’) fac_name,stud_name_thai,stud_sname_thai,sn_code,eng_score FROM test_new_student; ผลลัพธ์ที่ได้ โดยความหมายของด้านบนคือ ถ้าค่า fac_id ของตาราง test_new_student เป็น ’06’ ก็ให้แสดง คำว่า ‘คณะวิศวกรรมศาสตร์’ ถ้าค่า fac_id ของตาราง test_new_student เป็น ’08’ ก็ให้แสดง คำว่า ‘คณะคณะวิทยาศาสตร์’ ถ้าค่า fac_id ของตาราง test_new_student เป็น ’12’ ก็ให้แสดง คำว่า ‘คณะทรัพยากรธรรมชาติ’ โดยปกติถ้าเราเขียนคิวรีเพื่อนับจำนวนนักศึกษาโดยแยกตามคณะที่สังกัดของนักศึกษา เราสามารถเขียนคิวรีได้ดังนี้ SELECT fac_id, COUNT (*) fac_summary FROM test_new_student GROUP BY fac_id; ผลลัพธ์ที่ได้ : แสดงจำนวนนักศึกษาแยกตามคณะที่สังกัด การแสดงผลปกติจะแสดงในรูปแบบแถว คราวนี้ลองมาตั้งโจทย์ใหม่โดยยังมีความต้องการเหมือนเดิมคือ นับจำนวนนักศึกษาโดยแยกตามคณะที่สังกัดของนักศึกษา แต่เปลี่ยนจากการแสดงในรูปแบบแถวให้แสดงในรูปแบบคอลัมภ์แทน จากโจทย์เราสามารถประยุกต์ใช้งานฟังก์ชัน sum และ decode เพื่อจะแปลงข้อมูลในรูปแบบแถวให้อยู่ในรูปแบบ คอลัมภ์ได้ โดยเราสามารถเขียนคิวรีได้ดังนี้ SELECT ‘TotalStudent’ faculty, SUM (DECODE (fac_id, ’06’, 1)) Engineering, SUM (DECODE (fac_id, ’08’, 1)) Science, SUM (DECODE (fac_id, ’12’, 1)) Natural_Resources FROM test_new_student ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์