pymssql CRUD 예제

1 단계: 연결 합니다 

pymssql.connect 함수는 SQL Database에 연결 하는 데 사용 됩니다.

    
    import pymssql  
    conn = pymssql.connect(host='yourserver IP', user='youruserid', password='yourpassword', database='dbname') 

2 단계: 쿼리를 실행 합니다. (SELECT 예제)

    
    import pymssql  
    conn = pymssql.connect(host='yourserver IP', user='youruserid', password='yourpassword', database='dbname') 
    cursor = conn.cursor()  
    cursor.execute('SELECT c.CustomerID, c.CompanyName,COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC;')  
    row = cursor.fetchone()  
    while row:  
        print str(row[0]) + " " + str(row[1]) + " " + str(row[2])     
        row = cursor.fetchone()  

3 단계: 행 삽입 (INSERT 예제)

    
    import pymssql  
    conn = pymssql.connect(host='yourserver IP', user='youruserid', password='yourpassword', database='dbname') 
    cursor = conn.cursor()  
    cursor.execute("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express', 'SQLEXPRESS', 0, 0, CURRENT_TIMESTAMP)")  
    row = cursor.fetchone()  
    while row:  
        print "Inserted Product ID : " +str(row[0])  
        row = cursor.fetchone()  
    conn.commit()
    conn.close()

4 단계: Rollback 트랜잭션 

이 코드 예제는 트랜잭션의 사용을 보여 줍니다. 있습니다.

 - 트랜잭션 시작 

- 데이터 행 삽입 

- 롤백 트랜잭션이 삽입 취소

    
    import pymssql  
    conn = pymssql.connect(host='yourserver IP', user='youruserid', password='yourpassword', database='dbname') 
    cursor = conn.cursor()  
    cursor.execute("BEGIN TRANSACTION")  
    cursor.execute("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express New', 'SQLEXPRESS New', 0, 0, CURRENT_TIMESTAMP)")  
    conn.rollback()  
    conn.close()