python操作MSSQL专题

python操作MSSQL专题

[整理人:hkebao#126.com 整理时间:2010-01-12]

一、安装pymssql包

二、验证安装是否成功的办法就是在python命令行中导入 import pymssql 如果没报错说明成功配置了

三、详细的API说明整理

3.1 建立连接

conn = pymssql.connect(host='192.168.100.40',user='123456', password='789456', database='abc')

PS:要注意一点你的PY文件名称不为取为pymssql.py 你要这样取名字就等死吧！

3.2 得到cursor 对象

cur = conn.cursor()  有了这个对象之后就可以方便操作常用的数据调用API了

3.3 执行CIUD操作即 delete update insert create操作

cur.execute('create table a(id int,name varchar(20))')

cur.executemany('insert into a(id,name) values(%d,%s)',\

                [(1,'John'),(2,'Jane')])

其实executemany方法是有两个参数的。可以套用第二种写法这样的话我们是非常方便操作我们的自定义SQL语句！

conn.commit()  # you must call commit() to persist your data if you don't set autocommit to True

如果没有设置自动提交的话我们得手工设置一下。

PS：一般的设计是打开一次数据库连接之后做完全部要做的操作之后再一次性提交这个就是事务处理。

即我的做法一般是不将其设置为默认打开的。要手工去提交一次所以我往往会在得到连接对象之后加上这么一句：conn.autocommit(False)    将自动提交关闭

3.4 做查询操作的API

cur.execute('SELECT * FROM a WHERE name=%s', 'Jane')

PS：奇怪了为什么没有将这个查询的结果集返回给某个数据集对象呢？

将此查询的结果集赋给一个数据集即类似的ResultSet 对象

row = cur.fetchone()   

3.5 遍历得到的数据集

while row:

    print "ID=%d,Name=%s" % (row[0],row[1])

row = cur.fetchone()

最后记得要关闭数据库连接：conn.close()

 

 

以下介绍常用对象的方法及属性

一、Cursor对象的属性及方法（通过连接对象创建的）

属性篇

Rowcount    表示受影响的行数。

示例：

import pymssql

conn = pymssql.connect(host='localhost', user='sa', password='hkebao', database='tyspeed')

cur = conn.cursor()

cur.execute("insert into a(id,name) values(123,'dasfdsaf')")

print str(cur.rowcount)         返回1

conn.close()

表示执行当前的这条语句之后受影响到的行数是多少条！

 

方法篇

Close() 关闭cursor

Execute(operation) 

Execute(operation,params)  

示例：

cur.execute("insert into a(id,name) values(1,'a')")

cur.execute('insert into a(id,name) values(%d,%s)',\

                (1,'John'))

PS：execute的用法就是将一条SQL语句直接拼结好之后再执行一遍。

而execute(operation,params) 表示我们会将SQL语句进行了分拆。即动态拼结SQL语句将参数值动态赋值。这样的话比较方便构造SQL语句。如果你想一次插入多条语句的话我们可以用下面的

Executemany 方法。这个就表示一次执行多次SQL语句了。

Executemany(operation,params_seq)

示例：

cur.executemany('insert into a(id,name) values(%d,%s)',\

                [(1,'John'),(2,'Jane')])

PS：表示一次会执行多条更新语句！

或许我想这里面会有execute 与 executemany 的区别就在于一次多条执行还是只做一次更新操作

以下是提取SELECT操作

fetchone()

Fetch the next row of a query result, returning a tuple, or a dictionary if as_dict was passed to pymssql.connect(), or None if no more data is available. Raises OperationalError if previous call to execute*() did not produce any result set or no call was issued yet.

说明：如果在 connection中设置了字典类型的话则可以返回字典类型了。如果没有则返回元组

示例：

cur = conn.cursor()

cur.execute('SELECT * FROM a WHERE 1=1')

row = cur.fetchone()        #相当于是返回第一行了

while row:                  #如果还有下一行在JAVA中while(rs.next())表示还有下一行

    print "ID=%d,Name=%s" % (row[0],row[1])

    row = cur.fetchone() #再去取下一行记录 这个方法是表示提取下一行记录的

PS：如果把 while :里面的row = cur.fetchone() 删除掉的话就会进入死循环状态

因为第一行永远存在。而这个方法fetchone是表示返回下一行记录。这样游标是会走到下一行的

心得：我想这个方法我们可以用来提取一行记录。比如说类似这样的SQL语句

Select * from table where id=1      只有一条记录就可以用这个方法来处理

示例：

cur = conn.cursor()

cur.execute('SELECT * FROM a WHERE id=1')

row = cur.fetchone()

print "ID=%d,Name=%s" % (row[0],row[1])

整理：以后如果你想用到这样的SQL语句就可以考虑用这个API操作。当然这个API也可以全部提取其全部的记录集。不过我不建议这样写我还是建议你使用fetchall 方法。如果想一次提取部分数据集的话就可以用fetchmany 的操作了(注：分页不用fetchmany 而是动态拼结SQL语句)

 

 

fetchmany(size=None)

Fetch the next batch of rows of a query result, returning a list of tuples, or a list of dictionaries if as_dict was passed to pymssql.connect(), or an empty list if no more data is available. You can adjust the batch size using the size parameter, which is preserved across many calls to this method. Raises OperationalError if previous call to execute*() did not produce any result set or no call was issued yet.

说明：上面这个fetchone 表示一次只取一条下一条记录。而这个方法是一次取一批而且是可以指定其大小的。举例说明如下：

示例：

第一个例子：

cur = conn.cursor()

cur.execute('SELECT * FROM a WHERE 1=1')

row = cur.fetchmany(10)                     #表示一次只提取10条记录

for i in range(len(row)):                   #开始遍历此提取出来的结果集

print row[i][0],row[i][1]                #返回的是a list of tuples

问题：一次只提取10条记录可能不是我们所想要的结果吧！

我的理解：有的时候我们可能不需要将整个数据集的记录全部提取出来（如果你想要提取的话可以考虑用下面的fetchall方法呀。只想提取若干条记录就可以用这个方法了！）

fetchall()

Fetch all remaining rows of a query result, returning a list of tuples, or a list of dictionaries if as_dict was passed to pymssql.connect(), or an empty list if no more data is available. Raises OperationalError if previous call to execute*() did not produce any result set or no call was issued yet.

整理：一次提取全部的记录集 而且是 a list of tuples 提取全部记录集的操作就可以用它了！

示例：

cur = conn.cursor()

cur.execute('SELECT * FROM a WHERE 1=1')

row = cur.fetchall()                    #表示一次全部提取全部记录

for i in range(len(row)):               #遍历的方法

    print row[i][0],row[i][1]

conn.close()

 

心得：这三个fetchone() fetchmany(size)   fetchall()

分别表示一次只提取一条记录应用范围是select * from table where id=1

一次提取若干条记录可以应用如下的：select top 10 from table

一次全部提取记录：select * from table where 1=1

以下讨论的话题是DB-API的扩展话题（即版本要到达一定的级别才能生效的目前我的版本不能生效使用不过我看了一下应用范围是一样的。只不过就是返回来的数据结构有点区别而已）

fetchone_asdict()

fetchmany_asdict(size=None)

fetchall_asdict()

分别是以上的三种扩展。只不过返回来的是字典类型了！

 

二、连接对象学习整理

请看：

conn = pymssql.connect(host='localhost', user='sa', password='hkebao', database='tyspeed',timeout=10.0,login_timeout=5.0,charset=('utf-8'))

这个连接对象中带有若干个属性可以设置好登录起时与连接超时机制还有编码机制。版本高的话还可以支持连接数max_conn

不过一般我用得比较多的就这些了！

方法整理：

Autocommit(boolean)     表示设置好连接对象是否自动处理事务。即一执行完就会自动提交

Close()                 关闭

Cursor()                返回一个cursor对象 有了这个对象就可以方便查询处理数据库

Commit()                提交当前的事务如果conn.autocommit(false)就要显示声明此

Rollback()              回滚当前的提交操作。如果有这个东西在的话当前的更新不会生效

cur = conn.cursor()

cur.execute("insert into a(id,name) values(1025,'adsf')")

conn.rollback()         #已回滚则表示当前的操作肯定不会生效的了

conn.commit()

conn.close()

 

PS：现在发现原来觉得一团糟的知识点变得清楚多了。学东西一定要弄明白哪怕是多发一点时间也要把东西一个一个地弄地非常明白！

 

DEMO:连接到数据库然后将一个表的记录全部提取出来并打印

import pymssql

conn = pymssql.connect(host='localhost', user='sa', password='hkebao', database='tyspeed',timeout=10.0,login_timeout=5.0,charset=('utf-8'))

cur = conn.cursor()

cur.execute("select id,name from a")

row = cur.fetchall()

for i in range(len(row)):

    print row[i][0]

conn.close()

安装pymssql包整理

我的安装步骤：

1、   下载

wget

2、   解压缩并安装

tar zxvf freetds-stable.tgz

3、./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --enable-msdblib -

-enable-dbmfix --with-gnu-ld --enable-shared --enable-static

4、安装make && make install

5、编辑/etc/ld.so.conf，在其中插入一行：
/usr/local/freetds/lib
然后运行以下指令使更改生效：
ldconfig

因为安装pymssql包是需要如下两个软件所支持的：

Python

FreeTDS包

 

第二步：安装pymssql包

存放目录在:soft 目录下面的1.0版本!

解压缩 ----- > python setup.py install 即可实现

测试的方法：

conn = pymssql.connect(host='XXXXXX',user='XXXXXX', password='dXXXXXXX', database='XXXXXX',timeout=10.0,login_timeout=5.0)

 

搞定了！