Мой код читает некоторые файлы Excel и добавляет их в список, а также добавляет столбец для извлечения имени файла. Затем он объединяет все и отправляет в таблицу.
all_df_list = []
for file in files_list:
#reads and appends excel files
frame = pd.read_excel(pd.read_excel(file, header=0, engine='openpyxl'))
frame['filename'] = os.path.basename(file)
all_df_list.append(frame)
xls=pd.concat(all_df_list)
xls.to_sql(table, con=engine, if_exists='append', index=False, chunksize=10000)
Этот код работает просто отлично, и вывод выглядит примерно так:
Колонка А | Колонка Б | имя фильма |
---|---|---|
Первый | ряд | файл 01.xlsx |
Второй | ряд | файл 02.xlsx |
Теперь мне нужно изменить мой код, чтобы проверить это имя файла в столбце ( (os.path.basename(file)
) и либо перезаписать только те строки, либо удалить строки, где filename = (os.path.basename(file)
, а затем выполнить приведенный выше код. Например, если это имя файла существует, удалите соответствующие строки и выполните этот код выше, что-то в этом роде.
Любые идеи, как я могу это сделать? Я использую метод create_engine из sqlalchemy для доступа к базе данных.
ОБНОВЛЕННЫЙ ОТВЕТ:
Предполагая, что вы хотите свести к минимуму работу, выполняемую на стороне базы данных, и максимально использовать фреймы данных, это приведет к тому же результату, что и мой первоначальный ответ (далее).
Начальное состояние:
В целях тестирования инициализируйте основную таблицу базы данных SomeTable
, чтобы смоделировать ситуацию, в которой она была обновлена ранее:
xls=pd.concat([
pd.DataFrame({'Column A':['First'], 'Column B':['row'], 'filename':['file 01.xlsx']}),
pd.DataFrame({'Column A':['Second'], 'Column B':['row'], 'filename':['file 02.xlsx']}),
pd.DataFrame({'Column A':['Third'], 'Column B':['row'], 'filename':['file 03.xlsx']})
])
xls.to_sql("SomeTable", con=engine, if_exists='replace', index=False, chunksize=10000)
Содержимое SomeTable
:
Column A Column B filename
0 First row file 01.xlsx
1 Second row file 02.xlsx
2 Third row file 03.xlsx
Источник обновлений:
Укажите файлы, которые будут использоваться для обновления SomeTable
в базе данных, и загрузите их в dfBasenames
:
files_list = ['file 01.xlsx', 'file 04.xlsx']
import os
# accumulate new file names in files_list into "new_file"
basenames = set()
for file in files_list:
basenames.add(os.path.basename(file))
dfBasenames = pd.DataFrame({'filename': list(basenames)})
Содержимое dfBasenames
:
filename
0 file 04.xlsx
1 file 01.xlsx
Удалить устаревшие строки:
Удалить строки в df
со значением столбца имени файла, найденным в dfBasenames
:
df = df.join(dfBasenames.assign(is_stale=True).set_index('filename'), on='filename')
df = df[df['is_stale'].isna()].drop(columns=['is_stale'])
Содержимое df
:
Column A Column B filename
1 Second row file 02.xlsx
2 Third row file 03.xlsx
Обратите внимание, что исходная строка с filename == "file 01.xlsx"
была удалена.
Обновление df
из последних файлов:
Совокупные строки, найденные в файлах Excel в files_list
и concat
вместе с неустаревшими строками в df
:
all_df_list = []
for file in files_list:
#reads and appends excel files
frame = pd.read_excel(file)
frame['filename'] = os.path.basename(file)
print(f'\nfile {file}:\n{frame}')
all_df_list.append(frame)
df = pd.concat([df] + all_df_list)
Содержимое df
:
Column A Column B filename
1 Second row file 02.xlsx
2 Third row file 03.xlsx
0 1.a.01 1.b.01 file 01.xlsx
1 1.a.02 1.b.02 file 01.xlsx
0 4.a.01 4.b.01 file 04.xlsx
1 4.a.02 4.b.02 file 04.xlsx
Зафиксировать изменения в базе данных:
Перезаписать SomeTable
в базе данных с помощью df
:
df.to_sql("SomeTable", con=engine, if_exists='replace', index=False, chunksize=10000)
Содержимое SomeTable
:
Column A Column B filename
0 Second row file 02.xlsx
1 Third row file 03.xlsx
2 1.a.01 1.b.01 file 01.xlsx
3 1.a.02 1.b.02 file 01.xlsx
4 4.a.01 4.b.01 file 04.xlsx
5 4.a.02 4.b.02 file 04.xlsx
Полный тестовый код:
from sqlalchemy import create_engine
import pandas as pd
connectable = 'sqlite:///foo.db'
engine = create_engine(connectable)
# For testing purposes only, initialize SomeTable so it's not empty
xls=pd.concat([
pd.DataFrame({'Column A':['First'], 'Column B':['row'], 'filename':['file 01.xlsx']}),
pd.DataFrame({'Column A':['Second'], 'Column B':['row'], 'filename':['file 02.xlsx']}),
pd.DataFrame({'Column A':['Third'], 'Column B':['row'], 'filename':['file 03.xlsx']})
])
xls.to_sql("SomeTable", con=engine, if_exists='replace', index=False, chunksize=10000)
# read and print SomeTable for verification of initial state
df = pd.read_sql_table("SomeTable", connectable)
print('======== VERIFY INITIAL STATE: read_sql_table("SomeTable"):')
print(df)
# specify files to use to update SomeTable in the database
files_list = ['file 01.xlsx', 'file 04.xlsx']
import os
# accumulate new file names in files_list into "new_file"
basenames = set()
for file in files_list:
basenames.add(os.path.basename(file))
dfBasenames = pd.DataFrame({'filename': list(basenames)})
print('======== VERIFY FILENAMES TO UPDATE FROM:')
print(dfBasenames)
# delete rows in SomeTable copy with filename column found in dfBasenames
df = df.join(dfBasenames.assign(is_stale=True).set_index('filename'), on='filename')
df = df[df['is_stale'].isna()].drop(columns=['is_stale'])
print('======== VERIFY DELETION OF ROWS MATCHING NEW FILENAMES:')
print(df)
# aggregate rows found in Excel files in dfBasenames into all_df_list and concat to remaining non-stale rows of SomeTable copy
all_df_list = []
for file in files_list:
#reads and appends excel files
frame = pd.read_excel(file)
frame['filename'] = os.path.basename(file)
print(f'\nfile {file}:\n{frame}')
all_df_list.append(frame)
df = pd.concat([df] + all_df_list)
print('======== VERIFY UPDATED DF READY TO COMMIT TO DB:')
print(df)
# overwrite SomeTable in database
df.to_sql("SomeTable", con=engine, if_exists='replace', index=False, chunksize=10000)
# read and print table for verification of correct result
df = pd.read_sql_table("SomeTable", connectable)
print('======== VERIFY UPDATED TABLE: read_sql_table("SomeTable"):')
print(df)
Тестовый вывод:
======== VERIFY INITIAL STATE: read_sql_table("SomeTable"):
Column A Column B filename
0 First row file 01.xlsx
1 Second row file 02.xlsx
2 Third row file 03.xlsx
======== VERIFY FILENAMES TO UPDATE FROM:
filename
0 file 04.xlsx
1 file 01.xlsx
======== VERIFY DELETION OF ROWS MATCHING NEW FILENAMES:
Column A Column B filename
1 Second row file 02.xlsx
2 Third row file 03.xlsx
file file 01.xlsx:
Column A Column B filename
0 1.a.01 1.b.01 file 01.xlsx
1 1.a.02 1.b.02 file 01.xlsx
file file 04.xlsx:
Column A Column B filename
0 4.a.01 4.b.01 file 04.xlsx
1 4.a.02 4.b.02 file 04.xlsx
======== VERIFY UPDATED DF READY TO COMMIT TO DB:
Column A Column B filename
1 Second row file 02.xlsx
2 Third row file 03.xlsx
0 1.a.01 1.b.01 file 01.xlsx
1 1.a.02 1.b.02 file 01.xlsx
0 4.a.01 4.b.01 file 04.xlsx
1 4.a.02 4.b.02 file 04.xlsx
======== VERIFY UPDATED TABLE: read_sql_table("SomeTable"):
Column A Column B filename
0 Second row file 02.xlsx
1 Third row file 03.xlsx
2 1.a.01 1.b.01 file 01.xlsx
3 1.a.02 1.b.02 file 01.xlsx
4 4.a.01 4.b.01 file 04.xlsx
5 4.a.02 4.b.02 file 04.xlsx
ОРИГИНАЛЬНЫЙ ОТВЕТ:
Вот способ сделать то, что вы просили.
Начальное состояние: (То же, что и в ОБНОВЛЕННОМ ОТВЕТЕ выше.)
Источник обновлений:
Укажите файлы, которые будут использоваться для обновления SomeTable
в базе данных, и загрузите их во временную таблицу базы данных new_file
:
files_list = ['file 01.xlsx', 'file 04.xlsx']
basenames = set()
for file in files_list:
basenames.add(os.path.basename(file))
dfBasenames = pd.DataFrame({'filename': list(basenames)})
dfBasenames.to_sql("new_file", con=engine, if_exists='replace', index=False, chunksize=10000)
Содержимое new_file
:
filename
0 file 01.xlsx
1 file 04.xlsx
Удалить устаревшие строки:
Удалить строки в SomeTable
со значением столбца имени файла, найденным в таблице new_file
:
with engine.connect() as connection:
result = connection.execute('delete from SomeTable where exists (select 1 from new_file where new_file.filename = SomeTable.filename)')
Содержимое SomeTable
:
Column A Column B filename
0 Second row file 02.xlsx
1 Third row file 03.xlsx
Обратите внимание, что исходная строка с filename == "file 01.xlsx"
была удалена.
Обновите базу данных из последних файлов:
Агрегируйте строки, найденные в файлах Excel, в files_list
и добавьте их в SomeTable
:
all_df_list = []
for file in files_list:
#reads and appends excel files
frame = pd.read_excel(file)
frame['filename'] = os.path.basename(file)
all_df_list.append(frame)
xls = pd.concat(all_df_list)
xls.to_sql("SomeTable", con=engine, if_exists='append', index=False, chunksize=10000)
Содержимое SomeTable
:
Column A Column B filename
0 Second row file 02.xlsx
1 Third row file 03.xlsx
2 1.a.01 1.b.01 file 01.xlsx
3 1.a.02 1.b.02 file 01.xlsx
4 4.a.01 4.b.01 file 04.xlsx
5 4.a.02 4.b.02 file 04.xlsx
Полный тестовый код: Тестовый код (с операторами печати):
from sqlalchemy import create_engine
import pandas as pd
connectable = 'sqlite:///foo.db'
engine = create_engine(connectable)
# For testing purposes only, initialize SomeTable so it's not empty
xls=pd.concat([
pd.DataFrame({'Column A':['First'], 'Column B':['row'], 'filename':['file 01.xlsx']}),
pd.DataFrame({'Column A':['Second'], 'Column B':['row'], 'filename':['file 02.xlsx']}),
pd.DataFrame({'Column A':['Third'], 'Column B':['row'], 'filename':['file 03.xlsx']})
])
xls.to_sql("SomeTable", con=engine, if_exists='replace', index=False, chunksize=10000)
# read and print SomeTable for verification of initial state
df = pd.read_sql_table("SomeTable", connectable)
print('======== VERIFY INITIAL STATE: read_sql_table("SomeTable"):')
print(df)
# specify files to use to update SomeTable in the database
files_list = ['file 01.xlsx', 'file 04.xlsx']
import os
# accumulate new file names in files_list into "new_file"
basenames = set()
for file in files_list:
basenames.add(os.path.basename(file))
dfBasenames = pd.DataFrame({'filename': list(basenames)})
dfBasenames.to_sql("new_file", con=engine, if_exists='replace', index=False, chunksize=10000)
# read and print table for verification of correct result
df = pd.read_sql_table("new_file", connectable)
print('======== VERIFY FILENAMES TO UPDATE FROM: read_sql_table("new_file"):')
print(df)
# delete rows in SomeTable with filename column found in new_file table
with engine.connect() as connection:
result = connection.execute('delete from SomeTable where exists (select 1 from new_file where new_file.filename = SomeTable.filename)')
# read and print table for verification of correct result
df = pd.read_sql_table("SomeTable", connectable)
print('======== VERIFY DELETION OF ROWS MATCHING NEW FILENAMES: read_sql_table("SomeTable"):')
print(df)
# aggregate rows found in Excel files in files_list into all_df_list
all_df_list = []
for file in files_list:
#reads and appends excel files
frame = pd.read_excel(file)
frame['filename'] = os.path.basename(file)
print(f'\nfile {file}:\n{frame}')
all_df_list.append(frame)
# append rows in all_df_list to SomeTable
xls = pd.concat(all_df_list)
xls.to_sql("SomeTable", con=engine, if_exists='append', index=False, chunksize=10000)
# read and print table for verification of correct result
df = pd.read_sql_table("SomeTable", connectable)
print('======== VERIFY UPDATED TABLE: read_sql_table("SomeTable"):')
print(df)
Тестовый вывод:
======== VERIFY INITIAL STATE: read_sql_table("SomeTable"):
Column A Column B filename
0 First row file 01.xlsx
1 Second row file 02.xlsx
2 Third row file 03.xlsx
======== VERIFY FILENAMES TO UPDATE FROM: read_sql_table("new_file"):
filename
0 file 01.xlsx
1 file 04.xlsx
======== VERIFY DELETION OF ROWS MATCHING NEW FILENAMES: read_sql_table("SomeTable"):
Column A Column B filename
0 Second row file 02.xlsx
1 Third row file 03.xlsx
file file 01.xlsx:
Column A Column B filename
0 1.a.01 1.b.01 file 01.xlsx
1 1.a.02 1.b.02 file 01.xlsx
file file 04.xlsx:
Column A Column B filename
0 4.a.01 4.b.01 file 04.xlsx
1 4.a.02 4.b.02 file 04.xlsx
======== VERIFY UPDATED TABLE: read_sql_table("SomeTable"):
Column A Column B filename
0 Second row file 02.xlsx
1 Third row file 03.xlsx
2 1.a.01 1.b.01 file 01.xlsx
3 1.a.02 1.b.02 file 01.xlsx
4 4.a.01 4.b.01 file 04.xlsx
5 4.a.02 4.b.02 file 04.xlsx
Насколько я понимаю, вы хотите удалить строки на основе имени файла перед повторной вставкой строк. Может быть, это поможет вам
engine.execute("DELETE FROM %s WHERE filename = %s", (table, file))
если файл - это полный путь, вы должны использоватьos.path.basename(file)
вместо этого