Узнайте, как создавать таблицы и запрашивать данные в SQLite, а также получать доступ к SQLite из Pandas.

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

Что такое SQLite?

SQLite — это программная библиотека, которая предоставляет нам систему управления реляционными данными с открытым исходным кодом, в которой мы можем хранить большие объемы данных.

Его легко настроить, он автономен (требует минимальной поддержки) и не требует сервера. База данных SQLite буквально представляет собой обычный файл, что делает ее легко доступной и переносимой. Учитывая простоту SQLite, это наиболее широко распространенный движок базы данных.

SQLite хорош по многим причинам, но на практике вам не следует использовать SQLite, если вы обрабатываете гигантский объем данных. Фактически база данных ограничена 281 терабайтом.

Начиная

Если вы хотите продолжить, вы можете скачать копию моего Jupyter Notebook здесь.

Мы начинаем с импорта библиотеки SQLite в наш файл python. Эту библиотеку не нужно загружать, так как она уже должна быть включена в стандартную библиотеку, если вы используете Python 2.5 и выше.

import sqlite3 as db

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

conn = db.connect('my_database.db')

Обратите внимание, что механизм SQLite поддерживает базу данных в виде файла с расширением .db. Таким образом, приведенная выше строка кода будет подключаться к файлу с именем my_database.db, ЕСЛИ ОН СУЩЕСТВУЕТ в папке. Если он не существует, на вашем компьютере будет автоматически создан файл с именем my_database.db, как показано во фрагменте выше.

Теперь, чтобы взаимодействовать с нашей базой данных, нам нужно создать курсор. Это то, что мы будем использовать для выдачи команд, которые позволят нам запрашивать или изменять нашу базу данных.

c = conn.cursor()

Создание и изменение таблиц

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

Создать таблицу

Мы используем метод execute для курсора c, который мы создали ранее, и передаем наш оператор SQL.

c.execute("CREATE TABLE employees (empid INTEGER PRIMARY KEY, firstname NVARCHAR(20), lastname NVARCHAR(20))")

Приведенный выше оператор создаст таблицу с именем сотрудников с тремя столбцами: empid, имя и фамилия.

Вы можете проверить, что таблица была успешно создана, с помощью следующих операторов.

c.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(c.fetchall())

Приведенный выше запрос select покажет вам все таблицы, которые есть в вашей базе данных. Обратите внимание, что результаты запроса не выводятся, если у вас есть только первый оператор. Вы должны использовать c.fetchall() для отображения результатов.

Вставить данные в таблицу

Для заполнения таблицы у нас есть несколько вариантов. Если вы вставляете только несколько строк, вы можете использовать следующие операторы вставки для ввода двух строк в таблицу сотрудников.

c.execute("INSERT INTO employees VALUES (12986,'Michael','Scott')")
c.execute("INSERT INTO employees VALUES (12987,'Dwight','Schrute')")

Если у вас есть много строк для ввода в таблицу, рассмотрите возможность создания списка кортежей и использования вместо этого командыexecutemany().

new_employees = [(12987, 'Jim', 'Halpert'),
 (12988, 'Pam', 'Beesly'),
 (12989, 'Andy', 'Bernard'),
 (12990, 'Kevin', 'Malone'),
 (12991, 'Toby', 'Flenderson'),
 (12992, 'Angela', 'Martin'),
 (12993, 'Stanley', 'Hudson')]
c.executemany('INSERT INTO employees VALUES (?, ?, ?)', new_employees)

Обратите внимание, что мы заменили наш предыдущий оператор вставки на (?, ?, ?). Знаки вопроса здесь представляют собой заполнители для трех элементов, которые мы вводим в таблицу.

Давайте теперь посмотрим, что у нас в таблице. Мы можем использовать оператор select внутри команды execute аналогично тому, что мы делали ранее.

Из вывода мы видим, что все наши сотрудники были правильно введены в таблицу. Ура!

Фиксация изменений

Мы внесли пару изменений в нашу базу данных, но они еще не сохранены в базе данных! Это всего лишь временные модификации, поэтому мы должны обязательно зафиксировать эти изменения с помощью следующей команды.

conn.commit()

Если мы этого не сделаем, изменения будут потеряны, как только мы закроем соединение.

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

c.close()
conn.close()

SQLite через Pandas

Одна крутая вещь, которую вы можете сделать, это использовать как SQLite, так и Pandas. У Pandas есть метод read_sql_query, который позволит вам вернуть данные в виде кадра данных Pandas. Оттуда вам будет легче манипулировать данными в Pandas. Лично я предпочитаю работать с данными внутри Pandas.

Мы снова должны сначала установить соединение с нашей базой данных. Затем мы можем использовать pd.read_sql_queryи сохранить вывод как фрейм данных с именем df_employees.

#import library
import pandas as pd
con = db.connect('my_database.db')
df_employees = pd.read_sql_query('select * from employees', con)

Теперь, когда это кадр данных Pandas, вы можете использовать свою обычную функцию pandas для управления данными по своему усмотрению.

Например, предположим, что Стэнли (последний человек в таблице) никогда не предназначался для найма, поэтому нам нужно избавиться от него из таблицы.

df_new = df_employees[:-1]

Мы можем записать наш новый фрейм данных обратно в SQLite и заменить исходную таблицу сотрудников. Для записи в SQLite мы используем метод to_sql() для нового фрейма данных.

df_new.to_sql("employees", con, if_exists="replace")

Мы предоставляем три параметра внутри этого метода:

  1. Имя таблицы SQL
  2. Подключение к базе данных
  3. Как вести себя, если таблица уже существует. «Заменить» удалит исходную таблицу. «Fail» вызовет ошибку значения в Python. «Добавить» вставит данные в виде новых строк в таблицу.

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

pd.read_sql_query ('select * from employees', con)

И, как и раньше, как только мы закончим, давайте закроем соединение.

con.close()

Вот и все! SQLite довольно прост в использовании. Если вы ищете хорошую базу данных для практики SQL и/или SQLite, вы можете скачать базу данных Chinook здесь. Подробнее о данных внутри базы данных Chinook можно узнать здесь.

Спасибо за чтение! Дайте мне знать в комментариях, если у вас есть вопросы или вы хотите рассказать анекдот по SQL.