• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

pymssql 用法全解

开发技术 开发技术 4周前 (09-03) 32次浏览

pymssql包是Python语言用于连接SQL Server数据库的驱动程序(或者称作DB API),它是最终和数据库进行交互的工具。SQLAlchemy包就是利用pymssql包实现和SQL Server数据库交互的功能的。

一,pymssql包的基本组成

pymssql包由两个模块构成:pymssql 和 _mssql,pymssql 是建立在_mssql模块之上的模块,相对来说,_mssql性能更高。

pymssql 用法全解

pymssql模块由Connection和Cursor 两个大类构成:

  • Connection类代表MS SQL Sever数据库的一个连接,
  • Cursor类用于向数据库发送查询请求,并获取查询的的结果。

二,连接

连接对象用于连接SQL Server引擎,并设置连接的属性,比如连接超时,字符集等。

1,创建连接对象

pymssql通过类函数来构建连接对,在创建连接对象的同时,打开连接:

class pymssql.Connection(user, password, host, database, timeout, login_timeout, charset, as_dict)

2,构建Cursor对象

在创建连接对象之后,创建Cursor对象,使用Cursor对象向数据库引擎发送查询请求,并获取查询的结果:

Connection.cursor(as_dict=False)

as_dict是布尔类型,默认值是False,表示返回的数据是元组(tuple)类型;如果设置为True,返回的数据集是字典(dict)类型。

3,提交查询和自动提交模式

在执行查询之后,必须提交当前的事务,以真正执行Cursor对象的查询请求:

Connection.commit()

默认情况下,自动提交模式是关闭的,用户可以设置自动提交,pymssql自动执行Cursor发送的查询请求:

Connection.autocommit(status)

status是bool值,True表示打开自动提交模式,False表示关闭自动提交模式,默认值是False。

4,关闭连接

在执行完查询之后,关闭连接:

Connection.close()

三,Cursor

通过打开的连接对象来创建Cursor对象,通过Cursor对象向数据库引擎发送查询请求,并获取查询的结果。

1,执行查询

Cursor对象调用execute**()函数来执行查询请求,

Cursor.execute(operation)
Cursor.execute(operation, params)
Cursor.executemany(operation, params_seq)

参数注释:

  • operation:表示执行的sql语句,
  • params :表示sql语句的参数
  • params_seq:参数序列,用于sql语句包含多个参数的情况。

注意,除设置自动提交模式之外,必须在执行查询之后,通过连接对象来提交查询。

Connection.commit()

如果sql语句只包含一个参数,那么必须在sql语句中显式使用%s或%d作为占位符,分别用于引用字符型的参数和数值型的参数。

cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')

如果sql语句包含多个参数,那么使用list来传递参数:

cursor.executemany(
    "INSERT INTO persons VALUES (%d, %s, %s)",
    [(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike T.', 'Sarah H.')])

2,获取查询结果

Cursor对象调用fetch**()函数来获取查询的结果:

Cursor.fetchone()
Cursor.fetchmany(size=None)
Cursor.fetchall()

fetch**()函数是迭代的:

  • fetchone():表示从查询结果中获取下一行(next row)
  • fetchmany():表示从查询结果中获取下面的多行(next batch)
  • fetchall():表示从查询结果中获取剩余的所有数据行(all remaining)

3,跳过结果集

当查询的结果包含多个结果集时,可以跳过当前的结果集,跳到下一个结果集:

Cursor.nextset()

如果当前结果集还有数据行未被读取,那么这些剩余的数据行会被丢弃。

四,执行存储过程

Cursor对象有函数callproc(),用于执行存储过程:

Cursor.callproc(sp_name, paras)

第一个参数是存储过程的名称,第二个参数是一个元组类型,

五,pymssql模块的基本操作

 1,pymssql的基本操作

from os import getenv
import pymssql

server = getenv("PYMSSQL_TEST_SERVER")
user = getenv("PYMSSQL_TEST_USERNAME")
password = getenv("PYMSSQL_TEST_PASSWORD")

conn = pymssql.connect(server, user, password, "tempdb")
cursor = conn.cursor(as_dict=False)
cursor.execute("TSQL query")
cursor.executemany("INSERT INTO persons VALUES (%d, %s, %s)",
    [(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike T.', 'Sarah H.')])
# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()

cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cursor.fetchone()
while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()

conn.close()

2,以字典集返回数据行

conn = pymssql.connect(server, user, password, "tempdb")
cursor = conn.cursor(as_dict=True)

cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cursor:
    print("ID=%d, Name=%s" % (row['id'], row['name']))

conn.close()

3,使用with语句

with是上下文管理器,可以自动关闭上下文。

如果使用with语句来创建连接对象和Cursor对象,那么就不需要显式的关闭连接和Cursor对象,在语句执行完成之后,Python会自动检测连接对象和Cursor对象的作用域,一旦连接对象或Cursor对象不再有效,Python就会关闭连接或Cursor对象。

with pymssql.connect(server, user, password, "tempdb") as conn:
    with conn.cursor(as_dict=True) as cursor:
        cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
        for row in cursor:
            print("ID=%d, Name=%s" % (row['id'], row['name']))

六,附上代码库

 附上代码,以飨读者。

import pymssql
from sqlalchemy import create_engine
import pandas as pd
from sqlalchemy.sql import text as sql_text

class DBHelper:
    def __init__(self):
        self.name='DB Helper'
        self.db_host = r'sql server'
        self.db_name = 'db name'
        self.db_user = r'sa' 
        self.db_password = r'pwd'

######################################################
##                   data connection                ##
######################################################

    def get_engine(self):
        str_format = 'mssql+pymssql://{0}:{1}@{2}/{3}?charset=utf8'
        connection_str = str_format.format(self.db_user,self.db_password,self.db_host,self.db_name)
        engine = create_engine(connection_str,echo=False)
        return engine

    def get_pymssql_conn(self):
        conn = pymssql.connect(self.db_host, self.db_user, self.db_password, self.db_name)
        return conn


######################################################
##                common SQL APIs                   ##
######################################################

    def write_data(self,df,destination,if_exists='append',schema='dbo'):
        engine = self.get_engine()
        df.to_sql(destination, con=engine, if_exists=if_exists,index = False, schema=schema
, method='multi', chunksize=1000) def read_data(self,sql): engine = self.get_engine() df = pd.read_sql(sql, con=engine) return df def exec_sql(self,sql): engine = self.get_engine() con = engine.connect() with con.begin() as tran: con.execute(sql_text(sql).execution_options(autocommit=True)) def exec_sp(self,sp_name,*paras): with pymssql.connect(self.db_host, self.db_user, self.db_password, database=self.db_name) as conn: with conn.cursor(as_dict=False) as cursor: try: cursor.callproc(sp_name, paras) conn.commit() except Exception as e: print(e) def exec_sp_result(self,sp_name,*paras): with pymssql.connect(self.db_host, self.db_user, self.db_password, database=self.db_name) as conn: with conn.cursor(as_dict=True) as cursor: try: cursor.callproc(sp_name, paras) cursor.nextset() result=cursor.fetchall() conn.commit() df=pd.DataFrame.from_records(result) return df except Exception as e: print(e)

 

参考文档

pymssql introduction

 


程序员灯塔 , 版权所有
转载请注明原文链接:https://www.wangt.cc/2020/09/pymssql-%e7%94%a8%e6%b3%95%e5%85%a8%e8%a7%a3/
喜欢 (0)