python串連資料庫。

標籤：sys.path   path   hal   commit   slist   data   seo   pen   bsp   

準備工作。需要安裝pymssql 。。具體怎麼安裝請自行百度。教程很多。最簡單的就是pip install myssql  但是可能會出現版本不相容的問題。導致匯入後不能improt。也可能導致打包的時候出錯。

 

需要三個檔案。

1.解析設定檔，串連資料庫

# -*- coding: utf-8 -*-__author__ = ‘hero‘import pymssqlimport xml.dom.minidomimport osimport syscurPath = os.path.abspath(os.path.dirname(__file__))rootPath = os.path.split(curPath)[0]sys.path.append(rootPath)class ExchangeData(object):    global CONFIGTYPE_RETEST    global CONFIGTYPE_PING    global CONFIGTYPE_AGETEST    global CONFIGTYPE_IPX    CONFIGTYPE_RETEST = ‘RETEST‘    CONFIGTYPE_PING = ‘PING‘    CONFIGTYPE_AGETEST = ‘AGETEST‘    CONFIGTYPE_IPX = ‘IPX‘    ###載入資料庫連接資料    def loadDBConfig(self,dbType):        xmlDoc = xml.dom.minidom.parse(os.getcwd() +‘\\conf\\/DBConfig.xml‘#此處路徑為設定檔的路徑        root = xmlDoc.documentElement        sql_element = root.getElementsByTagName(dbType)[0]        return sql_element    def __exchangeDataByDBConnection(self):        sqlInfo = self.loadDBConfig(‘MSSQL‘)        db_host = sqlInfo.getElementsByTagName(‘HOST‘)[0].firstChild.data        db_database = sqlInfo.getElementsByTagName(‘DATABASE‘)[0].firstChild.data        db_user = sqlInfo.getElementsByTagName(‘USERNAME‘)[0].firstChild.data        db_pwd = sqlInfo.getElementsByTagName(‘PASSWORD‘)[0].firstChild.data        conn = pymssql.connect(user=db_user,password=db_pwd,database=db_database,host=db_host)        conn.autocommit(True)        return conn    ###讀取資料###    def getDataBySql(self,sqlStr):        try:            conn = self.__exchangeDataByDBConnection()            if not conn:                raise(NameError,‘資料庫連接失敗‘)            else:                cur = conn.cursor()                cur.execute(sqlStr)                resList = cur.fetchall()                conn.close()                return resList        except Exception,e:            print(e)            raise(NameError,‘資料庫操作失敗‘)    ###更改資料庫###    def setDataBySql(self,sqlStr):        try:            conn = self.__exchangeDataByDBConnection()            if not conn:                raise(NameError,‘資料庫連接失敗‘)                return False            else:                cur = conn.cursor()                cur.execute(sqlStr)                conn.commit()                conn.close()                return True        except Exception,e:            print(e)            raise(NameError,‘資料庫操作失敗‘)            return Falseif __name__ == ‘__main__‘:    pn = ‘BYAUX-CTN0003‘    sqlStr = "select * from producttype where productNo = ‘"+pn+"‘"    edata = ExchangeData()    resList = edata.getDataBySql(sqlStr=sqlStr)    for dataRow in resList:        print(dataRow[0])

 

2.進行增刪改查操作，檔案

  1 #coding=gbk  2   3 from exchangeData import *  4 import time  5   6 ###資料庫相關操作  7 class DataBaseOP(object):  8     ###根據使用者名稱密碼，驗證使用者權限傳回值不同，許可權不同###  9     def loginApTester(self,username,password): 10         loginSql = ‘SELECT rid FROM bdcomemployee WHERE bdcomLoginName = \‘‘+username+‘\‘ AND bdcomPassword = \‘‘+password+‘\‘‘ 11         loginSql = self.dbTrim(loginSql) 12         eData = ExchangeData() 13         dataRows = eData.getDataBySql(loginSql) 14         if len(dataRows)==0 or dataRows[0] is None: 15             return 0 16         elif dataRows[0][0]==17: 17             return 2 18         else: 19             return 1 20  98    127     ###修改密碼###128     def resetDBPwd(self,userName,oldPwd,newPwd):129         igStatus = self.loginApTester(userName,oldPwd)130         if igStatus>0:131             setSql = ‘UPDATE bdcomemployee SET bdcomPassword = \‘‘+newPwd+‘\‘ WHERE bdcomLoginName = \‘‘+userName+‘\‘ AND bdcomPassword = \‘‘+oldPwd+‘\‘‘132             self.dbTrim(setSql)133             eData = ExchangeData()134             return eData.setDataBySql(setSql)135         else:136             return False;137 138 139     155   169 170     def dbTrim(self,trimStr):171         return str(trimStr).replace(‘\t‘,‘‘).replace(‘\n‘,‘‘).replace(‘\r‘,‘‘)172 173 if __name__ == ‘__main__‘:174     dbop = DataBaseOP()175     dbop.loginApTester(‘liuke_ap‘,‘liuke‘)

 

3.設定檔

 

1 <?xml version="1.0" encoding="UTF-8"?>2 <JDBC>3     <MSSQL>4         <HOST>192.168.1.1</HOST>5         <DATABASE>abcd</DATABASE>6         <USERNAME>root</USERNAME>7         <PASSWORD>root</PASSWORD>8     </MSSQL>9 </JDBC>

 

python串連資料庫。