python连接数据库。

-*- coding: utf-8 -*- __author__ = ‘hero‘ import pymssql import xml.dom.minidom import os import sys curPath = 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 False if __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.进行增删改查操作，文件

<span style="color: #008080;">  1</span> <span style="color: #008000;">#</span><span style="color: #008000;">coding=gbk</span>
<span style="color: #008080;">  2</span> 
<span style="color: #008080;">  3</span> <span style="color: #0000ff;">from</span> exchangeData <span style="color: #0000ff;">import</span> *
<span style="color: #008080;">  4</span> <span style="color: #0000ff;">import</span><span style="color: #000000;"> time
</span><span style="color: #008080;">  5</span> 
<span style="color: #008080;">  6</span> <span style="color: #008000;">#</span><span style="color: #008000;">##数据库相关操作</span>
<span style="color: #008080;">  7</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> DataBaseOP(object):
</span><span style="color: #008080;">  8</span>     <span style="color: #008000;">#</span><span style="color: #008000;">##根据用户名密码，验证用户权限返回值不同，权限不同###</span>
<span style="color: #008080;">  9</span>     <span style="color: #0000ff;">def</span><span style="color: #000000;"> loginApTester(self,username,password):
</span><span style="color: #008080;"> 10</span>         loginSql = <span style="color: #800000;">‘</span><span style="color: #800000;">SELECT rid FROM bdcomemployee WHERE bdcomLoginName = \‘</span><span style="color: #800000;">‘</span>+username+<span style="color: #800000;">‘</span><span style="color: #800000;">\‘ AND bdcomPassword = \‘</span><span style="color: #800000;">‘</span>+password+<span style="color: #800000;">‘</span><span style="color: #800000;">\‘</span><span style="color: #800000;">‘</span>
<span style="color: #008080;"> 11</span>         loginSql =<span style="color: #000000;"> self.dbTrim(loginSql)
</span><span style="color: #008080;"> 12</span>         eData =<span style="color: #000000;"> ExchangeData()
</span><span style="color: #008080;"> 13</span>         dataRows =<span style="color: #000000;"> eData.getDataBySql(loginSql)
</span><span style="color: #008080;"> 14</span>         <span style="color: #0000ff;">if</span> len(dataRows)==0 <span style="color: #0000ff;">or</span> dataRows[0] <span style="color: #0000ff;">is</span><span style="color: #000000;"> None:
</span><span style="color: #008080;"> 15</span>             <span style="color: #0000ff;">return</span><span style="color: #000000;"> 0
</span><span style="color: #008080;"> 16</span>         <span style="color: #0000ff;">elif</span> dataRows[0][0]==17<span style="color: #000000;">:
</span><span style="color: #008080;"> 17</span>             <span style="color: #0000ff;">return</span> 2
<span style="color: #008080;"> 18</span>         <span style="color: #0000ff;">else</span><span style="color: #000000;">:
</span><span style="color: #008080;"> 19</span>             <span style="color: #0000ff;">return</span> 1
<span style="color: #008080;"> 20</span> 
<span style="color: #008080;"> 98</span>    
<span style="color: #008080;">127</span>     <span style="color: #008000;">#</span><span style="color: #008000;">##修改密码###</span>
<span style="color: #008080;">128</span>     <span style="color: #0000ff;">def</span><span style="color: #000000;"> resetDBPwd(self,userName,oldPwd,newPwd):
</span><span style="color: #008080;">129</span>         igStatus =<span style="color: #000000;"> self.loginApTester(userName,oldPwd)
</span><span style="color: #008080;">130</span>         <span style="color: #0000ff;">if</span> igStatus><span style="color: #000000;">0:
</span><span style="color: #008080;">131</span>             setSql = <span style="color: #800000;">‘</span><span style="color: #800000;">UPDATE bdcomemployee SET bdcomPassword = \‘</span><span style="color: #800000;">‘</span>+newPwd+<span style="color: #800000;">‘</span><span style="color: #800000;">\‘ WHERE bdcomLoginName = \‘</span><span style="color: #800000;">‘</span>+userName+<span style="color: #800000;">‘</span><span style="color: #800000;">\‘ AND bdcomPassword = \‘</span><span style="color: #800000;">‘</span>+oldPwd+<span style="color: #800000;">‘</span><span style="color: #800000;">\‘</span><span style="color: #800000;">‘</span>
<span style="color: #008080;">132</span> <span style="color: #000000;">            self.dbTrim(setSql)
</span><span style="color: #008080;">133</span>             eData =<span style="color: #000000;"> ExchangeData()
</span><span style="color: #008080;">134</span>             <span style="color: #0000ff;">return</span><span style="color: #000000;"> eData.setDataBySql(setSql)
</span><span style="color: #008080;">135</span>         <span style="color: #0000ff;">else</span><span style="color: #000000;">:
</span><span style="color: #008080;">136</span>             <span style="color: #0000ff;">return</span><span style="color: #000000;"> False;
</span><span style="color: #008080;">137</span> 
<span style="color: #008080;">138</span> 
<span style="color: #008080;">139</span>     
<span style="color: #008080;">155</span>   <span style="color: #008080;">169</span> 
<span style="color: #008080;">170</span>     <span style="color: #0000ff;">def</span><span style="color: #000000;"> dbTrim(self,trimStr):
</span><span style="color: #008080;">171</span>         <span style="color: #0000ff;">return</span> str(trimStr).replace(<span style="color: #800000;">‘</span><span style="color: #800000;">\t</span><span style="color: #800000;">‘</span>,<span style="color: #800000;">‘‘</span>).replace(<span style="color: #800000;">‘</span><span style="color: #800000;">\n</span><span style="color: #800000;">‘</span>,<span style="color: #800000;">‘‘</span>).replace(<span style="color: #800000;">‘</span><span style="color: #800000;">\r</span><span style="color: #800000;">‘</span>,<span style="color: #800000;">‘‘</span><span style="color: #000000;">)
</span><span style="color: #008080;">172</span> 
<span style="color: #008080;">173</span> <span style="color: #0000ff;">if</span> <span style="color: #800080;">__name__</span> == <span style="color: #800000;">‘</span><span style="color: #800000;">__main__</span><span style="color: #800000;">‘</span><span style="color: #000000;">:
</span><span style="color: #008080;">174</span>     dbop =<span style="color: #000000;"> DataBaseOP()
</span><span style="color: #008080;">175</span>     dbop.loginApTester(<span style="color: #800000;">‘</span><span style="color: #800000;">liuke_ap</span><span style="color: #800000;">‘</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">liuke</span><span style="color: #800000;">‘</span>)

 

3.配置文件

 

<span style="color: #008080;">1</span> <?xml version=<span style="color: #800000;">"</span><span style="color: #800000;">1.0</span><span style="color: #800000;">"</span> encoding=<span style="color: #800000;">"</span><span style="color: #800000;">UTF-8</span><span style="color: #800000;">"</span>?>
<span style="color: #008080;">2</span> <JDBC>
<span style="color: #008080;">3</span>     <MSSQL>
<span style="color: #008080;">4</span>         <HOST>192.168.1.1</HOST>
<span style="color: #008080;">5</span>         <DATABASE>abcd</DATABASE>
<span style="color: #008080;">6</span>         <USERNAME>root</USERNAME>
<span style="color: #008080;">7</span>         <PASSWORD>root</PASSWORD>
<span style="color: #008080;">8</span>     </MSSQL>
<span style="color: #008080;">9</span> </JDBC>

 

python连接数据库。

标签：sys.path   path   hal   commit   slist   data   seo   pen   bsp