#!/usr/bin/env python
#coding=utf-8
from __future__ import with_statement
from contextlib import closing
import inspect
import pymssql
import uuid
import datetime
#查詢操作
with closing(pymssql.connect(host='localhost',user='sa',password='pppp',database='blogs')) as conn :
  cur = conn.cursor()
  #SELECT 長連接查詢操作（逐條方式獲取數(shù)據(jù)）
  sql = "select * from pcontent"
  cur.execute(sql)
  for i in range(cur.rowcount):
    print cur.fetchone()
  #SELECT 短鏈接查詢操作（一次查詢將所有數(shù)據(jù)取出）
  sql = "select * from pcontent"
  cur.execute(sql)
  print cur.fetchall()
  #INSERT 
  sql = "INSERT INTO pcontent(title)VAlUES(%s)"
  uuidstr = str(uuid.uuid1())
  cur.execute(sql,(uuidstr,))
  conn.commit()
  print cur._result
  #INSERT 獲取IDENTITY（在插入一個值，希望獲得主鍵的時候經(jīng)常用到，很不優(yōu)雅的方式）
  sql = "INSERT INTO pcontent(title)VAlUES(%s);SELECT @@IDENTITY"
  uuidstr = str(uuid.uuid1())
  cur.execute(sql,(uuidstr,))
  print "arraysite:",cur.arraysize
  print cur._result[1][2][0][0]#不知道具體的做法，目前暫時這樣使用
  conn.commit()
  #Update
  vl = '中國'
  sql = 'update pcontent set title = %s where id=1'
  cur.execute(sql,(vl,))
  conn.commit()
  #參數(shù)化查詢這個是為了避免SQL攻擊的
  sql = "select * from pcontent where id=%d"
  cur.execute(sql,(1,))
  print cur.fetchall()
  # 調(diào)用存儲過程SP_GetALLContent 無參數(shù)
  sql = "Exec SP_GetALLContent"
  cur.execute(sql)
  print cur.fetchall()
  # 調(diào)用存儲過程SP_GetContentByID 有參數(shù)的
  sql = "Exec SP_GetContentByID %d"
  cur.execute(sql,(3,))
  print cur.fetchall()
  #調(diào)用存儲過程SP_AddContent 有output參數(shù)的(很不優(yōu)雅的方式)
  sql = "DECLARE @ID INT;EXEC SP_AddContent 'ddddd',@ID OUTPUT;SELECT @ID"
  cur.execute(sql)
  print cur._result