SQL-Ex blog

Строка подключения к SQL Server

Строка подключения представляет собой данные, требуемые для указания экземпляра баз данных и авторизации на нем. Могут быть незначительные нюансы в зависимости от типа экземпляра, но в целом требуется предоставить информацию об имени сервера, драйвере базы данных, имени пользователя, пароле и номере порта.

Детали этой строки подключения будут теми же самыми, которые вы используете для подключения к экземпляру с помощью SSMS. Обратите внимание на формат экземпляра {Сервер}\{Экземпляр} - у нас это будет DESKTOP-6L1B155\FogelDev. Также возможно подключение по имени сервера, если у вас запущен единственный экземпляр по умолчанию (без имени).

Подробная информация об экземпляре SQL Server

База данных SQL Server и код создания таблицы

Давайте сначала создадим базу данных с таблицей, структура которой будет использоваться для работы с Python в SQL Server. Следующий скрипт создаст базу данных и единственную таблицу для использования в демонстрационных целях.

CREATE DATABASE CODESIGHT_DEMOS;

USE CODESIGHT_DEMOS;

CREATE TABLE POSTS

(

ID INT IDENTITY(1,1) PRIMARY KEY,

NAME VARCHAR(200),

Author VARCHAR(100),

URL VARCHAR(100),

DATE DATE

)

INSERT INTO POSTS

VALUES('Properly Dealing with Encryption of Databases in an AlwaysOn Availability Group Scenario','Alejandro Cobar','https://codingsight.com/encryption-of-databases-in-alwayson-availability-group-scenario/','4/23/2021')

INSERT INTO POSTS

VALUES('An Overview of DataFrames in Python','Aveek Das','https://codingsight.com/an-overview-of-dataframes-in-python/','4/23/2021')

INSERT INTO POSTS

VALUES('SQL Server Business Intelligence (BI) – Traditional Tools and Technologies','Haroon Ashraf','https://codingsight.com/sql-server-business-intelligence-bi-tools-and-technologies/','4/19/2021')

Модули Python

Имеется множество модулей для подключения к экземпляру SQL Server, два из них являются основными. В этой статье мы остановимся на PYODBC и SQLAlchemy. Эти модули выполняют основную часть работы, связанной с подключением.

Потребуется еще код для чтения данных из таблиц SQL Server в память. Здесь на помощь приходит библиотека Pandas. Pandas может тесно интегрироваться с SQLALchemy для чтения данных непосредственно в объекты DataFrame (хранилище в памяти на основе массивов, которое может работать с молниеносной скоростью и выглядит как таблицы базы данных).

Давайте взглянем на несколько примеров для строки подключения.

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

Обратите также внимание на то, что это конкретное соединение использует авторизацию Windows. Т.е. будет предпринята попытка авторизоваться как пользователь моей машины Windows, на которой я залогинился. Если не указывать имя пользователя и пароль в строке подключения, это поведение будет принято по умолчанию.

• Servername = 'DESKTOP-6L1B155' (имя сервера)


• Instancename = 'FogelDev' (имя экземпляра)


• Databasename = 'CODESIGHT_DEMOS' (имя базы данных)


• Portnumber = '1433' (номер порта)

print("mssql+pyodbc://@{SERVER}:{PORT}\\{INSTANCE}/{DATABASE}?driver=SQL+Server+Native+Client+11.0".format(SERVER = Servername, INSTANCE = Instancename,DATABASE = Databasename,PORT = Portnumber))

MSSQLengine = sqlalchemy.create_engine("mssql+pyodbc://@{SERVER}\\{INSTANCE}:{PORT}/{DATABASE}?driver=SQL+Server+Native+Client+11.0".format(SERVER = Servername, INSTANCE = Instancename,DATABASE = Databasename,PORT = Portnumber))

Мы можем протестировать корректную работу этого подключения, выполнив запрос с помощью Pandas. Он должен прочитать запрос из строки подключения с помощью команды read_sql_query.

df = pd.read_sql_query("SELECT * FROM POSTS", MSSQLengine)
df

Авторизация SQL Server

Что если вам необходимо использовать авторизацию SQL Server для подключения? Тогда мы можем подправить наш код Python для строки подключения, добавив имя пользователя и пароль к движку SQLALchemy.

• Servername = 'DESKTOP-6L1B155' (имя сервера)


• Instancename = 'FogelDev' (имя экземпляра)


• Databasename = 'CODESIGHT_DEMOS' (имя базы данных)


• Portnumber = '1433' (имя базы данных)


• Username = 'CodingSightUser' (имя пользователя)


• Password = 'Password123' (пароль)

print("mssql+pyodbc://{USER}:{PASS}@{SERVER}:{PORT}\\{INSTANCE}/{DATABASE}?driver=SQL+Server+Native+Client+11.0".format(SERVER = Servername, INSTANCE = Instancename,DATABASE = Databasename,PORT = Portnumber,USER = Username, PASS = Password))
MSSQLengine = sqlalchemy.create_engine("mssql+pyodbc://{USER}:{PASS}@{SERVER}\\{INSTANCE}:{PORT}/{DATABASE}?driver=SQL+Server+Native+Client+11.0".format(SERVER = Servername, INSTANCE = Instancename,DATABASE = Databasename,PORT = Portnumber,USER = Username, PASS = Password))

И снова мы тестируем наше подключение, но теперь мы выбираем только одну запись из таблицы.

df = pd.read_sql_query("SELECT TOP 1 * FROM POSTS", MSSQLengine)
df

Запись данных

Наконец, посмотрим, как передать данные Python в нашу таблицу на SQL Server. Мы создаем объект Dataframe и добавляем его в таблицу.

Метод Dataframe:

newdata = [{'Name': 'How to Create the Date Table in Power BI', 'Author': 'Haroon Ashraf', 'URL':'https://codingsight.com/how-to-create-date-table-in-power-bi/', 'Date':'4/21/21'}]
load_df = pd.DataFrame(newdata)
load_df.to_sql("POSTS", MSSQLengine, if_exists='append',index = False, chunksize = 200)
Другой метод - это вставка данных непосредственно в таблицу с помощью команды execute.

Метод execute:

MSSQLengine.execute("INSERT INTO [POSTS] VALUES('SQL Query Optimization: 5 Core Facts to Boost Queries','Edwin Sanchez','https://codingsight.com/sql-query-optimization-5-core-facts-to-boost-queries/','3/31/21')");
После выполнения таблица будет выглядеть следующим образом. Она содержит 3 строки, загруженные в исходном скрипте создания базы данных на SQL Server, а четвертая и пятая строки загружены из Python.

Выводы

В данной статье приводятся параметры строки подключения для подключения кода Python к SQL Server. Мы проверили чтение данных из SQL Server при использовании двух методов авторизации - авторизации Windows и SQL Server. Также мы рассмотрели два метода загрузки данных, один из которых включал использование объекта DataFrame в Python, а другой непосредственно команду SQL INSERT.