What is pyODBC?

pyODBC is a python library that is used for connecting ODBC (Open Database Connectivity) databases through an interface. This simple to use package provides generic methods for accessing and interacting with various database platforms as well Microsoft Office programs such as Microsoft Access and Microsoft Excel.

How do you install it?

Utilize the pip command like any other python package. Run this from a Windows DOS prompt or Unix shell for Mac and Linux. Consider the use of Python virtual environments and installing there.

Windows CMD Install

pip install pyodbc

Unix Install

brew install unixodbc

pip install pyodbc

Linux Install

$ sudo apt install unixodbc

$ pip install pyodbc

Check installation and version

pip freeze

Check drivers available

Check what drivers are available to choose from for you connection

drivers= pyodbc.drivers()

print(drivers)

Create connection

It is suggested you wrap your connection in a try statement to ensure your connection is successful and to help troubleshoot if it fails. See below- encoding is set to utf-8, decoding is also available. Make sure to check your database choice for encoding and decoding options which may be set directly in the connection string. Autocommit is set to True; if you do not set this, a commit will need to be required after each transaction. Use Autocommit=True when you want every transaction in a batch to attempt a commit but if it fails it will continue to commit other transactions in a batch. Autocommit=False will ensure batch transactions roll back together upon failure by pairing the batch with rollback/commit.

conn=”driver={(your driver)};server=(your server);database=(your database);uid=(your username);pwd=(your password)”

try:

            cnxn = pyodbc.connect(conn)

            cnxn.setencoding('utf-8')

            self.cnxn = cnxn

            self.cnxn.autocommit = True

except pyodbc.Error as e:

            dbstate = e.args[0]

Database operation examples

Close Connection

It is important to close the connection after transactions are complete. Insert the below code snippet where it logically makes sense in your code:

self.cnxn.close()

Insert

pyODBC utilizes the cursor as a representation of a database cursor and is utilized for all transactions. Cursors are not isolated and are immediately visible by other cursors. Cursors do not manage database transactions and will use connection settings (which may be modified as mentioned above) for commit/rollback.

Note, converting the datetime.now() date object to a string prior to calling the query. Some databases expect a datetime as a string formatted parameter.

now = datetime.now()

        mydict = {

            'id': str(uuid.uuid4()),  

            'type': 'example',

            'createdAt': now.strftime("%Y/%m/%d %H:%M:%S"),  

            'updatedAt': now.strftime("%Y/%m/%d %H:%M:%S")  

        }

        query = f"INSERT INTO (your table) (id,type,createdAt,updatedAt) VALUES ('{(your paramter1)}','{(your paramter2)}','{(your paramter3)}','{(your paramter4)}')"

        try:

            crsr = self.cnxn.cursor()

            crsr.execute(query)

            return True

        except Exception as e:

            return False

Update

query = f"UPDATE (your table) SET userId = '{(your parameter1)}',type = '{(your parameter2)}',updatedAt = '{(your parameter3)}',title = '{(your parameter4)}' WHERE id = '{(your parameter5)}'"

         try:

            crsr = self.cnxn.cursor()

            crsr.execute(query)

            return True

        except Exception as e:

            return False

SELECT

You may iterate cursors. Each iteration returns a ROW object.

query = f"SET ROWCOUNT {(your limit integer)};"

query += f"SELECT * FROM (your table) where table.userId = '{(your parameter1)}' and table.type='{(your parameter2)}' order by table.updatedAt {(ASC/DESC)};"

        myList = []

        try:

            crsr = self.cnxn.cursor()

            for item in crsr.execute(query):

                myDictItem = {

                'id': item[0],  

                'type': item[1],

                'createdAt': item[2],  

                'updatedAt': item[3],  

                'userId': item[4],

                'title': item[5]

            }

                myList.append(myDictItem)

            return myList

        except Exception as e:

                return False

Delete

query = f"DELETE FROM (your table) WHERE userId = '{(your parameter)}'"

        try:

            crsr = self.cnxn.cursor()

            crsr.execute(query)

            return True

        except Exception as e:

            return False

Why pyODBC?

While there are alternatives to pyODBC, this package offers great advantages and flexibility that include the following:

• Made for multiple platforms
  • As mentioned above pyODBC integrates with Windows, Mac, various Linux versions without changing code
• Made for multiple databases
  • SQL Server, MySQL, Oracle, PostgreSQL, Microsoft Access & Excel…this package has many backends to utilize. After learning the package utilizing various backends becomes simplistic.
• Speed
  • The ODBC API means fast transactions and low latency making it an efficient tool
• Error Handling
  • pyODBC has built in error handing using pyODBC.Error to help troubleshoot database errors quickly

Conclusion

Implementation of pyODBC is a simple and straightforward process with incredible versatility. Integration with most major platforms and databases allows great flexibility in creating your core code base and changing effortlessly without a big rewrite. While there are many alternative packages to implement with python for database interactions, it is prudent to consider pyODBC as your choice. If you have any additional questions or would like to discuss this topic further, please contact us today!