Как подключить базу данных к python

Базы данных в Python: как подключить PostgreSQL и что это такое

Во время разработки приложений часто нужно подключать и использовать базы данных для хранения информации. Самая распространенная база данных — PostgreSQL, поэтому мы расскажем, как работать в Python именно с ней. Для этого существует множество модулей, например:

• Psycopg2
• py-postgresql
• pg8000

Мы расскажем именно про модуль Psycopg2. И выбрали мы его по таким причинам:

• Распространенность — Psycopg2 использует большинство фреймворков Python
• Поддержка — Psycopg2 активно развивается и поддерживает основные версии Python
• Многопоточность — Psycopg2 позволяет нескольким потокам поддерживать одно и то же соединение

Python-разработчик — с нуля до трудоустройства за 10 месяцев

• Постоянная поддержка от наставника и учебного центра
• Помощь с трудоустройством
• Готовое портфолио к концу обучения
• Практика с первого урока

Вы получите именно те инструменты и навыки, которые позволят вам найти работу

Установка Psycopg2

Для начала работы с модулем достаточно установить пакет при помощи pip:

install psycopg2-binary 

Если в вашем проекте используется poetry, то при первоначальной настройке проекта нужно добавить psycopg2-binary в зависимости. Для добавления в уже существующий проект воспользуйтесь командой:

Использование Psycopg2

Подключение к БД:

Для подключения к существующей базе данных необходимо знать основную информацию о вашей БД. Если вы не знаете, где ее взять, то пройдите сначала наш большой курс по Основам баз данных:

• Username — имя пользователя, которое вы используете для работы с PostgreSQL
• Password — пароль, который используется пользователем
• Host Name — имя сервера или IP-адрес, на котором работает PostgreSQL
• Database Name — имя базы данных, к которой мы подключаемся.

Для подключения к базе данных мы используем метод connect() , которому в качестве аргументов передаются вышеперечисленные данные:

import psycopg2 try: # пытаемся подключиться к базе данных conn = psycopg2.connect(dbname='test', user='postgres', password='secret', host='host') except: # в случае сбоя подключения будет выведено сообщение в STDOUT print('Can`t establish connection to database') 

Также подключение к базе данных может осуществляться с помощью Connection URI :

import psycopg2 try: # пытаемся подключиться к базе данных conn = psycopg2.connect('postgresql://user:password@host:port/database_name') except: # в случае сбоя подключения будет выведено сообщение в STDOUT print('Can`t establish connection to database') 

Читайте также: Вышел Python 3.11.0. В два раза быстрее, c детальным описанием ошибок и кучей новых типов

Взаимодействие Python с PostgreSQL

Итак, подключение к базе данных успешно выполнено. Дальше мы будем взаимодействовать с ней через объект cursor , который можно получить через метод cursor() объекта соединения. Он помогает выполнять SQL-запросы из Python.

# получение объекта курсора cursor = conn.cursor() 

С помощью cursor происходит передача запросов базе данных:

# Получаем список всех пользователей cursor.execute('SELECT * FROM users') all_users = cursor.fetchall() cursor.close() # закрываем курсор conn.close() # закрываем соединение 

Для получения результата после выполнения запроса используются следующие команды:

• cursor.fetchone() — вернуть одну строку
• cursor.fetchall() — вернуть все строки
• cursor.fetchmany(size=10) — вернуть указанное количество строк

Хорошей практикой при работе с базой данных является закрытие объекта cursor и соединения с базой. Для автоматизации этого процесса удобно взаимодействовать через контекстный менеджер , используя конструкцию with :

with conn.cursor as curs: curs.execute('SELECT * FROM users') all_users = curs.fetchall() 

В тот момент, когда объект cursor выходит за пределы конструкции with , происходит его закрытие и освобождение связанных с ним ресурсов.

По умолчанию результат возвращается в виде кортежа. Такое поведение возможно изменить, передав параметр cursor_factory в момент открытия объекта cursor , например, использовать NamedTupleCursor. Это вернет данные в виде именованного кортежа:

from psycopg2.extras import NamedTupleCursor # … with conn.cursor(cursor_factory=NamedTupleCursor) as curs: curs.execute('SELECT * FROM users WHERE name=%s', (name='Alfred')) alfred = curs.fetchone() alfred # (id=10, name='Alfred', age='90') # … 

Выполнение запросов

Psycopg2 преобразует переменные Python в SQL значения с учетом их типа. Все стандартные типы Python адаптированы для правильного представления в SQL.

Передача параметров в SQL-запрос происходит с помощью подстановки плейсхолдеров %s и цепочки значений в качестве второго аргумента функции:

with conn.cursor() as curs: curs.execute('SELECT id, name FROM users WHERE name=%s', ('John',)) curs.fetchall() # … with conn.cursor() as curs: curs.execute(INSERT INTO users (name, age) VALUES (%s, %s), ('John', 19)) # … conn.close() 

Подстановка значений в SQL-запрос используется для того, чтобы избежать атак типа SQL Injection. Также несколько полезных советов по построению запросов:

• Плейсхолдер должен быть %s даже если тип подставляемого значения отличается от строки
• Не заключайте плейсходер в кавычки
• Если в запросе используется знак % , он должен быть указан как %%

Профессия «Python-разработчик»

• Изучите Python — язык с простым и понятным синтаксисом
• Научитесь создавать полноценные сайты и веб-приложения
• Освойте популярный фреймворк Python — Django
• Разберитесь в базах данных и научитесь управлять ими с помощью SQL

Подключение База данных SQL Azure с помощью Python и драйвера pyodbc

В этом кратком руководстве описывается, как подключить приложение к базе данных в База данных SQL Azure и выполнять запросы с помощью Python и драйвера SQL Python — pyodbc. В этом кратком руководстве описан рекомендуемый подход без пароля для подключения к базе данных. Дополнительные сведения о бессерверных подключениях можно узнать в центре без пароля.

Необходимые компоненты

• Подписка Azure
• База данных SQL Azure, настроенная с проверкой подлинности Microsoft Entra. Вы можете создать базу данных с помощью краткого руководства по созданию базы данных.
• Последняя версия Azure CLI.
• Visual Studio Code с расширением Python.
• Python 3.8 или более поздней версии.

Настройка базы данных

Безопасные, бессерверные подключения к База данных SQL Azure требуют определенных конфигураций базы данных. Проверьте следующие параметры на логическом сервере в Azure, чтобы правильно подключиться к База данных SQL Azure в локальных и размещенных средах:

1. Для локальных подключений разработки убедитесь, что логический сервер настроен, чтобы разрешить подключение ip-адреса локального компьютера и других служб Azure:
2. Перейдите на страницу «Сеть » сервера.
3. Переключите переключатель «Выбранные сети«, чтобы отобразить дополнительные параметры конфигурации.
4. Выберите » Добавить ip-адрес клиента» (xx.xx.xx.xx.xx), чтобы добавить правило брандмауэра, которое будет включать подключения с адреса IPv4 локального компьютера. Кроме того, можно выбрать + Добавить правило брандмауэра, чтобы ввести конкретный IP-адрес.
5. Убедитесь, что выбран параметр Allow Azure services and resources to access this server проверка box.

• Настройте правила брандмауэра База данных SQL Azure.
• Настройте виртуальную сеть с частными конечными точками.

На сервере также должна быть включена проверка подлинности Microsoft Entra и назначена учетная запись администратора Microsoft Entra. Для локальных подключений к разработке учетная запись администратора Microsoft Entra должна быть учетной записью, с помощью azure CLI или Visual Studio. Вы можете проверить, включена ли проверка подлинности Microsoft Entra на странице идентификатора Microsoft Entra на логическом сервере.

Если вы используете личную учетную запись Azure, убедитесь, что настроена настройка Microsoft Entra и настроена для База данных SQL Azure, чтобы назначить свою учетную запись администратором сервера. Если вы используете корпоративную учетную запись, идентификатор Microsoft Entra, скорее всего, будет настроен для вас.

Создание проекта

Создайте проект Python с помощью Visual Studio Code.

Откройте Visual Studio Code и создайте новую папку для проекта и измените его.

mkdir python-sql-azure cd python-sql-azure 

py -m venv .venv .venv\scripts\activate 

python3 -m venv .venv source .venv/bin/activate 

Установка драйвера pyodbc

Чтобы подключиться к База данных SQL Azure с помощью Python, установите pyodbc драйвер. Этот пакет выступает в качестве поставщика данных для подключения к базам данных, выполнения команд и получения результатов. В этом кратком руководстве также вы устанавливаете flask и pydantic uvicorn пакеты для создания и запуска API.

Дополнительные сведения и конкретные инструкции по установке pyodbc драйвера во всех операционных системах см. в разделе «Настройка среды разработки для разработки pyodbc Python».

Создайте файл requirements.txt со следующими строками:

pyodbc fastapi uvicorn[standard] pydantic azure-identity 

pip install -r requirements.txt 

Настройка локального строка подключения

Для локальной разработки и подключения к База данных SQL Azure добавьте следующую AZURE_SQL_CONNECTIONSTRING переменную среды. Замените заполнители собственными значениями. Примеры переменных среды отображаются для оболочки Bash.

Интерактивная проверка подлинности предоставляет параметр без пароля при локальном запуске.

• Интерактивная аутентификация
• Аутентификация SQL

В Windows интерактивная проверка подлинности Microsoft Entra может использовать технологию многофакторной проверки подлинности Microsoft Entra для настройки подключения. В этом режиме, указав идентификатор входа, запускается диалоговое окно проверки подлинности Azure и позволяет пользователю вводить пароль для завершения подключения.

export AZURE_SQL_CONNECTIONSTRING='Driver=;Server=tcp:.database.windows.net,1433;Database=;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30' 

Дополнительные сведения см. в разделе «Использование идентификатора Записи Майкрософт» с драйвером ODBC. Если вы используете этот параметр, найдите окно, которое запрашивает учетные данные.

Вы можете напрямую пройти проверку подлинности в экземпляре SQL Server с помощью имени пользователя и пароля.

export AZURE_SQL_CONNECTIONSTRING='Driver=;Server=tcp:.database.windows.net,1433;Database=;UID=;PWD=;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30' 

Используйте осторожность при управлении строка подключения, содержащих секреты, такие как имена пользователей, пароли или ключи доступа. Эти секреты не должны быть зафиксированы в системе управления версиями или помещены в небезопасные расположения, к которым они могут обращаться непреднамеренные пользователи. Во время локальной разработки в реальном приложении вы обычно подключаетесь к локальной базе данных, которая не требует хранения секретов или подключения непосредственно к Azure.

Сведения о создании строка подключения можно получить из портал Azure:

1. Перейдите на сервер SQL Server Azure, выберите страницу баз данных SQL, чтобы найти имя базы данных и выбрать базу данных.
2. В базе данных перейдите на страницу строк Подключение ion, чтобы получить строка подключения сведения. Перейдите на вкладку ODBC .

Если вы установили Azure Arc и связали его с подпиской Azure, вы также можете использовать подход к управляемому удостоверению, показанный для приложения, развернутого в Служба приложений.

Добавление кода для подключения к База данных SQL Azure

В папке проекта создайте файл app.py и добавьте пример кода. Этот код создает API, который:

• Извлекает База данных SQL Azure строка подключения из переменной среды.
• Создает таблицу Persons в базе данных во время запуска (только для сценариев тестирования).
• Определяет функцию для получения всех Person записей из базы данных.
• Определяет функцию для получения одной Person записи из базы данных.
• Определяет функцию для добавления новых Person записей в базу данных.

import os import pyodbc, struct from azure import identity from typing import Union from fastapi import FastAPI from pydantic import BaseModel class Person(BaseModel): first_name: str last_name: Union[str, None] = None connection_string = os.environ["AZURE_SQL_CONNECTIONSTRING"] app = FastAPI() @app.get("/") def root(): print("Root of Person API") try: conn = get_conn() cursor = conn.cursor() # Table should be created ahead of time in production app. cursor.execute(""" CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY IDENTITY, FirstName varchar(255), LastName varchar(255) ); """) conn.commit() except Exception as e: # Table may already exist print(e) return "Person API" @app.get("/all") def get_persons(): rows = [] with get_conn() as conn: cursor = conn.cursor() cursor.execute("SELECT * FROM Persons") for row in cursor.fetchall(): print(row.FirstName, row.LastName) rows.append(f", , ") return rows @app.get("/person/") def get_person(person_id: int): with get_conn() as conn: cursor = conn.cursor() cursor.execute("SELECT * FROM Persons WHERE person_id) row = cursor.fetchone() return f", , " @app.post("/person") def create_person(item: Person): with get_conn() as conn: cursor = conn.cursor() cursor.execute(f"INSERT INTO Persons (FirstName, LastName) VALUES (?, ?)", item.first_name, item.last_name) conn.commit() return item def get_conn(): credential = identity.DefaultAzureCredential(exclude_interactive_browser_credential=False) token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE") token_struct = struct.pack(f's', len(token_bytes), token_bytes) SQL_COPT_SS_ACCESS_TOKEN = 1256 # This connection option is defined by microsoft in msodbcsql.h conn = pyodbc.connect(connection_string, attrs_before=) return conn 

В примере кода показаны необработанные инструкции SQL, которые не следует использовать в рабочем коде. Вместо этого используйте пакет реляционного сопоставителя объектов (ORM), например SqlAlchemy , который создает более безопасный уровень объектов для доступа к базе данных.

Локальный запуск и проверка приложения

Приложение готово к локальному тестированию.

app.py Запустите файл в Visual Studio Code.

uvicorn app:app --reload 

Развертывание в Службу приложений Azure

Приложение готово к развертыванию в Azure.

Создайте файл start.sh, чтобы gunicorn в службе приложение Azure может запускать uvicorn. Start.sh имеет одну строку:

gunicorn -w 4 -k uvicorn.workers.UvicornWorker app:app 

az webapp up \ --resource-group \ --name

az webapp config set \ --resource-group \ --name \ --startup-file start.sh 

az webapp identity assign \ --resource-group \ --name

Подключение Служба приложений База данных SQL Azure

В разделе «Настройка базы данных» настроена сеть и проверка подлинности Microsoft Entra для сервера базы данных SQL Azure. В этом разделе описана настройка базы данных и настройка Служба приложений с помощью строка подключения для доступа к серверу базы данных.

Для выполнения этих команд можно использовать любое средство или интегрированную среду разработки, которые могут подключаться к База данных SQL Azure, включая SQL Server Management Studio (SSMS), Azure Data Studio и Visual Studio Code с расширением mssql SQL Server. Кроме того, можно использовать портал Azure, как описано в кратком руководстве. Используйте редактор запросов портал Azure для запроса База данных SQL Azure.

Добавьте пользователя в База данных SQL Azure с командами SQL, чтобы создать пользователя и роль для доступа без пароля.

CREATE USER [] FROM EXTERNAL PROVIDER ALTER ROLE db_datareader ADD MEMBER [] ALTER ROLE db_datawriter ADD MEMBER [] 

az webapp config appsettings set \ --resource-group \ --name \ --settings AZURE_SQL_CONNECTIONSTRING="" 

Для развернутого приложения строка подключения должны выглядеть следующим образом:

Driver=;Server=tcp:.database.windows.net,1433;Database=;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30 

Тестирование развернутого приложения

Перейдите по URL-адресу приложения, чтобы проверить, работает ли подключение к База данных SQL Azure. URL-адрес приложения можно найти на странице обзора Служба приложений.

https://.azurewebsites.net 

Добавьте /docs к URL-адресу, чтобы просмотреть пользовательский интерфейс Swagger и проверить методы API.

Поздравляем! Теперь приложение подключено к База данных SQL Azure как в локальных, так и в размещенных средах.

Связанный контент

• Перенос приложения Python на использование бессерверных подключений с База данных SQL Azure — отображает управляемое удостоверение, назначаемое пользователем.
• Бессерверные подключения для служб Azure
• Рекомендации по управляемых удостоверениях

Python и MySQL: полное практическое пособие, ч. 1

Часть 1. Устанавливаем MySQL Server и MySQL Connector/Python и работаем с таблицами

MySQL — одна из самых популярных систем управления базами данных (СУБД) на рынке на сегодняшний день. В рейтинге DB-Engines Ranking по состоянию на ноябрь 2023 она уступает только СУБД Oracle.

В этом пособии рассматриваем, как работать с СУБД MySQL, разрабатывая приложения на Python. В том числе создадим небольшую базу данных с рейтингом фильмов и научимся взаимодействовать с ней непосредственно из кода.

Важно! Для работы с этим пособием практическое знакомство с такими концепциями Python, как: циклы for, функции, обработка исключений, создание виртуальной среды и установка пакетов Python с помощью pip. Также вам нужно предварительно ознакомиться с реляционными СУБД и запросами SQL (например, SELECT, DROP, CREATE и JOIN).

Почему используем именно MySQL: преимущества этой СУБД

SQL расшифровывается как Structured Query Language (язык структурированных запросов). Он широко используется для управления реляционными базами данных, включая MySQL.

MySQL — СУБД с открытым исходным кодом. Она была выпущена 23 мая 1995 года и быстро вошла в число лидеров рынка. Основные функции MySQL предоставляются бесплатно, но есть и платные дополнения.

Сейчас MySQL используется всеми основными технологическими компаниями, в том числе Google, LinkedIn, Netflix, X (Twitter) и Uber.

Успех MySQL обусловлен в том числе следующими преимуществами:

• Простота установки. MySQL доступна для всех основных операционных систем, в том числе Windows, macOS, Linux и Solaris. Она легко настраивается, а сторонние инструменты, такие как phpMyAdmin, ускоряют процесс установки.
• Высокая скорость работы. MySQL считается очень быстрой СУБД. Она требует сравнительно мало ресурсов и эффективно масштабируется в долговременной перспективе.
• Права доступа и безопасность пользователей. Сценарий MySQL позволяет установить уровень защиты паролем, назначить пароли администраторов, а также добавлять и отзывать права доступа. Этот сценарий упрощает управление пользователями. Для других СУБД, например, PostgreSQL, используются файлы конфигурации, с которыми сложнее работать.
• Поддержка сообщества. MySQL поддерживается обширным сообществом разработчиков программного обеспечения с открытым исходным кодом.

Установка MySQL Server и MySQL Connector/Python

Для создания нашего приложения вам понадобится установить три вещи:

• MySQL Server
• Python
• MySQL Connector/Python

Сервер MySQL производит всю работу с базой данных. После установки и настройки сервера можно подключить к нему приложение, написанное на Python, с помощью MySQL Connector/Python.

Установка MySQL Server

В официальной документации подробно рассказано, как загрузить и установить сервер MySQL. Вы найдете инструкции для всех популярных операционных систем, в том числе Windows, macOS, Linux и Solaris.

Для Windows лучше всего загрузить MySQL Installer . Можно воспользоваться веб-установщиком или скачать программу установки полностью. Установщик проведет вас через весь процесс.

На первой странице будет предложено выбрать тип установки: только сервер, только клиент, полная установка и выборочная установка. Для этого пособия понадобится только сервер:

Если у вас не установлен пакет Microsoft Visual C++ 2019 Redistributable Package, установка которого является предварительным требованием для MySQL Server 8.0, то откроется страница Check requirements, на которой будет предложено установить новейшую совместимую версию пакета. Нажмите кнопку Execute, чтобы установить его:

Откроется окно программы установки пакета Microsoft Visual C++ 2015-2019 Redistributable Package. Примите условия лицензии и нажмите кнопку Install:

После успешной установки пакета нажмите кнопку Close. Затем нажмите кнопку Next на странице Check requirements в окне MySQL Installer.

На странице Installation нажмите кнопку Execute. После успешной установки нажмите кнопку Next, чтобы перейти к настройке конфигурации продукта.

Настройка MySQL Server

На страницах Type and Networking и Authentication Method оставьте параметры без изменений и нажмите кнопку Next.

На странице Accounts and Roles необходимо ввести пароль для учетной записи root (администратор), а также можно добавить пользователей с различными правами доступа:

Хотя во время установки и необходимо задать учетные данные root, их можно изменить позже.

Важно! Запомните имя хоста, имя пользователя и пароль, потому что они потребуются для подключения к серверу MySQL.

Установка MySQL Connector/Python

Драйвер базы данных — это программный продукт, позволяющий приложению подключаться к системе управления базой данных и взаимодействовать с ней. Языкам программирования, таким как Python, требуется специальный драйвер, чтобы взаимодействовать с базой данных определенного поставщика.

Обычно такие драйверы поставляются в виде сторонних модулей. Python Database API (DB-API) определяет стандартный интерфейс, которому должны соответствовать все драйверы баз данных для Python. Подробно о нем рассказано в PEP 249. Все драйверы баз данных Python, например, sqlite3 для SQLite, psycopg для PostgreSQL и MySQL Connector/Python для MySQL, следуют этим правилам реализации.

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

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

Самый популярный коннектор MySQL для Python — MySQL Connector/Python. Его можно установить с помощью pip (рекомендуется установить его в виртуальной среде проекта):

pip install mysql-connector-python 

Чтобы убедиться в успешности установки, введите в терминале Python:

import mysql.connector 

Если этот код выполнен без ошибок, значит, mysql.connector установлен и готов к использованию. Если произошли какие-либо ошибки, убедитесь, что вы запустили соответствующую виртуальную среду и используете подходящий интерпретатор Python.

Подключение к MySQL Server

MySQL — система управления базами данных на базе сервера. На одном сервере может находиться несколько баз данных.Перед тем, как работать с базой данных, нужно установить соединение с сервером.

Процесс работы программы на Python, которая взаимодействует с базой данных MySQL, состоит из следующих шагов:

• Установить соединение с сервером MySQL.
• Создать базу данных.
• Подключиться к вновь созданной или существующей базе данных.
• Выполнить запрос SQL и получить результаты.
• Сообщить базе данных, если в таблицу внесены какие-либо изменения.
• Закрыть соединение с сервером MySQL.

Это обобщенное описание рабочего процесса. В различных приложениях он может быть реализован по-разному, но в любом случае для начала необходимо подключить базу данных к приложению.

Установка соединения

Первый шаг во взаимодействии с сервером MySQL — установка соединения. Для этого нужно подключиться (connect()) к базе данных из модуля mysql.connector. Эта функция принимает параметры host, user і password и возвращает объект MySQLConnection. Эти учетные данные можно получить от пользователя, а затем передать их методу connect():

from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Введите имя пользователя: "), password=getpass("Введите пароль: "), ) as connection: print(f""" Подключение установлено: хост: порт: пользователь: """) except Error as e: print(f"Ошибка подключения к серверу:\n") 

Приведенный выше код запрашивает учетные данные для подключения к серверу MySQL. В результате вы получаете объект MySQLConnection, который сохраняется в переменной connection. Теперь эту переменную можно использовать для взаимодействия с сервером.

В результате выполнения приведенного выше кода получим примерно такой вывод:

В этом коде есть также несколько важных моментов:

• Всегда нужно обрабатывать исключительные ситуации, которые могут возникнуть во время установки соединения с сервером MySQL. Для этого используется блок try. except. В нем перехватывается исключение и выводится его сообщение.
• После завершения работы с базой данных нужно всегда закрывать соединение. ЯЕсли оставить ненужное соединение открытым, это может привести к непредвиденным ошибкам и снижению производительности. В приведенном выше коде используется блок with, который очищает память от данных, которые требовались для соединения.
• Никогда не следует жестко кодировать учетные данные (имя пользователя и пароль) в сценарии Python. Жесткое кодирование этих данных представляет собой угрозу безопасности. В приведенном выше коде учетные данные запрашиваются у пользователя, а для скрытия пароля применяется модуль getpass. Хотя это и лучше, чем жесткое кодирование, существуют более безопасные способы хранения конфиденциальной информации, например, в переменных окружения.

Итак, вы установили соединение между своей программой и сервером MySQL. Теперь нужно либо создать базу данных, либо подключиться к существующей базе данных на сервере.

Создание базы данных

Чтобы создать базу данных, выполните следующий оператор SQL:

CREATE DATABASE movies_db;

Он создаст базу данных с именем movies_db.

Примечание. В MySQL нужно завершать оператор символом точки с запятой (;), который означает завершение запроса. Но MySQL Connector/Python автоматически добавляет точку с запятой в конце запроса, поэтому в коде Python ее использовать не требуется.

Для выполнения запроса SQL в Python нужно использовать объект курсор, в котором абстрагирован доступ к записям базы данных. MySQL Connector/Python предоставляет класс MySQLCursor, экземплярами которого являются объекты, способные выполнять запросы MySQL в Python. Экземпляр класса MySQLCursor также называется cursor.

Объекты cursor используют объект MySQLConnection для взаимодействия с сервером MySQL. Чтобы создать cursor, используйте метод .cursor() переменной connection:

cursor = connection.cursor()

В результате выполнения этого кода вы получите экземпляр класса.

Запрос, который нужно выполнить, передается методу cursor.execute() в строковом формате. В нашем конкретном случае вы отправите этому методу запрос CREATE DATABASE. Изменим содержимое блока после объявления переменной connection:

from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Введите имя пользователя: "), password=getpass("Введите пароль: "), ) as connection: query = "CREATE DATABASE movie_rating" with connection.cursor() as cursor: cursor.execute(query) except Error as e: print(e) 

После выполнения приведенного выше кода на сервере MySQL появится база данных с именем movie_rating.

Запрос CREATE DATABASE сохраняется в строковом виде в переменной query, а затем передается методу cursor.execute() для виконання. для выполнения. Для реализации процесса очистки в приведенном выше коде используется менеджер контекста с объектом cursor.

Если база данных с тем же именем уже существует на сервере, то может быть выдано сообщение об ошибке. Для проверки можно вывести список всех баз данных на сервере. Используя полученный нами объект MySQLConnection, выполним запрос SHOW DATABASES.

Для этого снова изменим содержимое блока с запросом:

query = "SHOW DATABASES" with connection.cursor() as cursor: cursor.execute(query) for db in cursor: print(db) 

Запустив обновленный код, получим список баз данных:

Команда SHOW DATABASES выводит не только те базы данных, которые вы создали, но и некоторые другие, в том числе information_schema, performance_schema и т. д. Эти базы данных создаются сервером MySQL автоматически и предоставляют доступ к различным метаданным базы данных и настройкам сервера MySQL.

Подключение к существующей базе данных

Подключиться к уже готовой базе данных, как это часто бывает в реальных условиях, можно с помощью того же самого метода connect(), только теперь нужно передать ему дополнительный параметр — database:

from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user=input("Введите имя пользователя: "), password=getpass("Введите пароль: "), database="movie_rating", ) as connection: print(f""" Соединение установлено: хост: порт: пользователь: база данных: """) except Error as e: print(f"Ошибка подключения к серверу:\n") 

Когда соединение будет установлено, имя базы данных сохранится в свойстве connection.database:

Приведенный выше код основан на коде из первого примера подключения. В предыдущий код добавлена строка, передающая имя базы данных в параметре database, и строка для вывода ее имени. Поэтому в результате выполнения этого кода будет установлено соединение с базой данных movie_rating.

Создание, изменение и удаление таблицы

В этом разделе вы воспользуетесь несколькими базовыми запросами DDL (Data Definition Language), в том числе CREATE, DROP и ALTER, из кода на Python. Вы рассмотрите структуру базы данных MySQL, которую будете использовать при освоении этого пособия. Также вы создадите все нужные таблицы и научитесь изменять их.

Описание схемы базы данных

Начнем с создания схемы базы данных для системы рейтингов фильмов. База данных будет состоять из трех таблиц:

• movies содержит общие сведения о фильмах и имеет следующие атрибуты:
  1. — id
  2. — title
  3. — release_year
  4. — genre
  5. — collection_in_mil
• reviewers содержит информацию о людях, которые отправляли обзоры, и имеет следующие атрибуты:
  1. — id
  2. — first_name
  3. — last_name
• ratings содержит информацию о назначенных рейтингах и имеет следующие атрибуты:
  1. — movie_id (внешний ключ)
  2. — reviewer_id (внешний ключ)
  3. — rating

Реальная система (например, IMDb) хранила бы много другой информации: адреса электронной почты, списки актеров и так далее. При желании вы можете добавить в эту базу данных новые таблицы и атрибуты, но для этого пособия будет достаточно описанных трех таблиц.

Ниже приведена схема нашей базы данных:

Таблицы movies и reviewers связаны отношениями «многие ко многим», потому что обзор одного и того же фильма могут сделать несколько человек, а один человек может сделать обзор нескольких фильмов.

Таблица ratings соединяет между собой таблицу movies и reviewers.

Создание таблиц с помощью оператора CREATE TABLE

Теперь, чтобы создать в MySQL новую таблицу, используем оператор CREATE TABLE. Приведенный ниже запрос MySQL создает таблицу movies в базе данных movie_rating:

CREATE TABLE movies( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100), collection_in_mil INT ) 

Если вы уже знакомы с операторами SQL, то большая часть приведенного выше кода вам понятна. Но у синтаксиса MySQL есть некоторые отличия.

Например, в MySQL реализовано много типов данных, в том числе YEAR, INT, BIGINT и другие. Кроме того, в MySQL используется ключевое слово AUTO_INCREMENT, которое указывает, что значение в столбце должно автоматически увеличиваться при вставке новых строк.

Для создания таблицы нужно передать этот запрос методу cursor.execute(), который принимает запрос MySQL и выполняет его в подключенной базе данных MySQL:

create_movies_table_query = """ CREATE TABLE movies( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100), collection_in_mil INT ) """ with connection.cursor() as cursor: cursor.execute(create_movies_table_query) connection.commit() 

Также обратите внимание на оператор connection.commit() в конце кода. По умолчанию коннектор MySQL не фиксирует транзакции автоматически. Изменения, указанные в транзакции, производятся только тогда, когда в конце указывается команда COMMIT. Вызывайте этот метод после каждой транзакции, чтобы производить фактические изменения в таблице.

Таким же образом, как вы создали таблицу movies, выполните следующий сценарий, чтобы создать таблицу reviewers:

create_reviewers_table_query = """ CREATE TABLE reviewers ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100) ) """ with connection.cursor() as cursor: cursor.execute(create_reviewers_table_query) connection.commit() 

При необходимости можно добавить больше информации о человеке, оставившем отзыв, например, адрес электронной почты или дату рождения. Для наших же целей достаточно полей first_name (имя) та last_name (фамилия).

Теперь для создания таблицы ratings выполните следующий сценарий:

create_ratings_table_query = """ CREATE TABLE ratings ( movie_id INT, reviewer_id INT, rating DECIMAL(2,1), FOREIGN KEY(movie_id) REFERENCES movies(id), FOREIGN KEY(reviewer_id) REFERENCES reviewers(id), PRIMARY KEY(movie_id, reviewer_id) ) """ with connection.cursor() as cursor: cursor.execute(create_ratings_table_query) connection.commit() 

Реализация внешних ключей (foreign key) в MySQL несколько отличается от стандартного SQL. В MySQL как родительский, так и дочерний внешний ключ должны использовать одну и ту же подсистему хранения (storage engine).

Подсистема хранения — это программный компонент, который используется в СУБД для выполнения операций SQL.

В MySQL используется два типа подсистем хранения:

• Транзакционные подсистемы хранения обеспечивают безопасные транзакции и позволяют производить откат транзакций с помощью простых команд, например, rollback. К этому типу относятся многие популярные подсистемы хранения MySQL, в том числе InnoDB и NDB.
• Нетранзакционные подсистемы хранения используют созданный вручную код для отмены операторов, действия которых зафиксированы в базе данных. MyISAM, MEMORY и многие другие подсистемы хранения MySQL являются нетранзакционными.

InnoDB используется по умолчанию и является самой популярной. Она обеспечивает целостность данных за счет поддержки ограничения внешнего ключа. Это значит, что любая операция CRUD на внешнем ключе проверяется во избежание несоответствий между таблицами.

Также обратите внимание , что в таблице ratings столбцы movie_id и reviewer_id, являющиеся внешними ключами, объявлены как первичный ключ. Благодаря этому один человек не может дважды назначить рейтинг одному и тому же фильму.

Вы можете использовать один и тот же cursor для выполнения нескольких операций. В этом случае все выполнения составят одну атомарную транзакцию, а не несколько отдельных транзакций. Например, вы можете выполнить все операторы CREATE TABLE с использованием одного cursor, а затем зафиксировать транзакцию один раз:

with connection.cursor() as cursor: cursor.execute(create_movies_table_query) cursor.execute(create_reviewers_table_query) cursor.execute(create_ratings_table_query) connection.commit() 

Приведенный выше код сначала выполнит все три оператора CREATE. Затем он отправит команду COMMIT на сервер MySQL, который зафиксирует транзакцию. Также вы можете использовать метод .rollback(), чтобы отправить команду ROLLBACK на сервер MySQL и отменить все изменения в данных, произведенные транзакцией.

Просмотр схемы таблицы с помощью оператора DESCRIBE

Теперь, когда все три таблицы созданы, можно просмотреть их схемы с помощью следующего оператора SQL:

DESCRIBE ;

Чтобы получить результаты от объекта cursor, потребуется использовать метод cursor.fetchall(). Этот метод возвращает все строки, полученные после последнего выполненного оператора. Предположим, что объект MySQLConnection уже находится в переменной connection. Вы можете распечатать все результаты, возвращенные методом cursor.fetchall():

show_table_query = "DESCRIBE movies" with connection.cursor() as cursor: cursor.execute(show_table_query) result = cursor.fetchall() for row in result: print(row) 

Получим описание таблицы, где каждый столбец представлен в виде кортежа, в котором указан тип данных столбца, указано, является ли он первичным ключом и так далее:

Изменение схемы таблицы с помощью оператора ALTER

В таблице movies есть столбец collection_in_mil, который содержит данные о кассовых сборах в миллионах долларов. С помощью приведенного ниже оператора MySQL вы можете изменить тип данных атрибута collection_in_mil с INT на DECIMAL:

ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1);

DECIMAL(4,1) означает десятичное число, которое состоит максимум из 4 цифр, из которых одна обозначает десятые доли, например, 120.1, 3.4, 38.0 и так далее. После выполнения оператора ALTER TABLE вы можете просмотреть обновленную схему таблицы с помощью рассмотренного выше оператора DESCRIBE.

Определим два запроса:

alter_table_query = """ ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1) """ show_table_query = "DESCRIBE movies" 

Выполним их после подключения:

with connection.cursor() as cursor: cursor.execute(alter_table_query) cursor.execute(show_table_query) result = cursor.fetchall() print("Схема таблицы movie после изменения:") for row in result: print(row) 

Удаление таблиц с помощью оператора DROP

Чтобы удалить таблицу, необходимо применить оператор DROP TABLE. Удаление таблицы — необратимый процесс.

Если вы выполните приведенный ниже код, то потребуется снова отправить запрос CREATE TABLE, чтобы пользоваться таблицей ratings в продолжении этого пособия.

Для удаления таблицы ratings передайте запрос на удаление методу cursor.execute():

drop_table_query = "DROP TABLE ratings" with connection.cursor() as cursor: cursor.execute(drop_table_query) 

На этом заканчивается первая часть пособия по Python и MySQL. В следующей части читайте, как заполнить таблицу данными, читать записи из одной и нескольких таблиц, а также изменять данные и удалять записи из таблицы.

Python: Работа с базой данных, часть 1/2: Используем DB-API

Python DB-API – это не конкретная библиотека, а набор правил, которым подчиняются отдельные модули, реализующие работу с конкретными базами данных. Отдельные нюансы реализации для разных баз могут отличаться, но общие принципы позволяют использовать один и тот же подход при работе с разными базами данных.

В статье рассмотрены основные методы DB-API, позволяющие полноценно работать с базой данных. Полный список можете найти по ссылкам в конец статьи.

Требуемый уровень подготовки: базовое понимание синтаксиса SQL и Python.

Готовим инвентарь для дальнейшей комфортной работы

Python имеет встроенную поддержку SQLite базы данных, для этого вам не надо ничего дополнительно устанавливать, достаточно в скрипте указать импорт стандартной библиотеки

import sqlite3

Примечание: внося изменения в базу не забудьте их применить, так как база с непримененными изменениями остается залоченной.

Вы можете использовать (последние два варианта кросс-платформенные и бесплатные):

• Привычную вам утилиту для работы с базой в составе вашей IDE;
• SQLite Database Browser
• SQLiteStudio

Python DB-API модули в зависимости от базы данных

База данных	DB-API модуль
SQLite	sqlite3
PostgreSQL	psycopg2
MySQL	mysql.connector
ODBC	pyodbc

Соединение с базой, получение курсора

Для начала рассмотрим самый базовый шаблон DB-API, который будем использовать во всех дальнейших примерах:

# Импортируем библиотеку, соответствующую типу нашей базы данных import sqlite3 # Создаем соединение с нашей базой данных # В нашем примере у нас это просто файл базы conn = sqlite3.connect('Chinook_Sqlite.sqlite') # Создаем курсор - это специальный объект который делает запросы и получает их результаты cursor = conn.cursor() # ТУТ БУДЕТ НАШ КОД РАБОТЫ С БАЗОЙ ДАННЫХ # КОД ДАЛЬНЕЙШИХ ПРИМЕРОВ ВСТАВЛЯТЬ В ЭТО МЕСТО # Не забываем закрыть соединение с базой данных conn.close()

При работе с другими базами данных, используются дополнительные параметры соединения, например для PostrgeSQL:

conn = psycopg2.connect( host=hostname, user=username, password=password, dbname=database)

Чтение из базы

# Делаем SELECT запрос к базе данных, используя обычный SQL-синтаксис cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3") # Получаем результат сделанного запроса results = cursor.fetchall() results2 = cursor.fetchall() print(results) # [('A Cor Do Som',), ('Aaron Copland & London Symphony Orchestra',), ('Aaron Goldberg',)] print(results2) # []

Обратите внимание: После получения результата из курсора, второй раз без повторения самого запроса его получить нельзя — вернется пустой результат!

Запись в базу

# Делаем INSERT запрос к базе данных, используя обычный SQL-синтаксис cursor.execute("insert into Artist values (Null, 'A Aagrh!') ") # Если мы не просто читаем, но и вносим изменения в базу данных - необходимо сохранить транзакцию conn.commit() # Проверяем результат cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3") results = cursor.fetchall() print(results) # [('A Aagrh!',), ('A Cor Do Som',), ('Aaron Copland & London Symphony Orchestra',)]

Примечание: Если к базе установлено несколько соединений и одно из них осуществляет модификацю базы, то база SQLite залочивается до завершения (метод соединения .commit()) или отмены (метод соединения .rollback()) транзакции.

Разбиваем запрос на несколько строк в тройных кавычках

Длинные запросы можно разбивать на несколько строк в произвольном порядке, если они заключены в тройные кавычки — одинарные (»’…»’) или двойные («»». «»»)

cursor.execute(""" SELECT name FROM Artist ORDER BY Name LIMIT 3 """)

Конечно в таком простом примере разбивка не имеет смысла, но на сложных длинных запросах она может кардинально повышать читаемость кода.

Объединяем запросы к базе данных в один вызов метода

Метод курсора .execute() позволяет делать только один запрос за раз, при попытке сделать несколько через точку с запятой будет ошибка.

Для тех кто не верит на слово:

cursor.execute(""" insert into Artist values (Null, 'A Aagrh!'); insert into Artist values (Null, 'A Aagrh-2!'); """) # sqlite3.Warning: You can only execute one statement at a time. 

Для решения такой задачи можно либо несколько раз вызывать метод курсора .execute()

cursor.execute("""insert into Artist values (Null, 'A Aagrh!');""") cursor.execute("""insert into Artist values (Null, 'A Aagrh-2!');""")

Либо использовать метод курсора .executescript()

cursor.executescript(""" insert into Artist values (Null, 'A Aagrh!'); insert into Artist values (Null, 'A Aagrh-2!'); """)

Данный метод также удобен, когда у нас запросы сохранены в отдельной переменной или даже в файле и нам его надо применить такой запрос к базе.

Делаем подстановку значения в запрос

Важно! Никогда, ни при каких условиях, не используйте конкатенацию строк (+) или интерполяцию параметра в строке (%) для передачи переменных в SQL запрос. Такое формирование запроса, при возможности попадания в него пользовательских данных – это ворота для SQL-инъекций!

Правильный способ – использование второго аргумента метода .execute()

Возможны два варианта:

# C подставновкой по порядку на места знаков вопросов: cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT ?", ('2')) # И с использованием именнованных замен: cursor.execute("SELECT Name from Artist ORDER BY Name LIMIT :limit", )

Примечание 1: В PostgreSQL (UPD: и в MySQL) вместо знака ‘?’ для подстановки используется: %s

Примечание 2: Таким способом не получится заменять имена таблиц, одно из возможных решений в таком случае рассматривается тут: stackoverflow.com/questions/3247183/variable-table-name-in-sqlite/3247553#3247553

UPD: Примечание 3: Благодарю Igelko за упоминание параметра paramstyle — он определяет какой именно стиль используется для подстановки переменных в данном модуле.
Вот ссылка с полезным приемом для работы с разными стилями подстановок.

Делаем множественную вставку строк проходя по коллекции с помощью метода курсора .executemany()

# Обратите внимание, даже передавая одно значение - его нужно передавать кортежем! # Именно по этому тут используется запятая в скобках! new_artists = [ ('A Aagrh!',), ('A Aagrh!-2',), ('A Aagrh!-3',), ] cursor.executemany("insert into Artist values (Null, ?);", new_artists)

Получаем результаты по одному, используя метод курсора .fetchone()

Он всегда возвращает кортеж или None. если запрос пустой.

cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3") print(cursor.fetchone()) # ('A Cor Do Som',) print(cursor.fetchone()) # ('Aaron Copland & London Symphony Orchestra',) print(cursor.fetchone()) # ('Aaron Goldberg',) print(cursor.fetchone()) # None

Важно! Стандартный курсор забирает все данные с сервера сразу, не зависимо от того, используем мы .fetchall() или .fetchone()

Курсор как итератор

# Использование курсора как итератора for row in cursor.execute('SELECT Name from Artist ORDER BY Name LIMIT 3'): print(row) # ('A Cor Do Som',) # ('Aaron Copland & London Symphony Orchestra',) # ('Aaron Goldberg',)

UPD: Повышаем устойчивость кода

Благодарю paratagas за ценное дополнение:
Для большей устойчивости программы (особенно при операциях записи) можно оборачивать инструкции обращения к БД в блоки «try-except-else» и использовать встроенный в sqlite3 «родной» объект ошибок, например, так:

try: cursor.execute(sql_statement) result = cursor.fetchall() except sqlite3.DatabaseError as err: print("Error: ", err) else: conn.commit()

UPD: Использование with в psycopg2

Благодарю KurtRotzke за ценное дополнение:
Последние версии psycopg2 позволяют делать так:

 with psycopg2.connect("dbname='habr'") as conn: with conn.cursor() as cur:

Некоторые объекты в Python имеют __enter__ и __exit__ методы, что позволяет «чисто» взаимодействовать с ними, как в примере выше.

UPD: Ипользование row_factory

Благодарю remzalp за ценное дополнение:
Использование row_factory позволяет брать метаданные из запроса и обращаться в итоге к результату, например по имени столбца.
По сути — callback для обработки данных при возврате строки. Да еще и полезнейший cursor.description, где есть всё необходимое.

Пример из документации:

import sqlite3 def dict_factory(cursor, row): d = <> for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d con = sqlite3.connect(":memory:") con.row_factory = dict_factory cur = con.cursor() cur.execute("select 1 as a") print(cur.fetchone()["a"])

Дополнительные материалы (на английском)

Краткий бесплатный он-лайн курс — Udacity — Intro to Relational Databases — Рассматриваются синтаксис и принципы работы SQL, Python DB-API – и теория и практика в одном флаконе. Очень рекомендую для начинающих!

• www.tutorialspoint.com/sql/index.htm
• www.tutorialspoint.com/sqlite
• www.sqlitetutorial.net

часть 1/2: Используем DB-API	часть 2/2: Используем ORM

Приглашаю к обсуждению:

• Если я где-то допустил неточность или не учёл что-то важное — пишите в комментариях, важные комментарии будут позже добавлены в статью с указанием вашего авторства.
• Если какие-то моменты не понятны и требуется уточнение — пишите ваши вопросы в комментариях — или я или другие читатели дадут ответ, а дельные вопросы с ответами будут позже добавлены в статью.