Upsert/Append to SQL database using SQL Alchemy/Pandas.

huangapple
go评论136阅读模式
英文:

Upsert/Append to SQL database using SQL Alchemy/Pandas

问题

我正在使用Python 3.11和SSMS 19。

再次遇到了与我的项目有关的一大堆问题。我已经让它工作得相当好了，但我现在意识到它并没有更新，只是一遍又一遍地将相同的行添加到我的数据库中。这意味着如果主键匹配，它会拒绝所有条目，如果我移除PK约束，它不会更新，只会添加一行新的数据。

编辑：
只是为了澄清，我想按列而不是按行或条目来执行此操作。如果我必须逐个添加每个更新，那么我可能干脆不写这个程序，因为我每3天都要为大约300行编写更新行。我需要代码能够自动逐行进行操作，并根据列来更新、合并或忽略。

这是目前正在运行的代码，我已经通过SQLAlchemy创建了引擎/连接。

  1. fulldf.to_sql('Shortage', engine, if_exists='append', index=False, chunksize=200, dtype={'Priority': BigInteger,
  2.                                                                'PN': String(255),
  3.                                                                'Part_Type': Text,
  4.                                                                'MPN': Text,
  5.                                                                'Proposed_MPN': Text,
  6.                                                                'Distributor': String(100),
  7.                                                                'Reason_for_Add': Text,
  8.                                                                'Line_Down': Date,
  9.                                                                'Engineering-Samples_Available': Text,
  10.                                                                'Build_Samples-Available': Text,
  11.                                                                'Comments': Text,                                                                                       
  12.                                                                'Supplier_Comments': Text,
  13.                                                                'Circuit_Location': Text,
  14.                                                                'Last_Updated': Date

这对于初始表格效果很好，但如果我有主键重复的情况，就不太适用了。

我的主键（在SQL中限制，这里是为了方便）是'PN'，所有的主键都是唯一的。
有些列，比如'Part_Type'，'MPN'，'Distributor'等，我只想要覆盖/忽略。这些值不应该改变，如果它们改变了，那将是一个特例。

对于日期列，我希望旧日期被删除，然后添加新日期，我认为可以通过if_exists='update'或者if_exists='replace'来实现，不会太困难。

作为事后想法-我真的不在意SQL注入。这个程序将由大约5个受信任的人在本地Intranet上使用，如果有人真的想破坏一个简单的（不需要生产环境的）数据库，他们可以随时这样做。我宁愿不要在不需要的情况下让事情变得复杂。

我尝试了一些方法，特别是这个upsert方法：

https://blog.alexparunov.com/upserting-update-and-insert-with-pandas

由于我的笔记本电脑受到了严格的限制，所以这对我来说不起作用，所以PostgreSQL和psycopg2无法初始化。是的，我已经尝试安装了psycopg2-binary。

我只是需要一些指导，但在这里没有找到太多信息。

编辑：
以下是我要做的事情的示例，以便更清楚：

我有数据框中的这个条目：

  1. P/N:		Supplier_Comments:		Comments:
  2. 1-100116	"6-27-23 Review with Holly"	"24000 reserved"

然后我已经在数据库中的部分如下：

  1. P/N:		Supplier_Comments:		Comments:
  2. 1-100116	"6-29-23: Shipping 24k Oct"	"Next Dock Date 7/31"

我需要它变成：

  1. P/N:		Supplier_Comments:		Comments:
  2. 1-100116	"6-27-23 Review with Holly"	"24000 reserved"
  3. 		"6-29-23: Shipping 24k Oct"	"Next Dock Date 7/31"

而不是：

  1. 1-100116	"6-27-23 Review with Holly"	"24000 reserved"	
  2. 1-100116	"6-29-23: Shipping 24k Oct"	"Next Dock Date 7/31"

我也不想合并不同的主键。我只想让已经存在的主键与SQL条目合并。

我希望这可以自动完成，而不需要我浏览行来查找数据库中已经存在的主键。

英文:

I'm using Python 3.11 and SSMS 19.

Once again, I'm running into a ton of fun with my project. I've gotten it working reasonably well, but I realize now that it isn't updating, just adding the same lines over and over into my database. Meaning it rejects all of the entries if a primary key matches, and if I remove the PK constraint, it doesn't update, it just adds a new line.

Edit:
Just for clarification, I want to do this by column, not by row or entry. If I have to add each update individually, I might as well not even write the program, as I would be writing update lines for ~300 rows every 3 days. I need the code to go line by line by itself, and either update, merge, or ignore depending on the column.

Here's what's working right now, I have the engine/connection created through SQLAlchemy.

  1. fulldf.to_sql('Shortage',engine, if_exists='append', index=False, chunksize=200, dtype={'Priority': BigInteger,
  2.                                                                'PN':String(255),
  3.                                                                'Part_Type': Text,
  4.                                                                'MPN': Text,
  5.                                                                'Proposed_MPN': Text,
  6.                                                                'Distributor': String(100),
  7.                                                                'Reason_for_Add': Text,
  8.                                                                'Line_Down': Date,
  9.                                                                'Engineering-Samples_Available': Text,
  10.                                                                'Build_Samples-Available': Text,
  11.                                                                'Comments': Text,                                                                                       
  12.                                                                'Supplier_Comments': Text,
  13.                                                                'Circuit_Location': Text,
  14.                                                                'Last_Updated': Date

This works awesome for the initial table, and if I didn't have any Primary Key repeats. Unfortunately I do and I will, so it's not great for that.

My primary key (constrained in SQL, not here for ease of use) is 'PN', of which all are unique.
Some columns like 'Part_Type','MPN','Distributor', etc I just want to be overwritten/ignored. Those values shouldn't change and if they do, that will be a case-by-case basis.

For the Date columns, I want the old date removed and the other added in, which I believe can be accomplished with if_exists='update' or perhaps if_exists='replace'? Either way, that wont be too much of a hurdle.

As an afterthought- I really don't care about SQL injection here. This program is going to be used by roughly 5 trusted people on the local intranet, and if someone really wants to blow up a simple (unnecessary for production) database, they're more than welcome to. I'd rather not complicate something when I don't need to.

I've tried a few things, namely this upsert method:

https://blog.alexparunov.com/upserting-update-and-insert-with-pandas

Which does not work for me as my laptop is locked down harder than Fort Knox, so PostgreSQL and psycopg2 can't initialize. And yes, I've tried installed psycopg2-binary.

I'm just needing some guidance and haven't found much on here.

Edit:
Here's an example for clarity of what I'm looking to do:

I have this entry in the dataframe:

  1. P/N:		Supplier_Comments:		Comments:
  2. 1-100116	"6-27-23 Review with Holly	"24000 reserved

and then I have the part that's already in the database:

  1. P/N:		Supplier_Comments:		Comments:
  2. 1-100116	"6-29-23: Shipping 24k Oct"	"Next Dock Date 7/31"

I need it to become:

  1. P/N:		Supplier_Comments:		Comments:
  2. 1-100116	"6-27-23 Review with Holly	"24000 reserved
  3. 		6-29-23: Shipping 24k Oct"	"Next Dock Date 7/31"

NOT

  1. 1-100116	"6-27-23 Review with Holly	"24000 reserved	
  2. 1-100116	"6-29-23: Shipping 24k Oct"	"Next Dock Date 7/31"

I also don't want to combine different primary keys, either. I just want primary keys that already exist to be joined with the SQL entry.

I want this to be done automatically, without me having to go through the rows to find which primary keys already exist in the database.

答案1

得分: 1

你可以使用.to_sql()将更新上传到临时表，然后使用MERGE来更新主表。

  1. import pandas as pd
  2. import sqlalchemy as sa
  4. engine = sa.create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql_199")
  6. # 为此示例设置环境
  7. with engine.begin() as conn:
  8.     conn.exec_driver_sql("DROP TABLE IF EXISTS Shortage")
  9.     conn.exec_driver_sql(
  10.         """
  11.     CREATE TABLE Shortage (
  12.     PN varchar(255) primary key,
  13.     Part_Type varchar(50),
  14.     Comments varchar(max)
  15.     )
  16.     """
  17.     )
  18.     conn.exec_driver_sql(
  19.         """
  20.     INSERT INTO Shortage (PN, Part_Type, Comments)
  21.     VALUES ('Part_A', 'original type', 'A comment.')
  22.     """
  23.     )
  25. # 更新表格
  26. fulldf = pd.DataFrame(
  27.     [
  28.         ("Part_A", "updated type", "Another comment."),
  29.         ("Part_B", "some other type", "This is a new part."),
  30.     ],
  31.     columns=["PN", "Part_Type", "Comments"],
  32. )
  33. with engine.begin() as conn:
  34.     fulldf.to_sql("#temp_table", conn, if_exists="replace", index=False)
  35.     conn.exec_driver_sql(
  36.         """
  37.     MERGE Shortage WITH (HOLDLOCK) AS main
  38.     USING (SELECT PN, Part_Type, Comments FROM #temp_table) AS temp
  39.     ON (main.PN = temp.PN)
  40.     WHEN MATCHED THEN
  41.       UPDATE SET 
  42.         Part_Type = temp.Part_Type, 
  43.         Comments = main.Comments + ' ' + temp.Comments
  44.     WHEN NOT MATCHED THEN
  45.       INSERT (PN, Part_Type, Comments) VALUES (temp.PN, temp.Part_Type, temp.Comments);
  46.     """
  47.     )
  49. # 检查结果
  50. print(pd.read_sql_table("Shortage", engine))
英文:

You can use .to_sql() to upload the updates to a temporary table and then use MERGE to update the main table.

  1. import pandas as pd
  2. import sqlalchemy as sa
  4. engine = sa.create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql_199")
  6. # set up environment for this example
  7. with engine.begin() as conn:
  8.     conn.exec_driver_sql("DROP TABLE IF EXISTS Shortage")
  9.     conn.exec_driver_sql(
  10.         """\
  11.     CREATE TABLE Shortage (
  12.     PN varchar(255) primary key,
  13.     Part_Type varchar(50),
  14.     Comments varchar(max)
  15.     )
  16.     """
  17.     )
  18.     conn.exec_driver_sql(
  19.         """\
  20.     INSERT INTO Shortage (PN, Part_Type, Comments)
  21.     VALUES ('Part_A', 'original type', 'A comment.')
  22.     """
  23.     )
  25. # update the table
  26. fulldf = pd.DataFrame(
  27.     [
  28.         ("Part_A", "updated type", "Another comment."),
  29.         ("Part_B", "some other type", "This is a new part."),
  30.     ],
  31.     columns=["PN", "Part_Type", "Comments"],
  32. )
  33. with engine.begin() as conn:
  34.     fulldf.to_sql("#temp_table", conn, if_exists="replace", index=False)
  35.     conn.exec_driver_sql(
  36.         """\
  37.     MERGE Shortage WITH (HOLDLOCK) AS main
  38.     USING (SELECT PN, Part_Type, Comments FROM #temp_table) AS temp
  39.     ON (main.PN = temp.PN)
  40.     WHEN MATCHED THEN
  41.       UPDATE SET 
  42.         Part_Type = temp.Part_Type, 
  43.         Comments = main.Comments + ' ' + temp.Comments
  44.     WHEN NOT MATCHED THEN
  45.       INSERT (PN, Part_Type, Comments) VALUES (temp.PN, temp.Part_Type, temp.Comments);
  46.     """
  47.     )
  49. # check results
  50. print(pd.read_sql_table("Shortage", engine))
  51. """
  52.        PN        Part_Type                     Comments
  53. 0  Part_A     updated type  A comment. Another comment.
  54. 1  Part_B  some other type          This is a new part.
  55. """

huangapple
  • 本文由 发表于 2023年7月27日 23:36:43
  • 转载请务必保留本文链接：https://go.coder-hub.com/76781372.html
  • dataframe
  • pandas
  • python-3.x
  • sql-server
  • sqlalchemy
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定