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

Вступление

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

Python DB API

В основе поддержки Python для программирования баз данных лежит Python DB API ( PEP – 249 ), который не зависит от какого-либо конкретного движка базы данных. В зависимости от базы данных, которую мы используем на уровне персистентности, соответствующая реализация Python DB API должна быть импортирована и использована в нашей программе. В этом уроке мы продемонстрируем, как использовать Python для подключения к базе данных MySQL и выполнения транзакций с ней. Для этого мы будем использовать пакет MySQLdb Python.

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

Один из способов-загрузить соответствующий установщик для ОС и битной версии непосредственно с официального сайта MySQL .
Другой способ-использовать pip для его установки.

$ pip install mysql-connector-python

Если на локальном компьютере установлена конкретная версия MySQL, то вам может понадобиться конкретная версия MySQL connector, чтобы не возникало проблем с совместимостью, которые мы можем получить с помощью следующей команды:

$ pip install mysql-connector-python==

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

$ pip install mysqlclient

Подключение к базе данных

Как только мы установим соединитель на место, оператор import MySQLdb не должен выдавать никаких ошибок при выполнении файла Python.

Предпосылки

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

Убедитесь, что ваш сервер MySQL работает. Это можно проверить с помощью MySQL WorkBench -> Статус сервера.
Откройте MySQL WorkBench или MySQL CLI. Создайте новую базу данных. Назовем его python db .

CREATE DATABASE pythondb;
USE pythondb;

Создайте нового пользователя python user с паролем python pwd 123 и предоставьте доступ к python db

CREATE USER 'pythonuser'@'localhost' IDENTIFIED BY 'pythonpwd123'
GRANT ALL PRIVILEGES ON pythondb.* To 'pythonuser'@'localhost'
FLUSH PRIVILEGES

Проверка подключения к python db

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

#!/usr/bin/python

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

cursor = dbconnect.cursor()
cursor.execute("SELECT VERSION()")

data = cursor.fetchone()
if data:
  print('Version retrieved: ', data)
else:
  print('Version not retrieved.')

dbconnect.close()

Выход

Version retrieved: 5.7.19

Номер версии, показанный выше, является просто фиктивным номером. Он должен соответствовать установленной версии сервера MySQL.

Давайте подробнее рассмотрим пример программы выше, чтобы узнать, как она работает. Во-первых, import MySQLdb используется для импорта необходимого модуля python.

Метод MySQLdb.connect() принимает имя хоста, имя пользователя, пароль и имя схемы базы данных для создания соединения с базой данных. При успешном соединении с базой данных он вернет объект соединения (который здесь называется dbconnect ).

Используя объект connection, мы можем выполнять запросы, фиксировать транзакции и откатывать транзакции перед закрытием соединения.

Как только мы получим объект соединения, нам нужно получить объект MySQL Cursor для выполнения запросов с помощью метода execute . Результирующий набор транзакции может быть получен с помощью методов fetchall , fetchone или fetchmany , которые будут рассмотрены далее в этом руководстве.

Существует три важных метода, связанных с транзакциями базы данных, помимо метода execute. Сейчас мы вкратце познакомимся с этими методами.

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

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

Наконец, метод dbconnect.close() используется для закрытия соединения с базой данных. Для выполнения дальнейших транзакций нам необходимо создать новое соединение.

Создайте новую таблицу

Как только соединение с python db будет успешно установлено, мы готовы перейти к следующему шагу. Давайте создадим в нем новую таблицу:

import MySQLdb

dbconnect = MySQLdb.connect("localhost","pythonuser","pythonpwd123","pythondb" )

cursor = dbconnect.cursor()
cursor.execute("DROP TABLE IF EXISTS MOVIE")

query = "CREATE TABLE MOVIE(  \
          id int(11) NOT NULL,\
          name varchar(20),\
          year int(11),\
          director varchar(20),\
          genre varchar(20),\
          PRIMARY KEY (id))"

cursor.execute(query)

dbconnect.close()

После выполнения описанного выше скрипта вы сможете увидеть новую таблицу movie , созданную для схемы python db . Это можно просмотреть с помощью MySQL WorkBench.

Выполнение операций CRUD

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

Создание новой записи

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

#!/usr/bin/python

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

cursor = dbconnect.cursor()

query = 'insert into movie(id, name, year, director, genre)  \
       values (1, "Bruce Almighty", 2003, "Tom Shaydac", "Comedy")'
try:
   cursor.execute(query)
   dbconnect.commit()
except:
   dbconnect.rollback()
finally:
   dbconnect.close()

Чтение строк из таблицы

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

указатель.fetchall() – можно использовать для получения всех строк
указатель.fetchmany() – можно использовать для получения выбранного количества строк
указатель.fetchone() – можно использовать для получения только первой строки из результирующего набора

Для простоты мы будем использовать SQL-запрос “select all” и использовать цикл for над результирующим набором метода fetchall для печати отдельных записей.

#!/usr/bin/python

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

cursor = dbconnect.cursor()

query = "SELECT * FROM movie"
try:
   cursor.execute(query)
   resultList = cursor.fetchall()
   for row in resultList:
      print ("Movie ID =", row[0])
      print ("Name =", row[1])
      print ("Year =", row[2])
      print ("Director = ", row[3])
      print ('Genre = ', row[4])
except:
   print ("Encountered error while retrieving data from database")
finally:
   dbconnect.close()

Выход:

Movie ID = 1
Name = Bruce Almighty
Year = 2003
Director = Tom Shaydac
Genre = Comedy

Обновление строки

Давайте теперь обновим жанр “Брюса Всемогущего” от комедии до сатиры:

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

# The cursor object obtained below allows SQL queries to be executed in the database session.
cursor = dbconnect.cursor()

updatequery = "update movie set genre = 'Satire' where id = 1"

cursor.execute(updatequery)

dbconnect.commit()

print(cursor.rowcount, "record(s) affected")