Как удалить и вставить строки на основе столбца с помощью SQLAlchemy в Python?

Мой код читает некоторые файлы 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 для доступа к базе данных.

Почему в Python есть оператор "pass"?
Почему в Python есть оператор "pass"?
Оператор pass в Python - это простая концепция, которую могут быстро освоить даже новички без опыта программирования.
Некоторые методы, о которых вы не знали, что они существуют в Python
Некоторые методы, о которых вы не знали, что они существуют в Python
Python - самый известный и самый простой в изучении язык в наши дни. Имея широкий спектр применения в области машинного обучения, Data Science,...
Основы Python Часть I
Основы Python Часть I
Вы когда-нибудь задумывались, почему в программах на Python вы видите приведенный ниже код?
LeetCode - 1579. Удаление максимального числа ребер для сохранения полной проходимости графа
LeetCode - 1579. Удаление максимального числа ребер для сохранения полной проходимости графа
Алиса и Боб имеют неориентированный граф из n узлов и трех типов ребер:
Оптимизация кода с помощью тернарного оператора Python
Оптимизация кода с помощью тернарного оператора Python
И последнее, что мы хотели бы показать вам, прежде чем двигаться дальше, это
Советы по эффективной веб-разработке с помощью Python
Советы по эффективной веб-разработке с помощью Python
Как веб-разработчик, Python может стать мощным инструментом для создания эффективных и масштабируемых веб-приложений.
1
0
31
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

ОБНОВЛЕННЫЙ ОТВЕТ:

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

Начальное состояние: В целях тестирования инициализируйте основную таблицу базы данных 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)вместо этого

Другие вопросы по теме