pymssql文档

时间:2022-10-27 15:35:58

原文地址 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.25.0 7.07.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_stringparams     

   此方法发送一个查询向此对象实例所连接的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_stringparams)

   此方法发送一个查询向此对象实例所连接的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 INSERTUPDATEDELETE, 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_stringparams)

   该方法发送一个查询到该对象实例所连接的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_stringparams)

   该方法发送一个查询到该对象实例所连接的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.

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

msgstateseverity and line will be integers, srvnameprocname 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(valuedbtypename=Noneoutput=Falsenull=Falsemax_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: SQLBINARYSQLBITSQLBITNSQLCHARSQLDATETIME,SQLDATETIM4SQLDATETIMNSQLDECIMALSQLFLT4SQLFLT8SQLFLTNSQLIMAGE,SQLINT1SQLINT2SQLINT4SQLINT8SQLINTNSQLMONEYSQLMONEY4,SQLMONEYNSQLNUMERICSQLREALSQLTEXTSQLVARBINARYSQLVARCHAR,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.

pymssql文档的更多相关文章

  1. pymssql文档(转)

    pymssql methods set_max_connections(number) -- Sets maximum number of simultaneous database connecti ...

  2. C#给PDF文档添加文本和图片页眉

    页眉常用于显示文档的附加信息,我们可以在页眉中插入文本或者图形,例如,页码.日期.公司徽标.文档标题.文件名或作者名等等.那么我们如何以编程的方式添加页眉呢?今天,这篇文章向大家分享如何使用了免费组件 ...

  3. dotNET跨平台相关文档整理

    一直在从事C#开发的相关技术工作,从C# 1.0一路用到现在的C# 6.0, 通常情况下被局限于Windows平台,Mono项目把我们C#程序带到了Windows之外的平台,在工作之余花了很多时间在M ...

  4. ABP文档 - Javascript Api - AJAX

    本节内容: AJAX操作相关问题 ABP的方式 AJAX 返回信息 处理错误 HTTP 状态码 WrapResult和DontWrapResult特性 Asp.net Mvc 控制器 Asp.net ...

  5. ABP文档 - EntityFramework 集成

    文档目录 本节内容: Nuget 包 DbContext 仓储 默认仓储 自定义仓储 特定的仓储基类 自定义仓储示例 仓储最佳实践 ABP可使用任何ORM框架,它已经内置了EntityFrame(以下 ...

  6. ABP文档 - SignalR 集成

    文档目录 本节内容: 简介 安装 服务端 客户端 连接确立 内置功能 通知 在线客户端 帕斯卡 vs 骆峰式 你的SignalR代码 简介 使用Abp.Web.SignalR nuget包,使基于应用 ...

  7. ABP文档 - 通知系统

    文档目录 本节内容: 简介 发送模式 通知类型 通知数据 通知重要性 关于通知持久化 订阅通知 发布通知 用户通知管理器 实时通知 客户端 通知存储 通知定义 简介 通知用来告知用户系统里特定的事件发 ...

  8. ABP文档 - Hangfire 集成

    文档目录 本节内容: 简介 集成 Hangfire 面板授权 简介 Hangfire是一个综合的后台作业管理器,可以在ABP里集成它替代默认的后台作业管理器,你可以为Hangfire使用相同的后台作业 ...

  9. ABP文档 - 后台作业和工作者

    文档目录 本节内容: 简介 后台作业 关于作业持久化 创建一个后台作业 在队列里添加一个新作业 默认的后台作业管理器 后台作业存储 配置 禁用作业执行 Hangfire 集成 后台工作者 创建一个后台 ...

随机推荐

  1. SQL数据库基础(二)

    数据类型: --类似于C#中的数据类型 Datetime   范围是:1753.1.1—— 9999.12.31 Smalldatetime      1900.1.1 ——2079.6.6 操作: ...

  2. Android 自定义View,仿微信视频播放按钮

    闲着,尝试实现了新版微信视频播放按钮,使用的是自定义View,先来个简单的效果图...真的很简单哈. 由于暂时用不到,加上时间原因,加上实在是没意思,加上……,本控件就没有实现自定义属性,有兴趣的朋友 ...

  3. Gray Code -- LeetCode

    原标题链接: http://oj.leetcode.com/problems/gray-code/  这道题要求求出n位的格雷码相应的二进制数,主要在于找到一种格雷码的递增方法(格雷码并非唯一的,能够 ...

  4. qt添加资源文件方法

    File->new file->file and classes->Qt->qt resources->   add name   add->add prefix- ...

  5. Restaurant

    Restaurant Time Limit:4000MS     Memory Limit:262144KB     64bit IO Format:%I64d & %I64u Submit  ...

  6. numpy用法小结

    前言 个人感觉网上对numpy的总结感觉不够详尽细致,在这里我对numpy做个相对细致的小结吧,在数据分析与人工智能方面会有所涉及到的东西在这里都说说吧,也是对自己学习的一种小结! numpy用法的介 ...

  7. [algorithm] Dijkstra双栈算法表达式求值算法

    一.原理 Dijkstra所做的一个算法,双栈求值,用两个栈(一个保存运算符,一个用于保存操作数), 表达式由括号,运算符和操作数组成. (1).将操作数压入操作数栈 (2).将运算符压入运算符栈: ...

  8. [Python] 拉格朗日插值

    #-*— coding:utf-8 -*- #Program 0.3 Lagrange Interpolation import matplotlib.pyplot as plt import num ...

  9. (2)Mac环境搭建

    创建HelloWorld项目 将刚才下载的压缩包解压到你指定的文件夹里. 进入到目录cocos2d-x-3.2alpha0/tools/cocos2d-console/bin/cocos.py 打开终 ...

  10. OD 实验(十五) - 对一个程序的逆向

    程序: 打开程序 出现一个 NAG 窗口 这是主界面 点击 Exit 程序出现 NAG 窗口,然后退出 用 PEiD 看一下 是用 VC++ 6.0 写的程序 逆向: 用 OD 载入程序 跑一下程序 ...