Connecting to SQL Server from Python app on CentOS 7

歐耶，可以在 Mac OS X / Linux 連 MS SQL 真方便。Driver 的部分，沒有使用 FreeTDS，直接用 MS提供的Driver.

from:
https://janikarhunen.fi/connecting-to-sqlserver-from-python-app-on-centos-7.html

上面的教學文章，我沒有全部服用，請直接改服用  MS 官方提供的解法：

https://docs.microsoft.com/zh-tw/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server

Install unixODBC

Microsoft’s SQL Server ODBC needs unixODBC driver to be installed, so let’s install the unixODBC driver from sources first. You can read more about the driver from unixODBC homepage.

Download and install the unixODBC driver

Download the source tarball and unpackage. I work in dir /home/user/source.

curl -O 'ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz'
tar -xz -f unixODBC-2.3.4.tar.gz

Remove any previously installed versions of the driver (optional step)

sudo rm -rf /usr/lib64/libodbc*

這裡要加 -rf 不然需要按很多次 y.

不知道為什麼不使用 yum install unixODBC unixODBC-devel

Install the unixODBC driver.

# Change to source dir
cd unixODBC-2.3.4

export CPPFLAGS="-DSIZEOF_LONG_INT=8"
./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-stats=no 1> configure_std.log 2> configure_err.log
make 1> make_std.log 2> make_err.log
sudo make install 1> makeinstall_std.log 2> makeinstall_err.log

# the Microsoft driver expects unixODBC to be here /usr/lib64/libodbc.so.1, so add soft links to the '.so.2' files
cd /usr/lib64
sudo ln -s libodbccr.so.2   libodbccr.so.1
sudo ln -s libodbcinst.so.2 libodbcinst.so.1
sudo ln -s libodbc.so.2     libodbc.so.1

Note: Check the logs as you work on for potential issues.

Check the unixODBC installation

Check that the unixODBC installation was successful with the following commands

# Should return something like the following
odbc_config --version --longodbcversion --cflags --ulen --libs --odbcinstini --odbcini

執行結果：

2.3.4
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

Install the Microsoft ODBC Driver for Linux

Now we are ready to install the latter part of driver. Read more from this Microsoft’s MSDNarticle.

https://docs.microsoft.com/zh-tw/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server

Microsoft ODBC Driver 13 for SQL Server

RedHat Enterprise Server 6

sudo su
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit
sudo yum update
sudo yum remove unixODBC #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql-13.0.1.0-1
sudo yum install unixODBC-utf16-devel #this step is optional but recommended*
#Create symlinks for tools
ln -sfn /opt/mssql-tools/bin/sqlcmd-13.0.1.0 /usr/bin/sqlcmd 
ln -sfn /opt/mssql-tools/bin/bcp-13.0.1.0 /usr/bin/bcp

我沒有安裝 mssql-tools-14.0.2.0-1 ，只有安裝 msodbcsql-13.0.1.0-1

只做到這裡，執行範例程式的錯誤訊息是：

pyodbc.Error: (‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘ODBC Driver 13 for SQL Server’ : file not found (0) (SQLDriverConnect)”

發生的原因是沒有去 yum remove unixODBC, 造成了 conflicts.

做到這裡，可以直接使用 MS 提供的 sample code 連進mssql 不需要設定/etc/odbc.ini，在這附上 2 個 connect 的範例。

範例1: 使用DSN （需設定 odbc.ini）

import pyodbc
dsn = 'YourDSN'
user = 'user'
password = 'password'
database = 'DB name'
con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
connection = pyodbc.connect(con_string)
connection.close()
print "Done ^_^y"

範例2: MS 範例（較好）

來源：
https://docs.microsoft.com/zh-tw/azure/sql-database/sql-database-connect-query-python

import pyodbc
server = 'your_server.database.windows.net'
database = 'your_database'
username = 'your_username'
password = 'your_password'
driver= '{ODBC Driver 13 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid")
row = cursor.fetchone()
while row:
 print (str(row[0]) + " " + str(row[1]))
 row = cursor.fetchone()

# register the SQL Server database DSN information in /etc/odbc.ini

[MyMSSQLServer]
Driver      = ODBC Driver 13 for SQL Server
Description = My MS SQL Server
Trace       = No
Server      = 10.100.1.10

相關文章：

Connecting to SQL Server from RHEL 6 or Centos 7
https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-RHEL-6-or-Centos-7

Connecting to Oracle from RHEL or Centos
https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-Oracle-from-RHEL-or-Centos