在MacOS下使用sqlalchemy 连接sqlserver2012 数据库

在MacOS下使用sqlalchemy 连接sqlserver 数据库

前言

最近有要求，要将数据库换成巨硬家的sqlserver 2012 因为在网上苦苦找不到sqlalchemy 配置连接SqlServer的具体中文步骤，翻了官方文档后把步骤整理出来

前期准备

已经安装好sqlalchemy

运行环境介绍

系统：MacOS 10.13.1 Beta Python 环境: 3.6.0 sqlalchemy 1.1.4 pycode 4.0.19 sqlserver 2012

实施步骤

在sqlalchemy里对于连接sqlserver 是这样介绍的 原文地址http://docs.sqlalchemy.org/en/latest/core/engines.html#sqlalchemy.create_engine

Microsoft SQL Server

The SQL Server dialect uses pyodbc as the default DBAPI. pymssql is also available:

pyodbc

engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

pymssql

engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')

因此要想使用sqlalchemy 先要配置pyodbc正常使用当然要是比较省事儿的话可以选择安装pymssql

方法一、安装pymssql

pip3 install pymssql

安装pymssql 需要提前安装依赖

brew install freetds@0.91

brew link freetds@0.91

数据库链接字符串为

ENGINE = create_engine(mssql+pymssql://sa:123456@localhost/test?charset=utf8)

方法二、 安装 pyodbc

如果觉得太慢的话可以后面使用加 -i 参数选择国内源 比如豆瓣

pip3 install pyodbc

pip install pyodbc

配置pyodbc

在Mac os 下pyodbc不是仅仅安装上就可以使用的还需要一些配置才可以 关于pyodbc的官方文档见https://github.com/mkleehammer/pyodbc/wiki

Macos 配置文档见https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX

以下是对文档中步骤的大致翻译

安装Install FreeTDS and unixODBC 使用Mac 上的命令行软件安装工具

brew update
brew install unixodbc
brew install freetds --with-unixodbc

遇到的坑

在安装过程中有可能因为网络原因导致下载的包不完整导致报类似SHA256 Checksum mismatch 的错误，只要根据提示到到下载路径删除掉不完整的包就可以了

Treetds 配置

使用命令得到freetds.conf的位置

tsql -C

然后在这个文件的后面追加自己的sql server 配置

例如：

[MYMSSQL]
host = mssqlhost.xyz.com
port = 1433
tds version = 7.3

关于不同的数据库与对于的tds version看下面http://www.freetds.org/userguide/choosingtdsprotocol.htm通过上面的配置 mac就可以和SqlServer服务器连接上了，可以使用下面的命令连接进入到sql命令行状态

tsql -S MYMSSQL -U myuser -P mypassword

MYMSSQL 在配置文件总给一个数据库连接起的名字

myuser 连接用的用户

mypassword 用户对应的密码

连接成功后的状态

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

可以使用一条sql语句测试一下例如"SELECT @@VERSION"

注意输入完sql语句后 在下一行(enter)输入go才会执行

编辑odbcinst.ini 和 odbc.ini 配置文件

通过命令来 获取odbcinst.ini 和odbc.ini的文件位置

odbcinst -j

将如下内容写入odbcinst.ini

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1

将如下内容写入odbc.ini

[MYMSSQL]
Description         = Test to SQLServer
Driver              = FreeTDS
Servername          = MYMSSQL

注意这里的 Servername 就是freetds.conf中配置的名字

接着输入

isql MYMSSQL myuser mypassword

可以输入一些sql语句测试一下

示例代码

通过pyodbc连接数据库

import pyodbc
# the DSN value should be the name of the entry in odbc.ini, not freetds.conf
#将myuser 和mypassword 自己环境中的
conn = pyodbc.connect('DSN=MYMSSQL;UID=myuser;PWD=mypassword')
crsr = conn.cursor()
rows = crsr.execute("select @@VERSION").fetchall()
print(rows)
crsr.close()
conn.close()

如果能出现结果证明连接正常

通过sqlalchemy 操作数据库 例如创建一张表

#!/usr/bin/env python3
import pyodbc,sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import sqltypes

ENGINE = create_engine('mssql+pyodbc://admin:123456@MYMSSQL')
Base = declarative_base()
class Userinfo(Base):
    __tablename__ = 'userinfo'
    id = Column(Integer, primary_key=True, autoincrement=True)
    account_id = Column(String(255), nullable=False)
    username = Column(String(255),nullable=False)
    encrypted_password = Column(sqltypes.VARBINARY(500), nullable=False)
    token = Column(String(255))

def init_db():
    Base.metadata.create_all(ENGINE)

def drop_db():
    Base.metadata.drop_all(ENGINE)

def session():
    cls = sessionmaker(bind=ENGINE)
    return cls()
#drop_db()
init_db()