카테고리 보관물: Python MSSQL

Python MSSQL 관련 자료

Python – Pymssql MSSQL 활용

댓글 남기기

Python – pymssql

MS-SQL 2014 Express

Download

https://www.microsoft.com/ko-kr/download/details.aspx?id=42299

Install

https://docs.microsoft.com/en-us/sql/connect/python/pymssql/step-1-configure-development-environment-for-pymssql-python-development

환경설정

1. SQL Server 구성 관리자 설정 (원격 연결 설정)

http://devx.tistory.com/entry/SQL-Server-2008-R2-EXPRESS-%EC%84%A4%EC%B9%98-%ED%9B%84-%EC%99%B8%EB%B6%80%EC%A0%91%EC%86%8D-%EC%95%88%EB%90%A0%EB%95%8C

2. Telnet 에서 원격 연결 확인

telnet localhost 1433

<참고: 윈도우 telnet 활성화 시키기>

http://blog.naver.com/ooa1769/220371447875

MS-SQL 시스템 DB

  • Master DB : SQL Server의 운영에 필요한 내용을 전반적으로 관리
  • Msdb DB : SQL Agent Service가 이용하는 데이터베이스(자동화와 관련한 잡이나 경고 등) 관리
  • Model DB : 관리자가 만드는 모든 데이터베이스의 원형 DB
  • Tempdb DB : 임시 테이블이나 정렬 작업등을 일시 저장하기위한 테이블(SQL Server Service가 시작될 때 항상 초기화 됨)

DB 개체 ( Object )

  • 데이터베이스 (DB) : 최소한 하나 이상의 데이터파일과 하나 이상의 로그 파일로 구성
  • 테이블 (Table) : 실제 데이터를 저장하는 핵심 오브젝트
  • 뷰 ( View ) : 가상의 논리적인 테이블, View를 사용하는 이유는 편의성과 보안성
  • 저장 프로시저 ( Stored Procedure ) : SQL 프로그래밍, 속도가 빠름
  • 트리거 ( Trigger ) : 테이블에 데이터가 입력, 수정, 삭제될 때 동작하는 프시저의 한 형태
  • 제약조건 ( Constraint )
  • 기본키(PK) 제약 조건 : Not Null, No Duplication
  • Unique 제약 조건 : No Duplicate
  • 외래키(FK) 제약 조건 : 관계 형성에 따른 자식 테이블의 입력, 수정 제약 조건
  • Check, Rule : 특정 컬럼에 입력이 허용 가능한 데이터나 데이터 범위를 지정
  • 디폴트(Default) : 아무런 값도 입력되지 않은 컬럼에 들어가는 값
  • 인덱스 ( Index ) : 데이터의 검색 속도를 향상시키기 위해 사용
  • 데이터베이스 다이어그램 : 테이블과 관계를 시각적으로 보여주는 인터페이스

SQL, DDL, DML

SQL ( Structured Query Language )

  • SQL문장을 사용하며 RDBMS는 물리적으로 데이터베이스에서 명령을 수행
  • 결과를 사용자에게 반환

SQL ( Structured Query Language )문장의 유형

DDL ( Data Definition Language )
  • CREATE : 데이터베이스 및 데이터베이스 오브젝트 생성
  • DROP : 데이터베이스 및 데이터베이스 오브젝트 삭제
  • ALTER : 데이터베이스 및 데이터베이스 오브젝트 수정
DML ( Data Manipulation Language )
  • SELECT : 테이블의 데이터 조회
  • INSERT : 테이블에 데이터 입력
  • UPDATE : 테이블에 데이터 수정
  • DELETE : 테이블에 데이터 삭제
    DCL ( Data Control Language)
    • GRANT : 특정 자원 사용에 대한 권한 부여
    • REVOKE : 특정 자원에 부여된 권한 취소 ( 권한을 주기 전 상태 )
    • DENY : 특정 자원에 대한 권한 모두 취소 ( 모두 부정 )

데이터베이스 (database) 만들기

  • MSSM 에서 만들어 보기

테이블(table) 작업

  • MSSM 에서 creat, alter, drop 해 보기
  • MSSM 에서 insert, update, delete 해 보기

sample db 다운로드[https://www.microsoft.com/en-us/download/details.aspx?id=23654]

SQL 연습

W3SCHOOLS https://www.w3schools.com/sql/default.asp

Python 에서 MSSQL 연결

Python 모듈 설치

구글 검색 : Install pymssql Python Driver 및 다운로드

https://docs.microsoft.com/en-us/sql/connect/python/pymssql/step-1-configure-development-environment-for-pymssql-python-development

PIP 명령으로 설치

pip install pymssql-2.1.3-cp36-cp36m-win32.whl

Python에서 pymssql 사용하는 일반적인 절차

  • pymssql 모듈을 import
  • pymssql.connect() 메소드를 사용하여 MSSQL에 Connect
  • 호스트명, 로그인, 암호, 접속할 DB 등을 파라미터로 지정
  • DB 접속이 성공하면, Connection 객체로부터 cursor() 메서드를 호출
  • Cursor 객체의 메서드를 통해 MSSQL 서버와 작업 관리
  • Cursor 객체의 execute() 메서드를 사용하여 SQL 문장을 DB 서버로 전송
  • SQL 쿼리의 경우 Cursor 객체의 fetchall(), fetchone(), fetchmany() 등의 메서드를 사용
  • 데이타를 서버로부터 가져온 후, Fetch 된 데이타를 사용
  • Insert, Update, Delete 등의 DML(Data Manipulation Language) 문장을 실행
  • Connection 객체 commit() 은 실제로 DML 문장을 서버에 실제 실행
  • Connection 객체 rollback() 실행 으로 DML 문장 취소
  • Connection 객체의 close() 메서드를 사용하여 DB 연결 닫기

Step 1: Connect

import pymssql

conn = pymssql.connect(server='localhost', user='userid', password='password', database='Northwind')

Step 2: Execute query

fetchoen – 1

““
import pymssql

conn = pymssql.connect(server=’localhost’, user=’userid’, password=’password’, database=’Northwind’)
cursor = conn.cursor()
cursor.execute(‘SELECT CustomerId, City FROM Customers ORDER BY Country; ‘)
#rows = cursor.fetchall()
row = cursor.fetchone()
#print(type(row))
print(row)

row = cursor.fetchone()
print(row)


#### fetchoen - 2
```python
import pymssql

conn = pymssql.connect(server='localhost', user='userid', password='password', database='Northwind')
cursor = conn.cursor()
cursor.execute('SELECT CustomerId, City  FROM Customers ORDER BY Country; ')
row = cursor.fetchone()
if row is None:
    print("null")
else:
    while row:
        print(str(row[0]),str(row[1]))
        row = cursor.fetchone()

fetchall

import pymssql

conn = pymssql.connect(server='localhost', user='userid', password='password', database='Northwind')
cursor = conn.cursor()
cursor.execute('SELECT CustomerId, City  FROM Customers ORDER BY Country; ')
rows = cursor.fetchall()
#rows = cursor.fetchall()
if rows is None:
    print("null")
else:
    for row in rows:
        print(row)

Step 3: Insert a row

import pymssql

conn = pymssql.connect(server='localhost', user='user', password='password' database='PythonDB')
cursor = conn.cursor()
cursor.execute("Insert Into member (id, pwd, email) Values ('k2moon', 'pass', 'k2moon@email.com') ")

cursor.execute('SELECT id,email  FROM member ORDER BY idx; ')
row = cursor.fetchone()
if row is None:
    print("null")
else:
    while row:
        print(str(row[0]),str(row[1]))
        row = cursor.fetchone()
#conn.commit()

conn.close()

Step 4: Rollback a transaction

import pymssql
conn = pymssql.connect(server='localhost', user='userid', password='password', database='PythonDB')

cursor = conn.cursor()
cursor.execute("Insert Into member (id, pwd, email) Values ('k3moon', 'pass', 'k3moon@email.com') ")

cursor.execute("BEGIN TRANSACTION")

cursor.execute('SELECT id,email  FROM member ORDER BY idx; ')
row = cursor.fetchone()
if row is None:
    print("null")
else:
    while row:
        print(str(row[0]),str(row[1]))
        row = cursor.fetchone()

conn.rollback()

print('rollback')

cursor.execute('SELECT id,email  FROM member ORDER BY idx; ')
row = cursor.fetchone()
if row is None:
    print("null")
else:
    while row:
        print(str(row[0]),str(row[1]))
        row = cursor.fetchone()

conn.close()