[Tutor] MSSQL Connection

[János Juhász]

Hi Leon,


> > Hi John,
> >
> > Here's the code (I do have permissions to truncate, works manually 
under
> > the same user.
> >
> >
> > import pymssql
> >
> > con = pymssql.connect
> > (host='server',user='user',password='pwd',database='DB_QA')
> > cur = con.cursor()
> >
> >
> > query="truncate TABLE bbConsolidatedMessageAcks"
> > cur.execute(query)
> > print "Deleted Records: %d" % cur.rowcount
> >

I use two alternatives to connect to mssql server.

ADODB connection via win32com.client 
------------------
import win32com.client 

cn =win32com.client.Dispatch('ADODB.connection')
cn.Provider='sqloledb'
cn.Open('Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security 
Info=False;Initial Catalog=production;Data Source=036fefersqls001')
# The argument of cn.Open(), the connection string can be created very 
easy on windows.
#  Create a conn.udl on your desktop
#  Open it with double click, and build a new connection string, test it, 
save it
#  Copy and paste from your .udl

stockcode = '100701-06'
sql = "select * from stocks where stock = '%s'" % stockcode

print sql
rs = win32com.client.Dispatch('ADODB.recordset')
rs.Open(sql, cn)
print rs.Fields[0].Value
rs.Close()

cn.Close() 
-----------------

But I usually use ODBC with this kind of class.

import dbi, odbc

class myDB:
        def __init__(self):
                """Open a new connection."""
                self.cn = 
odbc.odbc('DSN=myDB;UID=query;PWD=query;DATABASE=myDB')
 
        def closeDB(self):
                """Close the connection."""
                self.cn.close()

        def Execute(self, sql):
                cr = self.cn.cursor()
                cr.execute(sql)
                cr.close()

        def Query(self, sql):
                try:
                        cr = self.cn.cursor()
                        cr.execute(sql)
                        self.colnames = [field_prop[0] for field_prop in 
cr.description]
                        self.result = cr.fetchall()
                        self.rownum = len(self.result)
                        return self.result
                except:
                        self.colnames = [None]
                        self.result = [[None]]
                        self.rownum = 0
                        return [[None]]

test = myDB()
print(test.Query('Select * from testtable') )


Best regards,
Janos