Pymssql database Operation MSSQL2005 Instance analysis

This paper describes the method of pymssql database operation MSSQL2005. Share to everyone for your reference. Specific as follows:

Use the MSSQL2005 to connect through the pymssql. The possible use of the database operating methods are summarized as follows, if you want to use the time for reference.

#!/usr/bin/env python#coding=utf-8from __future__ Import with_statementfrom contextlib import Closingimport Inspectimport pymssqlimport uuidimport datetime# query operation with closing (pymssql.connect ' localhost ', host= ' sa ', Password= ' pppp ', database= ' blogs ')) as Conn:cur = Conn.cursor () #SELECT Long Connection query operation (get data in a way) sql = "SELECT * FROM Pconten  T "Cur.execute (SQL) for I in Range (Cur.rowcount): Print Cur.fetchone () #SELECT Short link query operation (once the query takes all data out) 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 get identity (often when inserting a value that you want to get the primary key In a very elegant way) sql = "INSERT into Pcontent (%s); SELECT @ @IDENTITY "uuidstr = str (UUID.UUID1 ()) Cur.execute (SQL, (Uuidstr,)) print" Arraysite: ", cur.arraysize print cur. _result[1][2][0][0] #不知道具体的做法, for the time being this use Conn.commit () #Update VL = ' China ' sql = ' Update pcontent set title =%s where Id=1 ' Cur.execute (SQL, (VL,)) conn.commit () #参数化查询这个是为了避免SQL攻击的 sql = "SELECT * from Pcontent where id=%d" Cur.execut E (SQL, (1,)) print Cur.fetchall () # Call stored procedure sp_getallcontent parameterless sql = "Exec sp_getallcontent" Cur.execute (SQL) Print CU R.fetchall () # Call stored procedure Sp_getcontentbyid sql = "Exec Sp_getcontentbyid%d" Cur.execute (SQL, (3,)) Print Cur.fetchall () #调用存储过程SP_AddContent with output parameters (very not graceful way) sql = "DECLARE @ID INT; EXEC sp_addcontent ' ddddd ', @ID OUTPUT; Select @ID "Cur.execute (SQL) Print Cur._result

Hopefully this article will help you with Python programming.

