- Шаг 3. Подтверждение концепции, подразумевающее подключение к SQL с помощью pyodbc
- Подключение
- Выполнение запроса
- Вставка строки
- Azure Active Directory и строка подключения
- Пример строки подключения для использования с интерактивной проверкой подлинности Azure Active Directory
- Дальнейшие действия
- Шаг 3. Подтверждение концепции, подразумевающее подключение к SQL с помощью pymssql
- Шаг 1. Подключение
- Шаг 2. Выполнение запроса
- Шаг 3. Вставка строки
- Шаг 4. Откат транзакции
- Дальнейшие действия
- Подключение питон к sql
- Подключение к БД
- Добавление данных в таблицу
- Извлечение данных из таблицы
- Удаление данных из таблицы
- Удаление таблицы
- Подведение итогов
- Материалы по теме
Шаг 3. Подтверждение концепции, подразумевающее подключение к SQL с помощью pyodbc
Этот пример является подтверждением концепции. Пример кода упрощен для ясности и для него не гарантируется соблюдение рекомендаций корпорации Майкрософт.
Чтобы приступить к работе, выполните следующий пример скрипта. Создайте файл с именем test.py и добавляйте фрагменты кода по ходу работы.
Подключение
import pyodbc # Some other example server values are # server = 'localhost\sqlexpress' # for a named instance # server = 'myserver,port' # to specify an alternate port server = 'tcp:myserver.database.windows.net' database = 'mydb' username = 'myusername' password = 'mypassword' # ENCRYPT defaults to yes starting in ODBC Driver 18. It's good to always specify ENCRYPT=yes on the client side to avoid MITM attacks. cnxn = pyodbc.connect('DRIVER=;SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password) cursor = cnxn.cursor()
Выполнение запроса
Функция cursor.execute может использоваться для извлечения результирующего набора из запроса к базе данных SQL. Эта функция принимает запрос и возвращает результирующий набор, по которому может быть выполнена итерация с использованием cursor.fetchone().
#Sample select query cursor.execute("SELECT @@version;") row = cursor.fetchone() while row: print(row[0]) row = cursor.fetchone()
Вставка строки
В этом примере вы узнаете, как безопасно выполнить инструкцию INSERT и передать параметры. Параметры защищают приложение от внедрения кода SQL.
#Sample insert query count = cursor.execute(""" INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) VALUES (. )""", 'SQL Server Express New 20', 'SQLEXPRESS New 20', 0, 0, CURRENT_TIMESTAMP).rowcount cnxn.commit() print('Rows inserted: ' + str(count))
Azure Active Directory и строка подключения
pyODBC использует драйвер Microsoft ODBC для SQL Server. Если ваша версия драйвера ODBC — 17.1 или более поздняя, интерактивный режим Azure Active Directory драйвера ODBC можно использовать через pyODBC.
Этот интерактивный параметр работает, если Python и pyODBC разрешают драйверу ODBC отображать диалоговое окно. Этот параметр доступен только в ОС Windows.
Пример строки подключения для использования с интерактивной проверкой подлинности Azure Active Directory
В следующем примере представлена строка подключения ODBC, определяющая интерактивную проверку подлинности Azure Active Directory.
См. сведения о параметрах проверки подлинности драйвера ODBC в руководстве по использованию Azure Active Directory с драйвером ODBC.
Дальнейшие действия
Дополнительную информацию можно найти в Центре разработчика Python.
Шаг 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.
Подключение питон к sql
У библиотеки есть весьма простая, понятная документация и большое комьюнити .
Создадим файл main.py, в котором будет происходить вся магия и импортируем в него ранее установленный модуль:
Подключение к БД
Первым делом нам нужно подключиться к базе. Создадим объект класса pymysql, вызовем у него метод connect и передадим в него параметры для подключения к нашей базе данных (БД):
import pymysql connection = pymysql.connect( host='127.0.0.1', port=3306, user='first_user', password='qwerty', database='db_name', cursorclass=pymysql.cursors.DictCursor )
- host: если ваша БД находится на локальной машине, то его значение будет localhost , либо 127.0.0.1, либо IP адрес хостинга, на котором вы развернули СУБД
- port: стандартный 3306
- user: это логин пользователя
- password: пароль
- db_name: имя нашей базы данных
Обернем код в блок try/except, в блоке try будем подключаться к БД, а в блоке except будем выводить в терминал возможные ошибки:
import pymysql try: connection = pymysql.connect( host='127.0.0.1', port=3306, user='first_user', password='qwerty', database='db_name', cursorclass=pymysql.cursors.DictCursor ) print("successfully connected. ") print("#" * 20) except Exception as ex: print("Connection refused. ") print(ex)
После работы с базой рекомендуется закрывать соединение. Создадим ещё один блок try/finally, внутри блока try мы будем писать наши запросы к БД, а внутри блока finally будем закрывать наше соединение:
import pymysql try: connection = pymysql.connect( host='127.0.0.1', port=3306, user='first_user', password='qwerty', database='db_name', cursorclass=pymysql.cursors.DictCursor ) print("successfully connected. ") print("#" * 20) try: pass finally: connection.close() except Exception as ex: print("Connection refused. ") print(ex)
Чтобы начать работать с MySQL, нам нужно создать объект cursor. Это объект, который содержит в себе различные методы для проведения SQL команд. М ы можем как просто положить его значение в переменную cursor = connection.cursor() , так и воспользоваться контекстным менеджером with. Мне второй вариант нравится больше, так как выглядит лаконичней, да и документация подсказывает нам, как правильно работать с библиотекой:
import pymysql try: connection = pymysql.connect( host='127.0.0.1', port=3306, user='first_user', password='qwerty', database='db_name', cursorclass=pymysql.cursors.DictCursor ) print("successfully connected. ") print("#" * 20) try: # create table with connection.cursor() as cursor: pass finally: connection.close() except Exception as ex: print("Connection refused. ") print(ex)
Теперь давайте создадим простую таблицу, на которой сегодня потренируемся. Создаем переменную create_table_query и пишем запрос.
Создать таблицу users со следующими строками:
# create table with connection.cursor() as cursor: create_table_query = "CREATE TABLE `users`(id int AUTO_INCREMENT," \ " name varchar(32)," \ " password varchar(32)," \ " email varchar(32), PRIMARY KEY (id));"
Для того чтобы выполнить запрос на создание таблицы, вызываем у cursor метод execute, и передаем в него наш запрос. Выведем в print сообщение об успешном исполнении:
# create table with connection.cursor() as cursor: create_table_query = "CREATE TABLE `users`(id int AUTO_INCREMENT," \ " name varchar(32)," \ " password varchar(32)," \ " email varchar(32), PRIMARY KEY (id));" cursor.execute(create_table_query) print("Table created successfully")
Добавление данных в таблицу
Таблицу мы создали, теперь давайте заполним её данными. За добавление данных в таблицу в SQL отвечает метод INSERT. Пишем запрос. Дословно говорим:
Вставить в таблицу users, перечисляем поля, которые хотим заполнить, а затем данные, которыми мы хотим наполнить запись в таблице.
Например, у нас будет пользователь Анна, с паролем qwerty и почтой от gmail:
# insert data with connection.cursor() as cursor: insert_query = "INSERT INTO `users` (name, password, email) VALUES ('Anna', 'qwerty', 'anna@gmail.com');"
Вызываем метод execute у cursor и передаем в него наш запрос. Для того чтобы наши данные занеслись в таблицу и сохранились там, нам нужно закоммитить или зафиксировать наш запрос. Вызываем метод commit у объекта connection:
# insert data with connection.cursor() as cursor: insert_query = "INSERT INTO `users` (name, password, email) VALUES ('Anna', 'qwerty', 'anna@gmail.com');" cursor.execute(insert_query) connection.commit()
Теперь в нашей таблице создалась одна запись с пользователем Анна.
Извлечение данных из таблицы
Напишем запрос на извлечение всех данных из таблицы. Звездочка в данном запросе забирает все, что есть в таблице:
# select all data from table with connection.cursor() as cursor: select_all_rows = "SELECT * FROM `users`" cursor.execute(select_all_rows)
У cursor есть замечательный метод fetchall, который извлекает из таблицы все строки. Нам лишь остается пробежаться по ним циклом и распечатать.
# select all data from table with connection.cursor() as cursor: select_all_rows = "SELECT * FROM `users`" cursor.execute(select_all_rows) rows = cursor.fetchall() for row in rows: print(row) print("#" * 20)
Удаление данных из таблицы
Напишем запрос на удаление. Говорим: Удалить запись из таблицы users, где id равен 1. У нас ведь пока только одна запись:
# delete data with connection.cursor() as cursor: delete_query = "DELETE FROM `users` WHERE cursor.execute(delete_query) connection.commit()
Удаление таблицы
Последний запрос, который мы выполним. Давайте дропним нашу таблицу. Под словом дропнут подразумевается удаление всей таблицы целиком. Будьте аккуратны: данному запросу, как и запросу на создание таблицы, коммит не требуется:
# drop table with connection.cursor() as cursor: drop_table_query = "DROP TABLE `users`;" cursor.execute(drop_table_query)
Полный код файла main.py:
import pymysql try: connection = pymysql.connect( host='127.0.0.1', port=3306, user='first_user', password='qwerty', database='db_name', cursorclass=pymysql.cursors.DictCursor ) print("successfully connected. ") print("#" * 20) try: # create table with connection.cursor() as cursor: create_table_query = "CREATE TABLE `users`(id int AUTO_INCREMENT," \ " name varchar(32)," \ " password varchar(32)," \ " email varchar(32), PRIMARY KEY (id));" cursor.execute(create_table_query) print("Table created successfully") insert data with connection.cursor() as cursor: insert_query = "INSERT INTO `users` (name, password, email) VALUES ('Anna', 'qwerty', 'anna@gmail.com');" cursor.execute(insert_query) connection.commit() # delete data with connection.cursor() as cursor: delete_query = "DELETE FROM `users` WHERE cursor.execute(delete_query) connection.commit() # drop table with connection.cursor() as cursor: drop_table_query = "DROP TABLE `users`;" cursor.execute(drop_table_query) # select all data from table with connection.cursor() as cursor: select_all_rows = "SELECT * FROM `users`" cursor.execute(select_all_rows) rows = cursor.fetchall() for row in rows: print(row) print("#" * 20) finally: connection.close() except Exception as ex: print("Connection refused. ") print(ex)
Подведение итогов
Теперь вы знаете как можно подключаться к СУБД MySQL с помощью Python, а также выполнять любые запросы, включая создание таблиц, занесение в них данных, а также удаление строк и самих таблиц.
Надеюсь, статья вам помогла и вы узнали что-то новое. 👍