Introduction
Python 3.5 is a very powerful programming language. We can connect it with MSSQL Server and fetch records from Tables to display the client-side.
I will explain step by step how to connect Python with the MSSQL Server 2012.
Basic Configuration Required
- Python 3.5.2 should be installed and set the global environment variable.
- Login to MS SQL Server with SQL Server Authentication
Python Code Development
- Open Sublime Text.
- Create one file with the name of «test» and Save it to E:\ drive.
- open Command Prompt
- Install the MSSQL Server package for Python by using the below command.
- pip install pymssql
- Also, don’t forget to upgrade MS SQL Server Driver for Python like below.
- Upgrade Ms SQL Server Driver
- It will install a package for MS SQL so you can import it into a class file like below.
- import pymssql
Complete Code for Database connection from Python with MS SQL Server
The below code will do MS SQL Server database connection with Python.
- import pymssql
- class PythonDbConnect:
- def __init__(self, name):
- self.name = name
- def PrintRecordsFromDatabase(self):
- conn = pymssql.connect(host=r«DELL», user=‘gul’, password=‘test’, database=‘test’)
- print(«Connected from DB»)
- cursor = conn.cursor()
- cursor.execute(‘SELECT * FROM mmt;’)
- row = cursor.fetchone()
- while row:
- print (str(row[0]) + » « + str(row[1]) + » « + str(row[2]) + » « + str(row[3]) + » « + str(row[4]))
- row = cursor.fetchone()
- dbObj = PythonDbConnect(«Connect MS SQL»)
- dbObj.PrintRecordsFromDatabase()
In the above class PythonDbConnect, method PrintRecordsFromDatabase will connect from Ms SQL Server Database and return records from Table.
Connection string should be defined like below.
- conn = pymssql.connect(host=r«DELL», user=‘gul’, password=‘test’, database=‘test’)
Here server name is DELL, user name is gul, password is test and database name is test.
Below table
Now go to command prompt and execute like below.
E:\python <filename>
Example
Conclusion
Python provides pymssql package for the connection from Python to the MSSQL Server. It is very easy but it should be installed. Also, fetching data from MS SQL Server by Python is very fast.
Пересказ статьи Haq Nawaz. How to Connect & Query SQL Server using Python
Сегодня я расскажу, как подключиться к базам данных SQL, используя Python. Это распространенный вопрос, который возникает в серии ETL. Поэтому я решил рассмотреть его и направлять посетителей сюда, если у них возникнут проблемы с подключением.
Эта установка SQL Server позволит нам:
- Установить подключение к базе данных SQL Server из Python
- Выполнять запросы к базе данных SQL Server
- Извлекать и сохранять данные в фрейме данных
Весь код доступен на GitHub. Сопутствующее видео руководство доступно на YouTube.
Предварительно вам нужно установить и сконфигурировать SQL Server. Об этом здесь. Мы также будем использовать Jupyter notebook для отладки подключения к базе данных. Установку Jupyter notebook можно посмотреть на видео-ролике.
Для SQL Server мы используем SQL Server Native Client 11. Его можно загрузить отсюда. Просто загрузите установщик и выполните установку с параметрами по умолчанию. Можно проверить в редакторе регистра, что SQL Server Native Client установлен. Ключ регистра будет находиться здесь:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server Native Client 11.0\CurrentVersion
Если с установкой покончено, давайте запустим Jupyter notebook. Для подключения к базе данных SQL Server мы используем библиотеку pyodbc. Мы можем установить ее с помощью команды pip.
Pip install pyodbc
Эта команда установит библиотеку на нашу машину. Теперь мы можем импортировать ее в наш блокнот. Кроме того, давайте импортируем библиотеку pandas, чтобы мы могли читать данные и сохранять их в табличной структуре.
import pyodbc
import pandas as pd
Определяем переменные для хранения учетных данных и сведений о базе данных. Используем функцию connect из библиотеки pyodbc для установки соединения с базой данных.
#имя пользователя базы данных. Используйте конфигурационный файл
# или переменные окружения
uid = "etl"
#пароль базы данных. Используйте конфигурационный файл
# или переменные окружения
pwd = "demopass"
driver = "{SQL Server Native Client 11.0}"
server = "localhost" # или имя вашего компьютера
database = "AdventureWorksDW2019;"
В функции connect мы конкатенируем переменную для создания строки подключения. Поскольку мы установили версию SQL Server Express, то именем экземпляра по умолчанию является SQLExpress. Если у вас установлена стандартная редакция или Developer Edition, то вам не требуется указывать экземпляр после имени сервера.
conn = pyodbc.connect('DRIVER=' + driver + ';SERVER=' + server + '\SQLEXPRESS' + ';DATABASE=' + database + ';UID=' + uid + ';PWD=' + pwd)
Нам требуется запрос SQL для тестирования подключения к указанной базе данных SQL Server. Давайте пошлем простой оператор SELECT для получения каких-нибудь данных из нашей базы данных.
sql = "SELECT * FROM [dbo].[DimProductCategory]"
Теперь мы можем использовать функцию запроса на чтение SQL в pandas для выполнения запроса к базе данных. У нас есть все необходимое для этой функции. Мы предоставляем это в переменной sql (запрос SQL) и переменной conn (информация для подключения).
df = pd.read_sql_query(sql, conn)
df.head()
Выполним ячейку блокнота с помощью клавиш SHIFT+ENTER. Если ячейка выполняется без ошибок, то вы успешно все завершили и можете продолжить создавать конвейеры ETL.
Наиболее частой ошибкой является отсутствие Native client. Проверьте его установку. Затем может быть выключен протокол TCP/IP. В этом случае откройте менеджер конфигурации SQL Server и выберите конфигурацию сети. Убедитесь, что протокол TCP/IP включен. Если нет, щелкните правой кнопкой и включите его. После внесения изменений не забудьте перезапустить службу SQL Server.
Ссылки по теме
1. Импорт данных из файла Excel в базу данных SQL Server с помощью Python
2. Краткое руководство по запуску кода Python в SQL Server
Overview
SQL Server 2017 is in RC2 as of the draft of this lesson. We can use CTP 2.1 or higher, to install and configure Machine Learning Server as well as Machine Learning Services (In-Database). We would need an installable setup of SQL Server 2017 which you can download from here. After you have downloaded SQL Server 2017, unzip the setup and execute the setup file.
Explanation
Installing SQL Server 2017 RC2 with Machine Learning Services
The first step in the installation wizard asks for the product key. This is a little different from CTP 2.1 and other versions which start with global updates. You can select the free / evaluation edition as a starting point.
The next step will required you to accept the terms of the license. Click on the check-box to the accept the terms and click Next.
The next step will install setup files and check installation rules. You may get a warning related to your firewall if your Windows Firewall is turned on.
Assuming that SQL Server 2017 has never been installed, select new installation as shown below. In case you do not have any other SQL Server instances installed, you will find the bottom area of the wizard blank. In my case, I already have the SQL Server 2017 CTP 2.1 installed, hence it shows the available instances.
Select In-Database Machine Learning Services under the Database Engine Services and Machines Learning Server with Python under Shared Features as shown below. We need a Python server as well as extensions to communicate with this server from the database engine using T-SQL.
Provide the instance name for the SQL Server being installed. You can make it the default installation if it’s the first time you are installing SQL Server on your machine. Alternatively you can also install it as a named instance.
Configure the services account settings. If you do not wish to make any changes, you can proceed with the default options too.
Select Windows Authentication as the authentication mode for the Database Engine. You also need to assign an administrator of the server. If you are the administrator of the server, you can just click on Add Current User, assuming that you are installing SQL Server.
Now you are required to provide your consent to download and install Python. Click on the Accept button and proceed to the next step.
Finally you are ready to install SQL Server with Machine Learning Services as well as Python. Click on Install button and this should complete the installation process.
After you complete SQL Server 2017 RC2 installation, install SQL Server Management Studio (SSMS) from the installation wizard because we are using this tool to execute T-SQL queries as well as Python scripts.
Installing Visual Studio 2017 with Data science and analytical applications features
Microsoft has been using the Visual Studio shell for development tools and environments like SSMS and SQL Server Data Tools (SSDT). For developing data science solutions, Microsoft has made provisions in Visual Studio 2017 (VS2017) by adding a category of solutions named “Data science and analytical applications”. For developing R solutions and working directly with R server, these feature can be extremely useful.
You can download Visual Studio 2017 Community Edition from here, provided you are just using it for learning purposes. After downloading the web installer when you are at the feature selection page, you can select the Python related features as shown below. This would install components in VS2017 that would provide a development environment for Python solutions.
After you have successfully installed Visual Studio 2017 with data science features, you can open Visual Studio 2017 and create a new Python command-line project from the File > New > Project menu. Once the blank Python project opens, you will see the Python development environment as shown below.
The Solution Explorer window shows the different files in the solution. You can see the Anaconda distribution of Python in the Solution Explorer. The bottom left section shows different debugging windows. You would get intellisense as you type code in the .py file. You can type just 2 lines as shown below, and you will be able to find that the code detected the Anaconda version which we installed as part of the SQL Server setup.
Our focus is on working with Python scripts from T-SQL, so we will limit our discussion of developing Python solutions using Visual Studio 2017 for now.
Explore and test execution of Python Scripts
We have completed the installation of all the tools related to Python development in SQL Server. Now it’s time to test whether we are able to execute Python scripts from T-SQL using SSMS. By default external scripts are not enabled in the database engine. You need to enable the external scripts option using a simple command as shown in this article. You may have to restart the server for the changes to take effect. After the external scripts execution is enabled, we can use sp_execute_external_script to execute R scripts as shown below. Execute the below script using SSMS.
--Python Version execute sp_execute_external_script @language = N'Python', @script = N' import sys print(sys.version) '
It the same command that we have used in Visual Studio and the same output is being rendered here as well. We would look at the usage of this stored procedure to execute external scripts in the upcoming chapters. For now, we have successfully verified the integration of Python with the SQL Server Database Engine as well as Visual Studio 2017.
Now that we have completed a successfully installation, configuration and acceptance testing of components for executing Python scripts from SQL Server, let’s learn some basic concepts of Python programming in the next lesson.
Additional Information
- Consider exploring the Python related options and features in Visual Studio environment after you open a Python project.
Python is an excellent general purpose language that can be used for batch processing and other tasks on your server.
To install Python on Windows Server operating system, you just need to run the installer and use the simplest configuration.
Steps to Install Python on Windows Server Operating System
-
Download the installer (full as opposed to the web sintaller) and save it to your temp folder.
-
Right-click on the file and select Run as administrator.
-
You’ll see a User Account Control popup window with a question, “Do you want to allow the following program to make changes to this computer?” Just click on Yes.
-
Check the Add Python 3.7 to PATH checkbox at the bottom of the window (or whatever the latest version you’re installing).
-
In Optional Features screen, make sure you at least check the following: pip, py launcher, and for all users. Click Next.
-
On the next screen, Advanced Options, make sure you check Install for all users which then will change the value of Customize install location, just accept the default installation in C:\Program Files unless you have a reason to install it somewhere else. Click Install.
-
Once you see Setup was successful just click the Close button.
-
Make sure that C:\Program Files\Python37 and C:\Program Files\Python37\Scripts are in the Path of your System variables.
If you’re able to click on Edit button, you can see all the path entries in each line which is easier to read and edit. In this case, the button is greyed out due to the group policy.
If you don’t care where the program is installed, you can just clik on the Install Now, there’s nothing wrong with the setup and Python will run and this is generally fine for desktop installation.
For server installation, you should be more mindful with the program location better location rather than the default installation under a specific user folder who ran the installer. So it’s better to choose Customize installation.
As a final check, you can open Programs and Features and check if Python and Python Launcher are shown.
That’s all there is to have your server running Python scripts.
If you work mostly with Microsoft stack and need to connect to a SQL Server database using Python, then check this post about Python SQL Server Driver.
Further Reading
How to Activate Built-in Web Server
How to Use Python to Connect to SQL Server
Download
Python Download
With SQL Server 2016 Microsoft added Machine Learning support with R Language in SQL engine itself and called it SQL Server R Services.
Going ahead with the new SQL Server 2017 version Microsoft added Python too as part of Machine Learning with existing R Language, and thus renamed it to SQL Server Machine Learning Services.
Installation/Setup
Here are few steps to get you started with Python programming in SQL Server, so that you can run Python scripts with T-SQL scripts within SSMS:
1. Feature Selection: While installing SQL Server 2017 make sure you’ve selected below highlighted services
2. Configure “external scripts enabled”: Post installation run below SQL statements to enable this option
sp_configure 'external scripts enabled' GO sp_configure 'external scripts enabled', 1; GO RECONFIGURE; GO sp_configure 'external scripts enabled' GO
3. Restart SQL Server service: by “services.msc” program from command prompt, and run below SQL statement, this should show run _value = 1
sp_configure 'external scripts enabled' GO
If still you don’t see run _value = 1, then try restarting the Launchpad service mentioned below in Step #4.
4. Launchpad Service: Make sure this service is running, in “services.msc” program from command prompt. Restart the service, and it should be in Running state.
Its a service to launch Advanced Analytics Extensions Launchpad process that enables integration with Microsoft R Open using standard T-SQL statements. Disabling this service will make Advanced Analytics features of SQL Server unavailable.
Post restarting this service, it should return run _value = 1 on running the query mentioned at Step #3
Run Python from SSMS
So as you’ve installed SQL Server with ML services with Python & R, and enabled the components, now you can try running simple “Hello World!” program to test it:
EXEC sp_execute_external_script
@language = N'Python',
@script = N'print(''Hello Python !!! from T-SQL'')'
Let’s do simple math here:
EXEC sp_execute_external_script @language = N'Python', @script = N' x = 5 y = 6 a = x * y b = x + y print(a) print(b)'
If you still face issues you can go through addition configuration steps mentioned in [MSDN Docs link].