This tutorial explains how we can connect to a Microsoft SQL Server database from Python running under Linux or Windows using pyodbc library.
Pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification. Using it, we can easily connect Python applications to data sources with an ODBC driver.
What this tutorial covers ?
- Introduction to ODBC Driver for CentOS and Windows
- Install Python's pyodbc module
- Install ODBC Driver on CentOS.
- Python code to connect to MSSQL DB from CentOS with Data Source Name
- Python code to connect to MSSQL DB from CentOS without Data Source Name
- Install ODBC Driver on Windows.
- Python code to connect to MSSQL DB from Windows.
Requirements
- Microsoft SQL Server
- CentOS or RedHat Linux system
- Windows system
Difficulty
The implementation explained here is straightforward.
- Basic
SQL Instance
In this tutorial, we'll use Steem SQL instance. Details are mentioned below :
- Server: sql.steemsql.com
- User: steemit
- Password: steemit
- Database: DBSteem
- Port: 1433
SQL Server Setup
- The SQL server installation must be setup to allow external connections.
- Note the credentials to connect the SQL server.
- Note the port to which SQL server is listening.
- Make sure connection is not blocked by firewall. To check this, attempt a telnet from the host system and check if the connection fails.
Python Library to connect to MSSQL DB
- pyodbc : It is a Python module that allows to connect to almost any database using ODBC driver.
Installation of pyodbc
The easiest method to install pyodbc is by using pip. Use below command to install pyodbc library.
pip install pyodbc
Installation under CentOS or RedHat
Required packages under CentOS
- FreeTDS : It is the
driverthats allows applications to talk to the MSSQL DB. - UnixODBC : It is the
driver manager.
Installation Steps
- All required packages are available in
epelrepository. For CentOS 7, it is available at http://mirror.vbctv.in/epel/7/x86_64/e/epel-release-7-9.noarch.rpm
Add this repository with below command.
rpm -Uvh http://mirror.vbctv.in/epel//7/x86_64/e/epel-release-7-9.noarch.rpm
- Install the required packages using yum.
yum install -y unixODBC unixODBC-devel freetds
We can connect to SQL server with or without Data Source Name (DSN). Both the methods are mentioned below.
Method 1 : Configure using Data Source Name (DSN)
Configure MSSQL Server in FreeTDS's setting :
- Edit /etc/freetds.conf file and add below SQL server details appropriately. Note that we have named the SQL server
steemsqlserver.
[steemsqlserver]
host = sql.steemsql.com
port = 1433
tds version = 7.0
- Test if the newly added connection works with below command.
tsql -S steemsqlserver -U steemit -P steemit
- Run some SQL command to make sure that everything is working fine. (For this example, we are running a SQL query to fetch all Steem accounts that start with
rohan).
select name from DBSteem.dbo.Accounts where name like 'rohan%'
go
If everything works fine, it will print the result of the query.
Setup unixODBC to use FreeTSD
- Run
odbcinst -jto know where the configuration files are located. We need file location fordriversandsystem data source. In CentOS they are /etc/odbcinst.ini and /etc/odbc.ini respectively. However, the output of the command will give the file location.
!
- Edit the /etc/odbcinst.ini as below.
If the Driver and Setup does not work then search the libtds files using find command.
- Edit the /etc/odbc.ini as below.
Connect from Python Application
- Use below command to connect to SQL DB using
pyodbc
pyodbc.connect('DSN='steemsqlserverdatasource'; UID='steemit'; PWD=''steemit; DATABASE='DBSteem';'
Example : Below code connects to the Steem SQL server and list all accounts which starts with 'rohan'.
Code Output
Method 2 : Configure without Data Source Name.
In this case, we specify the driver, SQL server name and ports and TDS Version in the pyodbc connection string.
pyodbc.connect('Driver='FreeTDS'; Server='sql.steemsql.com'; UID='steemit'; PWD='steemit'; Database='DBSteem'; TDS_Version='7.0'; Port='1433';')
- The below code shows implementation of example, mention in Method 1, without Data Source Name.
Code Output
Installation under Windows
- Download the ODBC Drive for Windows from below link. The latest ODBC driver for SQL Server available at the time of writing this tutorial is ODBC Driver 13.
https://www.microsoft.com/en-us/download/details.aspx?id=53339
- Install the downloaded driver.
- Once the driver is installed, we can now connect to the SQL server from python application using pyodbc. The connection string for pyodbc is mentioned below.
pyodbc.connect('Driver={ODBC Driver 13 for SQL Server}; Server=sql.steemsql.com; UID=steemit; PWD=steemit; Database=DBSteem;')
- Below code shows implementation of same example mentioned in Method 1.
- Code Output
We saw how easily we can connect to Microsoft SQL server from CentOS or Windows using Python's pyodbc library.
If you have any questions or comments , I'd would love to hear from you in comment section.
Posted on Utopian.io - Rewarding Open Source Contributors
Thank you for the contribution. It has been approved.
You can contact us on Discord.
[utopian-moderator]
Thankyou.
@originalworks
The @OriginalWorks bot has determined this post by @rohancmr to be original material and upvoted it!
To call @OriginalWorks, simply reply to any post with @originalworks or !originalworks in your message!
Hey @rohancmr I am @utopian-io. I have just upvoted you!
Achievements
Suggestions
Get Noticed!
Community-Driven Witness!
I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!
Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x