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

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

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

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase

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

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

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

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

Что такое MySQLdb?

MySQLdb - это интерфейс для подключения к серверу базы данных MySQL из Python. Он реализует Python Database API v2.0 и построен поверх MySQL C API.

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

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

#!/usr/bin/python

import MySQLdb

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

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

Чтобы установить модуль MySQLdb, используйте следующую команду -

For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python

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

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

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

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

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

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

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

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

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

пример

Ниже приведен пример подключения к базе данных MySQL «TESTDB».

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

При запуске этого сценария на моем компьютере с Linux он дает следующий результат.

Database version : 5.0.45

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

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

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

пример

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

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

ВСТАВИТЬ операцию

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

пример

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

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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/python

import MySQLdb

# Open database connection
db = MySQLdb.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))
..................................

ПРОЧИТАТЬ операцию

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

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

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

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

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

пример

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

#!/usr/bin/python

import MySQLdb

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

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

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 fecth data"

# disconnect from server
db.close()

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

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

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

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

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

пример

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

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

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

• Atomicity - Либо транзакция завершается, либо вообще ничего не происходит.

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

• Isolation - Промежуточные результаты транзакции не видны вне текущей транзакции.

• Durability - После фиксации транзакции последствия сохраняются даже после сбоя системы.

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 Operation

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

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

db.commit()

ROLLBACK Операция

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

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

db.rollback()

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

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

db.close()

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

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

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

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

Sr.No.	Исключение и описание
1	Warning Используется для некритических проблем. Должен быть подклассом StandardError.
2	Error Базовый класс для ошибок. Должен быть подклассом StandardError.
3	InterfaceError Используется для ошибок в модуле базы данных, а не в самой базе данных. Должен быть подкласс Error.
4	DatabaseError Используется для ошибок в базе данных. Должен быть подкласс Error.
5	DataError Подкласс DatabaseError, который указывает на ошибки в данных.
6	OperationalError Подкласс DatabaseError, который относится к таким ошибкам, как потеря соединения с базой данных. Эти ошибки обычно находятся вне контроля сценария Python.
7	IntegrityError Подкласс DatabaseError для ситуаций, которые могут повредить реляционную целостность, таких как ограничения уникальности или внешние ключи.
8	InternalError Подкласс DatabaseError, который относится к внутренним ошибкам модуля базы данных, таким как курсор больше не активен.
9	ProgrammingError Подкласс DatabaseError, который ссылается на такие ошибки, как неправильное имя таблицы и другие вещи, в которых можно спокойно винить вас.
10	NotSupportedError Подкласс DatabaseError, который относится к попытке вызвать неподдерживаемую функцию.

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