Python-рецепты. Дружим python и MySQL.


Поводом к написанию этой статьи послужила необходимость гибкой работы с довольно большим набором однотипных данных. Данные представлены в виде CSV-файла и работать с ним ввиду его размера (50 Мб) и формата (поиск одной единственной нужной записи может занять 1-3 секунды) не очень удобно. Поэтому было принято решение импортировать его в базу данных - MySQL.

Для работы с MySQL необходимо установить драйвер для работы с этой базой данных для питона: скачиваем модуль MySQLdb, распаковываем и устанавливаем. Так как я использую Ubuntu (которую и вам советую), то установка будет выполняется одно-единственной командой:

$ sudo apt-get install python-mysqldb

Любители Windows (или те кто устанавливает из исходников) могут выбрать файл для установки по адресу.

Настало время объяснить принципы работы с базой данных на питоне: подключаемся к базе данных, создаем курсор (объект для работы с базой данных), выполняем требуемые запросы, применяем изменения базы данных, закрываем соединение с БД.

Перейдем к практике. Есть csv-файл, каждая строка которого имеет вид «name;email;adres;telefon» (например «Сергей Иванович;admin@yandex.ru;Москва, ул. Заречная 5, кв. 109″; 33-76-152). Есть mysql база данных bd с таблицей contacts. Приведенный ниже скрипт закинет содержимое файла в базу данных.

#!/usr/bin/python
# -*- coding: utf-8

import MySQLdb
import string

# распаковка строки, в которой поля записаны с разделителем ";"
def unpack_line(line):
    line = string.replace(line, "'", "")
    els = string.split(line, ";")
    # выделяем имя, емейл, адрес и телефон
    fname = els[0]
    fmail = els[1]
    fadres = els[2]
    ftel = els[3]
    return fname, fmail, fadres, ftel

# подключаемся к базе данных (не забываем указать кодировку, а то в базу запишутся иероглифы)
db = MySQLdb.connect(host="localhost", user="root", passwd="пароль", db="contacts", charset='utf8')
# формируем курсор, с помощью которого можно исполнять SQL-запросы
cursor = db.cursor()

# открываем исходный csv-файл
f = open("log", "r")
# представляем его в виде массива строк
lines = f.readlines()

for line in lines:
    # если в строе присутствует емейл (определяем по наличию "@")
    if string.find(line, "@") > -1:
        # извлекаем данные из строки
        fname, fmail, fadres, ftel = unpack_line(line)
        # подставляем эти данные в SQL-запрос
        sql = """INSERT INTO contacts(name, mail, adres, tel)
        VALUES ('%(name)s', '%(mail)s', '%(adres)s', '%(tel)s')
        """%{"name":fname, "mail":fmail, "adres":fadres, "tel":ftel}
        # исполняем SQL-запрос
        cursor.execute(sql)
        # применяем изменения к базе данных
        db.commit()

# закрываем соединение с базой данных
db.close()
# закрываем файл
f.close()

Точно таким же образом можно удалять и обновлять данные в базе: изменяется только SQL-запрос. Немного иначе обстоит дело с выборкой данных, а точнее с обработкой выбранной из базы информацией. После выполнения запроса на выборку данных с помощью функции курсора fetchall() можно получить результат запроса. Он представляется в виде массива записей, каждая из которых является массивом содержимого полей. Для того чтобы не запутаться с полями в SQL-запросе необходимо указывать выбираемые поля, и в том же порядке извлекать их из каждой записи.

Разберем выполнение и обработку SELECT-запросов напримере: извлечем первых 10 пользователей из только что заполненной базы, у которых емейл расположен на Яндексе.

#!/usr/bin/python
# -*- coding: utf-8
import MySQLdb
import string

# соединяемся с базой данных
db = MySQLdb.connect(host="localhost", user="root", passwd="пароль", db="contacts", charset='utf8')
# формируем курсор
cursor = db.cursor()

# запрос к БД
sql = """SELECT mail, name FROM eadres WHERE mail LIKE '%yandex.ru' LIMIT 10"""
# выполняем запрос
cursor.execute(sql)

# получаем результат выполнения запроса
data =  cursor.fetchall()
# перебираем записи
for rec in data:
    # извлекаем данные из записей - в том же порядке, как и в SQL-запросе
    mail, name = rec
    # выводим информацию
    print name, mail

# закрываем соединение с БД
db.close()

Еще раз напомню, что извлекать данные из записи нужно в том же порядке (и количестве), в каком они записаны в SQL-запросе.

Вот  пожалуй и все. До встречи!

, ,



  1. #1 by Konstantin on 10 Декабрь 2010 - 10:45

    Пара вопросов по реализации:
    1) А использовать модуль csv для работы с csv-файлом не кошерно? Это позволит работать с каждой строкой как с dictionary, а не надеятся на порядок полей в файле, и не надо будет специально отфильтровывать headerline.
    2) какой выигрыш в применении базы данных если для запихивания в неё проводится полное считывание файла в лист?

    спасибо, что показали LIMIT – буду знать что есть такой MySQL specific аналог TOP.

    • #2 by toly on 10 Декабрь 2010 - 10:51

      Объем файла – 50 метров. Осуществлять поиск с наименьшим задействованием оперативки через MySQL – самый оптимальный вариант. В противном случае единичный запуск скрипта будет, пусть и на небольшое время, тратить 50 метров. С MySQL таких проблем не будет. К тому же скрипт предстояло запускать на дешевой VDS’ке – оперативкой там сильно не поразбрасываешься.

      Спасибо за отзыв.

      • #3 by procool on 17 Март 2012 - 10:08

        речь не о том, хорошо или плохо использовать базу,
        а скорее о том что вы файл для занесения в базу в память целиком записали.. как бы vds не поперхнулась:)

        построчно из дескриптора нельзя читать было?

  2. #4 by bender on 10 Февраль 2012 - 15:46

    if string.find(line, «@») > -1 можно легко заменить на:
    if ‘@’ in line:

  3. #6 by SoftDed on 30 Март 2012 - 11:51

    Огромное спасибо за совет!
    Переписал половину своего боевого скрипта!

  4. #7 by Солнцеворот on 13 Декабрь 2012 - 20:50

    А почему в запросе
    /*
    sql = «»"SELECT mail, name FROM eadres WHERE mail LIKE ‘%yandex.ru’ LIMIT 10″»"
    */
    столько кавычек?

    • #8 by toly on 14 Декабрь 2012 - 12:22

      в питоне можно использовать тройные кавычки для обозначения строк

  5. #9 by Andry on 19 Декабрь 2012 - 8:53

    Большое спасибо за исчерпывающий пример!

  6. #10 by alexey on 10 Январь 2013 - 12:27

    Почему при операции INSERT данные не добавляются в базу. Хотя у id включена auto_incrment и он считается, но данных не видно.Ошибок не возникает. А оператор select проходит на ура.

    • #11 by toly on 10 Январь 2013 - 12:45

      Думаю нужно попробовать выполнить тот же INSERT в консоли для БД – может ошибку какую выдаст

  7. #12 by alexey on 12 Январь 2013 - 22:55

    Ошибки не выдает. Данные добавляются

  8. #13 by alexey on 12 Январь 2013 - 23:06

    Вопрос снят!) я забыл строчку db.commit()

  9. #14 by Dmitry on 18 Июль 2013 - 12:37

    Спасибо вам за статью!
    У меня возник такой вопрос, если запросы производятся довольно часто и скрипт в ходе запросов может меняться, то как можно избежать постоянных подключений к серверу? Есть какая-то команда MySqldb для проверки коннекта?

    • #15 by toly on 18 Июль 2013 - 15:04

      по завершении работы с базой нужно завершать коннект.
      в данном случае на один запуск скрипта будет один коннект.
      если запросы производятся очень часто, то можно использовать пулл коннектов.
      с MySQL я уже неработаю (как и с чистым SQL). Очень удобен ORM (SQLAlchemy или джанговский) с postgresql. Рекомендую.

  10. #16 by Егор on 21 Декабрь 2014 - 3:26

    Огромное спасибо за статью.
    Долго сидел и не мог понять, почему не могу добавить в БД, а потом наткнулся на Вашу статью и понял, что не сделал commit:) Хотя и в консоли БД проверял запрос и что тока уже не делал.
    И ещё много фичей взял у Вас для оптимизации кода)

  11. #17 by Snowman8526 on 16 Июнь 2015 - 18:38

    Меня интересует такая тема. Когда делаешь что-то в phpMyAdmin вам возвращается надпись запрос обработан за 0.0012 сек. Я планирую делать на моём слабеньком сервере нагруженный обмен данными и меня будет интересовать как отдельные запросы, так и общее работа цикла. Можно ли делать запрос на вывод этих данных? Или проще использовать средства phpMyAdmin? Но там не видно какой запрос грузит систему, а мне как новичку в БД будет сложно понять. Спасибо.

  12. #19 by Snowman8526 on 17 Июнь 2015 - 17:38

    Ещё один маленький вопрос. После запроса SELECT он мне вращает некое значения типа ((0,),) или ((u’qwe’,),) как можно получить чистое значение? Пытался избавиться конструкцией типа (stroka.replace(‘((‘,»)) но потом понял, что это типа кортежа только не кортеж. Что это за тип данных?

    Я понимаю, что вы уже sql не пользуетесь, но может вы встречались с такой проблемой.
    Спасибо.

    • #20 by toly on 17 Июнь 2015 - 17:46

      Это кортеж с вложенным кортежем. Если достоверно известно, что будет возвращено одно значение, то можно обратиться так
      In [8]: a = ((1, ), )
      In [9]: a[0][0]
      Out[9]: 1

    • #22 by toly on 17 Июнь 2015 - 17:50

      Но лучше итерировать и множественно присваивать, например:

      In [10]: a = ((1, ‘vova’, ), (2, ‘vasya’, ), )

      In [11]: for id, user in a:
      ….: print id, user
      ….:
      1 vova
      2 vasya

      In [12]:

    • #23 by toly on 17 Июнь 2015 - 17:52

      И еще момент – получение типа переменной:
      In [12]: a = ((1, ‘vova’, ), (2, ‘vasya’, ), )

      In [13]: type(a)
      Out[13]: tuple

      In [14]:

  13. #24 by Sergey on 9 Декабрь 2015 - 0:41

    Всем привет.
    Как будет выглядеть запрос, если имя вводится с клавиатуры и нужно проверить имеется ли это имя в БД ?

(никто не узнает)