На просторах интернета на данный момент не так много адекватных статей по поводу работы с СУБД 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 метода экранирования:
- con.escape
- con.escape_string
- con.string_literal
- И автоматический механизм подстановки значений:cur.execute(«SELECT *FROM `city` WHERE id_city=’?'», (city,))
Тут символ подстановки (?) замещается значением из кортежа (city,) автоматически экранируя спец. символы.
Но не существует единого правила оформления подстановки значений в запрос. Поэтому есть переменная paramstyle которая может определить формат подстановки.
У меня MySQLdb.paramstyle равен ‘format’.
Варианты формата:
- qmark – параметры обозначаются знаков вопроса (?)
- numeric — параметры обозначаются числами
- named — параметры обозначаются именами
- format — параметры обозначаются в стиле printf.
- 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. Удачных экспериментов!
но там я нашел только MySQL-python-1.2.5.win32-py2.7.exe, который при установке говорит что питон 2.7 не найден, подскажите, где скачать для питон 3.3?
Спасибо
github.com/farcepest/MySQLdb1
Помогите мне с проблемой. Под виндовс 7 32bit установил python 2.7.8, django 1.7.1, mysql. Все вроде бы работает сайт открывается и с базы данных вытягивает данные, но в консоле shell когда выполняю такую команду
выходит ошибка
Установлен модуль для мой операционки MySQL-python-1.2.5.win32-py2.7, ставил также дополнительно mysqlclient 1.3.4, pymysql но ничего не происходит ошибка так и есть!