CentOS8使用Python3脚本管理MSSQL2008R2
笺注:MSSQL服务器在 [url=http://blog.zhuohua.store/viewthread.php?tid=104&extra=page%3D1]MSSQL2008R2的备份与还原[/url] 的基础上进行的。库data1中的表t_1的表结构:
[attach]19903[/attach]
注释:具体表名为 [color=DarkRed]data1.dbo.t_1[/color]
创建了一个数据库用户zhuohua,对库data1有完全控制的权限:
[attach]19904[/attach]
防火墙记得打开TCP 1433端口,
打开方法可参考:[url=http://blog.zhuohua.store/viewthread.php?tid=45&extra=page%3D1]Windows2008R2_UPUPW搭建WordPress+Discuz!7.2[/url]
笺注:假如只是在远程客户端通过Python3脚本管理MSSQL数据库,那么MSSQL服务器连Python3都不需要安装。
######
######
Linux客户端连接公网安装第三方库([color=Blue]pymssql[/color]):(可以指定软件的版本)
[root@centos8 ~]# pip3 install [color=Blue]pymssql==2.2.5[/color] -i http://mirrors.aliyun.com/pypi/simple --trusted-host=[color=DarkRed]mirrors.aliyun.com[/color]
Looking in indexes: http://mirrors.aliyun.com/pypi/simple
Collecting pymssql==2.2.5
Downloading [color=Purple]http://mirrors.aliyun.com/pypi/packages/9a/6d/d20882e975f110b3810b8289b53c9dcce9f975dd3f9305f5262381336389/pymssql-2.2.5-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.whl[/color] (2.6MB)
100% |████████████████████████████████| 2.6MB 1.8MB/s
Installing collected packages: pymssql
Successfully installed pymssql-2.2.5
[root@centos8 ~]#
列出当前环境所有已经安装的第三方库的名称和其版本号:
[root@centos8 ~]# pip3 freeze
[color=Purple]pymssql==2.2.5[/color]
[root@centos8 ~]#
######
例子一:
Linux客户端远程测试与数据库的连接的脚本:(/root/xx.py)
[root@centos8 ~]# cat /root/xx.py
#coding=utf-8
import pymssql
def db_connect():
db = pymssql.connect('192.168.168.137:1433','[color=DarkRed]sa[/color]','Jacky888','[color=Blue]master[/color]') #连接数据库;MSSQL服务器的IP地址(192.168.168.137)、端口号(1433)、用户名([color=DarkRed]sa[/color])、用户密码、其中一个库的名称([color=Blue]master[/color])
cursor = db.cursor([color=Blue]as_dict=True[/color]) #指定返回值为字典
Sql_1 = "select @@VERSION as MSSQL的版本信息" #查询MSSQL的版本信息
cursor.execute(Sql_1) #执行SQL语句
Result_1 = cursor.[color=DarkRed]fetchone[/color]() #使用[color=DarkRed]fetchone[/color]只能获取一个元素
print(Result_1)
print(type(Result_1))
print("-" * 10)
print("数据库连接成功,数据库的版本信息:")
Result_2 = Result_1['MSSQL的版本信息'] #获取字典中键[color=Blue]'MSSQL的版本信息'[/color]的值
print(Result_2)
print("-" * 10)
Result_3 = Result_2[color=Blue][0:34][/color] #获取结果的前面34个字符
print(Result_3)
db.commit() #把执行任务提交到数据库
db.close()
def func_main():
try:
db_connect()
except Exception as e:
print(f"数据库连接失败,原因: {e}")
if __name__ == "__main__":
func_main()
脚本运行的结果:
[root@centos8 ~]# python3 xx.py
{'MSSQL的版本信息': 'Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) \n\tApr 2 2010 15:48:46 \n\tCopyright (c) Microsoft Corporation\n\tEnterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)\n'}
[color=Purple]<class 'dict'>[/color]
----------
数据库连接成功,数据库的版本信息:
[color=Purple]Microsoft SQL Server 2008 R2 (RTM)[/color] - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
----------
[color=Purple]Microsoft SQL Server 2008 R2 (RTM)[/color]
[root@centos8 ~]#
######
例子二:
Linux客户端远程测试与数据库的连接的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8
import pymssql
def db_connect():
db = pymssql.connect('192.168.168.137:1433','[color=DarkRed]zhuohua[/color]','123','[color=Blue]data1[/color]') #连接数据库;MSSQL服务器的IP地址(192.168.168.137)、端口号(1433)、用户名([color=DarkRed]zhuohua[/color])、用户密码、其中一个库的名称([color=Blue]data1[/color])
cursor = db.cursor()
Sql_1 = "select @@VERSION as sql_server_v;" #查询MSSQL的版本信息
cursor.execute(Sql_1) #执行SQL语句
Result_1 = cursor.[color=DarkRed]fetchone()[/color]
print(Result_1)
print(type(Result_1))
print("-" * 10)
print("数据库连接成功,数据库的版本信息:")
print(Result_1[0]) #输出结果的第一项
print("-" * 10)
Result_2 = Result_1[0][0:34] #输出结果的第一项前面34个字符
print(Result_2)
db.commit() #把执行任务提交到数据库
db.close()
def func_main():
try:
db_connect()
except Exception as e:
print(f"数据库连接失败,原因: {e}")
if __name__ == "__main__":
func_main()
脚本运行的结果:
[root@centos8 ~]# python3 xx.py
('Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) \n\tApr 2 2010 15:48:46 \n\tCopyright (c) Microsoft Corporation\n\tEnterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)\n',)
[color=Purple]<class 'tuple'>[/color]
----------
数据库连接成功,数据库的版本信息:
[color=Purple]Microsoft SQL Server 2008 R2 (RTM)[/color] - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
----------
[color=Purple]Microsoft SQL Server 2008 R2 (RTM)[/color]
[root@centos8 ~]#
######
例子三:
Linux客户端远程输出库data1中表t_1的所有记录:(/root/yy.py)
[root@centos8 ~]# cat /root/yy.py
#coding=utf-8
import pymssql
def query_data():
db = pymssql.connect('192.168.168.137:1433','[color=DarkRed]zhuohua[/color]','123','[color=Blue]data1[/color]')
cursor = db.cursor()
Sql_1 = "select * from [color=DarkRed]t_1[/color]" #这是在库data1中查询表t_1
cursor.execute(Sql_1)
Result_1 = cursor.[color=Blue]fetchall[/color]() #使用[color=Blue]fetchall[/color]可以获取多个元素
print(Result_1)
print(type(Result_1))
print("-" * 10)
for Key_1 in Result_1: #自定义输出格式
id = Key_1[0]
name = Key_1[1]
age = Key_1[2]
print(f"id={id},姓名={name},年龄={age}")
db.commit() #把执行任务提交到数据库
db.close()
def func_main():
try:
query_data()
except Exception as e:
print(f"输出失败,原因: {e}")
if __name__ == "__main__":
func_main()
脚本运行的结果:
[root@centos8 ~]# python3 yy.py
[(1, 'zhuohua', 25), (2, 'Python', 35), (3, '李小芳', 45), (4, '张三', -55)]
[color=Purple]<class 'list'>[/color]
----------
id=1,姓名=zhuohua,年龄=25
id=2,姓名=Python,年龄=35
id=3,姓名=李小芳,年龄=45
id=4,姓名=张三,年龄=-55
[root@centos8 ~]#
######
例子四:
Linux客户端远程输出库data1中表t_1的所有记录:
[root@centos8 ~]# cat xx.py
#coding=utf-8
import pymssql
def query_data():
db = pymssql.connect('192.168.168.137:1433','zhuohua','123','[color=Blue]data1[/color]')
cursor = db.cursor()
Sql_1 = "select * from [color=DarkRed]t_1[/color]"
cursor.execute(Sql_1)
Result_1 = cursor.[color=Blue]fetchall()[/color]
print(Result_1)
print(type(Result_1))
print("-" * 10)
for Key_1 in Result_1: #自定义输出格式
id = Key_1[0]
name = Key_1[1]
age = Key_1[2]
[color=DarkRed]age_abs = abs(Key_1[2])[/color]
print(f"id={id},姓名={name},年龄={age},\n年龄的绝对值={age_abs}")
db.commit() #把执行任务提交到数据库
db.close()
def func_main():
try:
query_data()
except Exception as e:
print(f"输出失败,原因: {e}")
if __name__ == "__main__":
func_main()
脚本运行的结果:
[root@centos8 ~]# python3 xx.py
[(1, 'zhuohua', 25), (2, 'Python', 35), (3, '李小芳', 45), (4, '张三', -55)]
<class 'list'>
----------
id=1,姓名=zhuohua,年龄=25,
[color=Purple]年龄的绝对值=25[/color]
id=2,姓名=Python,年龄=35,
[color=Purple]年龄的绝对值=35[/color]
id=3,姓名=李小芳,年龄=45,
[color=Purple]年龄的绝对值=45[/color]
id=4,姓名=张三,年龄=-55,
[color=Purple]年龄的绝对值=55[/color]
[root@centos8 ~]#
######
例子五:
Linux客户端远程输出数据库中某个表里符合指定条件的记录的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8
import pymssql
def query_data():
db = pymssql.connect('192.168.168.137:1433','zhuohua','123','[color=Blue]data1[/color]')
cursor = db.cursor()
Sql_1 = "select * from t_1 [color=DarkRed]where name = 'Python' or id =3[/color]"
cursor.execute(Sql_1)
Result_1 = cursor.[color=Blue]fetchall()[/color]
print(Result_1)
print(type(Result_1))
print("-" * 10)
for Key_1 in Result_1: #自定义输出格式
id = Key_1[0]
name = Key_1[1]
age = Key_1[2]
[color=DarkRed]name_len = len(Key_1[1])[/color]
print(f"id={id},姓名={name},年龄={age},\n姓名的长度={name_len}")
db.commit() #把执行任务提交到数据库
db.close()
def func_main():
try:
query_data()
except Exception as e:
print(f"输出失败,原因: {e}")
if __name__ == "__main__":
func_main()
脚本运行的结果:(假如找到匹配的记录时)
[root@centos8 ~]# python3 xx.py
[(2, 'Python', 35), (3, '李小芳', 45)]
<class 'list'>
----------
id=2,姓名=Python,年龄=35,
[color=Purple]姓名的长度=6[/color]
id=3,姓名=李小芳,年龄=45,
[color=Purple]姓名的长度=3[/color]
[root@centos8 ~]#
脚本运行的结果:(假如没有找到匹配的记录时)
[root@centos8 ~]# python3 xx.py
[color=Purple][]
<class 'list'>
----------[/color]
[root@centos8 ~]#
######
例子六:
Linux客户端把输出结果写入(追加)到本地文件的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8
import pymssql
def query_data():
db = pymssql.connect('192.168.168.137:1433','zhuohua','123','data1')
cursor = db.cursor()
Sql_1 = "select * from t_1;"
cursor.execute(Sql_1)
Result_1 = cursor.fetchall()
print(Result_1)
db.commit() #把执行任务提交到数据库
db.close()
def func_main():
try:
query_data()
except Exception as e:
print(f"输出失败,原因: {e}")
if __name__ == "__main__":
func_main()
脚本运行的结果:
[root@centos8 ~]# python3 xx.py [color=Blue]>> 1.txt[/color]
[root@centos8 ~]#
[root@centos8 ~]# cat 1.txt
[(1, 'zhuohua', 25), (2, 'Python', 35), (3, '李小芳', 45), (4, '张三', -55)]
[root@centos8 ~]#
######
例子七:
Linux客户端远程清空数据库中某个表的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8
import pymssql
def truncate_table():
db = pymssql.connect('192.168.168.137:1433','zhuohua','123','[color=Blue]data1[/color]')
cursor = db.cursor()
Sql_1 = "truncate table [color=DarkRed]t_1[/color]" #清空库data1中的表t_1的所有记录
cursor.execute(Sql_1)
db.commit() #把执行任务提交到数据库
db.close()
print ("清空表成功。")
def func_main():
try:
truncate_table()
except Exception as e:
print(f"清空表失败,原因: {e}")
if __name__ == "__main__":
func_main()
脚本运行的结果:
[root@centos8 ~]# python3 xx.py
[color=Purple]清空表成功。[/color]
[root@centos8 ~]#
######
例子八:
Linux客户端把本地文件里的所有记录添加到数据库中的某个表的脚本:
[root@centos8 ~]# cat xx.py
#coding=utf-8
import pymssql
def func1():
Path_1 = "./1.txt"
f_name = open(Path_1,'r')
Result_1 = f_name.readlines() #输出结果为列表,包含换行符
f_name.close()
print(Result_1)
print(type(Result_1))
print("-" * 10)
print(Result_1[0])
print(type(Result_1[0]))
print("-" * 10)
print("-" * 10)
Result_2 = Result_1[0].[color=Blue]strip()[/color] #去除左边和右边的空格、换行符
Result_2 = Result_2[color=Blue][2:][/color] #去掉前面2个字符
Result_2 = Result_2[color=Blue][:-2][/color] #去掉最后2个字符
Result_3 = Result_2.split('[color=DarkRed]), ([/color]')
print(Result_3)
insert_record(Result_3)
def insert_record(Result_3):
db = pymssql.connect('192.168.168.137:1433','zhuohua','123','data1')
cursor = db.cursor()
for Key_1 in Result_3:
#字段id为主键、标识符列,会自动增长,不用写
name = Key_1.split(',')[1]
age = Key_1.split(',')[2]
Sql_1 = f"INSERT INTO t_1(name,age) VALUES({name},{age})"
cursor.execute(Sql_1)
db.commit() #把执行任务提交到数据库
db.close()
if __name__ == "__main__":
func1()
脚本运行的结果:
[root@centos8 ~]# python3 xx.py
["[(1, 'zhuohua', 25), (2, 'Python', 35), (3, '李小芳', 45), (4, '张三', -55)]\n"]
<class 'list'>
----------
[(1, 'zhuohua', 25), (2, 'Python', 35), (3, '李小芳', 45), (4, '张三', -55)]
<class 'str'>
----------
----------
["1, 'zhuohua', 25", "2, 'Python', 35", "3, '李小芳', 45", "4, '张三', -55"]
[root@centos8 ~]#
再次在Linux客户端远程输出库data1中表t_1的所有记录:
[root@centos8 ~]# python3 yy.py
[(1, 'zhuohua', 25), (2, 'Python', 35), (3, '李小芳', 45), (4, '张三', -55)]
<class 'list'>
----------
id=1,姓名=zhuohua,年龄=25
id=2,姓名=Python,年龄=35
id=3,姓名=李小芳,年龄=45
id=4,姓名=张三,年龄=-55
[root@centos8 ~]#
######
######
Linux客户端卸载安装好的第三方库([color=Blue]pymssql[/color]):
[root@centos8 ~]# pip3 uninstall [color=Blue]pymssql[/color] -y
[color=Purple]Uninstalling pymssql-2.2.5:
Successfully uninstalled pymssql-2.2.5[/color]
[root@centos8 ~]#
Linux客户端离线安装第三方库(pymssql):(需要先手动下载软件包)
[root@centos8 ~]# pip3 install [color=Blue]pymssql-2.2.5-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.whl [/color]
[color=Purple]Processing ./pymssql-2.2.5-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.whl
Installing collected packages: pymssql
Successfully installed pymssql-2.2.5[/color]
[root@centos8 ~]#
相关文章:
[url=http://blog.zhuohua.store/viewthread.php?tid=139&extra=page%3D1]字典[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=197&extra=page%3D1]Python3脚本管理MSSQL2014[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=522&extra=]Zabbix5.0.12_调用Python3脚本监控Windows下的MSSQL[/url]
页:
[1]