Все, что вам нужно для начала работы!

SQL и Python быстро стали основными навыками для любого, кто серьезно занимается анализом данных! Это руководство по Python SQLite - единственное руководство, которое вам нужно для начала работы с SQLite на Python. В этом посте мы расскажем:

  • Загрузка библиотеки
  • Создание и подключение к вашей базе данных
  • Создание таблиц базы данных
  • Добавление данных
  • Запрос данных
  • Удаление данных
  • И многое другое!

SQLite3 (мы будем называть его просто SQLite) является частью стандартного пакета Python 3, поэтому вам не нужно ничего устанавливать. Если вы не используете Python 3, ознакомьтесь с этой ссылкой, чтобы начать работу.

Если вы хотите начать работу с SQL, ознакомьтесь с моим полным Учебником по SQL для начинающих, который включает бесплатный загружаемый PDF-файл и другие бесплатные материалы.

Что вы создадите

Следуя этому руководству, вы создадите базу данных в SQLite с помощью Python. В частности, этот пост проведет вас через все шаги по созданию базы данных, которая охватывает следующую таблицу, включая все отношения:

Типы данных, доступные в SQLite для Python

SQLite для Python предлагает меньше типов данных, чем другие реализации SQL. Это может немного ограничивать. Однако, как вы увидите, SQLite упрощает многие другие вещи. Давайте кратко рассмотрим доступные типы данных:

  • NULL - включает значение NULL.
  • INTEGER - включает целое число.
  • REAL - включает значение с плавающей точкой (десятичное).
  • ТЕКСТ. - Включает текст
  • BLOB. - Включает в себя большой двоичный объект, который хранится точно как вход

В этом списке вы можете заметить ряд отсутствующих типов данных, таких как даты. К сожалению, при использовании SQLite вы ограничены этими типами данных.

Начало работы с SQLite в Python

Начнем с загрузки учебника в библиотеке. Мы можем сделать это с помощью следующей команды:

import sqlite3

Давайте перейдем к созданию нашей базы данных.

Создание базы данных SQLite на Python

В этом разделе руководства Python SQLite мы рассмотрим различные способы создания базы данных на Python с помощью SQLite. Для этого мы создадим объект Connection, который будет представлять базу данных. Этот объект создается с помощью функции SQLite connect ().

Давайте сначала создадим файл .db, так как это очень стандартный способ поддержки базы данных SQLite. Мы представим соединение с помощью переменной с именем conn. Мы создадим файл с именем orders.db.

conn = sqlite3.connect('orders.db')

С помощью этой строки кода мы создали новый объект connection, а также новый файл с именем orders.db в каталоге, в котором вы работаете. Если вы хотите указать конкретный каталог, вы можете написать:

conn = sqlite3.connect(r'PATH-TO-YOUR-DIRECTORY/orders.db')

Если файл уже существует, функция connect просто подключится к этому файлу.

Примечание: обратите внимание, что мы включили букву r перед строкой, содержащей этот путь. Это позволяет Python знать, что мы работаем с необработанной строкой, а это означает, что / не будет использоваться для экранирования символов. Вы можете узнать больше о необработанных строках, просмотрев эту ссылку.

Функция connect создает соединение с базой данных SQLite и возвращает объект, представляющий его.

Базы данных в памяти

Другой способ создания баз данных с использованием SQLite в Python - это их создание в памяти. Это отличный способ создания баз данных, которые можно использовать в целях тестирования, поскольку они существуют только в оперативной памяти.

conn = sqlite3.connect(:memory:)

Однако для целей этого руководства и для большинства случаев использования, с которыми вы столкнетесь, вы будете использовать метод, который мы описали ранее.

Создание объекта курсора

Теперь, когда мы создали объект подключения к базе данных, наша следующая задача - создать объект курсор. Проще говоря, объект курсора позволяет нам выполнять SQL-запросы к базе данных. Мы создадим переменную cur для хранения объекта курсора:

cur = conn.cursor()

Теперь, когда у нас есть объект курсора, мы можем использовать его для выполнения SQL-запросов в следующем стиле:

cur.execute("YOUR-SQL-QUERY-HERE;")

Обратите внимание, что мы заключили наш SQL-запрос в кавычки - это важно. Не имеет значения, используем ли мы одинарные, двойные или тройные кавычки. Для более длинных запросов часто лучше использовать тройные кавычки, поскольку они позволяют нам писать многострочные запросы.

Создание наших таблиц в SQLite для Python

На этом этапе руководства Python SQLite давайте создадим нашу первую таблицу с использованием SQLite в Python! Теперь, когда у нас есть объект соединения (conn) и объект курсора (cur), мы можем создать нашу первую таблицу. Следуя схеме базы данных, которую мы показали ранее:

Начнем с таблицы users.

cur.execute("""CREATE TABLE IF NOT EXISTS users(
   userid INT PRIMARY KEY,
   fname TEXT,
   lname TEXT,
   gender TEXT);
""")
conn.commit()

В приведенном выше коде мы делаем несколько вещей:

  1. Использование функции execute в объекте курсора для выполнения SQL-запроса
  2. Использование SQL для создания таблицы с именем users
  3. ЕСЛИ НЕ СУЩЕСТВУЕТ поможет нам при повторном подключении к базе данных. Запрос позволит нам проверить, существует ли таблица, и если это так, ничего не изменится.
  4. Мы создаем четыре столбца: ИД пользователя, fname, lname и пол. ИД пользователя назначается первичным ключом.
  5. Мы зафиксировали изменения, используя функцию commit для объекта подключения.

Чтобы создать другую нашу таблицу, мы можем следовать аналогичному шаблону и написать следующие команды:

cur.execute("""CREATE TABLE IF NOT EXISTS orders(
   orderid INT PRIMARY KEY,
   date TEXT,
   userid TEXT,
   total TEXT);
""")
conn.commit()

После выполнения этих двух сценариев ваша база данных будет иметь две таблицы. Теперь мы готовы начать добавлять данные!

Добавление данных с помощью SQLite в Python

Давайте посмотрим, как добавить данные с помощью SQLite на Python в только что созданную базу данных. Подобно запросу создания таблицы, запрос на добавление данных использует объект курсора для выполнения запроса.

cur.execute("""INSERT INTO users(userid, fname, lname, gender) 
   VALUES('00001', 'Nik', 'Piepenbreier', 'male');""")
conn.commit()

Часто, когда мы работаем в Python, у нас есть переменные, которые содержат для нас значения. Например, у нас может быть кортеж, содержащий информацию о пользователе, которая может выглядеть так:

user = ('00002', 'Lois', 'Lane', 'Female')

Если бы мы хотели загрузить эти данные в нашу базу данных, мы бы использовали другое соглашение:

cur.execute("INSERT INTO users VALUES(?, ?, ?, ?);", user)
conn.commit()

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

Здесь важно отметить, что SQLite ожидает, что значения будут в формате кортежа. Однако переменная может содержать список, если элементы списка являются кортежами. Например, мы могли бы добавить больше пользователей с помощью переменной:

more_users = [('00003', 'Peter', 'Parker', 'Male'), ('00004', 'Bruce', 'Wayne', 'male')]

В этом случае вместо функции execute нам нужно использовать функцию executemany:

cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", more_users)
conn.commit()

Если бы мы использовали здесь функцию execute для объекта курсора, функция предполагала бы, что мы передаем два элемента в таблицу напрямую (два кортежа), а не два набора по четыре элемента в каждом! К счастью, в этом случае функция не сработала бы, но будьте осторожны с тем, какую функцию вы используете!

SQLite и предотвращение инъекционных атак

Между прочим, использование метода (?,?,…), О котором мы говорили выше, также помогает защитить от атак SQL-инъекций. По этой причине рекомендуется использовать этот метод вместо ранее упомянутого. Кроме того, печатать легче, так что это беспроигрышный вариант!

Некоторые сценарии для загрузки дополнительных данных

Если вы самостоятельно следите за учебным курсом Python SQLite, давайте загрузим еще несколько данных, чтобы сделать следующие разделы более содержательными. Ниже приведены сценарии, которые можно скопировать и вставить, чтобы вставить образцы данных в обе таблицы:

Вы можете загрузить эти данные, используя следующие запросы:

cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", customers)
cur.executemany("INSERT INTO orders VALUES(?, ?, ?, ?);", orders)
conn.commit()

Выбор данных в SQLite с помощью Python

Далее в этом руководстве по Python SQLite мы рассмотрим, как выбирать данные с помощью SQLite в Python! Мы будем следовать той же структуре, что и для выполнения запросов выше, но мы также добавим к ней еще один элемент.

Использование fetchone () в SQLite с Python

Начнем с использования функции fetchone (). Мы создаем переменную one_result, чтобы вытащить только результат

cur.execute("SELECT * FROM users;")
one_result = cur.fetchone()
print(one_result)

Это возвращает:

[(1, 'Nik', 'Piepenbreier', 'male')]

Использование fetchmany () в SQLite с Python

Допустим, мы хотим вернуть более одного результата, мы могли бы использовать функцию fetchmany (). Давайте запустим другой скрипт, чтобы получить 3 результата:

cur.execute("SELECT * FROM users;")
three_results = cur.fetchmany(3)
print(three_results)

Это вернет следующее:

[(1, 'Nik', 'Piepenbreier', 'male'), (2, 'Lois', 'Lane', 'Female'), (3, 'Peter', 'Parker', 'Male')]

Использование fetchall () в SQLite с Python

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

cur.execute("SELECT * FROM users;")
all_results = cur.fetchall()
print(all_results)

Удаление данных в SQLite с помощью Python

Теперь мы рассмотрим, как удалять данные с помощью SQLite в Python. Мы можем сделать это, используя структуру, аналогичную описанной выше. Допустим, мы хотим удалить любого пользователя с фамилией «Паркер», мы могли бы написать:

cur.execute("DELETE FROM users WHERE lname='Parker';")
conn.commit()

Когда мы затем запустим запрос ниже:

cur.execute("select * from users where lname='Parker'")
print(cur.fetchall())

Будет распечатан пустой список, подтверждающий, что запись была удалена.

Объединение таблиц с SQLite в Python

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

Для этого напишем следующее:

cur.execute("""SELECT *, users.fname, users.lname FROM orders
    LEFT JOIN users ON users.userid=orders.userid;""")
print(cur.fetchall())

Точно так же вы можете применить некоторые другие уловки SQL. Чтобы получить полное представление о SQL, прочтите мое полное руководство для новичков здесь.

Заключение: Учебное пособие по Python SQLite

В этом руководстве по Python SQLite мы изучили все, что вам нужно знать, чтобы начать работу с SQLite в Python. Мы начали с того, как загрузить библиотеку, изучили, как создать базу данных и таблицы, как добавлять данные, как запрашивать таблицы и как удалять данные.