python-整理--连接MSSQL

 1 import _mssql
 2 # 登录连接超时时间60S 
 3 # Timeout for connection and login in seconds, default 60.
 4 print(_mssql.login_timeout) 
 5 
 6 # 从这个错误严重程序起开始引发异常.默认值6适合大多数情况.
 7 # Minimum severity of errors at which to begin raising exceptions. The default value of 6 should be appropriate in most cases.
 8 print(_mssql.min_error_severity)
 9 
10 # 设置允许在任何给定时间打开的并发连接的最大数目。默认值是25。
11 # Sets maximum number of simultaneous connections allowed to be open at any given time. Default is 25.
12 # 这是个方法,加入参数即为设置.此处打印出来的是4096 数据库版本为SQLEXPRESS2014
13 print(_mssql.get_max_connections())
14 
15 #########################################################################################
16 # 建立连接使用这个对象 _mssql.MSSQLConnection 以下是连接参数
17 # 可以通过调用pymssql.connect创建这个类的一个实例（）。它接受以下参数。请注意，您可以使用关键字参数，而不是位置arguments.5。
18 # server (str) 
19     #r‘.\SQLEXPRESS‘ – SQLEXPRESS instance on local machine (Windows only)
20     #r‘(local)\SQLEXPRESS‘ – Same as above (Windows only)
21     #‘SQLHOST‘ – Default instance at default port (Windows only)
22     #‘SQLHOST‘ – Specific instance at specific port set up in freetds.conf (Linux/*nix only)
23     #‘SQLHOST,1433‘ – Specified TCP port at specified host
24     #‘SQLHOST:1433‘ – The same as above
25     #‘SQLHOST,5000‘ – If you have set up an instance to listen on port 5000
26     #‘SQLHOST:5000‘ – The same as above
27 # user (str) – 用户名 Database user to connect as
28 # password (str) – 密码 User’s password
29 # charset (str) –  字符集的名称为连接设置。Character set name to set for the connection.
30 # database (str) – 要初始连接到数据库中;通过默认，SQL Server的选择被设置为默认的特定用户数据库 The database you want to initially to connect to; by default, SQL Server selects the database which is set as the default for the specific user
31 # appname (str) – 设置为用于连接的应用程序的名称 Set the application name to use for the connection
32 # port (str) – TCP端口用于连接到服务器 the TCP port to use to connect to the server
33 # tds_version (str) – TDS协议版本索要。默认值：‘7.1‘ TDS protocol version to ask for. Default value: ‘7.1’
34 # conn_properties – SQL查询在连接建立时发送到服务器。可以是一个字符串或另一种字符串可迭代的。 默认值： SQL queries to send to the server upon connection establishment. Can be a string or another kind of iterable of strings. Default value:
35     #SET ARITHABORT ON;
36     #SET CONCAT_NULL_YIELDS_NULL ON;
37     #SET ANSI_NULLS ON;
38     #SET ANSI_NULL_DFLT_ON ON;
39     #SET ANSI_PADDING ON;
40     #SET ANSI_WARNINGS ON;
41     #SET ANSI_NULL_DFLT_ON ON;
42     #SET CURSOR_CLOSE_ON_COMMIT ON;
43     #SET QUOTED_IDENTIFIER ON;
44     #SET TEXTSIZE 2147483647; -- http://msdn.microsoft.com/en-us/library/aa259190%28v=sql.80%29.aspx
45 
46 # 建立连接对象
47 dbconn=_mssql.connect(server=r‘.\SQLEXPRESS‘,user=‘sa‘,password=‘123456‘,database=‘MirrorNet‘,charset=‘utf8‘)
48 # 是否连接状态
49 print(dbconn.connected)
50 # TDS版本MSSQLConnection.tds_version
51 print(dbconn.tds_version)
52 # 插入一个语句
53 re=dbconn.execute_non_query("insert into WuJiang(Name,WuLi,ZhiLi,Sudu,WuPin,ZhiWu) values(%s,%d,%d,%d,%s,%s)",(‘曹操‘,215,230,180,‘倚天剑‘,‘丞相‘))
54 # 影响的行数
55 print(dbconn.rows_affected)
56 # 标识值返回
57 print(dbconn.identity)
58 
59 dbconn.execute_query(‘SELECT * FROM WuJiang‘)
60 for row in dbconn:
61     print("Id={0},Name={1},WuLi={2},ZhiLi={3},Sudu={4},WuPin={5},ZhiWu={6}".format(row[‘Id‘], row[‘Name‘],row[‘WuLi‘],row[‘ZhiLi‘],row[‘Sudu‘],row[‘WuPin‘],row[‘ZhiWu‘]))
62 dbconn.close()