DataFrame.to_sql(name, con, *, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)
name - строка с именем таблицы SQL.
con - объект соединения SQLAlchemy позволяет использовать любую БД, поддерживаемую этой библиотекой. Для объектов sqlite3.Connection предусмотрена устаревшая поддержка. Если передать sqlalchemy.engine.Connection, который уже находится в транзакции, то транзакция не будет зафиксирована. При передаче sqlite3.Connection невозможно будет отменить вставку записи.
Пользователь несет ответственность за закрытие соединения для подключаемого
SQLAlchemy.
schema=None - схема (если версия базы данных поддерживает это). Если None, то использует схему по умолчанию.
if_exists='fail' - как вести себя, если таблица уже существует.
fail: вызовет исключение ValueError.replace: удалит и снова создаст таблицу перед вставкой новых значений.append: вставить новые значения в существующую таблицу.index=True - запишет индекс DataFrame в виде столбца. Использует index_label в качестве имени столбца в таблице.
index_label=None - строка с меткой столбца для создания индекса в таблице SQL. Если None (по умолчанию), а индекс имеет значение True, то используются имена индексов DataFrame. Также принимает список меток столбцов для создания MultiIndex.
chunksize=None - если указано, то возвращает итератор, где chunksize - количество строк, включаемых в каждый фрагмент.
dtype=None - желаемый тип данных для столбцов результирующего DataFrame. Например, np.float64 (пытается применить ко всем столбцам) или словарь {’a’: np.float64, ‘b’: np.int32, ‘c’: ‘Int64’}, где ключ является именем столбца. Аргумент игнорируется, если вместо запроса передается таблица.
method=None - управляет вставкой SQL:
None: используется стандартное предложение SQL INSERT ... (по одному на строку).multi: передать несколько значений в одном предложении INSERT.(pd_table, conn, keys, data_iter)DataFrame.to_sql() или `None, если вызываемый объект, переданный в метод, не возвращает целое число строк.Количество затронутых возвращаемых строк представляет собой con.rowcount подключаемого объекта sqlite3.Cursor или объект SQLAlchemy, который может не отражать точное количество записанных строк.
Метод DataFrame.to_sql() модуля pandas пишет записи, хранящиеся в DataFrame, в базу данных SQL. Поддерживаются базы данных, используемые в SQLAlchemy. Таблицы можно создавать заново, добавлять к ним или перезаписывать.
Поднимает исключение ValueError, когда таблица уже существует и аргумент if_exists имеет значение 'fail' (по умолчанию).
Примеры подключений к БД, а также другую дополнительную информацию смотрите в описании функции
pandas.read_sql().
Создадим базу данных SQLite в памяти.
>>> from sqlalchemy import create_engine >>> engine = create_engine('sqlite://', echo=False)
Создадим с нуля DataFrame из 3 строк.
>>> import pandas as pd >>> df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']}) >>> df # name # 0 User 1 # 1 User 2 # 2 User 3 >>> df.to_sql(name='users', con=engine) # 3 >>> from sqlalchemy import text >>> with engine.connect() as conn: # если возникают ошибки, то обновите `SQLAlchemy` # например, pip install -U SQLAlchemy ... conn.execute(text("SELECT * FROM users")).fetchall() # [(0, 'User 1'), (1, 'User 2'), (2, 'User 3')]
Аргумент con также принимает sqlalchemy.engine.Connection:
>>> with engine.begin() as connection: ... df1 = pd.DataFrame({'name' : ['User 4', 'User 5']}) ... # добавим записи из нового `df1` ... df1.to_sql(name='users', con=connection, if_exists='append') # 2
Это разрешено для поддержки операций, требующих, чтобы одно и то же соединение DBAPI использовалось для всей операции.
>>> df2 = pd.DataFrame({'name' : ['User 6', 'User 7']}) # добавим записи из нового `df2` >>> df2.to_sql(name='users', con=engine, if_exists='append') # 2 >>> with engine.connect() as conn: ... conn.execute(text("SELECT * FROM users")).fetchall() # [(0, 'User 1'), (1, 'User 2'), (2, 'User 3'), # (0, 'User 4'), (1, 'User 5'), (0, 'User 6'), # (1, 'User 7')]
Перезапишем таблицу, используя только df2.
>>> df2.to_sql(name='users', con=engine, if_exists='replace', ... index_label='id') # 2 >>> with engine.connect() as conn: ... conn.execute(text("SELECT * FROM users")).fetchall() # [(0, 'User 6'), (1, 'User 7')]
Используем метод вставки (аргумент method), чтобы ничего не делать в случае конфликта первичного ключа в таблице в базе данных PostgresSQL.
>>> from sqlalchemy.dialects.postgresql import insert # определим функцию >>> def if_conflict_nothing(table, conn, keys, data_iter): ... # 'a' является первичным ключом в 'conflict_table' ... data = [dict(zip(keys, row)) for row in data_iter] ... stmt = insert(table.table).values(data).on_conflict_do_nothing(index_elements=["a"]) ... result = conn.execute(stmt) ... return result.rowcount >>> df_conflict.to_sql(name="conflict_table", con=conn, if_exists="append", method=if_conflict_nothing) # 0
Вызываемый объект (функция) для обновления столбцов b и c в случае конфликта первичного ключа в таблице в базе данных MySQL:
>>> from sqlalchemy.dialects.mysql import insert >>> def if_conflict_update(table, conn, keys, data_iter): ... # обновим столбцы 'b' и 'c' при конфликте первичного ключа ... data = [dict(zip(keys, row)) for row in data_iter] ... stmt = ( ... insert(table.table) ... .values(data) ... ) ... stmt = stmt.on_duplicate_key_update(b=stmt.inserted.b, c=stmt.inserted.c) ... result = conn.execute(stmt) ... return result.rowcount >>> df_conflict.to_sql(name="conflict_table", con=conn, if_exists="append", method=if_conflict_update) # 2
Указываем желаемый dtype (особенно полезно для целых чисел с пропущенными значениями). Обратите внимание, что, хотя pandas вынужден хранить данные в виде чисел с плавающей запятой, база данных поддерживает целые числа с нулевым значением. При извлечении данных с помощью Python получаем обратно целочисленные скаляры.
>>> df = pd.DataFrame({"A": [1, None, 2]}) >>> df # A # 0 1.0 # 1 NaN # 2 2.0 >>> from sqlalchemy.types import Integer >>> df.to_sql(name='integers', con=engine, index=False, ... dtype={"A": Integer()}) # 3 >>> with engine.connect() as conn: ... conn.execute(text("SELECT * FROM integers")).fetchall() # [(1,), (None,), (2,)]