ODBC access to SQL Server from Linux with Python

Introduction

I wonder if there are other uses besides machine learning. .. .. It's a niche. .. While thinking, I will explain how to connect to Microsoft SQL Server from Linux via ODBC. Let's run the test in Python.

Install DataDirect SQL Server ODBC driver in Linux environment

1. Download the trial version of SQL Server ODBC driver for Linux from the website. ..

2. After downloading, execute the following Linux command to move the package to a new folder.

mkdir datadirect

mv PROGRESS_DATADIRECT_ODBC_SQLSERVER_LINUX_XX.tar.Z datadirect/

cd datadirect/

3. Extract the package with the following command.

tar -xvf PROGRESS_DATADIRECT_ODBC_SQLSERVER_LINUX_64.tar.Z

4. Execute the following command in the terminal to install the KSH interpreter.

For Debian

sudo apt-get install ksh

For CentOS / RHEL

sudo yum install ksh

5. Execute the following script in the terminal to execute the installation.

./unixmi.ksh

6. When the prompt is displayed, enter Enter or "Y" to check the OS.

7. I agree to the license agreement with "yes".

8. Enter your name and company name at the prompt. Enter EVAL for the serial number and key.

9. A screen will appear asking you to install all the included drivers or select one driver to install. Here, select option 2.

10. A list of drivers to install will be displayed. Select option 11'SQL Server Wire Protocol'.

11. Check the path. If you cannot confirm it, enter the path.

12. Wait for a cup of tea until the installation is complete. If you want to install another Linux driver, you can try various things with "Y", but this time, use "N" to exit the installer.

SQL Server ODBC driver settings

1. Installation path /Progress/DataDirect/Connect_64_for_ODBC_71 (The end differs depending on the version) Go to and execute the following command in the terminal. Add the environment variables ODBCINI, ODBCINST, LD_LIBRARY_PATH.

sudo chmod +x odbc.sh

./odbc.sh

2. Check if the variables ODBCINI, ODBCINST and LD_LIBRARY_PATH are installed normally with the following command. You should see the path to the file odbc.ini.

echo $ODBCINI

./odbc.sh

3. If the environment variables are not added correctly, add these values to ~ / .bashrc.

export LD_LIBRARY_PATH=/install_path/Progress/DataDirect/Connect64_for_ODBC_71/lib

export ODBCINI=/ install_path/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini

export ODBCINST=/ install_path/Progress/DataDirect/Connect64_for_ODBC_71/odbcinst.ini

4. Refresh the terminal settings with the following command.

source ~/.bashrc

5. Path /install_path/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini Open the odbc.ini file located in and configure the SQL Server connection details as shown in the following sample odbc.ini.

[ODBC Data Sources]

SQL Server Legacy Wire Protocol=DataDirect 7.1 SQL Server Legacy Wire Protocol

[ODBC]
IANAAppCodePage=4

InstallDir=/home/progress/Progress/DataDirect/Connect64_for_ODBC_71

Trace=0

TraceFile=odbctrace.out

TraceDll=/home/progress/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddtrc27.so

[SQL Server Wire Protocol]
Driver=/home/progress/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddsqls27.so

Description=DataDirect 7.1 SQL Server Wire Protocol

AlternateServers=

AlwaysReportTriggerResults=0

AnsiNPW=1

ApplicationName=

ApplicationUsingThreads=1

AuthenticationMethod=1

BulkBinaryThreshold=32

BulkCharacterThreshold=-1

BulkLoadBatchSize=1024

BulkLoadFieldDelimiter=

BulkLoadOptions=2

BulkLoadRecordDelimiter=

ConnectionReset=0

ConnectionRetryCount=0

ConnectionRetryDelay=3

Database=<database_name>

EnableBulkLoad=0

EnableQuotedIdentifiers=0

EncryptionMethod=0

FailoverGranularity=0

FailoverMode=0

FailoverPreconnect=0

FetchTSWTZasTimestamp=0

FetchTWFSasTime=1

GSSClient=native

HostName=<server_name>

HostNameInCertificate=

InitializationString=

Language=

LoadBalanceTimeout=0

LoadBalancing=0

LoginTimeout=15

LogonID=<logon id> (optional)

MaxPoolSize=100

MinPoolSize=0

PacketSize=-1

Password=<password> (optional)

Pooling=0

PortNumber=1433

QueryTimeout=0

ReportCodePageConversionErrors=0

SnapshotSerializable=0

TrustStore=

TrustStorePassword=

ValidateServerCertificate=1

WorkStationID=

XMLDescribeType=-10

6. Save the odbc.ini file and exit.

SQL Server ODBC Connection Test

1. Let's test the ODBC driver. This time, let's do it with a simple Python script. Run the following command to install the pyodbc package.

pip install pyodbc

If you get the exception that the <sql.h> library cannot be found, you may need to install the unixODBC package. You can install it by executing the following command.

Debain:

sudo apt-get install unixodbc unixodbc-dev

CentOS/Redhat:

sudo apt-get install unixODBC unixODBC-devel

2. Save the following script in a python file and execute it. Check if you can connect to SQL Server successfully.

import pyodbc

 

##connecting to your database through ODBC

cnxn = pyodbc.connect('DSN=SQL Server Wire Protocol;UID=sa;PWD=pass', autocommit=True)

cursor = cnxn.cursor()

3. After successfully connecting to SQL Server, you can add the following script to the above file to output the table in the database.

##Print TableNames

for row in cursor.tables():

                if row.table_type == "TABLE":

                                print (row.table_name)

                                print (row)

4. You can also add the following script and execute the script. Notice that there is a Northwind database that you are trying to query.

##Executing a simple query and printing the records

cursor.execute("SELECT EmployeeID, LastName, FirstName, Address, City, Region from Northwind.dbo.Employees")

for row in cursor.fetchall():

    print (row)

This article uses SQL Server Authentication mode, but the DataDirect SQL Server ODBC and JDBC drivers also support Windows Authentication and Kerberos, so take that opportunity.