Skip to content

Python-Liunx-SQL-DB.md

Latest commit

 

History

History
106 lines (70 loc) · 3.86 KB

Python-Liunx-SQL-DB.md

File metadata and controls

106 lines (70 loc) · 3.86 KB

Using SQL Database with Python on Ubuntu

Requirements

You might already have some of the following required installations. ??

Install the required modules

Open your terminal and navigate to a directory where you plan on creating your python script. Enter the following commands to install FreeTDS and pymssql.

sudo apt-get --assume-yes update  
sudo apt-get --assume-yes install freetds-dev freetds-bin
sudo apt-get --assume-yes install python-dev python-pip
sudo pip install pymssql

Create a database and retrieve your connection string

See the getting started page to learn how to create a sample database and retrieve your connection string. It is important you follow the guide to create an AdventureWorks database template. The examples shown below will only work with the AdventureWorks schema.

Connect to your SQL Database

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

Execute a query and retrieve the result set

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()

Inserting a row, passing parameters, and retrieving the generated primary key 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()

Transactions

This code example demonstrates the use of transactions in which you begin a transaction, insert a tuple and then rollback your transaction.

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)")
cnxn.rollback()

Stored procedures

with pymssql.connect("yourserver", "yourusername", "yourpassword", "yourdatabase") as conn:
with conn.cursor(as_dict=True) as cursor:
    cursor.execute("""
    CREATE PROCEDURE FindProductNameName
        @name VARCHAR(100)
    AS BEGIN
        SELECT * FROM Product WHERE Name = @name
    END
    """)
    cursor.callproc('FindPerson', ('Bike'))
    for row in cursor:
        print str(row[0]) + " " + str(row[1]) + " " + str(row[2])