python - postgresql 접속하기

postgresql 접속하기

환경 : windows 7 64bit, python 2.7.9 64bit, postgresql 9.4.2, psycopg2 2.6

참고 : http://zetcode.com/db/postgresqlpythontutorial/

       http://www.stickpeople.com/projects/python/win-psycopg/

       http://pythonhosted.org/psycopg2/usage.html

       http://initd.org/psycopg/docs/usage.html#with-statement

참고서적 : https://www.packtpub.com/big-data-and-business-intelligence/postgresql-cookbook  chapter 11.

** postgresql 과 oracle 은 한번 연결에 하나의 database 만 사용할 수 있다.

  -- http://www.sqlines.com/postgresql/pgadmin/change_database

1. psycopg2 설치하기

    다운로드 사이트 : http://www.stickpeople.com/projects/python/win-psycopg/

    에서 psycopg2-2.6.0.win-amd64-py2.7-pg9.4.1-release.exe 파일 다운받아 설치하면 끝.

2. psycopg2 를 이용하여 postgresql db 접속하기

  -- 2 가지 방법을 가능.

  -- http://pythonhosted.org/psycopg2/module.html

     ; host = postgresql server ip 

     ; dbname = database = 접속할 postgresql database name

     ; user = postgresql 에 접속할 user name

     ; password = 접속할 user 의 password

     ; port = postgresql server port ( 생략하면 5432)

	# -*- coding: utf-8 -*-
	import psycopg2 as pg2
	conn = ppg2.connect("host = localhost dbname=test user=postgres password=1234 port=5432")

view raw pg_connect.py hosted with ❤ by GitHub

	# -*- coding: utf-8 -*-
	import psycopg2 as pg2
	# conn = pg2.connect("host = localhost dbname=test user=postgres password=1234 port=5432")
	conn=pg2.connect(database="test",user="postgres",password="1234",host="127.0.0.1",port="5432")

view raw pg_connect2.py hosted with ❤ by GitHub

3. database 만들고, table 만들기

	# -*- coding: utf-8 -*-
	import psycopg2 as pg2
	try:
	# port 기본값 = 5432
	conn = pg2.connect("host = localhost dbname=postgres user=postgres password=1234")
	# autocommit 없으면, InternalError: CREATE DATABASE cannot run inside a transaction block
	conn.autocommit = True
	cur = conn.cursor()
	# database 만들기
	cur.execute('create database testdb') # superuser 만 create database 명령어 가능..
	cur.execute('SELECT version()')
	ver = cur.fetchone()
	except Exception as e:
	print 'postgresql database connection error!'
	print e
	else:
	print ver
	finally:
	if conn:
	conn.close()
	try:
	# 다른 db 로 바꿀려면 재접속 해야함...
	conn = pg2.connect("host = localhost dbname=testdb user=postgres password=1234")
	cur = conn.cursor()
	# table 만들기
	cur.execute("CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(20), Price INT)")
	# data insert
	cur.execute("INSERT INTO Cars VALUES(1,'Audi',52642)")
	cur.execute("INSERT INTO Cars VALUES(2,'Mercedes',57127)")
	cur.execute("SELECT * FROM Cars")
	rows = cur.fetchall()
	conn.commit()
	except Exception as e:
	print 'postgresql database connection error!'
	print e
	if conn:
	conn.rollback()
	else:
	print rows
	finally:
	if conn:
	conn.close()

view raw pgsql.py hosted with ❤ by GitHub

4. with 문 사용하기

    ; http://initd.org/psycopg/docs/usage.html#with-statement 

    ; psycopg2 2.5 부터 사용 가능.

  -- connection 이 with 문을 벗어날때, 예외가 없으면 commit 발생하고, 예외시에는 rollback 발생. 

      connection close 발생 안함.

  -- cursor 가 with 문 벗어날때, resource 해제하며(cursor.close()), transaction 상태는 바뀌지 않는다.

	# -*- coding: utf-8 -*-
	import psycopg2 as pg2
	conn = None
	try:
	with pg2.connect("host = localhost dbname=test_db user=postgres password=1234") as conn:
	with conn.cursor() as cur:
	sql = "create table Car(Id integer primary key, Name varchar(20), Price int)"
	cur.execute(sql)
	cur.execute("INSERT INTO Car VALUES(1,'Audi',52642)")
	cur.execute("INSERT INTO Car VALUES(2,'Mercedes',57127)")
	cur.execute("SELECT * FROM Car")
	rows = cur.fetchall()
	# cur.close()
	# conn.commit()
	except Exception as e:
	print 'Error : ', e
	else:
	print rows
	finally:
	if conn:
	conn.close()

view raw psycopg2_with.py hosted with ❤ by GitHub