SQL-Ex blog

Поскольку MySQL может играть такую важную роль в разработке приложений, я захотел дать обзор доступа к данным MySQL из кода приложения. Эта статья демонстрирует использование MySQL Connector в Python для установления соединения и выполнения запроса. Хотя я излагаю только один подход доступа к MySQL, примеры должны дать вам основное представление о том, что требуется для подключения к базе данных и получения необходимой информации.

При создании примеров в этой статье я использовал компьютер Mac, на котором были установлены следующие компоненты:

• MySQL 8.0


• Python 3.10


• PyCharm Community Edition IDE


• Модуль MySQL Connector/Python 8.0

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

Замечание. Если вы хотите следовать за изложением, вы найдете определение таблицы и исходный набор данных в приложении к этой статье.

Определение подключения к MySQL

При подключении к базе данных MySQL в Python вам необходимо проделать несколько основных шагов:

1. Импортировать метод connect из модуля MySQL Connector.


2. Использовать метод connect для создания объекта подключения, который содержит детали вашего подключения.


3. Использовать объект подключения для выполнения вашего кода, относящегося к данным.


4. Закрыть подключение.

Когда вы создаете подключение, Python будет генерировать либо объект MySQLConnection, либо объект CMySQLConnection. Это зависит от того, установлено ли в вашей системе расширение Connector C. Это расширение взаимодействует с клиентской библиотекой MySQL C, которая может помочь улучшить производительность для больших наборов данных. Поскольку это расширение было установлено в моей системе, Python генерировал объект CMySQLConnection, когда я выполнял код примера; в противном случае генерировался бы объект MySQLConnection.

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

# импорт  метода connect

    from mysql.connector import connect

# определяем объект подключения

conn = connect(

      user = 'root',

      password = 'SqlPW_py@310!ab',

      host = 'localhost',

      database = 'travel')

print('A connection object has been created.')

# закрываем соединение с базой данных

conn.close()

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

Этот код начинается с выполнения оператора from, который импортирует метод connect из модуля MySQL Connector (mysql.connector). Затем код использует этот метод для создания объекта подключения, который присваивается переменной conn. В этом случае метод connect принимает четыре аргумента: user, password, host и database. Четыре аргумента заключаются в скобки и разделяются запятыми.

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

После создания объекта подключения я выполняю оператор print. Я добавил этот оператор в первую очередь как заполнитель, чтобы показать место, где обычно вставляется код, относящийся к данным и который я буду обсуждать позже в этой статье. Пока оператор print просто возвращает предложение, говорящее, что создан объект подключения.

Последний шаг - это закрытие соединения. Для этого вам нужно только вызвать метод close объекта connection. Это все, что вам необходимо сделать, чтобы установить соединение с базой данных MySQL. В большинстве случаев вам не покажется этот процесс сложным.

За подробностями о подключении к MySQL с помощью метода connect объекта подключения обратитесь к документации MySQL.

Чтение данных подключения из файла

В предыдущем примере метод connect содержал аргументы, необходимые для подключения к экземпляру MySQL. Однако этот метод может также получить аргументы из файла опций MySQL, который является текстовым файлом и обычно имеет расширение .cnf. Для демонстрации того, как это работает, я создал файл с именем connectors.cnf и добавил в него следующую информацию:

[client]
user = root
password = SqlPW_py@310!ab
host = localhost
database = travel

Это те же самые аргументы, которые вы видели в предыдущем примере, без кавычек или запятых. Аргументы сгруппированы вместе в разделе опций [client]. По умолчанию метод connect читает из групп [client] и [connector_python] в указанном файле опций.

Как только вы разместите файл опций, то сможете получить к нему доступ в вашем коде Python, указав аргумент option_files в методе connect, как показано в следующем примере:

# импорт метода connect

from mysql.connector import connect

# определяем объект подключения

conn = connect(option_files =

  '/users/mac/documents/config/connectors.cnf')

# проверка информации подключения

print('The user ' + conn.user + ' is connected to the '

       + conn.database + ' database.')

# закрываем соединение с базой данных

conn.close()

Вместо перечисления отдельных аргументов в методе connect я добавил аргумент option_files с указанием пути, где находится файл connectors.cnf. При выполнении этого кода Python извлекает информацию о подключении из файла и использует ее для создания объекта подключения.

Замечание. Мое использование файла опций ни в коем случае не означает, что этот подход достаточен для обеспечения безопасности ваших соединений Python/MySQL. Как утверждалось выше, этот код является демонстрацией возможностей.

В этом примере я также изменил оператор print. Я сделал это для проверки, что из файла опций получена корректная информация. Оператор возвращает имя пользователя и имя базы данных, что представляется мне достаточным для проверки того, что все работает правильно. Для получения этой информации я использовал переменную conn с указанием свойства user и свойства database, которые описаны в объекте подключения. Затем я конкатенирую эти значения со строковыми значениями, необходимыми для построения возвращаемого сообщения.

Перехват ошибок в соединении MySQL

При доступе к MySQL из приложений Python вы можете включить обработку исключений, относящуюся к соединению с MySQL. Чтобы добавить обработку исключений, заключите код подключения в блок try, а затем добавьте оператор except для перехвата любых ошибок MySQL, как показано в следующем примере:

# импорт методов  connect и Error

from mysql.connector import connect, Error

 # пытаемся выполнить блок кода

try: 

  # определяем объект подключения

  conn = connect(option_files =

    '/users/mac3/documents/config/connectors.cnf')

  # проверяем информацию подключения

  print('The user ' + conn.user + ' is connected to the '

    + conn.database + ' database.')

  # закрываем соединение с базой данных

  conn.close()

# захватываем исключение и печатаем сообщение об ошибке

except Error as err:

  print('Error message: ' + err.msg)

Обратите внимание, что теперь код импортирует метод Error наряду с методом connect. Затем код включает блок try, который содержит код, связанный с подключением, и оператор print. Это тот же самый код, который вы видел в предыдущем примере.

После блока try код содержит оператор except, который использует метод Error для захвата исключения MySQL создает объект ошибки с именем err. Затем следует оператор print, который возвращает содержимое свойства msg, которое определено для объекта ошибки.

При выполнении кода Python будет сначала пытаться выполнить код в блоке try. Если ошибки не возникнет, код продолжит выполнение пока не закончится (когда закроется соединение). Если возникнет ошибка MySQL, выполнение кода прекратится, и Python вернет сообщение об ошибке. Например, если базы не существует, вы получите сообщение, говорящее, что указанная базе данных неизвестна.

Заметим, что рассмотренная здесь обработка ошибок специфична для MySQL Connector. Если ошибка возникает по другой причине, скажем, из-за нарушения синтаксиса Python, ошибка не будет возвращаться оператором print. Python будет обрабатывать эту ошибку так же, как он обычно обрабатывает ошибки других типов. Если вы хотите включить обработку ошибок самого кода Python, вы должны специально добавить ее в свое приложение.

Запрос данных в базе данных MySQL

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

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


2. Определяем запрос и сохраняем его в переменной.


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


4. Используем объект курсора для вызова такого метода, как, например, fetchall для захвата результатов запроса и сохранения их в переменной.


5. Используем переменную с результатами для выполнения других операций с данными.


6. Закрываем курсор.

Эти шаги выполняются в дополнение к установлению основного соединения. Фактически вам необходим объект соединения для создания курсора, что делает возможным выполнять запросы к соответствующей базе данных. Следующий код Python демонстрирует пример применения этих шагов для получения данных из таблицы airplanes в базе данных travel:

# импорт методов connect и Error

from mysql.connector import connect, Error

# пытаемся выполнить блок кода

try:

  # определяем объект подключения

  conn = connect(option_files =

    '/users/mac3/documents/config/connectors.cnf')

  # открываем курсор, определяем и выполняем запрос, извлекаем результаты

  cursor = conn.cursor()

  query = 'SELECT plane_id, plane, max_weight FROM airplanes'

  cursor.execute(query)

  result = cursor.fetchall()

  # печатаем результаты в каждой строке

  for r in result:

    print(r)

  # закрываем курсор и соединение с базой данных

  cursor.close()

  conn.close()

# перехватываем исключение и печатаем сообщение об ошибке

except Error as err:

  print('Error message: ' + err.msg)

После описания соединения я использовал переменную conn для вызова метода cursor и создания объекта курсора, который я присвоил переменной cursor. Затем я определил оператор SELECT и присвоил его переменной query.

Затем я использовал объект курсора для вызова метода execute с передачей переменной query в качестве параметра. После чего я использовал объект курсора для вызова метода fetchall, чтобы извлечь все строки, возвращаемые оператором SELECT. Вы можете также использовать метод fetchall для получения заданного числа строк, если результат запроса очень велик. Так вы могли бы извлечь N строк, вывести эти строки, извлечь следующие N строк и т.д. Наконец, я присвоил результаты переменной result, которая хранит строки данных как последовательность кортежей.

После занесения результирующего набора в переменную вы можете использовать данные этой переменной повсюду в вашем приложении. Например, я создал простой цикл for, который в итерациях проходит по результирующему набору и присваивает данные переменной r построчно. Затем содержимое переменной r печатается на каждом шаге цикла. Наконец, я использовал объект курсора для вызова метода close, чтобы закрыть курсор. В моей системе Python вернул следующие результаты после выполнения кода:

(1001, 'A340-600', 837756)
(1002, 'A350-800 XWB', 546700)
(1003, 'A350-900', 617295)
(1004, 'A380-800', 1267658)
(1005, 'A380-843F', 1300000)
(1006, 'A.109 Airedale', 2750)
(1007, 'A.61 Terrier', 2400)
(1008, 'B.121 Pup', 1600)
(1009, 'B.206', 7500)
(1010, 'D.5-108 Husky', 2400)
(1011, 'Baron 56 TC Turbo Baron', 5990)
(1012, 'Baron 58 (and current G58)', 5500)
(1013, 'Beechjet 400 (same as MU-300-10 Diamond II)', 15780)
(1014, 'Bonanza 33 (F33A)', 3500)
(1015, 'Bonanza 35 (G35)', 3125)
(1016, '747-8F', 987000)
(1017, '747-SP', 696000)
(1018, '757-300', 270000)
(1019, '767-200', 315000)
(1020, '767-200ER', 395000)

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

# импорт методов connect и Error

from mysql.connector import connect, Error

# пытаемся выполнить блок кода

try:

  # определяем объект подключения

  conn = connect(option_files =

    '/users/mac3/documents/config/connectors.cnf')

   # открываем курсор, определяем и выполняем запрос, извлекаем результаты

  cursor = conn.cursor()

  query = ('SELECT plane_id, plane, max_weight FROM airplanes ' 

    'WHERE max_weight > 100000 ' 

    'ORDER BY max_weight DESC')

  cursor.execute(query)

  result = cursor.fetchall()

  # печатаем результаты в каждой строке

  for r in result:

    print(r)

  # закрываем курсор и соединение с базой данных

  cursor.close()

  conn.close()

 # перехватываем исключение  печатаем сообщение об ошибке

except Error as err:

  print('Error message: ' + err.msg)

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

(1005, ‘A380-843F’, 1300000)
(1004, ‘A380-800’, 1267658)
(1016, ‘747-8F’, 987000)
(1001, ‘A340-600’, 837756)
(1017, ‘747-SP’, 696000)
(1003, ‘A350-900’, 617295)
(1002, ‘A350-800 XWB’, 546700)
(1020, ‘767-200ER’, 395000)
(1019, ‘767-200’, 315000)
(1018, ‘757-300’, 270000)

Как видно, результаты включают только самолеты , у которых значение max_weight превышает 10000. Кроме того, результаты отсортированы по убыванию значений max_weight.

Использование значений переменных в запросе

При использовании MySQL Connector для запроса данных часто оказывается полезным передать значения переменных в оператор SELECT (или любой другой оператор SQL). Вы можете сделать это, включив специальные маркеры %s в качестве заместителя в местах, куда должны быть вставлены значения переменных. Следующий пример показывает оператор SELECT, который содержит два таких маркера:

# import the connect and Error methods

from mysql.connector import connect, Error

# try to run the block of code

try: 

  # define a connection object

  conn = connect(option_files =

    '/users/mac3/documents/config/connectors.cnf')

  # open cursor and define query

  cursor = conn.cursor()

  query = ('SELECT plane_id, plane, max_weight FROM airplanes '

    'WHERE max_weight BETWEEN %s AND %s '

    'ORDER BY max_weight DESC')

   # define input variables

  weight_start = 1000

  weight_end = 10000

   # pass variables into query and fetch results

  cursor.execute(query, (weight_start, weight_end))

  result = cursor.fetchall()

  # print the results in each row

  for r in result:

    print(r)

  # close the cursor and database connection

  cursor.close()

  conn.close()

# catch exception and print error message

except Error as err:

  print('Error message: ' + err.msg)

Я добавил маркеры %s в предложение WHERE как часть оператора BETWEEN. Когда оператор SELECT будет выполняться, на место маркеров будут подставлены значения переменных.

Например, я жестко закодировал значения переменных, присвоив значения переменным weight_start и weight_end. В большинстве случаев вы, вероятно, захотите получить эти значения в диалоге с пользователем или как-то еще, а затем динамически присвоить их переменным при работе программы, но сейчас жесткое кодирование достаточно для демонстрации работы процесса.

После определения переменных вы можете передать их в качестве аргументов метода execute, который выполняет ваш запрос. Переменные должны быть добавлены вторым аргументом, заключенным в скобки, и разделены запятыми. Кроме того, вы должны указать их в том же порядке, в каком они применяются в запросе. Теперь код Python возвращает следующие результаты:

(1009, 'B.206', 7500)
(1011, 'Baron 56 TC Turbo Baron', 5990)
(1012, 'Baron 58 (and current G58)', 5500)
(1014, 'Bonanza 33 (F33A)', 3500)
(1015, 'Bonanza 35 (G35)', 3125)
(1006, 'A.109 Airedale', 2750)
(1007, 'A.61 Terrier', 2400)
(1010, 'D.5-108 Husky', 2400)
(1008, 'B.121 Pup', 1600)

Как ожидалось, результаты содержат только только те самолеты, у которых значение max_weight лежит между 1000 и 10000. Если вы построили свое приложение с пользовательским вводом, пользователь может указать другие значения переменных, которые переданы в приложение динамически.

Начало работы с Python и MySQL

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

Однако имейте в виду, что эта статья ни в коей мере не является исчерпывающим руководством по использованию Python для разработки приложений, управляемых данными. Хотя Python может рассматриваться как легкий язык по сравнению с такими языками как Java и C#, он все же язык общего назначения со многими своими сложностями и нюансами. Прежде чем разрабатывать приложение, которое подключается к MySQL, вам следует иметь твердые знания языка и его возможностей по созданию эффективных и безопасных приложений. Эта статья является лишь начальной точкой доступа к данным MySQL, хотя ее должно быть достаточно для демонстрации того, как легко можно взамодействовать с базой данных MySQL и получать необходимые данные.

Приложение. Подготовка среды MySQL

Примеры Python в этой статье подключаются к базе данных travel на локальном экземпляре MySQL и получают данные из таблицы airplanes, которая имеет внешний ключ к таблице manufacturers. Это та же самая база данных и те же таблицы, которые вы видели в предыдущих статьях этой серии, хотя я ограничил количество данных, которые я вставил в таблицу. Этого достаточно для демонстрации концепции этой статьи. Если вы планируете проделать примеры, начните со следующего скрипта, выполнив его на экземпляре вашей MySQL:

DROP DATABASE IF EXISTS travel;

 CREATE DATABASE travel;

 USE travel;

 

CREATE TABLE manufacturers (

  manufacturer_id INT UNSIGNED NOT NULL,

  manufacturer VARCHAR(50) NOT NULL,

  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  last_update TIMESTAMP NOT NULL 

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (manufacturer_id) );

 

CREATE TABLE airplanes (

  plane_id INT UNSIGNED NOT NULL,

  plane VARCHAR(50) NOT NULL,

  manufacturer_id INT UNSIGNED NOT NULL,

  engine_type VARCHAR(50) NOT NULL,

  engine_count TINYINT NOT NULL,

  max_weight MEDIUMINT UNSIGNED NOT NULL,

  wingspan DECIMAL(5,2) NOT NULL,

  plane_length DECIMAL(5,2) NOT NULL,

  parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,

  icao_code CHAR(4) NOT NULL,

  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  last_update TIMESTAMP NOT NULL 

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (plane_id),

  CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) 

    REFERENCES manufacturers (manufacturer_id) );

Этот скрипт создаст базу данных travel database и добавит в нее таблицы manufacturers и airplanes в том порядке, который не нарушает ограничений внешнего ключа таблицы airplanes. После создания таблиц вы можете выполнить следующие операторы INSERT:

INSERT INTO manufacturers (manufacturer_id, manufacturer)

VALUES (101,'Airbus'), (102,'Beagle Aircraft Limited'), 

  (103,'Beechcraft'), (104,'Boeing');

 

INSERT INTO airplanes 

  (plane_id, plane, manufacturer_id, engine_type, engine_count, 

    wingspan, plane_length, max_weight, icao_code)

VALUES

  (1001,'A340-600',101,'Jet',4,208.17,247.24,837756,'A346'),

  (1002,'A350-800 XWB',101,'Jet',2,212.42,198.58,546700,'A358'),

  (1003,'A350-900',101,'Jet',2,212.42,219.16,617295,'A359'),

  (1004,'A380-800',101,'Jet',4,261.65,238.62,1267658,'A388'),

  (1005,'A380-843F',101,'Jet',4,261.65,238.62,1300000,'A38F'),

  (1006,'A.109 Airedale',102,'Piston',1,36.33,26.33,2750,'AIRD'),

  (1007,'A.61 Terrier',102,'Piston',1,36,23.25,2400,'AUS6'),

  (1008,'B.121 Pup',102,'Piston',1,31,23.17,1600,'PUP'),

  (1009,'B.206',102,'Piston',2,55,33.67,7500,'BASS'),

  (1010,'D.5-108 Husky',102,'Piston',1,36,23.17,2400,'D5'),

  (1011,'Baron 56 TC Turbo Baron',103,'Piston',2,37.83,28,5990,'BE56'),

  (1012,'Baron 58 (and current G58)',103,'Piston',2,37.83,29.83,5500,'BE58'),

  (1013,'Beechjet 400 (same as MU-300-10 Diamond II)',103,'Jet',2,43.5,48.42,15780,'BE40'),

  (1014,'Bonanza 33 (F33A)',103,'Piston',1,33.5,26.67,3500,'BE33'),

  (1015,'Bonanza 35 (G35)',103,'Piston',1,32.83,25.17,3125,'BE35'),

  (1016,'747-8F',104,'Jet',4,224.42,250.17,987000,'B748'),

  (1017,'747-SP',104,'Jet',4,195.67,184.75,696000,'B74S'),

  (1018,'757-300',104,'Jet',2,124.83,178.58,270000,'B753'),

  (1019,'767-200',104,'Jet',2,156.08,159.17,315000,'B762'),

  (1020,'767-200ER',104,'Jet',2,156.08,159.17,395000,'B762');

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