[315]
16 Июл 2011, 20:03

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

Комментарии (11)
zzoll
16 Июл 2011, 20:54
#

Познавательно. Действительно, лучше я пока не видел. Спасибо за статью.

douch
16 Июл 2011, 22:24
#

Работу с датой бы ещё. Но всёровно супер!

provod
17 Июл 2011, 00:02
#

Наконец-то человеческий текст, а не груда не отформатированного бреда сумасшедшего.

mordoc
17 Июл 2011, 18:43
#

хм, а я и не знал про изменения в модуле MySQLdb.

sentryperm
10 Сен 2012, 14:07
#

Теперь можно использовать DictCursor, чтобы получать именованные row

cur = con.cursor(mdb.cursors.DictCursor)
rows = cur.fetchall()
for row in rows:
print row["Id"]
sentryperm
10 Сен 2012, 14:11
#

Вернее MySQLdb.cursors.DictCursor

grom
10 Сен 2012, 22:28
#

Действительно, не знал. Посмотрел ещё оказывается так можно:

import MySQLdb as db

con = db.connect(host="localhost", user="root", passwd="5213", db="test", cursorclass=db.cursors.DictCursor)

cur = con.cursor()
cur.execute("SELECT *FROM city")

firstRow = cur.fetchone()  #{'id_city':'1', 'name':'Санкт-Петербург'}
basist107
09 Фев 2014, 16:10
#

Вы пишите, что используете питон 3.х и даёте ссылку

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

но там я нашел только MySQL-python-1.2.5.win32-py2.7.exe, который при установке говорит что питон 2.7 не найден, подскажите, где скачать для питон 3.3?

Спасибо

grom
18 Фев 2014, 12:40
#

Два с половиной года назад там была поддержка третьей версии. Сейчас стоит сноска на неофициальный порт для Windows, для Python 2.7-3.*.

github.com/farcepest/MySQLdb1

basist107
22 Мар 2014, 10:47
#

спасибо 🙂

blazer-05
31 Дек 2014, 12:38
#

Здравствуйте.
Помогите мне с проблемой. Под виндовс 7 32bit установил python 2.7.8, django 1.7.1, mysql. Все вроде бы работает сайт открывается и с базы данных вытягивает данные, но в консоле shell когда выполняю такую команду

from django.db import connection
cursor = connection.cursor()

выходит ошибка

raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e)
ImproperlyConfigured: Error loading MySQLdb module: this is MySQLdb version (1, 2, 3, 'final', 0), but _mysql is version (1, 3, 4, 'final', 1)

Установлен модуль для мой операционки MySQL-python-1.2.5.win32-py2.7, ставил также дополнительно mysqlclient 1.3.4, pymysql но ничего не происходит ошибка так и есть!

Добавить комментарий

Войдите, чтобы написать о чем-нибудь...
Вход Регистрация
Web.onRails
Здесь вы можете спросить или написать обо всём, что касается Веб-разработки.
написать о чем-нибудь...
Метки:
Лучшее
[52]
16 Окт 2011, 15:38
Вывести все элементы POST
[просмотров 1206]
[49]
17 Сен 2011, 15:13
FileZilla перетаскивание файлов
[просмотров 1125]
[74]
31 мая 2011, 11:48
Python проверка существования переменной
[просмотров 1081]
[58]
29 мая 2012, 12:08
Узнать версию PHP из командной строки
[просмотров 1080]
[315]
16 Июл 2011, 20:03
Python работа с MySQL
[просмотров 1073]
[2]
26 Июн 2018, 23:10
Как в React сделать поле input file с кнопкой?
[просмотров 991]
[119]
21 Июл 2011, 14:04
Python Imaging Library (PIL)
[просмотров 984]