Linux uses PYODBC and freetds to connect to SQL Server

Installing UNIXODBC

Use PYODBC to install UNIXODBC on Linux

Unixodbc-devel packages can be installed with Yum or apt

Compile and install: http://www.unixodbc.org/download.html

Successful Installation verification

#odbcinst -j

unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Installing the connection driver

The UNIXODBC does not have a driver connected to SQL Server , FREETDS provides the relevant connection driver, or it can be installed via a script provided by Microsoft, where FreeTDS is chosen to provide a connection driver

Microsoft Driver Details https://docs.microsoft.com/zh-cn/sql/connect/odbc/linux-mac/ installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

Installing FreeTDS

Https://github.com/FreeTDS/freetds

git or download items into a folder

Example uses version 0.91 of FreeTDS https://github.com/FreeTDS/freetds/releases/tag/branch-0_91

* * Ensure that the following tools are installed before installation: Automake,autoconf,autogen,gettext,libtool,make,gcc,perl

Enter directory to run after decompression

./autogen.sh

Make

Make install

The above installation is for the source code installation on GitHub, and when you download other packages, some of the configure files have already been generated and need to be run./configure make,make install.

The default installation path is/USER/LOCAL/ETC

Test: Edit freetds.conf The connection information file in which SQL Server is joined is generally in/user/local/etc

[server]
  host = 192.168.6.6
  port = 1433
  tds version = 7.0
  client chaeset = UTF-8

Where TDS version can view the sample selection in the file, the wrong value may cause the connection to fail unexpected EOF from the server

Test the connection using TSQL

#tsql -S server -U user -P password

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

Installing PYODBC

https://pypi.org/project/pyodbc/

Pip install pyodbc or download package backward Master Directory python setup.py install

Edit/etc/odbcinst.conf file, which may also be in/usr/local/etc

Add FreeTDS Connection Library

Include in the file

 
[SQL Server]

Driver          = /usr/local/lib/libtdsodbc.so

Where driver is the supplied driver for FreeTDS, SQL Server uses the driver name for the connection string

Test:

 
import pyodbc
DBCONNECTSTR = ‘DRIVER={SQL Server};SERVER=192.168.6.6;port=1433;DATABASE=test;UID=user;PWD=password;TDS_Version=7.0;‘
conn=pyodbc.connect(DBCONNECTSTR)
conn.close()

Note The value of tds_version is consistent with the connection example in/usr/local/etc/freetds.conf

More python Connect SQL Server methods:

Https://wiki.python.org/moin/SQL%20Server

Https://www.cnblogs.com/AppleZhang/p/7878971.html

Linux uses PYODBC and freetds to connect to SQL Server