2. 集成开发环境 JetBrains PyCharm Community Edition 2018.3.4 x64

3. python 连接SQLServer数据库（两种方法）

# -*- coding:utf-8 -*-

#方法一

import pymssql

# server = "192.168.1.76" # 连接服务器地址

# user = "sa" # 连接帐号

# password = "sa" # 连接密码

#

# with pymssql.connect(server, user, password, "Contract_Dev",charset="utf8") as conn:

# with conn.cursor(as_dict=True) as cursor: # 数据存放到字典中

# cursor.execute('SELECT * FROM contract_Statelog WHERE Id=%d',2)

# for row in cursor:

# print("Id=%d, contract_StateName=%s" % (row['Id'], row['contract_StateName']))

#方法二

class MSSQL:

def __init__(self,host,user,pwd,db):

self.host = host

self.user = user

self.pwd = pwd

self.db = db

def __GetConnect(self):

if not self.db:

raise(NameError,"没有设置数据库信息")

self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")

cur = self.conn.cursor()

if not cur:

raise(NameError,"连接数据库失败")

else:

return cur

def ExecQuery(self,sql):

cur = self.__GetConnect()

cur.execute(sql)

resList = cur.fetchall()

#查询完毕后必须关闭连接

self.conn.close()

return resList

def ExecNonQuery(self,sql):

cur = self.__GetConnect()

cur.execute(sql)

self.conn.commit()

self.conn.close()

def ExecNonQueryInsert(self, sql):

cur = self.__GetConnect()

cur.execute(sql)

returnId=int(cur.lastrowid)

# print(returnId)

self.conn.commit()

self.conn.close()

return returnId

ms = MSSQL(host="192.168.1.76",user="sa",pwd="sa",db="Contract_Dev")

# reslist = ms.ExecQuery("select TOP 3 * from S_City")

# for i in reslist:

# print (i)

newsql2="insert into contract_Statelog(contract_SignedId ,contract_State ,contract_StateName ,CreateTime ,sDesc) values(1,1,'qq','2019-1-1','desc')"

print (newsql2)

Id=ms.ExecNonQueryInsert(newsql2.encode('utf-8'))

print(Id)

# newsql="update contract_Statelog set contract_StateName='%s' where id=1"%u'测试'

# print (newsql)

# ms.ExecNonQuery(newsql.encode('utf-8'))

# newsql="delete from contract_Statelog where id=1"

# print (newsql)

# ms.ExecNonQuery(newsql.encode('utf-8'))