Python连接SqlServer数据库-pymssql进阶

• 支持环境：

Python:	Python 2.x: 2.7 or newer. Python 3.x: 3.3 or newer.
FreeTDS:	0.82 or newer.
Cython:	0.15 or newer.
Microsoft SQL Server:    2005 or newer.

• Windows安装方式:

从https://www.lfd.uci.edu/~gohlke/pythonlibs/下载最新的Pymssq的whl，

pymssql‑2.1.4.dev5‑cp37‑cp37m‑win32.whl

pymssql‑2.1.4.dev5‑cp37‑cp37m‑win_amd64.whl

下载完成后通过pip install pymssql‑2.1.4.dev5‑cp37‑cp37m‑win_amd64.whl 安装

使用方式

A、使用pymssql     

1、导入包

import pymssql     

1、创建连接对象：

conn=pymssql.connect(server='.', user='', password='', database='', timeout=0, login_timeout=60, charset='UTF-8', as_dict=False, host='', appname=None, port='1433', conn_properties, autocommit=False, tds_version='7.1')

Parameters: 参数说明
server (str) – database host 主机名（主机地址）
user (str) – database user to connect as 用户名
password (str) – user’s password 密码
database (str) – the database to initially connect to 数据库名称
timeout (int) – query timeout in seconds, default 0 (no timeout) 查询超时
login_timeout (int) – timeout for connection and login in seconds, default 60 登录超时
charset (str) – character set with which to connect to the database 连接所使用字符集，中文一般使用 'utf8'

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: See _mssql.connect() 连接数据库自动发起的查询语句，默认为‘

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;

’ as_dict (bool) – whether rows should be returned as dictionaries instead of tuples是否使用字典作为查询结果返回方式，默认为元组
appname (str) – Set the application name to use for the connection
port (str) – the TCP port to use to connect to the server  TCP 端口
autocommit (bool) – Whether to use default autocommiting mode or not 连接关闭时是否自动提交，默认为不自动提交
tds_version (str) – TDS protocol version to use. 使用的TDS版本，默认为7.1

*设置最大连接数 

pymssql. set_max_connections ( number )

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

2、连接对象相关操作

Connection.autocommit(status)设置自动提交，status为Bool值

Connection.close()关闭连接

Connection.cursor()返回一个cursor对象，用于数据库查询等操作

Connectreturnvalueion.commit()提交更改

Connection.rollback()回滚更改

3、cursor对象相关操作

属性