September 13, 2019
Connect to MS SQL Server using Python on Mac
Introduction
I wanted to connect to an MS SQL server database using Python from my Mac laptop. I struggled a bit but eventually I had a working solution. I thought it can be useful so I am sharing it…
Steps
- Make sure you have brew installed on mac. If not, you can get it from here.
- brew update
- brew install unixodbc freetds
- To find where freetds.conf is located (ex. ~/homebrew/etc) type…
|
1 |
tsql -C |
- Create a symbolic link if it does not exist
|
1 |
ln -s ~/homebrew/etc/freetds.conf /usr/local/etc |
- Open the following file…
|
1 |
~/homebrew/etc/freetds.conf |
- Go to the end of the file and copy the snippet below then exit and save
|
1 2 3 4 5 |
# A typical Microsoft server [MYMSSQL] host = server ip or host name port = 1433 tds version = 7.3 |
- To find where odbc.ini and odbcinst.ini are located type…
|
1 |
odbcinst -j |
- Edit odbcinst.ini and copy the following…
|
1 2 3 4 5 |
[FreeTDS] Description=FreeTDS Driver for Linux & MSSQL Driver=/usr/local/lib/libtdsodbc.so Setup=/usr/local/lib/libtdsodbc.so UsageCount=1 |
- Create symbolic link if it does not exist
|
1 |
ln -s ~/homebrew/lib/libtdsodbc.so /usr/local/lib/ |
- Edit odbc.ini and copy the following snippet…
|
1 2 3 4 5 |
[MYMSSQL] Description = Test to SQLServer Driver = FreeTDS Database = mydbname Servername = MYMSSQL |
- Check connection:
|
1 |
isql MYMSSQL user_name password |
- if you provide the correct user name and password. You should get something like…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select count(*) from coupons; +------------+ | | +------------+ | 185 | +------------+ SQLRowCount returns 1 1 rows fetched SQL> |
- Make sure you have virtualenv installed, if not you can do so by…
|
1 |
pip install virtualenv |
- Create a virtual env…
|
1 2 3 4 5 6 |
cd mkdir .virtualenvs cd .virtualenvs virtualenv -p python3 myenv cd myenv source bin/activate |
- Install pyodbc
|
1 |
pip install pyodbc |
- Create a new python file and add the following snippet…
|
1 2 3 4 5 6 7 |
import pyodbc conn = pyodbc.connect('DSN=MYMSSQL;UID=user_name;PWD=password') crsr = conn.cursor() rows = crsr.execute("select count(*) from coupons").fetchall() print(rows) crsr.close() conn.close() |
- Run from the terminal…
|
1 |
python test.py |
- If everything is ok you should get something like…
|
1 |
[(185, )] |
That is all for today. Thanks for visiting.
About Author
admin
Sr. Software QA Engineer @ ActiveCampaign
One Comment
Awesome! Thanks for sharing!