Microsoft sql server and python

Create Python apps using SQL Server on Windows

In this section you will create a simple Python app. The Python app will perform basic Insert, Update, Delete, and Select.

Step 2.1 Install the Python driver for SQL Server

pip install virtualenv #To create virtual environments to isolate package installations between projects virtualenv venv venv\Scripts\activate pip install pyodbc 

Step 2.2 Create a database for your application

Connect to SQL Server using SQLCMD and execute the following statement to create a database called SampleDB.

sqlcmd -S localhost -U sa -P your_password -Q "CREATE DATABASE SampleDB;" 

Step 2.3 Create a Python app that connects to SQL Server and executes queries

Create a new folder for the sample

mkdir SqlServerSample cd SqlServerSample 

Execute the T-SQL scripts below in the terminal with sqlcmd to a table and insert some row.

sqlcmd -S localhost -U sa -P your_password -Q "USE DATABASE SampleDB; CREATE TABLE Employees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));" sqlcmd -S localhost -U sa -P your_password -Q "USE DATABASE SampleDB; INSERT INTO Employees (Name, Location) VALUES (N'Jared', N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');" 

Using your favorite text editor, create a new file called crud.py in the SqlServerSample folder. Paste the code below inside into the new file. This will insert, update, delete, and read a few rows.

import pyodbc server = 'localhost' database = 'SampleDB' username = 'sa' password = 'your_password' cnxn = pyodbc.connect('DRIVER=;SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) cursor = cnxn.cursor() print ('Inserting a new row into table') #Insert Query tsql = "INSERT INTO Employees (Name, Location) VALUES (. );" with cursor.execute(tsql,'Jake','United States'): print ('Successfully Inserted!') #Update Query print ('Updating Location for Nikita') tsql = "UPDATE Employees SET Location = ? WHERE Name = ?" with cursor.execute(tsql,'Sweden','Nikita'): print ('Successfully Updated!') #Delete Query print ('Deleting user Jared') tsql = "DELETE FROM Employees WHERE Name = ?" with cursor.execute(tsql,'Jared'): print ('Successfully Deleted!') #Select Query print ('Reading data from table') tsql = "SELECT Name, Location FROM Employees;" with cursor.execute(tsql): row = cursor.fetchone() while row: print (str(row[0]) + " " + str(row[1])) row = cursor.fetchone() 

Run your Python script from the terminal.

Inserting a new row into table Successfully Inserted! Updating Location for Nikita Successfully Updated! Deleting user Jared Successfully Deleted! Reading data from table Jake United States 

Congratulations! You created your first Python app with SQL Server! Check out the next section to learn about how you can make your Python app faster with SQL Server’s Columnstore feature.

Have Questions?

Happy to help! You can find us on GitHub, MSDN Forums, and StackOverflow. We also monitor the #SQLServerDev hashtag on Twitter.

Источник

Шаг 3. Подтверждение концепции, подразумевающее подключение к SQL с помощью pymssql

Этот пример следует рассматривать только как подтверждение концепции. Пример кода упрощен для ясности и он не обязательно рекомендуется к использованию корпорацией Майкрософт.

Шаг 1. Подключение

Функция pymssql.connect используется для подключения к базе данных SQL.

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

Шаг 2. Выполнение запроса

Функция cursor.execute может использоваться для извлечения результирующего набора из запроса к базе данных SQL. Эта функция фактически принимает любой запрос и возвращает результирующий набор, по которому может быть выполнена итерация с использованием 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() 

Шаг 3. Вставка строки

В этом примере вы узнаете, как безопасно выполнить инструкцию INSERT и передать параметры. Передача параметров в виде значений защищает приложение от внедрения SQL-кода.

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

Шаг 4. Откат транзакции

Этот пример кода демонстрирует использование транзакций, в которых можно:

• начать транзакцию;
• вставить строку данных;
• откатить транзакцию для отмены вставки.

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

Дальнейшие действия

Дополнительную информацию можно найти в Центре разработчика Python.

Источник

Python language extension in SQL Server Machine Learning Services

This article describes the Python extension for running external Python scripts with SQL Server Machine Learning Services. The extension adds:

• A Python execution environment
• Anaconda distribution with the Python 3.5 runtime and interpreter
• Standard libraries and tools
• Microsoft Python packages:
  • revoscalepy for analytics at scale.
  • microsoftml for machine learning algorithms.

  Installation of the Python 3.5 runtime and interpreter ensures near-complete compatibility with standard Python solutions. Python runs in a separate process from SQL Server, to guarantee that database operations are not compromised.

  Python components

  SQL Server includes both open-source and proprietary packages. The Python runtime installed by Setup is Anaconda 4.2 with Python 3.5. The Python runtime is installed independently of SQL tools, and is executed outside of core engine processes, in the extensibility framework. As part of the installation of Machine Learning Services with Python, you must consent to the terms of the GNU Public License.

  SQL Server does not modify the Python executables, but you must use the version of Python installed by Setup because that version is the one that the proprietary packages are built and tested on. For a list of packages supported by the Anaconda distribution, see the Continuum analytics site: Anaconda package list.

  The Anaconda distribution associated with a specific database engine instance can be found in the folder associated with the instance. For example, if you installed SQL Server 2017 database engine with Machine Learning Services and Python on the default instance, look under C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES .

  Python packages added by Microsoft for parallel and distributed workloads include the following libraries.

  Library	Description
  revoscalepy	Supports data source objects and data exploration, manipulation, transformation, and visualization. It supports creation of remote compute contexts, as well as a various scalable machine learning models, such as rxLinMod. For more information, see revoscalepy module with SQL Server.
  microsoftml	Contains machine learning algorithms that have been optimized for speed and accuracy, as well as in-line transformations for working with text and images. For more information, see microsoftml module with SQL Server.

  Microsoftml and revoscalepy are tightly coupled; data sources used in microsoftml are defined as revoscalepy objects. Compute context limitations in revoscalepy transfer to microsoftml. Namely, all functionality is available for local operations, but switching to a remote compute context requires RxInSqlServer.

  Using Python in SQL Server

  You import the revoscalepy module into your Python code, and then call functions from the module, like any other Python functions.

  Supported data sources include ODBC databases, SQL Server, and XDF file format to exchange data with other sources, or with R solutions. Input data for Python must be tabular. All Python results must be returned in the form of a pandas data frame.

  Supported compute contexts include local, or remote SQL Server compute context. A remote compute context refers to code execution that starts on one computer such as a workstation, but then switches script execution to a remote computer. Switching the compute context requires that both systems have the same revoscalepy library.

  Local compute context, as you might expect, includes execution of Python code on the same server as the database engine instance, with code inside T-SQL or embedded in a stored procedure. You can also run the code from a local Python IDE and have the script execute on the SQL Server computer, by defining a remote compute context.

  Execution architecture

  The following diagrams depict the interaction of SQL Server components with the Python runtime in each of the supported scenarios: running script in-database, and remote execution from a Python terminal, using a SQL Server compute context.

  Python scripts executed in-database

  When you run Python «inside» SQL Server, you must encapsulate the Python script inside a special stored procedure, sp_execute_external_script.

  After the script has been embedded in the stored procedure, any application that can make a stored procedure call can initiate execution of the Python code. Thereafter SQL Server manages code execution as summarized in the following diagram.

  

  1. A request for the Python runtime is indicated by the parameter @language=’Python’ passed to the stored procedure. SQL Server sends this request to the launchpad service. In Linux, SQL uses a launchpadd service to communicate with a separate launchpad process for each user. See the Extensibility architecture diagram for details.
  2. The launchpad service starts the appropriate launcher; in this case, PythonLauncher.
  3. PythonLauncher starts the external Python35 process.
  4. BxlServer coordinates with the Python runtime to manage exchanges of data, and storage of working results.
  5. SQL Satellite manages communications about related tasks and processes with SQL Server.
  6. BxlServer uses SQL Satellite to communicate status and results to SQL Server.
  7. SQL Server gets results and closes related tasks and processes.

  Python scripts executed from a remote client

  You can run Python scripts from a remote computer, such as a laptop, and have them execute in the context of the SQl Server computer, if these conditions are met:

  • You design the scripts appropriately
  • The remote computer has installed the extensibility libraries that are used by Machine Learning Services. The revoscalepy package is required to use remote compute contexts.

  The following diagram summarizes the overall workflow when scripts are sent from a remote computer.

  

  1. For functions that are supported in revoscalepy, the Python runtime calls a linking function, which in turn calls BxlServer.
  2. BxlServer is included with Machine Learning Services (In-Database) and runs in a separate process from the Python runtime.
  3. BxlServer determines the connection target and initiates a connection using ODBC, passing credentials supplied as part of the connection string in the Python script.
  4. BxlServer opens a connection to the SQL Server instance.
  5. When an external script runtime is called, the launchpad service is invoked, which in turn starts the appropriate launcher: in this case, PythonLauncher.dll. Thereafter, processing of Python code is handled in a workflow similar to that when Python code is invoked from a stored procedure in T-SQL.
  6. PythonLauncher makes a call to the instance of the Python that is installed on the SQL Server computer.
  7. Results are returned to BxlServer.
  8. SQL Satellite manages communication with SQL Server and cleanup of related job objects.
  9. SQL Server passes results back to the client.

  Next steps

  Обратная связь

  Отправить и просмотреть отзыв по

  Источник