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

Вступление

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

API БД Python

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

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

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

1

<!-- -->

 $ pip install mysql-connector-python

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

 $ pip install mysql-connector-python==<insert_version_number_here>

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

 $ pip install mysqlclient

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

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

Предпосылки

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

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

1

<!-- -->

 CREATE DATABASE pythondb; 
 USE pythondb;

Создайте нового пользователя pythonuser с паролем pythonpwd123 и предоставьте доступ к pythondb

1

<!-- -->

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

Проверка вашего подключения к pythondb

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

 #!/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 ).

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

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

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

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

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

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

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

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

 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 созданный для схемы pythondb . Это можно просмотреть с помощью 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()

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

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

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

Для простоты мы будем использовать SQL-запрос «выбрать все» и использовать цикл 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")

Выход:

 1 record(s) affected

Удаление записи

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

 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 = "DELETE FROM movie WHERE id = 1" 
 
 cursor.execute(updatequery) 
 
 dbconnect.commit() 
 
 print(cursor.rowcount, "record(s) deleted")

После выполнения вышеуказанного сценария вы должны увидеть следующий вывод, если все пойдет хорошо.