In an ideal database administrator world, the whole lot can be the identical. All servers can be equivalent and インフラエンジニア 未経験 run the same workloads. They’re easier to handle. But, that’s not the case. Nowadays, System administrators need to handle totally different environments. This couldn’t be extra evident in need to perform SQL queries from a Linux machine. On this submit, you’ll learn to hook up with SQL Server from Linux!
One method to connect with SQL Server from Linux is to use a Python module. However earlier than you get that far, let’s first cowl what setting I’m working with.
In this text, I’ll be demonstrating this process utilizing Ubuntu 16.04 and I’ll be connecting to SQL Server 2012 R2. However, the same approach should apply to different Linux flavors and SQL Server versions as properly. Database administrators rejoice!
Conditions
To get started, you’re going to wish to install a couple of stipulations. First, since you’ll be connecting to a SQL Server instance from Python, you’ll want a Python module. A typical Python module to hook up with SQL known as PyODBC. This module permits you to question SQL databases by way of ODBC through a SQL Server ODBC driver for Linux. To get the most recent model put in, use pip (the Python bundle manager).
If this doesn’t work, you may not have pip installed. To put in pip:
Next, it’s worthwhile to create a Python script. I’m going to name this one sql_server.py. To create a Python script, first create a clean file.
Then, using your editor of choice, add the beneath strains. The shebang followed by the trail to the Python binary tells the interpreter this is a Python script. The import assertion then allows you to call the library strategies inside of the pyodbc module. Save this script.
Once you have the Python script created, run the script:
If this runs with out error, the pyodbc module has been put in successfully.
Next, add the code to execute a test question. To do that, create an ODBC string.
To study more about crafting ODBC strings, here‘s a superb resource.
The ODBC string is picky about what’s included. It took a while to figure out learn how to make this work but here’s what mine appears to be like like. Below I’m passing the ODBC string as an argument to the connect() method that’s included with the pyodbc module.
A lot of the ODBC string is evident, however one essential fact is the double backslash for the UID. All the time be certain that you’ve escaped any backslashes in the ODBC string. Additionally, some of the options I’m utilizing are optionally available.
Additionally, you may both use the hostname for SERVER as I’ve performed above or you can use the SQL Server IP tackle.
Be at liberty to add or remove them as you see fit to match your SQL Server.
Next, it’s good to create a cursor object that can permit you to cross a T-SQL assertion to. This is finished with the cursor() method.
Now you might have an object with an execute() technique that can be utilized to move any T-SQL statement we’d like into as proven under. This creates a rows variable containing the ensuing dataset.
You’re now to the point where you’ll need to determine what to do with the dataset. You possibly can ship the outcomes to a CSV file, put the results into another database or write the contents to the console.
Beneath, I’m printing the outcomes to the console if the dataset is populated.
You may see that if the dataset is printed out to the console, the output isn’t too fairly. At this point, it’s up to you to resolve methods to format or parse the information from the database. The laborious half is over!
You’ll now end up with a script that appears like this:
Summary
In this weblog put up, you discovered how to use the pyodbc Python module on Linux to connect with a SQL Server data source. The toughest half for me was figuring out the ODBC string however when you get that covered, you should be easy crusing.