Python: Робота з базою даних, 1/2 частина: Використовуємо DB-API

Python DB-API – це не конкретна бібліотека, а набір правил, яким підпорядковуються окремі модулі, що реалізують роботу з конкретними базами даних. Окремі нюанси реалізації для різних баз можуть відрізнятися, але загальні принципи дозволяють використовувати один і той же підхід при роботі з різними базами даних.

У статті розглянуто основні методи DB-API, що дозволяють повноцінно працювати з базою даних. Повний список можете знайти за посиланнями в кінець статті.

Необхідний рівень підготовки: базове розуміння синтаксису SQL і Python.

Готуємо інвентар для подальшої комфортної роботи
  • Python має вбудовану підтримку SQLite бази даних, для цього вам не потрібно нічого додатково встановлювати, достатньо в скрипті вказати імпорт стандартної бібліотеки
    import sqlite3

  • Завантажити тестову базу даних, з якою будемо працювати.
    У даній статті буде використовуватися відкрита (MIT ліцензія) тестова база даних «Chinook».
    Завантажити її можна за наступними посиланнями:
    chinookdatabase.codeplex.com
    github.com/lerocha/chinook-database
    Нам потрібен для таботы тільки бінарний файл “Chinook_Sqlite.sqlite"

  • Для зручності роботи з базою (перегляд, редагування) нам потрібна програма браузер баз даних, підтримує SQLite.
    У статті робота з браузером не розглядається, але він допоможе Вам наочно бачити що відбувається з базою в процесі наших експериментів.
    Примітка: вносячи зміни в базу не забудьте їх застосувати, так як база з непримененными змінами залишається залоченной.

    Ви можете використовувати (останні два варіанти крос-платформні та безкоштовні):
Python DB-API модулі в залежності від бази даних
База даних DB-API модуль
SQLite sqlite3
PostgreSQL psycopg2
MySQL mysql.connector
ODBC pyodbc
З'єднання з базою, отримання курсору
Для початку розглянемо самий базовий шаблон DB-API, який будемо використовувати у всіх подальших прикладах:

# Імпортуємо бібліотеку, що відповідає типу нашої бази даних 
import sqlite3

# Створюємо з'єднання з нашою базою даних
# У нашому прикладі у нас це просто файл бази
conn = sqlite3.connect('Chinook_Sqlite.sqlite')

# Створюємо курсор - це спеціальний об'єкт який робить запити та отримує їх результати
cursor = conn.cursor()

# ТУТ БУДЕ НАШ КОД РОБОТИ З БАЗОЮ ДАНИХ
# КОД ПОДАЛЬШИХ ПРИКЛАДІВ ВСТАВЛЯТИ В ЦЕ МІСЦЕ

# Не забуваємо закрити з'єднання з базою даних
conn.close()

При роботі з іншими базами даних, використовуються додаткові параметри з'єднання, наприклад для PostrgeSQL:
conn = psycopg2.connect( host=hostname, user=username, password=password, dbname=database)

Читання з бази
# Робимо SELECT запит до бази даних, використовуючи звичайний синтаксис SQL
cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3")

# Отримуємо результат зробленого запиту
results = cursor.fetchall()
results2 = cursor.fetchall()

print(results) # [('A Cor Do Som',), ('Aaron Copland & London Symphony Orchestra',), ('Aaron Goldberg',)]
print(results2) # []

Зверніть увагу: Після отримання результату з курсору, другий раз без повторення самого запиту його отримати не можна — повернеться порожній результат!

Запис в базу
# Робимо INSERT запит до бази даних, використовуючи звичайний синтаксис SQL
cursor.execute("insert into Artist values (Null, 'A Aagrh!') ")

# Якщо ми не просто читаємо, але й вносимо зміни в базу даних - необхідно зберегти транзакцію
conn.commit()

# Перевіряємо результат
cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3")
results = cursor.fetchall()
print(results) # [('A Aagrh!',), ('A Cor Do Som',), ('Aaron Copland & London Symphony Orchestra',)]

Примітка: Якщо до бази встановлено кілька з'єднань і один з них здійснює модификацю бази, то база SQLite залочивается до завершення (метод з'єднання .commit () або скасування (метод з'єднання .rollback()) транзакції.

Розбиваємо запит на кілька рядків у потрійних лапках
Довгі запити можна розбивати на декілька рядків в довільному порядку, якщо вони укладені у потрійні лапки — одинарні ("'..."') або подвійні ("""...""")
cursor.execute("""
SELECT name
FROM Artist
ORDER BY Name LIMIT 3
""")

Звичайно в такому простому прикладі розбиття не має сенсу, але на довгих складних запитах вона може кардинально підвищувати читаність коду.

Об'єднуємо запити до бази даних в один виклик методу
Метод курсору .execute() дозволяє робити тільки один запит за раз, при спробі зробити кілька через крапку з комою буде помилка.
Для тих хто не вірить на слово:
cursor.execute("""
insert into Artist values (Null, 'A Aagrh!');
insert into Artist values (Null, 'A Aagrh-2!');
""")
# sqlite3.Warning: You can only execute one statement at a time.

Для рішення такої задачі можна або кілька разів викликати метод курсору .execute()
cursor.execute("""insert into Artist values (Null, 'A Aagrh!');""")
cursor.execute("""insert into Artist values (Null, 'A Aagrh-2!');""")

Або використовувати метод курсору .executescript()
cursor.executescript("""
insert into Artist values (Null, 'A Aagrh!');
insert into Artist values (Null, 'A Aagrh-2!');
""")

Даний метод зручний, коли у нас запити зберігаються в окремій змінної або навіть у файлі і нам його треба застосувати такий запит до бази.

Робимо підстановку значення запит
Важливо! Ніколи, ні при яких умовах, не використовуйте конкатенацию рядків (+) або інтерполяцію параметра в рядку (%) для передачі змінних в SQL-запит. Таке формування запиту, при можливості попадання в нього користувальницьких даних – це ворота для SQL-ін'єкцій!

Правильний спосіб – використання другого аргументу методу .execute()

Можливі два варіанти:
# C подставновкой за порядком на місця знаків запитань:
cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT ?", ('2'))

# І з використанням именнованных замін:
cursor.execute("SELECT Name from Artist ORDER BY Name LIMIT :limit", {"limit": 3})

Примітка 1: PostgreSQL замість знака? Для підстановки використовується: %s
Примітка 2: Таким способом не вийде замінювати імена таблиць, одне з можливих рішень у такому разі розглядається тут: stackoverflow.com/questions/3247183/variable-table-name-in-sqlite/3247553#3247553

Робимо множинне вставку рядків проходячи по колекції з допомогою методу курсору .executemany()
# Зверніть увагу, навіть передаючи одне значення - його потрібно передавати кортежем!
# Саме тому тут використовується кома в дужках!
new_artists = [
('A Aagrh!',),
('A Aagrh!-2',),
('A Aagrh!-3',),
]
cursor.executemany("insert into Artist values (Null, ?);", new_artists)

Отримуємо результати по одному, використовуючи метод курсору .fetchone()
Він завжди повертає кортеж або None. якщо запит порожній.
cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3")
print(cursor.fetchone()) # ('A Cor Do Som',)
print(cursor.fetchone()) # ('Aaron Copland & London Symphony Orchestra',)
print(cursor.fetchone()) # ('Aaron Goldberg',)
print(cursor.fetchone()) # None

Важливо! Стандартний курсор забирає всі дані з сервера відразу, не залежно від того, використовуємо ми .fetchall() або .fetchone()

Курсор як ітератор
# Використання курсора як ітератора
for row in cursor.execute('SELECT Name from Artist ORDER BY Name LIMIT 3'):
print(row)
# ('A Cor Do Som',)
# ('Aaron Copland & London Symphony Orchestra',)
# ('Aaron Goldberg',)

Додаткові матеріали (англійською)


У розробці знаходиться друга частина статті, де буде розглядатися робота з базою в Python з використанням SQLAlchemy.

Запрошую до обговорення:

  • Якщо я десь допустив неточність або не врахував щось важливе — пишіть в коментарях, важливі коментарі будуть пізніше додано статтю з зазначенням вашого авторства.
  • Якщо якісь моменти не зрозумілі і потрібне уточнення — пишіть ваші запитання в коментарях — або я або інші читачі дадуть відповідь, а слушні запитання з відповідями будуть пізніше додано статтю.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.