statement
My sample environment:
- System version: CentOS 7.1
- Database version: SQL Server2012
- python version: Python 3.6.3
- Connect to database tools: pyodbc, freetds
If you use Django to connect to the SQL Server database, please refer to the blog "Django 2.1 connect to use SQL Server(linux version)", which is different from that, Django 2.1 uses Microsoft ODBC Driver 11 for linux driver
Installation of driving and connecting tools
ODBC driver installation
Driver for database connection
yum -y install unixODBC*
pyodbc installation
For database connection
wget https://files.pythonhosted.org/packages/b4/41/f3eb5e56af207a8fcc02f1f84cc3fed9fcf315565e65f418ae815e399929/pyodbc-4.0.26.tar.gz tar xvf pyodbc-4.0.26.tar.gz cd pyodbc-4.0.26 python3 setup.py install
Configure ODBC
Find the required driver, easy to write configuration file
find /usr -name "*\.so" |egrep "libtdsodbc|libtdsS"
/usr/lib64/libtdsS.so /usr/lib64/libtdsodbc.so
vim /etc/odbcinst.ini
# Example driver definitions # Driver from the postgresql-odbc package # Setup from the unixODBC package [SQLServer] Description = FreeTDS ODBC driver for MSSQL Driver = /usr/lib64/libtdsodbc.so Setup = /usr/lib64/libtdsS.so FileUsage = 1
View existing ODBC drivers
odbcinst -q -d
[SQLServer]
ODBC connection test
Python 3.6.0 (default, Jan 24 2019, 21:08:32) [GCC 4.8.5 20150623 (Red Hat 4.8.5-16)] on linux Type "help", "copyright", "credits" or "license" for more information. >>> import pyodbc conn=pyodbc.connect('DRIVER={SQLServer};SERVER=192.168.88.81;port=1433;DATABASE=mydata;UID=sa;PWD=123456') cursor=conn.cursor() for row in cursor.execute("select TOP 50 * from table01"): print(row)
freetds connection mode
Install freetds
yum -y install freetds*
freetds configuration
vim etc/freetds.conf
[Mydata] #Database name host = 192.168.88.81 #Server address of database port = 1433 #Database port tds version = 8.0 #tds version client charset = UTF-8 #The client uses encoding. Sometimes the connection fails due to inconsistent encoding
Connection test
/usr/local/freetds/bin/tsql -S Mydata -Uuser1 -p 1433 -P'123456'
Connection success return information [next step after connection success]
locale is "zh_CN.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" 1>