如何在SQLite DB中只插入来自Python和Pandas DF的新值?

wnvonmuf  于 2022-09-21  发布在  SQLite
关注(0)|答案(1)|浏览(148)

我正在尝试使用Python脚本和Pandas DF中的数据将新数据/值“插入”到我的SQLite数据库中。第一个插入可以很好地使用df.to_sql()cursor.execute("SQL STATEMENT")。但我的问题是在尝试导入/插入新数据时。

问题当我执行新的插入时,我要么得到重复的值,要么简单地替换DB值。使用其中一个数据列作为PK将不起作用,因为它们都不是真正唯一的。

目标我正在寻找的是只导入新值,例如;如果包含其所有值的行不存在插入否则忽略

问题当INSERT中的一些数据是重复的且不应插入,而某些数据是新数据且应插入时,将新值插入数据库的正确方式是什么?

项目背景、当前代码、我做了什么

代码的“版本1”工作正常,我按预期获得数据,并按预期将其插入到数据库中。我正在通过一个API获取外汇汇率,标准化JSON,并创建一个Pandas DF。我做了一些数据结构,并删除和添加了一些列。然后将其导入到数据库。(不确定当前的工作流程是否是最佳实践,对此提出任何建议也将不胜感激)


# Call API...Get data...Save as JSON...

url = f'https://min-api.cryptocompare.com/data/{timeframe}?fsym={coin}&tsym={fx_converter}&limit={limiter}'
data = json.loads(requests.get(url).text)

# Normalize JSON...Create DataFrame

df = pd.json_normalize(data, ['Data'])

一些额外的df结构代码,似乎与Q无关


# Database Connection

cnxn = sqlite3.connect("fx_rates.db")
cursor = cnxn.cursor()

# Create table

table = f""" CREATE TABLE IF NOT EXISTS {coin} 
    (
        time                INTEGER NOT NULL,
        high                REAL,
        low                 REAL,
        open                REAL,
        volumefrom          INTEGER,
        volumeto            INTEGER,
        close               REAL,
        conversionType      TEXT,
        conversionSymbol    TEXT,
        date                TEXT
    )"""

cursor.execute(table)
cnxn.commit()

# Insert data

col = tuple(df.columns)

for i, value in df.iterrows():
    cursor.execute(
    f"""
        INSERT OR IGNORE INTO {coin}{col} 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, 
    (
        value['time'],
        value['high'],
        value['low'],
        value['open'],
        value['volumefrom'],
        value['volumeto'],
        value['close'],
        value['conversionType'],
        value['conversionSymbol'],
        value['date']
    )
)

cnxn.commit()
cnxn.close()

如果我现在再次运行代码,它会得到重复的值和行。这两列都不是唯一的,可以是主键。尝试使用df.to_sql(sql, if_exists='append' / 'replace',但这是不正确的,因为它只会添加重复项或简单地删除所有旧值并放入新加载。

使用某种类型的Insert和Select似乎是一种选择,但我不知道如何编码。一直在寻找这样的东西,但不知道如何使用df作为‘表’?

""" 
    SELECT date, conversionSymbol
    FROM table1 s
    WHERE NOT EXISTS 
        (
            SELECT 1
            FROM table2 d
            WHERE d.open = s.open
        )
"""

我觉得我错过了一些基本的东西,这真的很容易解决。有一种“简单”的方法可以使用SQL语句来实现这一点。或者,当数据在Pandas中时,解决方案是不可能的,也许我需要使用一些ORM,如SQLalChemy,我在这方面没有经验。

问题当INSERT中的一些数据是重复的且不应插入,而某些数据是新数据且应插入时,将新值插入数据库的正确方式是什么?

dgiusagp

dgiusagp1#

您可以创建基于多个列的主键,请参见Sqlite primary key on multiple columns

或者,您可以基于多个列定义唯一索引。它们或多或少是等同的。

相关问题