[python3] postgresql 예제

먼저 psycopg2을 설치 한다.

※ 혹시 CentOS 7 에서 psycopg2를 설치할 때 오류가 발생한 다면 아래의 방법을 시도해 보자

 

CentOS7에서 psycopg2 설치 오류 해결

$ pip install psycopg2
Collecting psycopg2
  Downloading psycopg2-2.9.3.tar.gz (380 kB)
     |████████████████████████████████| 380 kB 20.7 MB/s            
  Preparing metadata (setup.py) ... done
Building wheels for collected packages: psycopg2
  Building wheel for psycopg2 (setup.py) ... done
  Created wheel for psycopg2: filename=psycopg2-2.9.3-cp36-cp36m-linux_x86_64.whl size=403086 sha256=1650e6aa9fae31d2b8e45624ef481491f2901ab0101b53bcbae0a8228bc7ce11
  Stored in directory: /root/.cache/pip/wheels/25/89/7b/ebfb85abf80609378825b3bb975007b41fa51f31c98377914c
Successfully built psycopg2
Installing collected packages: psycopg2
Successfully installed psycopg2-2.9.3

 

아래는 psycopg2를 사용하여 postgresql DB에 데이터를 추가하고 조회하는 예제이다.

import psycopg2
  
try:
	conn = pymysql.connect(
		host='127.0.0.1',
		user='user',
		password='passwd',
		dbname='dbname',
		port='5432')
	cursor = conn.cursor()
    
	# 데이터 추가
	cursor.execute("""
		INSERT INTO users (id, name)
		VALUES (%s, %s)
	""", ('gdhong', '홍길동'))
	conn.commit()
    
	# 데이터 조회
	cursor.execute("""
		SELECT	id, name
		FROM		users
		WHERE		id = %s
	""", ('gdhong'))
	result = cur.fetchall()
	
finally:
	cursor.close()
	conn.close()