Панды to_sql делают индекс уникальным

Question

Я читал о решениях to_sql для панд, чтобы не добавлять дубликаты записей в базу данных. Я работаю с CSV-файлами журналов, каждый раз, когда я загружаю новый файл журнала, я затем читаю данные и делаю некоторые изменения с пандами, создавая новый фрейм данных. Затем я выполняю to_sql('Logs',con = db.engine, if_exists = 'append', index=True). С аргументом if_exists я проверяю, каждый раз, когда новый созданный кадр данных из нового файла добавляется к существующей базе данных. Проблема в том, что он добавляет дублирующие значения. Я хочу убедиться, что если файл, который уже был загружен, по ошибке загружен снова, он не будет добавлен в базу данных. Я хочу попробовать сделать это напрямую при создании базы данных, не находя обходного пути, например, просто проверяя, не использовалось ли ранее имя файла.

Я работаю с колбой-sqlalchemy.

Спасибо.

Answer 1

Лучше всего поймать дубликаты, настроив индекс в качестве первичного ключа, а затем используя try/except поймать нарушения уникальности. Вы упомянули еще один пост, который предложил смотреть IntegrityError исключения, и я согласен, что это лучший подход. Вы можете комбинировать это с функцией дедупликации, чтобы обеспечить бесперебойную работу обновлений таблицы.

Демонстрация проблемы

Вот игрушечный пример:

from sqlalchemy import *
import sqlite3

# make a database, 'test', and a table, 'foo'.
conn = sqlite3.connect("test.db")
c = conn.cursor()
# id is a primary key.  this will be the index column imported from to_sql().
c.execute('CREATE TABLE foo (id integer PRIMARY KEY, foo integer NOT NULL);')
# use the sqlalchemy engine.
engine = create_engine('sqlite:///test.db')

pd.read_sql("pragma table_info(foo)", con=engine)

   cid name     type  notnull dflt_value  pk
0    0   id  integer        0       None   1
1    1  foo  integer        1       None   0

Теперь два примера фреймов данных, df а также df2:

data = {'foo':[1,2,3]}
df = pd.DataFrame(data)
df
   foo
0    1
1    2
2    3

data2 = {'foo':[3,4,5]}
df2 = pd.DataFrame(data2, index=[2,3,4])
df2
   foo
2    3       # this row is a duplicate of df.iloc[2,:]
3    4
4    5

Переехать df в стол foo:

df.to_sql('foo', con=engine, index=True, index_label='id', if_exists='append')

pd.read_sql('foo', con=engine)
   id  foo
0   0    1
1   1    2
2   2    3

Теперь, когда мы пытаемся добавить df2ловим IntegrityError:

try:
    df2.to_sql('foo', con=engine, index=True, index_label='id', if_exists='append')
# use the generic Exception, both IntegrityError and sqlite3.IntegrityError caused trouble.
except Exception as e: 
    print("FAILURE TO APPEND: {}".format(e))

Выход:

FAILURE TO APPEND: (sqlite3.IntegrityError) UNIQUE constraint failed: foo.id [SQL: 'INSERT INTO foo (id, foo) VALUES (?, ?)'] [parameters: ((2, 3), (3, 4), (4, 5))]

Предлагаемое решение

На IntegrityErrorВы можете извлечь существующие данные таблицы, удалить повторяющиеся записи ваших новых данных, а затем повторить оператор добавления. использование apply() за это:

def append_db(data):
    try:
        data.to_sql('foo', con=engine, index=True, index_label='id', if_exists='append')
        return 'Success'
    except Exception as e:
        print("Initial failure to append: {}\n".format(e))
        print("Attempting to rectify...")
        existing = pd.read_sql('foo', con=engine)
        to_insert = data.reset_index().rename(columns={'index':'id'})
        mask = ~to_insert.id.isin(existing.id)
        try:
            to_insert.loc[mask].to_sql('foo', con=engine, index=False, if_exists='append')
            print("Successful deduplication.")
        except Exception as e2:
            "Could not rectify duplicate entries. \n{}".format(e2)
        return 'Success after dedupe'

df2.apply(append_db)

Выход:

Initial failure to append: (sqlite3.IntegrityError) UNIQUE constraint failed: foo.id [SQL: 'INSERT INTO foo (id, foo) VALUES (?, ?)'] [parameters: ((2, 3), (3, 4), (4, 5))]

Attempting to rectify...
Successful deduplication.

foo    Success after dedupe
dtype: object