Using SQL Server database for python3 connection (linux version)

Keywords: Programming Database odbc SQL Python

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> 

Posted by Omzy on Sat, 16 Nov 2019 07:43:30 -0800