Учебники

Python 3 — доступ к базе данных MySQL

Май 2, 2019

Стандарт Python для интерфейсов базы данных — это DB-API Python. Большинство интерфейсов баз данных Python соответствуют этому стандарту.

Вы можете выбрать правильную базу данных для вашего приложения. Python Database API поддерживает широкий спектр серверов баз данных, таких как —

  • овод
  • Msql
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server 2000
  • Informix
  • Interbase
  • оракул
  • Sybase
  • SQLite

Вот список доступных интерфейсов базы данных Python — интерфейсы базы данных Python и API . Вы должны загрузить отдельный модуль API БД для каждой базы данных, к которой вам нужен доступ. Например, если вам нужен доступ к базе данных Oracle, а также к базе данных MySQL, вы должны загрузить модули базы данных Oracle и MySQL.

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

  • Импорт модуля API.
  • Получение соединения с базой данных.
  • Выдача операторов SQL и хранимых процедур.
  • Закрытие соединения

Python имеет встроенную поддержку SQLite. В этом разделе мы изучим все концепции, используя MySQL. Модуль MySQLdb, популярный интерфейс с MySQL, не совместим с Python 3. Вместо этого мы будем использовать модуль PyMySQL .

Что такое PyMySQL?

PyMySQL — это интерфейс для подключения к серверу баз данных MySQL из Python. Он реализует API базы данных Python v2.0 и содержит чистую клиентскую библиотеку MySQL Python. Целью PyMySQL является быстрая замена MySQLdb.

Как установить PyMySQL?

Прежде чем продолжить, убедитесь, что на вашем компьютере установлен PyMySQL. Просто введите следующее в вашем скрипте Python и выполните его — 

#!/usr/bin/python3

import pymysql

Если он дает следующий результат, то это означает, что модуль MySQLdb не установлен — 

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      Import pymysql
ImportError: No module named pymysql

Последний стабильный выпуск доступен на PyPI и может быть установлен с помощью pip — 

pip install pymysql

В качестве альтернативы (например, если pip недоступен), tarball можно загрузить с GitHub и установить вместе с Setuptools следующим образом: 

$ # X.X is the desired pymysql version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

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

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

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

  • Вы создали базу данных TESTDB.

  • Вы создали таблицу EMPLOYEE в TESTDB.

  • В этой таблице есть поля FIRST_NAME, LAST_NAME, AGE, SEX и INCOME.

  • Идентификатор пользователя «testuser» и пароль «test123» установлены для доступа к TESTDB.

  • Модуль Python PyMySQL правильно установлен на вашем компьютере.

  • Вы прошли учебник по MySQL, чтобы понять основы MySQL.

Вы создали базу данных TESTDB.

Вы создали таблицу EMPLOYEE в TESTDB.

В этой таблице есть поля FIRST_NAME, LAST_NAME, AGE, SEX и INCOME.

Идентификатор пользователя «testuser» и пароль «test123» установлены для доступа к TESTDB.

Модуль Python PyMySQL правильно установлен на вашем компьютере.

Вы прошли учебник по MySQL, чтобы понять основы MySQL.

пример

Ниже приведен пример соединения с базой данных MySQL «TESTDB» — 

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print ("Database version : %s " % data)

# disconnect from server
db.close()

При запуске этого скрипта он выдает следующий результат. 

Database version : 5.5.20-log

Если соединение с источником данных установлено, то объект соединения возвращается и сохраняется в БД для дальнейшего использования, в противном случае для БД установлено значение Нет. Затем объект db используется для создания объекта курсора , который, в свою очередь, используется для выполнения запросов SQL. Наконец, перед выходом он гарантирует, что соединение с базой данных закрыто и ресурсы освобождены.

Создание таблицы базы данных

Как только соединение с базой данных установлено, мы готовы создавать таблицы или записи в таблицах базы данных, используя метод execute созданного курсора.

пример

Давайте создадим таблицу базы данных EMPLOYEE — 

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()

ВСТАВИТЬ Операция

Операция INSERT требуется, когда вы хотите создать свои записи в таблице базы данных.

пример

В следующем примере выполняется инструкция SQL INSERT для создания записи в таблице EMPLOYEE — 

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

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

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
   LAST_NAME, AGE, SEX, INCOME) \
   VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
   ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

пример

Следующий фрагмент кода является еще одной формой выполнения, где вы можете передавать параметры напрямую — 

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))
..................................

ЧИТАЙТЕ Операцию

Чтение Операция с любой базой данных означает получение некоторой полезной информации из базы данных.

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

  • fetchone () — извлекает следующую строку из набора результатов запроса. Результирующий набор — это объект, который возвращается, когда объект-курсор используется для запроса таблицы.

  • fetchall () — извлекает все строки в наборе результатов. Если некоторые строки уже были извлечены из результирующего набора, то он извлекает оставшиеся строки из результирующего набора.

  • rowcount — это атрибут только для чтения, который возвращает количество строк, на которые воздействовал метод execute ().

fetchone () — извлекает следующую строку из набора результатов запроса. Результирующий набор — это объект, который возвращается, когда объект-курсор используется для запроса таблицы.

fetchall () — извлекает все строки в наборе результатов. Если некоторые строки уже были извлечены из результирующего набора, то он извлекает оставшиеся строки из результирующего набора.

rowcount — это атрибут только для чтения, который возвращает количество строк, на которые воздействовал метод execute ().

пример

Следующая процедура запрашивает все записи из таблицы EMPLOYEE с зарплатой более 1000 — 

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE \
      WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
         (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

# disconnect from server
db.close()

Выход

Это даст следующий результат — 

fname = Mac, lname = Mohan, age = 20, sex = M, income = 2000

Операция обновления

ОБНОВЛЕНИЕ Операция в любой базе данных означает обновление одной или нескольких записей, которые уже доступны в базе данных.

Следующая процедура обновляет все записи, имеющие SEX как «M» . Здесь мы увеличиваем ВОЗРАСТ всех мужчин на один год.

пример 

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

УДАЛЕНИЕ Операция

Операция DELETE требуется, когда вы хотите удалить некоторые записи из вашей базы данных. Ниже описана процедура удаления всех записей из РАБОТНИКА, где ВОЗРАСТ больше 20 —

пример 

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Выполнение транзакций

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

  • Атомарность — либо транзакция завершена, либо ничего не происходит вообще.

  • Согласованность — транзакция должна начинаться в согласованном состоянии и оставлять систему в согласованном состоянии.

  • Изоляция — промежуточные результаты транзакции не видны за пределами текущей транзакции.

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

Атомарность — либо транзакция завершена, либо ничего не происходит вообще.

Согласованность — транзакция должна начинаться в согласованном состоянии и оставлять систему в согласованном состоянии.

Изоляция — промежуточные результаты транзакции не видны за пределами текущей транзакции.

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

Python DB API 2.0 предоставляет два метода для фиксации или отката транзакции.

пример

Вы уже знаете, как осуществлять транзакции. Вот похожий пример — 

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

Операция COMMIT

Фиксация — это операция, которая дает зеленый сигнал в базу данных для завершения изменений, и после этой операции никакие изменения не могут быть возвращены обратно.

Вот простой пример вызова метода commit . 

db.commit()

ROLLBACK Операция

Если вас не устраивает одно или несколько изменений, и вы хотите полностью отменить эти изменения, используйте метод rollback () .

Вот простой пример вызова метода rollback () . 

db.rollback()

Отключение базы данных

Чтобы отключить соединение с базой данных, используйте метод close (). 

db.close()

Если соединение с базой данных закрывается пользователем методом close (), все незавершенные транзакции откатываются БД. Однако вместо того, чтобы зависеть от какой-либо детали реализации нижнего уровня БД, вашему приложению было бы лучше явно не вызывать commit или rollback.

Обработка ошибок

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

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

Sr.No. Исключение и описание
1

Предупреждение

Используется для нефатальных проблем. Должен подкласс StandardError.

2

ошибка

Базовый класс для ошибок. Должен подкласс StandardError.

3

InterfaceError

Используется для ошибок в модуле базы данных, а не в самой базе данных. Ошибка подкласса.

4

Ошибка базы данных

Используется для ошибок в базе данных. Ошибка подкласса.

5

DataError

Подкласс DatabaseError, который ссылается на ошибки в данных.

6

OperationalError

Подкласс DatabaseError, который ссылается на ошибки, такие как потеря соединения с базой данных. Эти ошибки обычно находятся вне контроля скрипта Python.

7

IntegrityError

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

8

Внутренняя ошибка

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

9

ProgrammingError

Подкласс DatabaseError, который ссылается на ошибки, такие как неверное имя таблицы и другие вещи, которые можно смело обвинять в вас.

10

NotSupportedError

Подкласс DatabaseError, который относится к попытке вызвать неподдерживаемую функциональность.

Предупреждение

Используется для нефатальных проблем. Должен подкласс StandardError.

ошибка

Базовый класс для ошибок. Должен подкласс StandardError.

InterfaceError

Используется для ошибок в модуле базы данных, а не в самой базе данных. Ошибка подкласса.

Ошибка базы данных

Используется для ошибок в базе данных. Ошибка подкласса.

DataError

Подкласс DatabaseError, который ссылается на ошибки в данных.

OperationalError

Подкласс DatabaseError, который ссылается на ошибки, такие как потеря соединения с базой данных. Эти ошибки обычно находятся вне контроля скрипта Python.

IntegrityError

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

Внутренняя ошибка

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

ProgrammingError

Подкласс DatabaseError, который ссылается на ошибки, такие как неверное имя таблицы и другие вещи, которые можно смело обвинять в вас.

NotSupportedError

Подкласс DatabaseError, который относится к попытке вызвать неподдерживаемую функциональность.

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

101
Share: