As in our PHP and Node tutorials, we’ll assume that you’re running Ubuntu and that you’ve got most of the prerequisites installed. For Python, we’re going to use the industry-standard Python 3 syntax, at the latest version, 3.7. If you’re running a different operating system or different Python version, you may have to change a few things but the gist should be the same.

Install PyODBC

Once again, there’s very little in the way of prerequisites here. We’re using pyodbc, which you can install via pip:

pip install pyodbc

Other common libraries, such as the popular SQLAlchemy, can be used on Linux too and provide a complete ORM experience if that’s more up your alley.
Build A Connection
PyODBC is a little finnicky in how you connect to a database: you have to build the connection string yourself. I find it’s easiest to create a dictionary and use Python 3’s string formatting to do so:

import pyodbc

details = {
 'server' : 'localhost',
 'database' : 'MyDB',
 'username' : 'me',
 'password' : 'myPass'
 }

connect_string = 'DRIVER={{ODBC Driver 13 for SQL Server}};SERVER={server};PORT=1443; DATABASE={database};UID={username};PWD={password})'.format(**details)

connection = pyodbc.connect(connect_string)
print(connection)

Put that in a .py file and run it, and you should see the connection object information printed out in your terminal. If you’re getting an error, make sure you’ve correctly copied the syntax for the connection string, including the double braces around the driver name. Those tell the formatting function that the insides of the braces aren’t variable names, they’re part of the connection string itself.

Running Database Queries

After you establish a connection, pyodbc makes it easy for you to grab a cursor and begin running queries. The cursor class implements Python’s magic “state managers”, which means you can use it in a with-clause and wait on the results. This makes running queries much easier: you just put them in a with-clause and you don’t have to worry about closing the cursor.

As in our other examples, we’ll assume you’ve got a database with some basic employee data and do some simple manipulations to it:

#grab a new cursor object from the connection
cursor = connection.cursor()

tsql = "INSERT INTO ExampleSchema.Employees (Name, Site) VALUES (?,?);"
#we can use the cursor’s with-clause syntax with the values
with cursor.execute(tsql,'Donovan','Satellite Work Center'):
  print ('Successful Insertion!')

Other statements are just as simple. To read the data back out, we use the same basic structure and read out the cursor’s results from inside the with-clause:

tsql_select = "SELECT Name, Site FROM ExampleSchema.Employees;"
with cursor.execute(tsql_select):
  row = cursor.fetchone()
  while row:
    print (str(row[0]) + " works at " + str(row[1]))
    row = cursor.fetchone()

And updates and deletes are done in a similar fashion.

Python Best Practices

Because pyodbc makes extensive use of the while-clause structure, you may want to review that syntax to make sure you know when your cursors are opening and closing. In fact, the same syntax can be used for the connection itself, but having multiple nested layers of with-clauses is not very Pythonic. Instead, if you need to close the connection manually, you can use connection.close().

Make sure you’ve read our installation guide before we begin. The guide will assume you’ve got a working installation of MS SQL Server on Ubuntu. Other Linux variants should be similar, but you should double-check for differences in how commands are run.

Setting Configuration with mssql-conf

If you’re running SQL Server directly on your Linux machine, then the recommended way to change configuration is through the mssql-conf binary that was installed alongside your SQL Server installation.

As a simple example, we’ll look at changing the port that SQL Server runs on. By default, the port is set to 1433 but you may want to change it for security reasons. For example, we’ll set it to 14330. To change the port, start by running mssql-conf with the new port number:

sudo /opt/mssql/bin/mssql-conf set network.tcpport 14330

You should get back a message indicating the port has been updated. Now, you’ll need to restart the server so the configuration changes can take effect:

sudo systemctl restart mssql-server

The next time you connect to the server, you’ll need to use the updated port number. For a typical configuration change, that’s all that needs to happen. Using the mssql-conf binary, you can change everything from the log or data directory, to the high availability settings. You can run mssql-conf list to get a complete listing of commands to run, or run man mssql-conf to see the complete guide to the config binary.

Setting Configuration through Environment Variables

Using mssql-conf is great when you’re running SQL Server locally, but often times it’s not possible to execute commands directly on a database server. This is especially true in a containerized setting, like when running Docker. There, it can be helpful to set important configuration values through the environment, which can be manipulated in a container setting.

As above, we can change the configuration to make SQL Server run on a different port, port 14330. This time, we set an environment variable to make the change. Add the following to your Dockerfile before the line running SQL Server:

ENV MSSQL_TCP_PORT 14330

And then run docker build to build a new container with the different port. Make sure that you allow access on the new port, and that you restart the container with the new environment variable, and you should see SQL Server running with the new configuration on the new port.

As before, several important configuration values can be set using the environment, including data directories, high availability, and even the initial installation password. While it’s possible to use either mssl-conf or environment variables to configure SQL Server on Linux, it’s best to use mssql-conf if you can. That allows you to easily see what the existing configuration is, and change it in a central place.

If you haven’t already done so, you’ll want to take a look at our installation instructions to get your development environment ready. This guide will assume you’re running Ubuntu, but any Linux flavor will do. We’ll also assume you have node and npm binaries installed.

Install NPM Dependencies

First, you’ll need to install some npm libraries for interacting with SQL Server. I recommend tedious, which is a T-SQL only driver that Microsoft supports:

npm install tedious

Tedious works with version 4 of Node.js and later.  The tedious library is actually all you need to execute T-SQL commands. Other libraries, like the popular Sequelize library, can interact with MS SQL as well and provide a more full-featured ORM.

Test the Connection

A basic connection with tedious is fairly simple. You take the connection details and pass them to the connection constructor. Connections implement the EventEmitter API, which means you can wait on the asynchronous “connected” event to fire before continuing:

let tedious = require(‘tedious’);
let Connection = tedious.Connection;
const config = {
userName: 'myUser', // update me
password: 'myPass', // update me
server: 'localhost',
options: {
    database: 'MyDB'
  }
}

function handleConnection(err){
  if(err) console.error("error connecting :-(", err);
  else console.log("successfully connected!!")
}
let connection = new Connection(config);
connection.on('connect', handleConnection);

Our handleConnection function lets us asynchronously handle errors and notify us once we’ve connected. Put the above code in a module called connect.js and run it by typing node connect.js in a terminal. You should see “successfully connected!” pop up in your window.

Run Some Queries

Now that we’ve got a successful connection, we can use that as a basis for running more complicated queries. In tedious, these are done by creating new Request objects, and then calling a method to fill in the parameters. When the Request is executed and returns, it calls a callback that we provide.

As with our PHP example, we’ll assume you’ve got a basic employees table in your database. Here’s a simple select statement you can run with your connection from the previous section:

const statement = "SELECT Id, Name, Location FROM ExampleSchema.Employees;"
function handleResult(err, numRows, rows){
  if(err) return console.error("error running select statement", err);
  console.log("got", numRows, "rows back from the DB");
}

let results = [];
function handleRow(columns){
  columns.forEach(function(column){
    if(column.value !== null) results.push(column.value);
  });
}

let request = new tedious.Request(statement, handleResult);
request.on('row', handleRow);
connection.execSql(request);

Here we see the basic principles of MS SQL running in Node.js: everything is done asynchronously by creating requests and passing them to tedious to execute. The results are also events, and we use the EventEmitter “on row” syntax to build up the results.

Using the same paradigm we can also execute INSERT or UPDATE commands:

const statement = "INSERT INTO ExampleSchema.Employees OUTPUT INSERTED.Id (Name, Site) VALUES (@Name,@Site)"
const sampleUser = {name : "Joan", site : "Downtown"};
let request = new tedious.Request(statement, handleResult);
request.addParameter('Name', tedious.TYPES.NVarChar, sampleUser.name);
request.addParameter('Site', tedious.TYPES.NVarChar, sampleUser.site);
connection.execSql(request);

Best Practices

Tedious makes heavy use of the EventEmitter pattern, so you should be familiar with that before using the library in your code. In particular, you need to make sure that you’re not trying to execute statements before the connection is ready, or trying to read results before they’ve been written.

We’re assuming you’ve already got MS SQL Server running on an Ubuntu machine. If you haven’t, take a look at our installation guide to get up to speed. We’ll be using Ubuntu, but the instructions aren’t that much different for RHEL or others.

Install PHP Extensions

Using PEAR, you’ll need to install and configure a few extensions for PHP to be able to access the MS SQL Server. PHP comes with the PHP Data Objects interface that provides a common way to access many different databases, but you need to install an extension for MS SQL Server to be able to use it PDO.

To install them, run the following PEAR command:

sudo pecl install sqlsrv pdo_sqlsrv

Then configure PHP to use the new extensions using bash (note the sudo!):

sudo echo "extension= pdo_sqlsrv.so" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`
sudo echo "extension= sqlsrv.so" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`

Connect to the Database

Now that PHP’s PDO knows how to interact with MS SQL Server, you can use it to connect to the server and run database commands. To establish a connection, use the global $sqlserv_connect function. For example, to connect to a local server:

<?php

//format: serverName\instanceName, portNumber (default is 1433)
$serverName = "localhost";

$connectionInfo = array( "Database"=>"dbName", "UID"=>"myUserName", "PWD"=>"myPassword");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Got a connection!<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

Run that on the command line and you should see “Got a connection!” indicating that you were successfully connected to the database. The hard part is over! All that’s left is running some commands on the connection.

Execute Queries

Let’s assume you’ve got a simple SQL table set up to track employees in your organization, with fields for employee name, id, and work site. You’d like to run INSERT and UPDATE queries to add employees and change their data as part of your web application. Once you’ve got the connection set up as above, you can execute any command you’d like. To insert new users:

$tsql= "INSERT INTO ExampleSchema.Employees (Name, Site) VALUES (?,?);";
$params = array('Beverly','Remote Office');
$getResults= sqlsrv_query($conn, $tsql, $params);
$numRows = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $numRows == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ( "inserted ".$numRows." row(s) " . PHP_EOL);

Notice how we use the $conn object we created above, and pass it to the global function sqlsrv_query. We also pass in a T-SQL statement and the associated parameters. Once the statement is executed, you also need to check to make sure you got a valid result.

Updates work the same way. We can change just the $tsql statement and get a working update:

$tsql= "UPDATE ExampleSchema.Employees SET Site= ? WHERE Name = ?);";
$params = array('Undersea Research Site', 'Beverly'); //etc.

Best Practices

Now that you’ve got a running application, be sure to check out the documentation on PDO. In particular, you’ll want to confirm after each statement that you execute that you got a correct result and that you got no errors. If you’re having trouble with your MS SQL Server on Linux, the “print_r( sqlsrv_errors(), true)” statement can be very helpful in debugging.

As the PHP driver is relatively young, you might also want to check out the GitHub page and the official documentation for new releases and for explanations of how to work around known issues.

Backing Up Windows Data

If you’re using SQL Server Management Studio, making a backup is as simple as selecting it from the appropriate menu. Find your database in the Object Explorer, right click it, and select Tasks → Back Up Database. Use the backup menu to create a full database backup and save it to an appropriate location.

If you’re not using Management Studio, you’ll need to make the backup by using the TSQL BACKUP DATABASE command. The syntax is straightforward:

BACKUP DATABASE MyData  
TO DISK = 'Z:\WindowsBackups\MyData.bak'  
   WITH FORMAT,  
      MEDIANAME = 'Z_WindowsBackups',  
      NAME = 'Windows Database';

In both cases, you should end up with a .bak file generated by your Windows database in a location that you control. The .bak file contains the entire contents of your database, so make sure you treat it securely!

Transferring the Backup to the Linux Server

Now that you’ve got the backup file, the next step is to get in on the Linux machine that’s running SQL Server. The standard way to achieve this is via scp, a tool for transferring files to remote machines over a network. While Windows doesn’t come with scp installed, you can install PuTTY and use it transfer the files.

Once you’ve got PuTTY installed and established a connection to the Linux server, the command to transfer the files is again very straightforward:

pscp Z:\WindowsBackups\MyData.bak user@linux_host:MyData.bak

Where “linux_host” is the address of your Linux server. The command stores the backup file in the home directory of the remote server, under the same name.

Next, using PuTTY, you’ll need to ssh into the remove server and move the file to a location where we can restore it from. With SQL Server for Linux, the restore file needs to be in a subdirectory of the main SQL Server directory of /var/opt/mssql. Once you’re ssh’d in, you can create the backup subdirectory:

mkdir -p /var/opt/mssql/backup

Then you can move the .bak file into that directory:

mv  MyData.bak /var/opt/mssql/backup/MyData.bak

Restoring From the Backup File

Finally, to get the data onto the Linux SQL instance, you’ll need to use the .bak file to restore the data. Run the following command to get into a SQL shell:

sqlcmd -S localhost -U my_username

From there, all you need to do is run the RESTORE DATABASE command and pass in the filename you chose:

RESTORE DATABASE MyData
FROM DISK = '/var/opt/mssql/backup/MyData.bak';

That’s it! From there, you should be able to query the Linux server exactly as you could the Microsoft one.