Tag: postgresql

  • ฟังก์ชัน array_agg และ array_to_string ใน PostgreSQL 

    assadayut.s

    ผู้เขียนได้มีโอกาสพัฒนาโปรเจ็คใหม่ที่จะต้องไปใช้งานฐานข้อมูล PostgreSQL ซึ่งไม่เคยใช้งานมาก่อน โดยคำสั่งพื้นฐานทั่วไปก็จะเหมือนๆ กัน ตามมาตรฐานคำสั่ง SQL แต่ก็มีบางฟังก์ชันที่มีเฉพาะเจาะจงแตกต่างกันไปในแต่ละฐานข้อมูล อย่างเช่นที่จะกล่าวถึงในบล็อกนี้

    โดยโจทย์ก็คือ ต้องการรวมข้อมูลในฟิลด์เดียวกันที่อยู่กันคนละเรคคอร์ด ให้มาเชื่อมต่อกัน เช่น

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

    “ปัตตานี” “หนองจิก, โคกโพธิ์”
    “ยะลา”“เบตง, ธารโต, รามัน”
    “สงขลา”“กระแสสินธุ์, หาดใหญ่, คลองหอยโข่ง”

    ซึ่งภายใน PostgreSQL เราจะใช้งานฟังก์ชัน array_agg และ array_to_string

    array_agg(expression [order by [sort_expression {asc | desc}], [...])

    โดย

    expression จะเป็นฟิลด์ หรือกลุ่มฟิลด์ที่จะดึงมารวมเข้าไว้ด้วยกัน

    sort_expression ฟิลด์ที่จะใช้ในการเรียง

    array_to_string(anyarray, delimeter [, null_string])

    โดย

    anyarray ข้อมูลในรูปแบบ array

    delimeter ตัวอักษรหรือสัญลักษณ์ที่ใช้คั่นระหว่างข้อมูลที่มาเชื่อมต่อกัน

    null_string ตัวอักษรหรือสัญลักณ์ที่จะใช้แสดงเมื่อ element ใน array มีค่าเป็น null

    ตัวอย่างคำสั่ง

    select province, array_agg (amphoe) amphoe
from test
group by province

    ซึ่งจะได้ผลลัพธ์ดังรูป

    เนื่องจากจะเป็นการรวมข้อมูลในแต่ละจังหวัด เราก็เลยจะต้องใช้ฟังก์ชัน group by ร่วมด้วย

    จากผลลัพธ์จะเห็นว่าเราจะได้ข้อมูลที่ใกล้เคียงกับที่เราต้องการแล้ว เพียงแต่ข้อมูลอำเภอจะยังอยู่ในรูปแบบ array เพราะฉะนั้น เราจะต้องใช้ฟังก์ชัน array_to_string มาช่วยแปลงข้อมูล array มาอยู่ในรูปแบบ string ตามที่เราต้องการ ดังตัวอย่าง

    select province, array_to_string(array_agg(amphoe), ', ') amphoe
from test
group by province

    จะได้ผลลัพธ์ดังภาพ

    ซึ่งบทความนี้เป็นเพียงหนึ่งตัวอย่างของฟังก์ชันของ PostgreSQL ที่แตกต่างจากฐานข้อมูลอื่นๆ ซึ่งเราก็จะต้องศึกษาเรียนรู้กันต่อไป ถ้าผู้เขียนมีโอกาสได้ใช้งานฟังก์ชันหรือฟีเจอร์อะไรแปลกใหม่ ก็จะพยามนำมาบันทึกไว้ ในโอกาสต่อๆ ไป

    ขอบคุณครับ

    ข้อมูลอ้างอิง

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

    kanakorn.h

    ในการเชื่อมต่อกับ PostgreSQL จาก Python นั้น จะต้องใช้ Package ‘psycopg2’

    คำสั่งในการติดตั้งคือ

    pip install 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

    kanakorn.h

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

    บน PostgreSQL ใช้คำสั่งนี้ ในการสร้างและ Grant สิทธิ์

    CREATE USER username WITH ENCRYPTED PASSWORD 'userpasswordgohere';
GRANT USAGE ON SCHEMA someschema TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA someschema TO username;

    จบ