Предположим, что нам необходимо загрузить данные в таблицу из плоского файла, каждая строка которого представляет запись в базе данных и содержит значения полей, разделенные запятыми. В примерах 17.6 и 17.7 приводятся два таких файла с данными, которые мы будем использовать здесь.
Пример 17.6. PP4E\Dbase\Sql\data.txt
bob,devel,50000
sue,music,60000
ann,devel,40000
tim,admin,30000
kim,devel,60000
Пример 17.7. PP4E\Dbase\Sql\data2.txt
bob,developer,80000
sue,music,90000
ann,manager,80000
В следующем примере первая команда удаляет все записи в таблице, а затем мы разбиваем инструкцию SQL на несколько строк, используя тот факт, что Python автоматически объединяет смежные строковые литералы:
> Используется MySQL (в настоящее время интерфейс к этой базе данных
> доступен только для Python 2.X)
…сначала выполняется регистрация в базе данных MySQL…
> >> curs.execute(‘delete from people’) # все записи
> >> curs.execute(
… "load data local infile ‘data.txt’ "
… "into table people fields terminated by ‘,’")
|
>>> curs.execute(‘select * from people’) |
|||
|
>>> for |
row in curs.fetchall(): print(row) |
||
|
… (‘bob’, |
‘devel’, |
50000L) |
|
|
(‘sue’, |
‘music’, |
60000L) |
# длинные целые в Python 2.X |
|
(‘ann’, |
‘devel’, |
40000L) |
|
|
(‘tim’, |
‘admin’, |
30000L) |
|
|
(‘kim’, >>> conn |
‘devel’, .commit() |
60000L) |
|
Этот прием действует именно так, как и ожидалось. Но что если нам потребуется использовать другую систему баз данных, такую как SQLite, в которой отсутствует такая инструкция SQL? Или может быть вам просто потребуется выполнить что-то особенное, чего не позволяет эта инструкция MySQL. Не волнуйтесь — чтобы достичь того же результата при использовании SQLite и Python 3.X, потребуется написать совсем немного простого программного кода на языке Python (в выводе ниже опущены некоторые строки, не имеющие отношения к обсуждаемой теме):
C:\…\PP4E\Dbase\Sql> python
> >> from sqlite3 import connect
> >> conn = connect(‘dbase1’)
> >> curs = conn.cursor()
> >> curs.execute(‘delete from people’) # очистить таблицу
> >> curs.execute(‘select * from people’)
> >> curs.fetchall()
[]
> >> file = open(‘data.txt’)
> >> rows = [line.rstrip().split(‘,’) for line in file]
> >> rows[0]
[‘bob’, ‘devel’, ‘50000’]
> >> for rec in rows:
… curs.execute(‘insert into people values (?,
> >> curs.execute(‘select * from people’)
> >> for rec in curs.fetchall(): print(rec)
(‘bob’, ‘devel’, 50000)
(‘sue’, ‘music’, 60000)
(‘ann’, ‘devel’, 40000)
(‘tim‘, ‘admin‘, 30000)
(‘kim‘, ‘devel‘, 60000)
Здесь используется генератор списков, который собирает в список результаты разбиения всех строк в файле после удаления из них символов перевода строки, и итераторы файлов, выполняющие построчное чтение содержимого файлов. Цикл for в этом примере делает то же самое, что и инструкция load базы данных MySQL, но он может работать с базами данных разных типов, включая SQLite. Похожий результат можно также получить с помощью метода executemany, показанного выше, однако цикл for, использованный здесь, в целом является более универсальным.
Python и SQL
Фактически в вашем распоряжении имеется целый язык Python для обработки результатов запроса к базе данных, а программный код Python даже небольшого объема часто способен не только продублировать, но и превзойти возможности SQL. Например, в языке SQL имеются специальные агрегатные функции, вычисляющие такие значения, как сумма и среднее арифметическое:
> >> curs.execute("select sum(pay), avg(pay) from people where job = ‘devel’")
> >> curs.fetchall()
[(150000, 50000.0)]
Перекладывая обработку данных на программный код Python, иногда можно упростить запросы SQL и реализовать более сложную логику (хотя при этом, возможно, придется пожертвовать любыми оптимизациями производительности запросов, которые может предложить база данных). Вычисление суммы зарплат и среднего значения на языке Python можно реализовать с помощью простого цикла:
> >> curs.execute("select name, pay from people where job = ‘devel’")
> >> result = curs.fetchall()
> >> result
((‘bob’, 50000L), (‘ann’, 40000L), (‘kim’, 60000L))
> >> tot = 0
> >> for (name, pay) in result: tot += pay
…
> >> print(‘total:’, tot, ‘average:’, tot / len(result)) # используйте //
total: 150000 average: 50000.0 # для деления
# с усечением
Также для вычисления суммы, максимального и среднего значений можно задействовать более сложные инструменты, такие как генераторы и выражения-генераторы, как показано ниже:
> >> print(sum(rec[1] for rec in result)) # выражение—генератор 150000
> >> print(sum(rec[1] for rec in result) / len(result))
50000.0
> >> print(max(rec[1] for rec in result))
60000
Подход на основе языка Python является более универсальным, но потребность в нем не столь очевидна, пока не возникает необходимость в реализации более сложной логики обработки. Например, ниже приводятся чуть более сложные генераторы списков, которые отбирают из результатов имена сотрудников, зарплата которых выше или ниже среднего значения:
> >> avg = sum(rec[1] for rec in result) / len(result)
> >> print([rec[0] for rec in result if rec[1] > avg]) [‘kim’]
> >> print([rec[0] for rec in result if rec[1] < avg])
[‘ann‘]
Подобного рода задачи можно решать также с применением дополнительных возможностей языка SQL, таких как вложенные запросы, но рано или поздно мы достигнем уровня сложности, когда универсальная природа языка Python и, возможно, его переносимость, станут более привлекательными. Для сравнения ниже приводится эквивалентное решение на языке SQL:
>>> query = ("select name from people where job = ‘devel‘ and "
… "pay > (select avg(pay) from people where job = ‘devel’)")
>>> curs.execute(query)
>>> curs.fetchall()
[(‘kim’,)]
>>> query = ("select name from people where job = ‘devel’ and "
… "pay < (select avg(pay) from people where job =
‘devel’)")
>>> curs.execute(query)
>>> curs.fetchall()
[(‘ann‘,)]
Это наверняка не самые сложные запросы SQL, с которыми вам придется столкнуться, но за этой гранью код SQL может стать намного более сложным. Кроме того, в отличие от языка Python, SQL ограничивается решением задач, связанных лишь с базой данных. Представьте запрос, который сравнивает значения столбцов с данными, полученными из Интернета или введенными пользователем в графическом интерфейсе, — это простая операция в Python, с поддержкой Интернета и графических интерфейсов, которая выходит за пределы такого узкоспециализированного языка, как SQL. Объединяя Python и SQL, вы получаете все самое лучшее от обоих языков и можете выбирать, когда какой из них лучше подходит для достижения ваших целей.
При использовании Python вы также получаете доступ к уже написанным утилитам: ваш набор инструментов для работы с базами данных может безгранично расширяться новыми функциями, модулями и классами. Для иллюстрации ниже представлена более удобочитаемая реализация тех же операций с применением модуля преобразования записей в словари, написанного нами выше:
> >> from makedicts import makedicts
> >> recs = makedicts(curs, "select * from people where job = ‘devel’")
> >> print(len(recs), recs[0])
3 {‘pay’: 50000, ‘job’: ‘devel’, ‘name’: ‘bob’}
> >> print([rec[‘name’] for rec in recs])
[‘bob’, ‘ann’, ‘kim’]
> >> print(sum(rec[‘pay’] for rec in recs))
150000
> >> avg = sum(rec[‘pay’] for rec in recs) / len(recs)
> >> print([rec[‘name’] for rec in recs if rec[‘pay’] > avg])
[‘kim’]
> >> print([rec[‘name’] for rec in recs if rec[‘pay’] >= avg])
[‘bob‘, ‘kim‘]
Аналогично тип set в языке Python предоставляет такие операции, как пересечение, объединение и разность, которые могут служить альтернативами других операций SQL (в интересах экономии места мы оставим эту тему для самостоятельного изучения). Дополнительные расширения Python для работы с базами данных можно найти среди инструментов, созданных сторонними разработчиками. Например, существует множество пакетов, добавляющих объектно-ориентированные возможности к интерфейсу баз данных — механизмы ORM, которые мы рассмотрим ближе к концу этой главы.
Использованная литература:
Марк Лутц — Программирование на Python, 4-е издание, II том, 2011
