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 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.