作者:大眼妹886 | 来源:互联网 | 2023-08-10 22:20
如何防止插入重复数据和处理程序创建表错误。这是我的代码:
insert_query = '''INSERT INTO dbo.Tp_tabkeu(report_number,condition,power_error,status,Specification_dBM,CW_frequency) VALUES (?,?,?)
WHERE report_number!=values(a),condition!=values(b),power_error!=values(c),status!=values(d),Specification_dBM!=values(e),CW_frequency!=values(f);'''
for index,row in df.iterrows():
values = (row['report_num'],row['condition'],row[0],row[1],row[2],row[3])
INSERT INTO ... VALUES ... WHERE ...
是无效的语法。但是,您可以使用INSERT INTO ... SELECT ... WHERE ...
。
您已经确认您正在使用SQL Server,并且df.iterrows()
表示您正在使用熊猫,因此,您实际上也应该使用SQLAlchemy。 (当使用除SQLite以外的数据库时,pandas依赖于SQLAlchemy。)
此示例在名为[team]的表上执行条件INSERT ...
prov city team_name
---- ------- -----------
ON Toronto Maple Leafs
...,以(省,省)作为主键。
import sqlalchemy as sa
# ...
engine = sa.create_engine(connection_url)
# ...
sql = sa.text("""\
INSERT INTO team (prov,city,team_name)
SELECT :prov AS prov,:city AS city,:team_name AS team_name
WHERE NOT EXISTS (
SELECT * FROM team WHERE prov = :prov AND city = :city)
""")
params = {'prov': 'AB','city': 'Calgary','team_name': 'Flames'}
engine.execute(sql,params)