Monthly Archives: Grudzień 2017

Python i SQL Server

Gru 22

Posted by

2017-12-22

Aby połączyć się z bazą danych SQL Server, musimy użyć jednego z dostępnych pakietów:

Microsoft do komunikacji Pythona z SQL Server zaleca stosowanie pierwszego z nich, więc to nim zajmę się w dalszej części wpisu.

Instalacja pakietu pyodbc sprowadza się do wywołania polecenia:

pip install pyodbc

Drugim krokiem jest instalacja sterownika Microsoft ODBC Driver for SQL Server (https://docs.microsoft.com/pl-pl/sql/connect/odbc/download-odbc-driver-for-sql-server).

Aby nasze rozwiązanie było maksymalnie wydajne, warto zawsze używać najnowszej dostępnej wersji. Oto lista aktualnie dostępnych sterowników ODBC dla SQL Server:

  • {SQL Server} – wydany dla wersji SQL Server 2000
  • {SQL Native Client} – wydany dla wersji SQL Server 2005 (znany także jako wersja 9.0)
  • {SQL Server Native Client 10.0} – wydany dla wersji SQL Server 2008
  • {SQL Server Native Client 11.0} – wydany dla wersji SQL Server 2012
  • {ODBC Driver 11 for SQL Server} – obsługuje wersje SQL Server od 2005 do 2014
  • {ODBC Driver 13 for SQL Server} – obsługuje wersje SQL Server od 2005 do 2016

Po tych czynnościach możemy rozpocząć pracę z bazą danych.

Select

import pyodbc

server = "DB-SERVER"
database = "HelperDB"
user = "test"
password = "pass@word"
connection_string = "Driver={{ODBC Driver 13 for SQL Server}};Server={0};Database={1};Uid={2};Pwd={3}".format(server, database, user, password)

connection = pyodbc.connect(connection_string, autocommit = True)
cursor = connection.cursor()

command = "select UserId, UserName, CreationDate, Status from Users"
cursor.execute(command)
for row in cursor:
    userId = row[0]
    userName = row[1]
    creationDate = row[2]
    status = row[3]

connection.close()

Insert

import pyodbc

server = "DB-SERVER"
database = "HelperDB"
user = "test"
password = "pass@word"
connection_string = "Driver={{ODBC Driver 13 for SQL Server}};Server={0};Database={1};Uid={2};Pwd={3}".format(server, database, user, password)

connection = pyodbc.connect(connection_string, autocommit = True)
cursor = connection.cursor()

command = "insert into Users (UserName, CreationDate, Status) output INSERTED.UserId values (?, ?, ?)"
parameters = ["Test_user", "2017-12-20 13:01:09.540", "Active"]
cursor.execute(command, parameters)
result = cursor.fetchone()
userId = result[0]

connection.close()

W powyższym kodzie wykorzystane zostało polecenie output języka T-SQL, w celu pobrania wartości nadanej dla kolumny Identity.

Update

import pyodbc

server = "DB-SERVER"
database = "HelperDB"
user = "test"
password = "pass@word"
connection_string = "Driver={{ODBC Driver 13 for SQL Server}};Server={0};Database={1};Uid={2};Pwd={3}".format(server, database, user, password)

connection = pyodbc.connect(connection_string, autocommit = True)
cursor = connection.cursor()

command = "update Users set Status = ? where UserId = ?"
parameters = ["Disabled", 1]
cursor.execute(command, parameters)
row_count = cursor.rowcount

connection.close()

Delete

import pyodbc

server = "DB-SERVER"
database = "HelperDB"
user = "test"
password = "pass@word"
connection_string = "Driver={{ODBC Driver 13 for SQL Server}};Server={0};Database={1};Uid={2};Pwd={3}".format(server, database, user, password)

connection = pyodbc.connect(connection_string, autocommit = True)
cursor = connection.cursor()

command = "delete Users where UserId = ?"
parameters = [1]
cursor.execute(command, parameters)
row_count = cursor.rowcount

connection.close()

Stored procedure

Załóżmy, że w bazie danych mamy następującą procedurę składowaną:

create procedure GetData
	@id int,
	@id_out int output
as
    select @id_out = @id
	
    select 1 as Id, 'Test 1' as DisplayName
    union all
    select 2 as Id, 'Test 2' as DisplayName

    select 1 as [Key], 'Value 1' as [Value]
    union all
    select 2 as [Key], 'Value 2' as [Value]

Procedura posiada jeden parametr wejściowy, jeden wyjściowy i zwraca dwa zbiory danych. Oto w jaki sposób wywołać tą procedurę i odczytać zwracane przez nią dane:

import pyodbc

server = "DB-SERVER"
database = "HelperDB"
user = "test"
password = "pass@word"
connection_string = "Driver={{ODBC Driver 13 for SQL Server}};Server={0};Database={1};Uid={2};Pwd={3}".format(server, database, user, password)

connection = pyodbc.connect(connection_string, autocommit = True)
cursor = connection.cursor()

command = "declare @id_out int; exec GetData ?, @id_out out; select @id_out"
parameters = [3]
cursor.execute(command, parameters)
for row in cursor:
    id = row[0]
    displayName = row[1]   
cursor.nextset()
for row in cursor:
    key = row[0]
    value = row[1]
cursor.nextset()
row = cursor.fetchone()
id_out = row[0]

connection.close()

Więcej informacji można znaleźć tutaj:

Napisane w Python

Dodaj komentarz

Tagi: , ,