Skip to content

step-3-proof-of-concept-connecting-to-sql-using-pymssql.md

Latest commit

 

History

History
83 lines (64 loc) · 3.73 KB

step-3-proof-of-concept-connecting-to-sql-using-pymssql.md

File metadata and controls

83 lines (64 loc) · 3.73 KB
title Step 3: Proof of concept connecting to SQL using pymssql | Microsoft Docs
ms.custom
ms.date 01/19/2017
ms.prod sql
ms.prod_service connectivity
ms.reviewer
ms.technology connectivity
ms.topic conceptual
ms.assetid 2246ddeb-7c2f-46f3-8a91-cdd718d39b40
author David-Engel
ms.author v-daenge

Step 3: Proof of concept connecting to SQL using pymssql

[!INCLUDEDriver_Python_Download]

This example should be considered a proof of concept only. The sample code is simplified for clarity, and does not necessarily represent best practices recommended by Microsoft.

Step 1: Connect

The pymssql.connect function is used to connect to SQL Database.

    import pymssql  
    conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')

Step 2: Execute query

The cursor.execute function can be used to retrieve a result set from a query against SQL Database. This function essentially accepts any query and returns a result set which can be iterated over with the use of cursor.fetchone().

    import pymssql  
    conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')  
    cursor = conn.cursor()  
    cursor.execute('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;')  
    row = cursor.fetchone()  
    while row:  
        print str(row[0]) + " " + str(row[1]) + " " + str(row[2])     
        row = cursor.fetchone()

Step 3: Insert a row

In this example you will see how to execute an INSERT statement safely, pass parameters which protect your application from SQL injection value.

    import pymssql  
    conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')  
    cursor = conn.cursor()  
    cursor.execute("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express', 'SQLEXPRESS', 0, 0, CURRENT_TIMESTAMP)")  
    row = cursor.fetchone()  
    while row:  
        print "Inserted Product ID : " +str(row[0])  
        row = cursor.fetchone()  
    conn.commit()
    conn.close()

Step 4: Rollback a transaction

This code example demonstrates the use of transactions in which you:

  • Begin a transaction
  • Insert a row of data
  • Rollback your transaction to undo the insert
    import pymssql  
    conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')  
    cursor = conn.cursor()  
    cursor.execute("BEGIN TRANSACTION")  
    cursor.execute("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express New', 'SQLEXPRESS New', 0, 0, CURRENT_TIMESTAMP)")  
    conn.rollback()  
    conn.close()

Next steps

For more information, see the Python Developer Center.