CentOS7 连接SQL SERVER

安装 unixODBC 驱动

$ yum install unixODBC  -y # optional: unixODBC-devel 
# 检查安装
$ odbc_config --version --longodbcversion --cflags --ulen --libs --odbcinstini --odbcini
2.3.1
3.52
-DHAVE_UNISTD_H -DHAVE_PWD_H -DHAVE_SYS_TYPES_H -DHAVE_LONG_LONG -DSIZEOF_LONG_INT=8 -I/usr/include 
-DSIZEOF_SQLULEN=8
-L/usr/lib64 -lodbc
/etc/odbcinst.ini
/etc/odbc.ini
$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
$ isql --version
unixODBC 2.3.1

安装 Microsoft ODBC Driver

$ curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
$ yum remove unixODBC-utf16 unixODBC-utf16-devel # to avoid conflicts
$ ACCEPT_EULA=Y yum install msodbcsql -y
# optional: for bcp and sqlcmd
$ ACCEPT_EULA=Y yum install mssql-tools -y
$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
$ export PATH="$PATH:/opt/mssql-tools/bin

检查 msodbc 驱动安装

$ ls -l /opt/microsoft/msodbcsql/lib64/
total 16080
-rwxr-xr-x 1 root root 16463370 Sep  9 13:02 libmsodbcsql-13.1.so.9.1
$ cat /etc/odbcinst.ini
......

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1
UsageCount=1

# 定义DSN
$ vim odbcdsn.txt
[MSSQL-ABC]
Driver      = ODBC Driver 13 for SQL Server
Description = My MS SQL Server ABC
Trace       = No
Server      = 192.168.10.1
Port          = 1433
# 注册DSN
$ odbcinst -i -s -f ./odbcdsn.txt -l
# 检查
$ cat /etc/odbc.ini
[MSSQL-ABC]
Driver=ODBC Driver 13 for SQL Server
Description=My MS SQL Server ABC
Trace=No
Server=192.168.10.1
Port=1433

通过python测试连接MSSQL

(venv) $ pip install pypyodbc

Demo

#!/path-to-venv/bin/python
# -*- coding: utf-8 -*-
import pypyodbc
import datetime

connection = pypyodbc.connect('DSN=MSSQL-ABC;database=DB_NAME;uid=user;pwd=passwd')

cursor = connection.cursor()

SQLCommand = ("SELECT * FROM DB_NAME where ID between 1 and 10")

cursor.execute(SQLCommand)

rowCount = 0

for row in cursor:

    # res = str(row[1])

    print (row)

    rowCount = rowCount + 1

cursor.close()
connection.close()

卸载 MSODBC 驱动

yum erase msodbcsql mssql-tools