标题:
CentOS8使用Python3脚本管理MSSQL2008R2
[打印本页]
作者:
admin
时间:
2020-7-20 23:41
标题:
CentOS8使用Python3脚本管理MSSQL2008R2
笺注:MSSQL服务器在
MSSQL2008R2的备份与还原
的基础上进行的。 库data1中的表t_1的表结构:
2022-5-5 18:39
注释:具体表名为
data1.dbo.t_1
创建了一个数据库用户zhuohua,对库data1有完全控制的权限:
2022-5-5 18:40
防火墙记得打开TCP 1433端口, 打开方法可参考:
Windows2008R2_UPUPW搭建WordPress+Discuz!7.2
笺注:假如只是在远程客户端通过Python3脚本管理MSSQL数据库,那么MSSQL服务器连Python3都不需要安装。 ###### ###### Linux客户端连接公网安装第三方库(
pymssql
):(可以指定软件的版本) [root@centos8 ~]# pip3 install
pymssql==2.2.5
-i http://mirrors.aliyun.com/pypi/simple --trusted-host=
mirrors.aliyun.com
Looking in indexes: http://mirrors.aliyun.com/pypi/simple Collecting pymssql==2.2.5 Downloading
http://mirrors.aliyun.com/pypi/packages/9a/6d/d20882e975f110b3810b8289b53c9dcce9f975dd3f9305f5262381336389/pymssql-2.2.5-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.whl
(2.6MB) 100% |████████████████████████████████| 2.6MB 1.8MB/s Installing collected packages: pymssql Successfully installed pymssql-2.2.5 [root@centos8 ~]# 列出当前环境所有已经安装的第三方库的名称和其版本号: [root@centos8 ~]# pip3 freeze
pymssql==2.2.5
[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','
sa
','Jacky888','
master
') #连接数据库;MSSQL服务器的IP地址(192.168.168.137)、端口号(1433)、用户名(
sa
)、用户密码、其中一个库的名称(
master
) cursor = db.cursor(
as_dict=True
) #指定返回值为字典 Sql_1 = "select @@VERSION as MSSQL的版本信息" #查询MSSQL的版本信息 cursor.execute(Sql_1) #执行SQL语句 Result_1 = cursor.
fetchone
() #使用
fetchone
只能获取一个元素 print(Result_1) print(type(Result_1)) print("-" * 10) print("数据库连接成功,数据库的版本信息:") Result_2 = Result_1['MSSQL的版本信息'] #获取字典中键
'MSSQL的版本信息'
的值 print(Result_2) print("-" * 10) Result_3 = Result_2
[0:34]
#获取结果的前面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
(Build 7601: Service Pack 1) (Hypervisor)\n'}
---------- 数据库连接成功,数据库的版本信息:
Microsoft SQL Server 2008 R2 (RTM)
- 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1
(Build 7601: Service Pack 1) (Hypervisor) ----------
Microsoft SQL Server 2008 R2 (RTM)
[root@centos8 ~]# ###### 例子二: Linux客户端远程测试与数据库的连接的脚本: [root@centos8 ~]# cat xx.py #coding=utf-8 import pymssql def db_connect(): db = pymssql.connect('192.168.168.137:1433','
zhuohua
','123','
data1
') #连接数据库;MSSQL服务器的IP地址(192.168.168.137)、端口号(1433)、用户名(
zhuohua
)、用户密码、其中一个库的名称(
data1
) cursor = db.cursor() Sql_1 = "select @@VERSION as sql_server_v;" #查询MSSQL的版本信息 cursor.execute(Sql_1) #执行SQL语句 Result_1 = cursor.
fetchone()
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
(Build 7601: Service Pack 1) (Hypervisor)\n',)
---------- 数据库连接成功,数据库的版本信息:
Microsoft SQL Server 2008 R2 (RTM)
- 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1
(Build 7601: Service Pack 1) (Hypervisor) ----------
Microsoft SQL Server 2008 R2 (RTM)
[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','
zhuohua
','123','
data1
') cursor = db.cursor() Sql_1 = "select * from
t_1
" #这是在库data1中查询表t_1 cursor.execute(Sql_1) Result_1 = cursor.
fetchall
() #使用
fetchall
可以获取多个元素 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)]
---------- 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','
data1
') cursor = db.cursor() Sql_1 = "select * from
t_1
" cursor.execute(Sql_1) Result_1 = cursor.
fetchall()
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]
age_abs = abs(Key_1[2])
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)]
---------- id=1,姓名=zhuohua,年龄=25,
年龄的绝对值=25
id=2,姓名=Python,年龄=35,
年龄的绝对值=35
id=3,姓名=李小芳,年龄=45,
年龄的绝对值=45
id=4,姓名=张三,年龄=-55,
年龄的绝对值=55
[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
where name = 'Python' or id =3
" cursor.execute(Sql_1) Result_1 = cursor.
fetchall()
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]
name_len = len(Key_1[1])
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)]
---------- id=2,姓名=Python,年龄=35,
姓名的长度=6
id=3,姓名=李小芳,年龄=45,
姓名的长度=3
[root@centos8 ~]# 脚本运行的结果:(假如没有找到匹配的记录时) [root@centos8 ~]# python3 xx.py
[]
----------
[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
>> 1.txt
[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','
data1
') cursor = db.cursor() Sql_1 = "truncate table
t_1
" #清空库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
清空表成功。
[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].
strip()
#去除左边和右边的空格、换行符 Result_2 = Result_2
[2:]
#去掉前面2个字符 Result_2 = Result_2
[:-2]
#去掉最后2个字符 Result_3 = Result_2.split('
), (
') 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"]
---------- [(1, 'zhuohua', 25), (2, 'Python', 35), (3, '李小芳', 45), (4, '张三', -55)]
---------- ---------- ["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)]
---------- id=1,姓名=zhuohua,年龄=25 id=2,姓名=Python,年龄=35 id=3,姓名=李小芳,年龄=45 id=4,姓名=张三,年龄=-55 [root@centos8 ~]# ###### ###### Linux客户端卸载安装好的第三方库(
pymssql
): [root@centos8 ~]# pip3 uninstall
pymssql
-y
Uninstalling pymssql-2.2.5: Successfully uninstalled pymssql-2.2.5
[root@centos8 ~]# Linux客户端离线安装第三方库(pymssql):(需要先手动下载软件包) [root@centos8 ~]# pip3 install
pymssql-2.2.5-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.whl
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
[root@centos8 ~]# 相关文章:
字典
Python3脚本管理MSSQL2014
Zabbix5.0.12_调用Python3脚本监控Windows下的MSSQL
图片附件:
图片1.png
(2022-5-5 18:39, 42.97 KB) / 下载次数 79
http://blog.zhuohua.store/attachment.php?aid=19903&k=af47e05d1863bcdc5627f7f2b98edd0f&t=1713463875&sid=hDO0F8
图片附件:
图片2.png
(2022-5-5 18:40, 81.43 KB) / 下载次数 68
http://blog.zhuohua.store/attachment.php?aid=19904&k=c1ae88542ef9cf0cafd5db2c98906e76&t=1713463875&sid=hDO0F8
欢迎光临 blog.zhuohua.store (http://blog.zhuohua.store/)
Powered by Discuz! 7.2