下面通过一个简单的例子来说明如何连接SQL Server并进行一些常见的操作。Python通过pymssql第三方模块连接SQL Server,并对其进行增删改查相关操作。
pymssql项目地址:https://github.com/pymssql/pymssql
https://www.cndba.cn/Expect-le/article/3220https://www.cndba.cn/Expect-le/article/3220https://www.cndba.cn/Expect-le/article/3220https://www.cndba.cn/Expect-le/article/3220
https://www.cndba.cn/Expect-le/article/3220https://www.cndba.cn/Expect-le/article/3220https://www.cndba.cn/Expect-le/article/3220
import pymssql
class sqlserverapi:
def __init__(self,server='127.0.0.1',user='sa',password='123',dbname='tempdb'):
self._server = server
self._user = user
self._password = password
self._dbname = dbname
# 这里主要是通过用户名和密码来连接数据库,也可以通过操作系统认证来连接。
def ExcuteDMLSQL(self,sql):
"""
执行DML操作
:return:
"""
try:
conn = pymssql.connect(self._server, self._user, self._password, self._dbname)
cursor = conn.cursor()
cursor.execute(sql)
conn.commit() #这里注意,哪怕执行的DDL操作,也要手动执行提交操作要不,不会提交所执行的操作。除非启用了自动提交。
conn.close()
return 'TRUE'
except pymssql.Error as err:
print(err)
def executemanySQL(self):
try:
conn = pymssql.connect(self._server, self._user, self._password, self._dbname)
cursor = conn.cursor()
cursor.executemany(
"INSERT INTO lei VALUES (%d, %s, %s, %s)",
[(1, 'lei', 'sihong','男'),
(2, 'yi', 'sihong','女'),
(3, 'TOM', 'USA','男')])
conn.commit()
cursor.execute('SELECT * FROM lei WHERE sex=%s', '男')
row = cursor.fetchone()
while row:
print("ID=%d, Name=%s" % (row[0], row[1]))
row = cursor.fetchone()
conn.close()
return 'TRUE'
except pymssql.Error as err:
print(err)
if __name__ == '__main__':
sql = """
IF OBJECT_ID('lei', 'U') IS NOT NULL
DROP TABLE lei
CREATE TABLE lei (
id INT NOT NULL,
name VARCHAR(100),
address VARCHAR(100),
sex char(2),
PRIMARY KEY(id)
)
"""
dbapi = sqlserverapi()
#创建表
out = dbapi.ExcuteDMLSQL(sql)
print(out)
#一次插入多条数据并查询
out = dbapi.executemanySQL()
print(out)
关于该模块的使用,请查看官方文档:http://www.pymssql.orghttps://www.cndba.cn/Expect-le/article/3220https://www.cndba.cn/Expect-le/article/3220
https://www.cndba.cn/Expect-le/article/3220
版权声明:本文为博主原创文章,未经博主允许不得转载。
python pymssql