Pymssql-simple ms SQL Python extension module

Pymssql examples (strict DB-API compliance ):

 

Import Pymssql Conn = Pymssql . Connect ( Host ='Sql01' , User ='User' , Password ='Password' , Database ='Mydatabase' ) Cur = Conn . Cursor () Cur . Execute ( 'Create table persons (ID int, name varchar (100 ))' ) Cur . Executeworkflow ( "Insert into persons values (% D , % S )" , / [( 1,'John Doe' ) , ( 2,'Jane Doe' )]) Conn . Commit () # You must call commit () to persist your data If you don't set autocommit to true Cur . Execute ( 'Select * from persons where salesrep =% S ' ,'John Doe' ) Row = Cur. Fetchone () While Row : Print "Id = % D , Name = % S " % ( Row [ 0], Row [ 1]) Row = Cur. Fetchone () # If you call execute () with one argument, you can use % sign as usual # (It loses its special meaning ). Cur . Execute ( "Select * from persons where salesrep like 'J % '" ) Conn . Close ()

You can also use iterators instead OfWhile Loop. iterators are DB-API extensions, And are available since pymssql 1.0.

 

Rows as dictionaries

Since pymssql 1.0.2 rows can be fetched as dictionaries instead of tuples. This
Allows for accessing columns by name instead of index.

Import Pymssql Conn = Pymssql . Connect ( Host ='Sql01' , User ='User' , Password ='Password' , Database ='Mydatabase' ,As_dict =True ) Cur = Conn . Cursor () Cur . Execute ( 'Select * from persons where salesrep =% S ' ,'John Doe' ) For RowIn Cur : Print "Id = % D , Name = % S " % ( Row [ 'Id' ], Row [ 'Name' ] ) Conn . Close ()

: Http://sourceforge.net/projects/pymssql/files/pymssql/1.0.2/