时间戳

首页 > 解决方案 > 避免插入那些已经在 SQL 表中的记录

python - 避免插入那些已经在 SQL 表中的记录

问题描述

我正在尝试使用 sqlalchemy 将熊猫数据框插入 SQL。该表已经存在于数据库中，具有三列 ID、品牌和价格。ID 是标识列。如果品牌已经存在，我如何在从熊猫数据框中插入每一行之前检查。

    import pandas as pd

    cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
            'Price': [22000,25000,27000,35000]
            }

    df = pd.DataFrame(cars, columns = ['Brand', 'Price'])

    from sqlalchemy import create_engine, MetaData, Table, select
    from six.moves import urllib

    params = urllib.parse.quote_plus("DRIVER={SQL Server};SERVER=server;DATABASE=mydb;UID=user;PWD=psw")
    engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params) 
    engine.connect() 

    # suppose df is the data-frame that we want to insert in database
    df.to_sql(name='mytable',con=engine, index=False, if_exists='append')

    print("inserted)

标签： pythonpandassqlalchemy

解决方案

你真的在看一个有 30 年历史的关系数据库插入模式。如果它不在唯一索引中，则插入（自动增量不是有意义的唯一键）

我使用了 mariadb，但所有 DBMS 的方法都是相同的。只要坚持 SQL92 标准。

  1. 命名你的临时表
  2. 命名真实表
  3. 定义什么定义了唯一键

表定义

create table car (
    id double not null AUTO_INCREMENT,
    brand varchar(20) not null,
    price double,
    primary key (id, brand),
    unique key (brand)
)

python/sqlachemy 如果不存在则插入

import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, select

cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
            'Price': [22000,25000,27000,35000]
            }

df = pd.DataFrame(cars, columns = ['Brand', 'Price'])

engine = create_engine('mysql+pymysql://sniffer:sniffer@127.0.0.1/sniffer')

temptable = "temp"
table = "car"
key = ["Brand"]
df.to_sql(name=temptable,con=engine, index=False, if_exists='append')
conn = engine.connect()
transfersql = f"""insert into {table} ({",".join(df.columns)}) 
                 select * from {temptable} t 
                 where not exists 
                   (select 1 from {table} m 
                   where {"and".join([f" t.{col} = m.{col} " for col in key])}
                   )"""
print(transfersql)
conn.execute(transfersql)
conn.execute(f"drop table {temptable}")
conn.close()

输出（生成的 sql）

insert into car (Brand,Price) 
                 select * from temp t 
                 where not exists 
                   (select 1 from car m 
                   where  t.Brand = m.Brand 
                   )

推荐阅读