Экспорт pandas DataFrames в SQLite с помощью SQLAlchemy

Автор оригинала: Matt Makai.

Обычно при исследовательском анализе данных например, при изучении данных COVID-19 с пандами, для загрузки из файлов, таких как CSV, XML или JSON, в pandas DataFrame. Затем вы можете поработать с данные в DataFrame и хотите сохранить их в более надежном месте как реляционная база данных.

В этом руководстве рассказывается, как загрузить фрейм данных pandas из CSV. файл, вытащите некоторые данные из полного набора данных, затем сохраните подмножество данных в базу данных SQLite с использованием SQLAlchemy.

Настройка нашей среды разработки

Убедитесь, что у вас установлен Python 3. На данный момент Python 3.8.2 – последняя версия версия Python.

В этом уроке мы также будем использовать:

pandas ( домашняя страница проекта
и исходный код ), версия 1.0.3
в этом руководстве
SQLAlchemy
( домашняя страница проекта и
исходный код ), версия 1.3.15
для этого урока
SQLite ( домашняя страница проекта
и исходный код ),
какой Python
включает коннектор как часть стандартной библиотеки Python

Установите вышеуказанные библиотеки кода в новый Виртуальная среда Python используя следующие команды:

python -m venv pandasexport
source pandasexport/bin/activate

pip install pandas==1.0.3 sqlalchemy==1.3.15

Наша среда разработки сейчас готовы загрузить пример набора данных COVID-19, загрузить его в панда DataFrame, выполните некоторый анализ, а затем сохраните в базе данных SQLite.

Получение данных о COVID-19

Перейти к загрузить сегодняшние данные о географическом распределении Случаи COVID-19 во всем мире страницу в вашем веб-браузере. Это должно выглядеть примерно так Скриншот.

Загрузите CSV-версию данных о COVID-19 от 29 марта 2020 г.

Должна быть ссылка на скачивание данные в формате CSV, но организация изменила макет страницы несколько раз за последние несколько недель, что затрудняет поиск форматы, отличные от Excel (XLSX). Если у вас возникли проблемы с получением Версия CSV, просто скачайте этот с GitHub который привязан к копии, загруженной 28 марта 2020 г.

Импорт CSV в pandas

Необработанные данные находятся в файле CSV, и нам нужно загрузить их в память через pandas DataFrame.

Начните с запуска цикла Python Read-Evaluate-Print (REPL) на командная строка:

python

>>>

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

from pandas import read_csv

df = read_csv("covid-19-cases-march-28-2020.csv", encoding="ISO-8859-1")

Теперь данные загружены в переменную df , которая является экземпляром pandas DataFrame класс.

Когда мы запускаем функцию count в этом DataFrame, мы возвращаемся, что это имеет 7320 рядов.

df.count()

Затем мы возьмем этот набор из 7320 строк данных и выделим только строки, относящиеся к США.

Создание нового DataFrame из исходного DataFrame

Мы можем выбрать все строки данных для одной страны, используя функция pandas для соответствия столбцу countriesAndTerritories в страну по нашему выбору.

save_df = df[df['countriesAndTerritories']=="United_States_of_America"]

Переменная save_df содержит меньшее подмножество данных. Вы можете узнайте, что в нем, заставив его напечатать себя:

save_df

Вы должны увидеть что-то вроде следующего вывода:

dateRep  day  month  year  cases  deaths   countriesAndTerritories geoId countryterritoryCode  popData2018
7082  28/03/2020   28      3  2020  18695     411  United_States_of_America    US                  USA  327167434.0
7083  27/03/2020   27      3  2020  16797     246  United_States_of_America    US                  USA  327167434.0
7084  26/03/2020   26      3  2020  13963     249  United_States_of_America    US                  USA  327167434.0
7085  25/03/2020   25      3  2020   8789     211  United_States_of_America    US                  USA  327167434.0
7086  24/03/2020   24      3  2020  11236     119  United_States_of_America    US                  USA  327167434.0
...          ...  ...    ...   ...    ...     ...                       ...   ...                  ...          ...
7166  04/01/2020    4      1  2020      0       0  United_States_of_America    US                  USA  327167434.0
7167  03/01/2020    3      1  2020      0       0  United_States_of_America    US                  USA  327167434.0
7168  02/01/2020    2      1  2020      0       0  United_States_of_America    US                  USA  327167434.0
7169  01/01/2020    1      1  2020      0       0  United_States_of_America    US                  USA  327167434.0
7170  31/12/2019   31     12  2019      0       0  United_States_of_America    US                  USA  327167434.0

[89 rows x 10 columns]

89 строк данных из исходных 7320 строк. Давайте продолжим сохранение этого подмножества в реляционной базе данных SQLite.

Сохранение DataFrame в SQLite

Мы собираемся использовать SQLAlchemy для создания соединения в новую базу данных SQLite, которая в этом примере будет храниться в файле с именем save_pandas.db . Конечно, вы можете сохранить файл с любым именем вы хотите и в любом месте, а не только в каталоге, в котором вы находитесь выполнение Python REPL.

Начните с импорта функции create_engine из sqlalchemy библиотека.

from sqlalchemy import create_engine

Создайте соединение с помощью импортированной функции create_engine а затем вызвать на нем метод connect .

engine = create_engine('sqlite:///save_pandas.db', echo=True)
sqlite_connection = engine.connect()

Мы устанавливаем echo = True , чтобы видеть весь вывод, который поступает от нашего подключение к базе данных. Когда соединение будет успешным, вы см. вывод, аналогичный следующему:

2020-03-29 20:44:08,198 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-03-29 20:44:08,198 INFO sqlalchemy.engine.base.Engine ()
2020-03-29 20:44:08,199 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-03-29 20:44:08,199 INFO sqlalchemy.engine.base.Engine ()

Задайте имя переменной со строкой имени таблицы, которую вы хотите создать. Затем используйте эту переменную при вызове to_sql для объекта save_df , который является нашим фреймом данных pandas, который представляет собой подмножество исходного набора данных с 89 строками, отфильтрованными из оригинал 7320.

Обратите внимание, что в этом случае мы потерпим неудачу, если таблица уже существует в базе данных. Вы можете изменить if_exists на replace или добавить и добавить собственную обработку исключений в более надежном версия этой программы. Проверить pandas.DataFrame.to_sql документацию для подробной информации о ваших вариантах.

sqlite_table = "Covid19"
save_df.to_sql(sqlite_table, sqlite_connection, if_exists='fail')

Выход эха должен увеличиваться с большим количеством выходных данных.

2020-03-29 20:45:09,066 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("Covid19")
2020-03-29 20:45:09,066 INFO sqlalchemy.engine.base.Engine ()
2020-03-29 20:45:09,067 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("Covid19")
2020-03-29 20:45:09,067 INFO sqlalchemy.engine.base.Engine ()
2020-03-29 20:45:09,069 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE "Covid19" (
    "index" BIGINT, 
    "dateRep" TEXT, 
    day BIGINT, 
    month BIGINT, 
    year BIGINT, 
    cases BIGINT, 
    deaths BIGINT, 
    "countriesAndTerritories" TEXT, 
    "geoId" TEXT, 
    "countryterritoryCode" TEXT, 
    "popData2018" FLOAT
)


2020-03-29 20:45:09,069 INFO sqlalchemy.engine.base.Engine ()
2020-03-29 20:45:09,070 INFO sqlalchemy.engine.base.Engine COMMIT
2020-03-29 20:45:09,070 INFO sqlalchemy.engine.base.Engine CREATE INDEX "ix_Covid19_index" ON "Covid19" ("index")
2020-03-29 20:45:09,070 INFO sqlalchemy.engine.base.Engine ()
2020-03-29 20:45:09,071 INFO sqlalchemy.engine.base.Engine COMMIT
2020-03-29 20:45:09,072 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-03-29 20:45:09,074 INFO sqlalchemy.engine.base.Engine INSERT INTO "Covid19" ("index", "dateRep", day, month, year, cases, deaths, "countriesAndTerritories", "geoId", "countryterritoryCode", "popData2018") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2020-03-29 20:45:09,074 INFO sqlalchemy.engine.base.Engine ((7082, '28/03/2020', 28, 3, 2020, 18695, 411, 'United_States_of_America', 'US', 'USA', 327167434.0), (7083, '27/03/2020', 27, 3, 2020, 16797, 246, 'United_States_of_America', 'US', 'USA', 327167434.0), (7084, '26/03/2020', 26, 3, 2020, 13963, 249, 'United_States_of_America', 'US', 'USA', 327167434.0), (7085, '25/03/2020', 25, 3, 2020, 8789, 211, 'United_States_of_America', 'US', 'USA', 327167434.0), (7086, '24/03/2020', 24, 3, 2020, 11236, 119, 'United_States_of_America', 'US', 'USA', 327167434.0), (7087, '23/03/2020', 23, 3, 2020, 8459, 131, 'United_States_of_America', 'US', 'USA', 327167434.0), (7088, '22/03/2020', 22, 3, 2020, 7123, 80, 'United_States_of_America', 'US', 'USA', 327167434.0), (7089, '21/03/2020', 21, 3, 2020, 5374, 110, 'United_States_of_America', 'US', 'USA', 327167434.0)  ... displaying 10 of 89 total bound parameter sets ...  (7169, '01/01/2020', 1, 1, 2020, 0, 0, 'United_States_of_America', 'US', 'USA', 327167434.0), (7170, '31/12/2019', 31, 12, 2019, 0, 0, 'United_States_of_America', 'US', 'USA', 327167434.0))
2020-03-29 20:45:09,074 INFO sqlalchemy.engine.base.Engine COMMIT
2020-03-29 20:45:09,075 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
2020-03-29 20:45:09,075 INFO sqlalchemy.engine.base.Engine ()

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

sqlite_connection.close()

Мы можем просмотреть данные с помощью программы просмотра командной строки sqlite3 . чтобы убедиться, что он был правильно сохранен в файл SQLite.

В командной строке ( не в Python REPL ) введите:

sqlite3

Это откроет приглашение командной строки для взаимодействия с SQLite. базы данных. Однако мы еще не подключены к нашему save_pandas.db файл.

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Используйте команду .open с нашим именем файла save_pandas.db , чтобы получить доступ к базе данных. Затем используйте стандартный запрос SQL, чтобы получить все записей из таблицы Covid19 .

sqlite> .open save_pandas.db
sqlite> select * from Covid19;

Проводник SQLite должен выдать результат, как показано ниже:

7082|28/03/2020|28|3|2020|18695|411|United_States_of_America|US|USA|327167434.0
7083|27/03/2020|27|3|2020|16797|246|United_States_of_America|US|USA|327167434.0
7084|26/03/2020|26|3|2020|13963|249|United_States_of_America|US|USA|327167434.0
7085|25/03/2020|25|3|2020|8789|211|United_States_of_America|US|USA|327167434.0
7086|24/03/2020|24|3|2020|11236|119|United_States_of_America|US|USA|327167434.0
7087|23/03/2020|23|3|2020|8459|131|United_States_of_America|US|USA|327167434.0
7088|22/03/2020|22|3|2020|7123|80|United_States_of_America|US|USA|327167434.0
7089|21/03/2020|21|3|2020|5374|110|United_States_of_America|US|USA|327167434.0
7090|20/03/2020|20|3|2020|4835|0|United_States_of_America|US|USA|327167434.0
7091|19/03/2020|19|3|2020|2988|42|United_States_of_America|US|USA|327167434.0
7092|18/03/2020|18|3|2020|1766|23|United_States_of_America|US|USA|327167434.0
7093|17/03/2020|17|3|2020|887|16|United_States_of_America|US|USA|327167434.0
7094|16/03/2020|16|3|2020|823|12|United_States_of_America|US|USA|327167434.0
7095|15/03/2020|15|3|2020|777|10|United_States_of_America|US|USA|327167434.0
7096|14/03/2020|14|3|2020|511|7|United_States_of_America|US|USA|327167434.0
7097|13/03/2020|13|3|2020|351|10|United_States_of_America|US|USA|327167434.0
7098|12/03/2020|12|3|2020|287|2|United_States_of_America|US|USA|327167434.0
7099|11/03/2020|11|3|2020|271|2|United_States_of_America|US|USA|327167434.0
7100|10/03/2020|10|3|2020|200|5|United_States_of_America|US|USA|327167434.0
7101|09/03/2020|9|3|2020|121|4|United_States_of_America|US|USA|327167434.0
7102|08/03/2020|8|3|2020|95|3|United_States_of_America|US|USA|327167434.0
7103|07/03/2020|7|3|2020|105|2|United_States_of_America|US|USA|327167434.0
7104|06/03/2020|6|3|2020|74|1|United_States_of_America|US|USA|327167434.0
7105|05/03/2020|5|3|2020|34|2|United_States_of_America|US|USA|327167434.0
7106|04/03/2020|4|3|2020|22|3|United_States_of_America|US|USA|327167434.0
7107|03/03/2020|3|3|2020|14|4|United_States_of_America|US|USA|327167434.0
7108|02/03/2020|2|3|2020|20|1|United_States_of_America|US|USA|327167434.0
7109|01/03/2020|1|3|2020|3|1|United_States_of_America|US|USA|327167434.0
7110|29/02/2020|29|2|2020|6|0|United_States_of_America|US|USA|327167434.0
7111|28/02/2020|28|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7112|27/02/2020|27|2|2020|6|0|United_States_of_America|US|USA|327167434.0
7113|26/02/2020|26|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7114|25/02/2020|25|2|2020|18|0|United_States_of_America|US|USA|327167434.0
7115|24/02/2020|24|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7116|23/02/2020|23|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7117|22/02/2020|22|2|2020|19|0|United_States_of_America|US|USA|327167434.0
7118|21/02/2020|21|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7119|20/02/2020|20|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7120|19/02/2020|19|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7121|18/02/2020|18|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7122|17/02/2020|17|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7123|16/02/2020|16|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7124|15/02/2020|15|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7125|14/02/2020|14|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7126|13/02/2020|13|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7127|12/02/2020|12|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7128|11/02/2020|11|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7129|10/02/2020|10|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7130|09/02/2020|9|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7131|08/02/2020|8|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7132|07/02/2020|7|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7133|06/02/2020|6|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7134|05/02/2020|5|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7135|04/02/2020|4|2|2020|0|0|United_States_of_America|US|USA|327167434.0
7136|03/02/2020|3|2|2020|3|0|United_States_of_America|US|USA|327167434.0
7137|02/02/2020|2|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7138|01/02/2020|1|2|2020|1|0|United_States_of_America|US|USA|327167434.0
7139|31/01/2020|31|1|2020|1|0|United_States_of_America|US|USA|327167434.0
7140|30/01/2020|30|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7141|29/01/2020|29|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7142|28/01/2020|28|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7143|27/01/2020|27|1|2020|3|0|United_States_of_America|US|USA|327167434.0
7144|26/01/2020|26|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7145|25/01/2020|25|1|2020|1|0|United_States_of_America|US|USA|327167434.0
7146|24/01/2020|24|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7147|23/01/2020|23|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7148|22/01/2020|22|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7149|21/01/2020|21|1|2020|1|0|United_States_of_America|US|USA|327167434.0
7150|20/01/2020|20|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7151|19/01/2020|19|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7152|18/01/2020|18|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7153|17/01/2020|17|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7154|16/01/2020|16|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7155|15/01/2020|15|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7156|14/01/2020|14|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7157|13/01/2020|13|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7158|12/01/2020|12|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7159|11/01/2020|11|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7160|10/01/2020|10|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7161|09/01/2020|9|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7162|08/01/2020|8|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7163|07/01/2020|7|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7164|06/01/2020|6|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7165|05/01/2020|5|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7166|04/01/2020|4|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7167|03/01/2020|3|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7168|02/01/2020|2|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7169|01/01/2020|1|1|2020|0|0|United_States_of_America|US|USA|327167434.0
7170|31/12/2019|31|12|2019|0|0|United_States_of_America|US|USA|327167434.0
sqlite>

Все данные в столбце countriesAndTerritories совпадают. United_States_of_America уже здесь! Мы успешно экспортировали данные из DataFrame в файл базы данных SQLite.

Что дальше?

Мы только что импортировали данные из CSV в DataFrame pandas, выбрали подмножество этих данных затем сохраняло их в реляционной базе данных.

Вам следует взглянуть на Изучение панд путем изучения данных о COVID-19 учебник, чтобы узнать больше о том, как выбирать подмножества данных из DataFrame большего размера или перейдите на страницу pandas для больше руководств от остального сообщества Python.

Вы также можете получить представление о том, что кодировать дальше в своем проекте Python, чтение Страница полного содержания Python.

Вопросов? Свяжитесь со мной через Twitter @fullstackpython или @mattmakai . Я также на GitHub с имя пользователя mattmakai .

Что-то не так с этим сообщением? Вилка исходный код этой страницы на GitHub и отправьте запрос на перенос.