Python与SQL Server的交互:pyODBC, pymssql, SQLAlchemy

Windows平台下Python读取、写入SQL Server相关的函数库,文章结构如下:

Python+SQLserver

Python Drivers

PyODBC

Annaconda下可以用pip install pyodbc安装,也可以到这里下载。

首先建立connection对象:

import pyodbc
conn = pyodbc.connect(
    r'DRIVER={ODBC Driver 11 for SQL Server};'  #or {ODBC Driver 13 for SQL Server}
    r'SERVER=ServerHostName;'
    r'DATABASE=DBName;'
    r'UID=user;'
    r'PWD=password'
    )

添加游标(Cursor)对象并执行SQL查询语句:

cursor = conn.cursor()
cursor.execute('SQL Query Goes Here')
for row in cursor.fetchall():
  print(rows.[column name])

更多信息参见MSDN DOCs

pymssql

同样可以用pip install pymssql安装,也可以到这里,然后用pip安装wheel文件。

pymssql目前还不支持Python3.6,这点要注意下。

pymssql的用法跟pyODBC很像,下面是官网给出的例子:

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()
cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
    DROP TABLE persons
CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(100),
    salesrep VARCHAR(100),
    PRIMARY KEY(id)
)
""")
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()

详细用法参见pymssql docsMSDN DOCs

SQLAlchemy(Python SQL Toolkit)

SQLAlchemy提供了一系列丰富、完整、(我看不懂)的API用于数据库操作。这里只谈其create_engine方法。

from sqlalchemy import create_engine
# pyodbc
engine = create_engine('mssql+pyodbc://user:password@DSNname') #需要配置DSN,参见最后一节

# pymssql
engine = create_engine('mssql+pymssql://user:password@Hostname:port/DBname')

利用创建好的engine,可以结合pandas库进行批量的读取、写入操作。

用SQLAlchemy与其他类型的数据库建立链接的方法参见这里

Pandas

利用pyODBC和pymssql拉取的对象需要进一步处理才能进行常见的数据清洗等工作,而Pandas也提供了SQL相关的方法,在SQLAlchemy的辅助下,可以将DataFrame对象直接写入table。

读取:pd.read_sql()

API

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

其中的con参数,可以传入SQLAlchemy建立的engine对象,也可以是pyODBC或者pymssql建立的DBAPI2 connection对象。

写入:pd.DataFrame.to_sql()

API:

DataFrame.to_sql(name, con, flavor=None, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)

这里的con参数,只支持sqlite3的DBAPI2 connection对象,支持所有的SQLAlchemy engine对象。
name参数传入表名,用if_exists参数控制表存在时的动作:

  • ‘fail’: 啥也不干。
  • ’replace‘: 将原有表删除,新建表,插入数据。
  • ’append': 在表中插入数据。表不存在时新建表。

命令行

利用Sqlcmd命令,也可以在命令行下执行SQL文件,用法如下:

sqlcmd -U user -P password -S server -d DBName -i /path/to/myScript.sql

这样可以有如下思路,将数据写入.SQL文件,再生成.bat文件(批量)写入上述命令,之后完成执行。

DSN

Windows下可以配置DSN(Data Source Names)预先存储数据库连接的信息,在Control Panel -> Administrative Tools -> ODBC Data Source 下添加即可。

配置好DSN后,pyODBC的连接过程可以简化为:

conn = pyodbc.connect(r'DSN=DSNname;UID=user;PWD=password') #UID和PWD也可以在DSN中配置

拾遗

Python与文件的IO、SQL数据库的读写时有中文字符可能会有编码问题。一种方案是在中文字符串前添加N,如N'python大法好';另一种方案是传入encoding参数,常用的中文编码有GB2123GB18030,推荐的还是统一用UTF-8编码、解码。

利用如下命令,可以在SQLAlchemy中指定编码:

engine = create_engine('mssql+pymssql://user:password@HostName\DBname', connect_args = {'charset':'utf-8'})

其他自定义DBAPI connect()参数的方法参见这里

Creative Commons License
本文章遵从署名-相同方式共享4.0国际协议(CC BY-SA 4.0)
这意味着您可以署名转载本文章,并附上此协议。
我每周会分享一些有趣实用的英文文章,欢迎关注ddlee每周分享
这里可以找到我推荐的服务、应用程序、书籍和电影。

本文链接:https://blog.ddlee.cn/posts/8f1c0ee0/
分享文章:

小额捐助:

相关文章

The Devtools Way: devtools+RStudio快速R程序包开发 再次折腾我的WNDR4300:OpenWrt文件共享

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×