Python работа с MySQL

На просторах интернета на данный момент не так много адекватных статей по поводу работы с СУБД MySQL через Python. Эта статья представляет собой простое руководство для быстрого старта.

Я недавно отошёл от использования второй версии языка, и все примеры пишутся на Python 3.x.

И так без лишних слов. Подразумевается что у вас уже установлен MySQL и Python. Для работы с MySQL из Python требуется модуль MySQLdb. Python реализует стандарт для работы с реляционными базами данных Python Database API Specification V2.0 или по другому PEP 249. Модули для доступа к реляционным базам данных должны следовать этому стандарту, но могут вводить свои возможности.

Скачать модуль MySQLdb для необходимой версии Python можно тут:
http://www.lfd.uci.edu/~gohlke/pythonlibs/

Ознакомится с документацией к MySQLdb можно тут:
http://mysql-python.sourceforge.net/MySQLdb-1.2.2/

Ознакомится с Python Database API 2.0 можно тут:
http://www.rldp.ru/mysql/mysqldev/dbapi20.htm

Теперь создадим тестовую базу данных и таблицу. Для управления базами я использую phpMyAdmin.

CREATE DATABASE  `test` ;
USE `test`;
CREATE TABLE `city` ( `id_city` int(10) unsigned  NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY  (`id_city`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `city` (`id_city`, `name`) VALUES (1, 'Санкт-Петербург'), (2, 'Москва'), (3, 'Великий Новгород'), (4, 'Самара'), (5, 'Казань');

Проверим корректность установки модуля MySQLdb. В стандартной IDLE создаем новое окно (File->New Window) или (Ctrl+N) и прописываем следующее:

# -*- coding: utf-8 -*-
import MySQLdb
try: con = MySQLdb.connect(host="localhost", user="root", passwd="5213", db="test") cur = con.cursor() cur.execute('SET NAMES `utf8`') cur.execute('SELECT `name` FROM `city` ORDER BY `name` DESC') result = cur.fetchall() for row in result: print(row[0]) except MySQLdb.Error: print(db.error())

Жмём F5 или RUN->Rum Module и должны увидеть список городов. Если это произошло, значит всё сделано правильно.

Функций, свойства и методы модуля MySQLdb

Модуль MySQLdb реализует полный функционал стандарта Python Database API Specification V2.0 и некоторый свой функционал. Так же в модуле присутствуют методы, которые отмечены как нестандартные и могут работать не верно. В этом разделе будет кратко описаны основные возможности.

Соединение с базой данных

Для установки соединения с базой данных существует функция MySQLdb.connect.

MySQLdb.connect(host="localhost", user="root", passwd="5213", db="test")

Так же можно указать port, unix_socket, connect_timeout, compress, charset, sql_mode и некоторые другие (см. документацию)

В случае успеха, функция возвращает объект класса Connection. Экземпляр con класса Connection обладает следующими методами.

• con.close() – закрывает соединение с сервером базы данных.
• con.commit() – подтверждает все незавершенные транзакции
• con.rollback() – откатывает все изменение в базе данных до момента когда были запущенные незавершённые транзакции.
• con.cursor() – создаёт новый курсор. Это экземпляр класса Cursor. Курсор – это объект, который используется для SQL запросов и получения результата.
• con.autocommit() – используется для автоматического завершения транзакции.
• con.affected_rows() — возвратите число строк, на которые повлиял последний запрос. Лучше использовать cur.rowcount(). (не стандартный)
• con.errno() – возвращает номер ошибки если она произошла.
• con.error() – возвращает описание ошибки если она произошла.
• con.select_db() – используется для выбора базы данных. (не стандартный)
• con.insert_id() – Возвращает ID, сгенерированный для столбца AUTO_INCREMENT предыдущим запросом
• con.escape() – используется для экранирования символов (см. документацию)
• con.escape_string() – используется для экранирования символов (см. документацию)
• con.string_literal() – используется для экранирования символов (см. документацию)

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

Курсор

Что бы выполнить какие-то операции в базе данных, сначала требуется создать объект соединения con, а затем вызвать метод con.cursor() создав курсор. Методы и свойства экземпляра cur класса Cursor используются для выполнения запросов к базе данных.

• cur.close() – закрывает курсор предотвращая выполнения каких либо запросов с его помощью.
• cur.callproc(procname [, param]) – вызывает хранимую процедуру.
• cur.execute(query [, param]) – выполняет запрос к базе данных (query).
• cur.executemany(query [, paramsequence]) – многократное выполнение запросов к базе данных (query).
• cur.fetchone() – возвращает следующую запись из набора данных полученого вызовом cur.execute*()
• cur.fetchmany([size]) – возвращает последовательность записей из набора данных.
• cur.fetchall() – возвращает последовательность всех записей оставшихся в полученном наборе данных.
• cur.nextset() – пропускает все оставшиеся записи в текущем наборе данных и переходит к следующему набору.
• cur.setinputsize(sizes) – сообщает курсору о параметрах, которые будут переданы в последующих вызовах методов cur.execute*()
• cur.setoutputsize(sizes [, column]) – устанавливает размер буфера для определённого столбца в возвращаемом наборе данных.
• cur.description — возвращает последовательность кортежей с информацией о каждом столбце в текущем наборе данных. Кортеж имеет вид (name, type_code, display_size, internal_size, precision, scale, null_ok)
• cur.arraysize – целое число которое используется методом cur.fetchmany как значение по умолчанию.
• cur.rowcount — возвратите число строк, на которые повлиял последний запрос.

Способы выборки из таблиц

Объект cursor модуля MySQLdb поддерживает итерации. Это значит, что можно обойти все записи в наборе данных полученных из cur.execute без использования метода cur.fetchall, а просто используя инструкцию for row in cur:

# -*- coding: utf-8 -*-
import MySQLdb as db
try: con = db.connect(host="localhost", user="root", passwd="5213", db="test") cur = con.cursor() cur.execute('SET NAMES `utf8`') cur.execute('SELECT `name` FROM `city` ORDER BY `name` DESC') for row in cur: print(row[0]) except db.Error as e: print(con.error())

Так же за место цикла for можно использовать цикл while

while True: row = cur.fetchone() if not row: break print(row[0])

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

Функцию-генератор по «именовыванию» последовательности записей.

def gen_dict(cur): names = [x[0].lower() for x in cur.description] for row in cur: yield dict(zip(names, row))

Использовать её можно следующим образом:

for row in gen_dict(cur): print(row['name'])

Формирование запросов

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

Экранирование символов. В MySQLdb есть 4 метода экранирования:

1. con.escape
2. con.escape_string
3. con.string_literal
4. И автоматический механизм подстановки значений:cur.execute(«SELECT *FROM `city` WHERE id_city=’?'», (city,))

Тут символ подстановки (?) замещается значением из кортежа (city,) автоматически экранируя спец. символы.

Но не существует единого правила оформления подстановки значений в запрос. Поэтому есть переменная paramstyle которая может определить формат подстановки.

У меня MySQLdb.paramstyle равен ‘format’.

Варианты формата:

1. qmark – параметры обозначаются знаков вопроса (?)
2. numeric — параметры обозначаются числами
3. named — параметры обозначаются именами
4. format — параметры обозначаются в стиле printf.
5. pyformat — параметры обозначаются в стиле расширенного набора кодов формата Python — %(name)s

Но есть небольшая проблема.

cur.execute("""SELECT `name` FROM `city` WHERE `id_city`=%s""", (1,)) cur.execute("""SELECT `name` FROM `city` WHERE `id_ city`= (%(sid)s) """, {'sid': 1})

В Python 3.x такой подход работать не будет. Если заглянуть под капот модуля MySQLdb и найти функцию execute (\Lib\site-packages\MySQLdb\cursors.py), то можно видеть что в Python 3.x подстановка параметров осуществляется используя функцию format:

if isinstance(args, dict): query = query.format( **db.literal(args) ) elif isinstance(args, tuple) or isinstance(args, list): query = query.format( *db.literal(args) ) else: query = query.format( db.literal(args) )

А в Python 2.x используется простое формирование строки:

query = query % db.literal(args)

По всей видимости, разработчики забыли изменить документацию к функции. И поэтому формирование в стиле Си функции printf работает не будет.

Следовательно, нужно использовать следующий подход:

cur.execute("""SELECT `name` FROM `city` WHERE `id_city`={0}""", (1,)) cur.execute("""SELECT `name` FROM `city` WHERE `id_city`={0:s}""", (1,)) cur.execute("""SELECT `name` FROM `city` WHERE `id_city`={0}""", 1) cur.execute("""SELECT `name` FROM `city` WHERE `id_city`={city}""", {city:1})

Говоря об экранирование, используя функции escape_string и string_literal, они не работают с кодировками, что является серьёзной проблемой. Раньше была функция escape которая принимала один параметр, но теперь ей требуется 2 параметра, причём второй это словарь с ссылками на функции каждого из типов, и честно говоря для меня он остался загадкой. Поэтому лучше использовать вариант с автоматическим экранированием. Автоматическое экранирование спец. символов реализовано используя функцию escape.

Если вы разрабатываете Web приложение, то стоит позаботиться о преобразование символов & < > « в соответствующие сущности языка разметки, такие как & < > «. Для этого можно использовать функцию escape() модуля cgi:

import cgi srt = 'жирный текст' cgi.escape(srt, True) # жирный текст

Или написать что-то своё, с более расширенным функционалом.

Пример использования:

# -*- coding: utf-8 -*-
import MySQLdb as db import cgi
try: con = db.connect(host="localhost", user="root", passwd="5213", db="test") cur = con.cursor() cur.execute('SET NAMES `utf8`')
data = {}; data['name'] = cgi.escape('Крыжополь', True);
cur.execute("""INSERT INTO `city` (`name`) VALUES ({name})""", data)
print("Строка всталвена") except db.Error as e: print(con.error())

Обратите внимание на VALUES ({name}), метку {name} не нужно загонять в кавычки. Автоматическая подстановка сделает это за Вас, иначе будет выведено сообщение об ошибке.

Вот наверно и всё, что я хотел рассказать. Думаю этого достаточно для нормального старта с базой данных MySQL. Удачных экспериментов!

#python