Connection to MSSQL with Pythonan and pyodbc (on Mac OS X)

There are many python SQL drivers available. Microsoft however places its efforts and confidence in pyodbc driver.

If the library pyodbc is not installed, do the following

1. Open Terminal
2. Run this command:
1. pip3 install pyodbc

The following will be the result

brians-air:~ imac$ pip3 install pyodbc

Collecting pyodbc

  Downloading https://files.pythonhosted.org/packages/92/91/c0c473491b49a5492f911b745d1388da9c60dd152a93841dc90cf21d0e97/pyodbc-4.0.27-cp37-cp37m-macosx_10_9_x86_64.whl (63kB)

    100% |████████████████████████████████| 71kB 237kB/s 

Installing collected packages: pyodbc

Successfully installed pyodbc-4.0.27

You are using pip version 19.0.3, however version 19.2.3 is available.

You should consider upgrading via the 'pip install --upgrade pip' command.

It's now installed. But does it work? No... Not at first! At least not for me. That didn't work for me and when trying to import the library, this was the result.

Input:

import pyodbc

Output:

Traceback (most recent call last):
  File "/Users/imac/PycharmProjects/SQL1/Test.py", line 1, in <module>
    import pyodbc
ModuleNotFoundError: No module named 'pyodbc'

Process finished with exit code 1

.... So what's up with that?

Apparantly we need to add the Pyodbc to the project. Aha!

Add Pyodbc to the Project

1. Click File -> Settings to open the project settings window.
2. Click Project Interpreter from the Project: YourProjectName menu.
3. To add pyodbc, click the + button and enter pyodbc.
4. Click Install Package to install pyodbc.

So does it work? Of course not! Life is hard. This is the output:

/Users/imac/venv/bin/python /Users/imac/PycharmProjects/Sql2/Test.py
Traceback (most recent call last):
  File "/Users/imac/PycharmProjects/Sql2/Test.py", line 1, in <module>
    import pyodbc
ImportError: dlopen(/Users/imac/venv/lib/python3.7/site-packages/pyodbc.cpython-37m-darwin.so, 2): Library not loaded: /usr/local/opt/unixodbc/lib/libodbc.2.dylib
  Referenced from: /Users/imac/venv/lib/python3.7/site-packages/pyodbc.cpython-37m-darwin.so
  Reason: image not found

Process finished with exit code 1

.....Image not found. Whut'!?

After researching i found out I had to install the UnixODBC library.

I used Homebrew to install (read how here) and then it worked.

import Pyodbc

Output:

/Users/imac/venv/bin/python /Users/imac/PycharmProjects/Sql2/Test.py

Process finished with exit code 0

.... So now that is setup and we can import and start working with the pyodbc.

This is how the connection string looks like:

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=test;DATABASE=test;UID=user;PWD=password')

But there is a driver issue... The driver could not be found. Argh!

I found this little nifty script that showed me what drivers were available:

import pyodbc

driver_name = ''driver_names = [x for x in pyodbc.drivers() if x.endswith(' for SQL Server')]
if driver_names:
    driver_name = driver_names[0]
if driver_name:
    conn_str = 'DRIVER={}; ...'.format(driver_name)
    # then continue with ...    # pyodbc.connect(conn_str)    # ... etc.else:
    print('(No suitable driver found. Cannot connect.)')

The output was:

(No suitable driver found. Cannot connect.)

Conclusion: We don't have any drivers. We need to install the Microsoft ODBC Driver to connect to SQL.

It took me a while to figure out, but once I solved that sidequest (installing driver) I was ready to rock and roll! 

import pyodbc

conn = pyodbc.connect('driver={ODBC Driver 17 for SQL Server};'                                           'server=;'                                            'database=;'                                            'uid=;'                                            'pwd=;')
rs = conn.cursor()
rs.execute('SELECT top 10 * FROM myTable')

for row in rs:
    print(row)

Halleluja!!!!!














____________________________________________

Various stuff:

import pyodbc

def read(conn):
    print("Read")
    cursor = conn.cursor()
    cursor.execute("select * from dummy")
    for row in cursor:
        print(f'row = {row}')
    print()

def create(conn):
    print("Create")
    cursor = conn.cursor()
    cursor.execute(
        'insert into dummy(a,b) values(?,?);',
        (3232, 'catzzz')
    )
    conn.commit()
    read(conn)

def update(conn):
    print("Update")
    cursor = conn.cursor()
    cursor.execute(
        'update dummy set b = ? where a = ?;',
        ('dogzzz', 3232)
    )
    conn.commit()
    read(conn)

def delete(conn):
    print("Delete")
    cursor = conn.cursor()
    cursor.execute(
        'delete from dummy where a > 5'
    )
    conn.commit()
    read(conn) 

conn = pyodbc.connect(
    "Driver={SQL Server Native Client 11.0};"
    "Server=LAPTOP-OGHOFHSQ;"
    "Database=Test;"
    "Trusted_Connection=yes;"
)

read(conn)
create(conn)
update(conn)
delete(conn)

conn.close()