Connect to Microsoft SQL Server database on MacOS using Python

Connect to Microsoft SQL Server database on MacOS using Python

Connect to your MS SQL using python. The first thing you need is to install Homebrew. You need the copy the content in the square brackets which in my case is “ODBC Driver 13 for SQL Server”. Replace “ODBC Driver 13 for SQL Server” with the content you copied in the square brackets.

There are always situations where I want to automate small tasks. I like using Python for these kind of things, you can quickly get something working without much hassle. I needed to perform some database changes in a Microsoft SQL Server database and wanted to connect to it using Python. On Windows this is usually pretty straight forward. But I use macOS as my main operating system and I had some hurdles along the way so here is a quick how to.

Preparing

If Homebrew isn't installed yet let's do that first. It's an excellent package manager for macOS. Paste the following command in the terminal to install it:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Once finished run the following commands to install the Microsoft ODBC Driver 13.1 for SQL Server. This driver will be used to connect to the MS SQL database.

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install [email protected] [email protected]
Python package: pyodbc or pypyodbc

With the driver installed, we'll be using a Python package that uses the driver to connect to the database. There are two frequently used packages to connect in Python: pyodbc and pypyodbcpypyodbc is a pure Python re-implementation of pyodbc. The main thing I took a way was that pypyodbcis easier to set up on different platforms. I could not get pyodbc working, it simply wouldn't connect to the database.

Installing pypyodbc can be done using pip, the python package installer.

pip install pypyodbc
Code

Now that the necessary components have been installed, let's open our favorite code editor and write code. The first thing that needs to be done is importing pypyodbc. The script starts with this:

import pypyodbc

Then we need a connection to the database:

sqlConnection = pypyodbc.connect(
                "Driver={ODBC Driver 13 for SQL Server};"
        "Server=<server IP address>;"
        "Database=<database>;"
        "uid=<username>;pwd=<password>");

Note that you have to replace four values in this code: server IP addressdatabase , username and password. The value for the driver is a hard coded value which indicates what driver to use to connect to the database, this value points to the driver that was installed earlier.

Now all what rests is use the connection and run a query.

cursor = sqlConnection.cursor()
cursor.execute("select * from Values")

The cursor now contains the result of our query, the property cursor.rowcount returns the number of rows the query returned. It's now possible to loop through the rows and access the different columns:

for row in cursor:
    print(cursor)
    # first column
    firstColumn = row[0]
    # ...

When we're done, we need to clean up the cursor and database connection by closing it.

cursor.close()
sqlConnection.close()

And that's it, save the file and use the python <filename>.py or python3 <filename.py> command, this depends on your configuration, to run. Here is the entire script:

import pypyodbc

sqlConnection = pypyodbc.connect(
"Driver={ODBC Driver 13 for SQL Server};"
"Server=<server IP address>;"
"Database=<database>;"
"uid=<username>;pwd=<password>");

cursor = sqlConnection.cursor()
cursor.execute("select * from Values")

for row in cursor:
print(cursor)
# first column
firstColumn = row[0]
# ...

cursor.close()
sqlConnection.close()

The with syntax can also be used to automatically close the cursor and the connection, this is another way of writing the same script:

import pypyodbc

with pypyodbc.connect(
"Driver={ODBC Driver 13 for SQL Server};"
"Server=<server IP address>;"
"Database=<database>;"
"uid=<username>;pwd=<password>") as sqlConnection:

with sqlConnection.cursor() as cursor:

    cursor.execute("select * from Values")

    for row in cursor:
        print(cursor)
        # first column
        firstColumn = row[0]
        # ...

If you're looking for some more reading on the topic:

Thanks for reading. If you liked this post, share it with all of your programming buddies!

Further reading

☞ Python for Time Series Data Analysis

☞ Python Programming For Beginners From Scratch

☞ Python Network Programming | Network Apps & Hacking Tools

☞ Intro To SQLite Databases for Python Programming

☞ Ethical Hacking With Python, JavaScript and Kali Linux

☞ Beginner’s guide on Python: Learn python from scratch! (New)

☞ Python for Beginners: Complete Python Programming

Web Development Services

Web Development Services

As one of the best Web Application Development Company, it provides a fine quality mobile app development service at an affordable price. Especially, it encourage start-ups that have unique ideas, by offering a more competitive price

HireFullStackDeveloperIndia is rated as one of the top Web Application Development Company in India by various industry magazines and review sites. They have a right blend of award-winning designers, expert programmers and Google certified digital marketers which make them a unique one-stop solution for hundreds of our clients, spread across all countries.

A Good website reflects not only your business but also it is one of the main factors why a potential customer would convert into Client. A good website design helps increase traffic driving leads to grow business. The best web design company create a custom design for each corporate website so as to help them meet their business goals.

Get Quote: https://hirefullstackdeveloperindia.com/get-a-quote/

Develop this one fundamental skill if you want to become a successful developer

Throughout my career, a multitude of people have asked me&nbsp;<em>what does it take to become a successful developer?</em>

Throughout my career, a multitude of people have asked me what does it take to become a successful developer?

It’s a common question newbies and those looking to switch careers often ask — mostly because they see the potential paycheck. There is also a Hollywood level of coolness attached to working with computers nowadays. Being a programmer or developer is akin to being a doctor or lawyer. There is job security.

But a lot of people who try to enter the profession don’t make it. So what is it that separates those who make it and those who don’t? 

Read full article here

Hire Web Development Company in Chicago, USA

Hire Web Development Company in Chicago, USA

When you plan to develop a website, then you look forward to getting a complete solution, and hiring a Web Development Company in Chicago is the best result for this need.

Do you wonder what a full-stack developer is?

Let’s understand it further with this post…

A full stack developer is the one who apprehends the complete web development process and can fit with his expertise on any level of the development process.

However, finding a highly qualified full-stack developer is a tad tricky part of the business, as you need to understand and adopt the right technical expertise to initiate your web project.

If you are worried of picking the wrong resource, which could only hamper your business revenue goal through the website development then you need to consider the right approach suggested in this post, which can further help you hire full stack developer for your specific business needs.

What Does All Full Stack Developer do?

When it comes to web application development, then there is a different set of development, like for frontend and backend. They can be bifurcated into two basic divisions; one who builds the interfaces and rest who builds software engines behind it.

A full stack web developer can perform all these aspects, ranging from databases, front-end (client side) to server-side scripts. These developers have the capability of coding and even designing to a certain extent, also they can manage the complete project.

Full Stack Developers Are Responsible For:

  • Code programs and applications
  • Coordinate other developers and team members
  • Troubleshoot issues
  • Outline testing techniques for web apps
  • Manage web development

Technical expertise of a full-stack developer involves:

  • JavaScript
  • HTML
  • PHP
  • CSS
  • Angular
  • MySQL
  • Ruby
  • MongoDB
  • Node
  • Apache, etc.

Hiring a Full Stack programmer from HireFullStackDeveloperIndia is a lucrative deal, as we have a strong brood of highly experienced developers, who understand every bit of technology and the new market trends, and accordingly, update their knowledge bank.

Our development procedure involves a flexible assignation of different expertise of technologies and has catered our development services to many renowned brands globally.

Our team of full-stack developers is well-equipped with the complete knowledge of front-end and back-end technologies, which together offer a great solution. Our team holds the expertise to handle and manage the entire development and implementation process, whether it is for a website, web app or mobile app.

We are the master of many different technologies and have comprehensive knowledge about pertinent technologies consisting of MEAN stack, front end, back end, UI, server infrastructure, etc.

This is the sole reason, we are picked by a maximum number of businesses across the globe, needless to say, and we are competent enough to accomplish the entire development process based on the requirements of the esteemed clientele.

So without any further delay, simply get in touch with us, and help your business to scale higher…

You can hire the extremely talented and experienced team of full stack developer to form us at the most reasonable cost of $12-$18 hr and mark a change in your business with an effective web solution.