Python – pymssql
MS-SQL 2014 Express
Download
https://www.microsoft.com/ko-kr/download/details.aspx?id=42299
Install
환경설정
1. SQL Server 구성 관리자 설정 (원격 연결 설정)
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 및 다운로드
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()