安装 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
$ 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
