pymssql文档

原文地址 http://pymssql.org/en/latest/ref/_mssql.html

_mssql module reference

pymssql模块类，方法和属性的完整文档。

Complete documentation of _mssql module classes, methods and properties.

Module-level symbols

模块全局变量

Variables whose values you can change to alter behavior on a global basis.

_mssql.login_timeout

　　　连接和登录超时以秒为单位，默认为60。

Timeout for connection and login in seconds, default 60.

_mssql.min_error_severity

　　  这个错误的严重程度起开始引发异常。 默认值6,大多数情况下这应该是合适的。

Minimum severity of errors at which to begin raising exceptions. The default value of 6 should be appropriate in most cases.

Functions

_mssql.set_max_connections(number)

　　   设置允许在任何给定时间打开的并发连接的最大数目。默认值是25。

Sets maximum number of simultaneous connections allowed to be open at any given time. Default is 25.

_mssql.get_max_connections()

 　　  获取允许在任何给定时间打开的同时连接的当前最大数目。

Gets current maximum number of simultaneous connections allowed to be open at any given time.

MSSQLConnection class

class_mssql.MSSQLConnection

　　　此类表示MS SQL数据库连接。您可以查询并获得通过一个数据库连接的结果。
　　　你可以通过调用pymssql.connect创建这个类的一个实例（）。它接受以下参数。请注意，您可以使用关键字参数，而不是位置参数

This class represents an MS SQL database connection. You can make queries and obtain results through a database connection.

You can create an instance of this class by calling _mssql.connect(). It accepts the following arguments. Note that you can use keyword arguments, instead of positional arguments.

Parameters:

server (str) – Database server and instance you want to connect to. Valid examples are: r'.SQLEXPRESS' – SQLEXPRESS instance on local machine (Windows only) r'(local)SQLEXPRESS' – Same as above (Windows only) 'SQLHOST' – Default instance at default port (Windows only) 'SQLHOST' – Specific instance at specific port set up in freetds.conf (Linux/*nix only) 'SQLHOST,1433' – Specified TCP port at specified host 'SQLHOST:1433' – The same as above 'SQLHOST,5000' – If you have set up an instance to listen on port 5000 'SQLHOST:5000' – The same as above user (str) – Database user to connect as password (str) – User’s password charset (str) – Character set name to set for the connection. database (str) – 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 appname (str) – Set the application name to use for the connection port (str) – the TCP port to use to connect to the server tds_version (str) – TDS protocol version to ask for. Default value: None conn_properties – SQL queries to send to the server upon connection establishment. Can be a string or another kind of iterable of strings. Default value:

SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_NULLS ON;
SET ANSI_NULL_DFLT_ON ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ANSI_NULL_DFLT_ON ON;
SET CURSOR_CLOSE_ON_COMMIT ON;
SET QUOTED_IDENTIFIER ON;
SET TEXTSIZE 2147483647; -- http://msdn.microsoft.com/en-us/library/aa259190%28v=sql.80%29.aspx

New in version 2.1.1: The conn_properties parameter.　　2.1.1新版本连接参数

2.1.1版本更改：2.1.1之前，没有byconn属性指定的初始化查询是不是定制的，它的值被硬编码为上面显示的文字。

Changed in version 2.1.1: Before 2.1.1, the initialization queries now specified byconn_properties wasn’t customizable and its value was hard-coded to the literal shown above.

Note 注意

If you need to connect to Azure:　　如果您需要连接到Azure

• Use FreeTDS 0.91 or newer (this is already a requirement of pymssql) 使用freetds的0.91或更高版本（这已经是pymssql的要求）
• Use TDS 7.1 or newer 使用TDS7.1或更高
• Make sure FreeTDS is built with SSL support 确保FreeTDS编译有SSL支持
• Specify the database name you are connecting to in the database parameter 连接参数中指定数据库名
• Specify the username in username@servername form 指定用户名@服务器的用户名形式

New in version 2.1.1: The ability to connect to Azure.新版本2.1.1可连接到AZURE

2.2.0更新(这里是有错误吗,觉得版本号不对.不是才2.1.1吗,怎么这里讲2.2.0了呢),tds_version这个参数默认值是None.2.0.0到2.1.2默认值为7.1

Changed in version 2.2.0: The default value of the tds_version parameter was changed to None. Between versions 2.0.0 and 2.1.2 its default value was '7.1'.

Warning警告

该tds_version参数有没有一个默认值。这意味着两件事情：

The tds_version parameter has a default value of None. This means two things:

1. You can’t rely anymore in the old '7.1' default value and (不能再依靠旧值7.1)
2. Now you’ll need to either(你或可能需要)

• Specify its value explicitly by passing a value for this parameter or (传递一个明确的值)
• Configure it using facilities provided by FreeTDS (see here and here) (利用freetds的提供设施配置（见这里和这里）)

这可能看起来繁琐，但同时意味着使用pymssql/ pymssql没有在freetds.conf文件甚至没有freetds.conf在所有使用的节服务器时，你可以现在已完全配置SQL Server的连接的特性。与pymssql版本2.0.0及以上开始2.1.2版已经是可以设置TDS协议版本要求连接到服务器时，但如果没有指定使用的版本7.1。

This might look cumbersome but at the same time means you can now fully configure the characteristics of a connection to SQL Server when using pymssql/_mssql without using a stanza for the server in the freetds.conf file or even with no freetds.conf at all. Starting with pymssql version 2.0.0 and up to version 2.1.2 it was already possible to set the TDS protocol version to ask for when connecting to the server but version 7.1 was used if not specified.

MSSQLConnection object properties   SQLConnection对象的属性

MSSQLConnection.connected

　　   如果对象连接到数据库返回True否则返回False

True if the connection object has an open connection to a database, False otherwise.

MSSQLConnection.charset

　　   获取或设置字符集名(一般设为utf8)

Character set name that was passed to _mssql.connect().

MSSQLConnection.identity

　　  返回最后插入行的标识值。如果之前的操作并未涉及插入一行与标识列的表，则返回None。用法示例 - 假设人的表包含除name列标识列：

Returns identity value of last inserted row. If previous operation did not involve inserting a row into a table with identity column, None is returned. Example usage – assume that persons table contains an identity column in addition to name column:

conn.execute_non_query("INSERT INTO persons (name) VALUES('John Doe')")# 插入一条数据,ID为自增长的
print "Last inserted row has id = " + conn.identity # 得到这个自增长ID值

MSSQLConnection.query_timeout

　　　　在几秒钟内查询超时，默认为0，这意味着无限期地等待结果。由于道路的DB-Library为C工作，设置此属性影响从目前的Python脚本打开的所有连接

　　　 （或者，很从技术上说，所有连接从DBINIT的这个实例制作（））。

Query timeout in seconds, default is 0, which means to wait indefinitely for results. Due to the way DB-Library for C works, setting this property affects all connections opened from the current Python script (or, very technically, all connections made from this instance of dbinit()).

MSSQLConnection.rows_affected

         最后一次执行查询影响的行数,对于SELECT语句，此值为读取所有行之后才有意义。(增册改后返回受影响行数,如果执行insert后,即调用此属性,能保证它是该insert影响的行数吗)

Number of rows affected by last query. For SELECT statements this value is only meaningful after reading all rows.

MSSQLConnection.debug_queries

 　　  如果设置为true，所有的查询格式化和报价，只是被发送到SQL Server之前，之后打印到stderr。如果您怀疑格式化或引用的问题可能会有所帮助。

If set to true, all queries are printed to stderr after formatting and quoting, just before being sent to SQL Server. It may be helpful if you suspect problems with formatting or quoting.

MSSQLConnection.tds_version

　　　此连接使用的TDS版本。可以是4.2的，5.07.0，7.1和7.2。

　　　2.2.0版本有改变：正确性和一致性用于指示从8.0改为7.1 pymssql2.2.0 TDS7.1的值。(这里的版本2.2.0也可能有误)

The TDS version used by this connection. Can be one of 4.2, 5.0 7.0, 7.1 and 7.2.

Changed in version 2.2.0: For correctness and consistency the value used to indicate TDS 7.1 changed from 8.0 to 7.1 on pymssql 2.2.0.

MSSQLConnection object methods 方法

MSSQLConnection.cancel()

 　　　取消从上次SQL操作所有待处理结果。多次调用不异常

Cancel all pending results from the last SQL operation. It can be called more than one time in a row. No exception is raised in this case.

MSSQLConnection.close()

 　　  关闭连接并释放所使用的所有内存。多次调用不异常

Close the connection and free all memory used. It can be called more than one time in a row. No exception is raised in this case.

MSSQLConnection.execute_query(query_string)

 

MSSQLConnection.execute_query(query_string, params) 　　   

　　　此方法发送一个查询向此对象实例所连接的MS SQL Server中。将引发异常的故障。如果没有等待执行此命令之前结果或行，它们丢弃。
　　　调用此方法后你可能会遍历连接对象来获取查询返回的行。
　　　您可以使用Python的格式和所有价值得到正确引用。详情请参阅例子。
　　   这种方法的目的是要在返回结果的查询，即选择使用。

This method sends a query to the MS SQL Server to which this object instance is connected. An exception is raised on failure. If there are pending results or rows prior to executing this command, they are silently discarded.

After calling this method you may iterate over the connection object to get rows returned by the query.

You can use Python formatting and all values get properly quoted. Please see examples for details.

This method is intented to be used on queries that return results, i.e. SELECT.

MSSQLConnection.execute_non_query(query_string)

 

MSSQLConnection.execute_non_query(query_string, params)

　　　此方法发送一个查询向此对象实例所连接的MS SQL Server中。完成后，其结果（如果有的话）被丢弃。将引发异常的故障。如果没有等待执行此命令之前结果或行，它们丢弃。

　　　您可以使用Python的格式和所有价值得到正确引用。详情请参阅例子。

　　　这种方法对于INSERT，UPDATE，DELETE，以及用于数据定义语言命令，即，当你需要改变你的数据库架构。

This method sends a query to the MS SQL Server to which this object instance is connected. After completion, its results (if any) are discarded. An exception is raised on failure. If there are pending results or rows prior to executing this command, they are silently discarded.

You can use Python formatting and all values get properly quoted. Please see examples for details.

This method is useful for INSERT, UPDATE, DELETE, and for Data Definition Language commands, i.e. when you need to alter your database schema.

MSSQLConnection.execute_scalar(query_string)

 

MSSQLConnection.execute_scalar(query_string, params)

　　　该方法发送一个查询到该对象实例所连接的MS SQL服务器，然后从结果返回第一行的第一列中。将引发异常的故障。如果没有等待执行此命令之前结果或行，它们丢弃。

　　　您可以使用Python的格式和所有价值得到正确引用。详情请参阅例子。

　　　如果希望从一个查询只是一个单一的值，如在下面的例子中该方法是有用的。这种方法以同样的方式工作原理ITER（conn）的的.next（）[0]。剩余的行中，如果有的话，仍然可以调用此方法后迭代。

This method sends a query to the MS SQL Server to which this object instance is connected, then returns first column of first row from result. An exception is raised on failure. If there are pending results or rows prior to executing this command, they are silently discarded.

You can use Python formatting and all values get properly quoted. Please see examples for details.

This method is useful if you want just a single value from a query, as in the example below. This method works in the same way as iter(conn).next()[0]. Remaining rows, if any, can still be iterated after calling this method.

Example usage:

count = conn.execute_scalar("SELECT COUNT(*) FROM employees")

MSSQLConnection.execute_row(query_string)

 

MSSQLConnection.execute_row(query_string, params)

　　　该方法发送一个查询到该对象实例所连接的MS SQL服务器，然后返回从结果数据的第一行。将引发异常的故障。如果没有等待执行此命令之前结果或行，它们丢弃。

　　　您可以使用Python的格式和所有价值得到正确引用。详情请参阅例子。

　　　如果你想要一个单行，不想或不需要遍历连接对象此方法非常有用。此方法适用于相同的方式ITER（conn）的的.next（），以获得单列。剩余的行中，如果有的话，仍然可以调用此方法后迭代。

This method sends a query to the MS SQL Server to which this object instance is connected, then returns first row of data from result. An exception is raised on failure. If there are pending results or rows prior to executing this command, they are silently discarded.

You can use Python formatting and all values get properly quoted. Please see examples for details.

This method is useful if you want just a single row and don’t want or don’t need to iterate over the connection object. This method works in the same way as iter(conn).next() to obtain single row. Remaining rows, if any, can still be iterated after calling this method.

Example usage:

empinfo = conn.execute_row("SELECT * FROM employees WHERE empid=10")

MSSQLConnection.get_header()

　　　这种方法是基础设施，并不需要由代码调用。它得到了Python DB-API标准的头信息。返回描述当前结果头7元元组的列表。只有名称和DB-API兼容型填充，数据的其余为无，在规范允许的。(这个方法返回当前结果的字段头信息,也就是列名)

This method is infrastructure and doesn’t need to be called by your code. It gets the Python DB-API compliant header information. Returns a list of 7-element tuples describing current result header. Only name and DB-API compliant type is filled, rest of the data is None, as permitted by the specs.

MSSQLConnection.init_procedure(name)

　　   创建将用于与给定的名称来调用存储过程中的MSSQL存储过程对象。

Create an MSSQLStoredProcedure object that will be used to invoke thestored procedure with the given name.

MSSQLConnection.nextresult()

　　　移动到下一个结果，跳过所有未决行。这种方法获取并丢弃当前操作剩余的任何行，然后前进到下一个结果（如果有的话）。返回True值，如果下一组可用，无其他。将引发异常的故障。

Move to the next result, skipping all pending rows. This method fetches and discards any rows remaining from current operation, then it advances to next result (if any). Returns True value if next set is available, None otherwise. An exception is raised on failure.

MSSQLConnection.select_db(dbname)

   　　此功能使给定的数据库中的当前之一。将引发异常的故障。

This function makes the given database the current one. An exception is raised on failure.

MSSQLConnection.__iter__()

 

MSSQLConnection.next()

New in version 2.1.0.版本新增

这些方法实现了Python迭代器协议。你很可能不会使用迭代器直接调用它们，而是间接的。

These methods implement the Python iterator protocol. You most likely will not call them directly, but indirectly by using iterators.

MSSQLConnection.set_msghandler(handler)

New in version 2.1.1.版本新增

此方法允许设置一个消息处理功能的连接，以允许客户端来访问从服务器返回的消息。

传递给此方法消息处理函数处理程序的签名必须是：

This method allows setting a message handler function for the connection to allow a client to gain access to the messages returned from the server.

The signature of the message handler function handler passed to this method must be:

def my_msg_handler(msgstate, severity, srvname, procname, line, msgtext):
    # The body of the message handler.

信息状态,严重程度和行号是数值,服务器,过程和查询是字符串

msgstate, severity and line will be integers, srvname, procname and msgtext will be strings.

MSSQLStoredProcedure class 存储过程类

class_mssql.MSSQLStoredProcedure

　　　此类表示的存储过程。您可以通过调用init_procedure（）方法，在SQLConnection对象上创建这个类的一个对象。

This class represents a stored procedure. You create an object of this class by calling theinit_procedure() method on MSSQLConnection object.

MSSQLStoredProcedure object properties 存储过程对象的属性

MSSQLStoredProcedure.connection

An underlying MSSQLConnection object.     基本的MSSQLConnection连接对象

MSSQLStoredProcedure.name

The name of the procedure that this object represents.代表存储过程对象的名字

MSSQLStoredProcedure.parameters

The parameters that have been bound to this procedure.绑定到该存储过程的参数

MSSQLStoredProcedure object methods

这个方法绑定一个参数,value和dbtype必须

MSSQLStoredProcedure.bind(value, dbtype, name=None, output=False, null=False, max_length=-1)

This method binds a parameter to the stored procedure. value and dbtype are mandatory arguments, the rest is optional.

Parameters:

value – Is the value to store in the parameter. dbtype – Is one of: SQLBINARY, SQLBIT, SQLBITN, SQLCHAR, SQLDATETIME,SQLDATETIM4, SQLDATETIMN, SQLDECIMAL, SQLFLT4, SQLFLT8, SQLFLTN, SQLIMAGE,SQLINT1, SQLINT2, SQLINT4, SQLINT8, SQLINTN, SQLMONEY, SQLMONEY4,SQLMONEYN, SQLNUMERIC, SQLREAL, SQLTEXT, SQLVARBINARY, SQLVARCHAR,SQLUUID. name – Is the name of the parameter. Needs to be in "@name" form. output – Is the direction of the parameter. True indicates that it is an output parameter i.e. it returns a value after procedure execution (in SQL DDL they are declared by using the "output" suffix, e.g. "@aname varchar(10) output"). null – Boolean. Signals than NULL must be the value to be bound to the argument of this input parameter. max_length – Is the maximum data length for this parameter to be returned from the stored procedure.

MSSQLStoredProcedure.execute()

　　　执行存储过程

Execute the stored procedure.

Module-level exceptions 异常

Exception hierarchy:

MSSQLException
|
+-- MSSQLDriverException
|
+-- MSSQLDatabaseException

exception_mssql.MSSQLDriverException


　　　MSSQL驱动程序引发异常每当有MSSQL中的一个问题 - 例如，内存不足的数据结构，等等。

MSSQLDriverException is raised whenever there is a problem within _mssql – e.g. insufficient memory for data structures, and so on.

exception_mssql.MSSQLDatabaseException

　　 　　　MSSQL数据库异常引发每当有与数据库中的问题 - 例如，查询语法错误，无效的对象名称等。在这种情况下，你可以使用下面的属性来访问错误的详细信息：

MSSQLDatabaseException is raised whenever there is a problem with the database – e.g. query syntax error, invalid object name and so on. In this case you can use the following properties to access details of the error:

number

The error code, as returned by SQL Server.错误代码，由SQL Server返回。

severity

 　　  所谓的严重性级别，如由SQL Server返回。如果该属性的值小于pymssql.min ERROR_SEVERITY的值，这样的误差将被忽略和异常没有提出。

The so-called severity level, as returned by SQL Server. If value of this property is less than the value of _mssql.min_error_severity, such errors are ignored and exceptions are not raised.

state

The third error code, as returned by SQL Server.第三个错误代码，由SQL Server返回。

message

The error message, as returned by SQL Server.错误信息，如SQL Server返回。

你可以找到如何在pymssql例子页面的底部使用这些数据的例子。

You can find an example of how to use this data at the bottom of _mssql examples page.