Skip to content

Instantly share code, notes, and snippets.

@hbldh hbldh/records_mssql.py
Last active Mar 9, 2016

Embed
What would you like to do?
How to connect to a SQL Server 2012 on Azure using Records and pymssql as driver.
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
:mod:`records_mssql`
===================
Created by hbldh <henrik.blidh@nedomkull.com>
Created on 2016-03-08
How to connect to Microsoft Azure SQL Server 2012 using PyMSSQL package.
Gist connected to this blog post:
http://blog.nedomkull.com/connecting-to-sql-server-on-azure-with-python/
How-to articles for Python + pymssql on Azure.
https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-windows/
https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-mac-osx/
https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-ubuntu-linux/
--- IMPORTANT ---
When installing pymssql from pypi via pip on Windows,
the resulting installation is not built to use encrypted connection
(i.e. does not include OpenSSL), which is required for connecting to Azure databases.
As per usual on Windows, go to Gohlke's page and download pre-built wheel:
http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
-----------------
"""
from __future__ import division
from __future__ import print_function
from __future__ import unicode_literals
from __future__ import absolute_import
database_server_name = 'nedomkulltest'
mssql_host = '{0}.database.windows.net'.format(database_server_name)
mssql_port = 1433
mssql_user = 'dbadmin'
mssql_pwd = 'AlphaNumeric1'
mssql_db = 'exampledb'
mssql_driver = 'pymssql'
connection_string = 'mssql+{0}://{1}:{2}@{3}:{4}/{5}'.format(
mssql_driver, '{0}@{1}'.format(mssql_user, database_server_name),
mssql_pwd, mssql_host, mssql_port, mssql_db)
sql_query = """
SELECT c.CustomerID,
c.CompanyName,
COUNT(soh.SalesOrderID) AS OrderCount
FROM SalesLT.Customer AS c
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID,
c.CompanyName
ORDER BY OrderCount DESC;
"""
# Add this environment variable flag if you want FreeTDS
# (which powers pymssql) to output A LOT of debug data.
# import os
# os.environ['TDSDUMP'] = 'stdout'
print("Using PyMSSQL as it is.")
import pymssql
conn = pymssql.connect(
server=mssql_host,
port=mssql_port,
user='{0}@{1}'.format(mssql_user, database_server_name), # N.B. Use the server name only here!
password=mssql_pwd,
database=mssql_db)
cursor = conn.cursor()
cursor.execute(sql_query)
row = cursor.fetchone()
while row:
print(row)
row = cursor.fetchone()
print("Using PyMSSQL with SQLAlchemy")
from sqlalchemy import create_engine
engine = create_engine(connection_string)
results = engine.execute(sql_query)
for r in results:
print(r)
print("Using PyMSSQL with Records: SQL for Humans")
import records
db = records.Database(connection_string)
rows = db.query(sql_query)
for r in rows:
print(r)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.