使用 pandas 插入或更新 mysql 中是否存在

Question

我正在尝试将数据从 xlsx 文件插入到 mysqdl 表中。我想在表中插入数据，如果主键有重复项，我想更新现有数据，否则插入。我已经编写了脚本，但我意识到它的工作量太大，使用 pandas 很快。我怎样才能在熊猫中实现它？

 #!/usr/bin/env python3

import pandas as pd
import sqlalchemy

engine_str = 'mysql+pymysql://admin:mypass@localhost/mydb'
engine = sqlalchemy.create_engine(engine_str, echo=False, encoding='utf-8')\

file_name = "tmp/results.xlsx"
df = pd.read_excel(file_name)

原文由 r0xette 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

我可以想到两个选项，但 1 号可能更清洁/更快：

1. 让 SQL 决定更新/插入。检查 这个其他问题。您可以按“df”的行进行迭代，从 i=1 到 n 。在插入循环中，您可以编写如下内容：

 query = """INSERT INTO table (id, name, age) VALUES(%s, %s, %s)
ON DUPLICATE KEY UPDATE name=%s, age=%s"""
engine.execute(query, (df.id[i], df.name[i], df.age[i], df.name[i], df.age[i]))

1. 定义一个 python 返回 True 或 False --- 的函数，当记录存在时，然后在循环中使用它：

 def check_existence(user_id):
    query = "SELECT EXISTS (SELECT 1 FROM your_table where user_id_str = %s);"
    return list(engine.execute(query,  (user_id, ) ) )[0][0] == 1

您可以遍历行并在插入之前进行此检查

另请检查 此问题中的解决方案 以及 可能适用于您的情况的解决方案。

原文由 cd98 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

Pangres 是这项工作的工具。

此处概述： https ://pypi.org/project/pangres/

使用函数 pangres.fix_psycopg2_bad_cols 来“清理”DataFrame 中的列。

此处的代码/用法： https ://github.com/ThibTrip/pangres/wiki https://github.com/ThibTrip/pangres/wiki/Fix-bad-column-names-postgres 示例代码：

 # From: <https://github.com/ThibTrip/pangres/wiki/Fix-bad-column-names-postgres>
import pandas as pd

# fix bad col/index names with default replacements (empty string for '(', ')' and '%'):

df = pd.DataFrame({'test()':[0],
                   'foo()%':[0]}).set_index('test()')
print(df)

test()  foo()%
     0      0

# clean cols, index w/ no replacements
df_fixed = fix_psycopg2_bad_cols(df)

print(df_fixed)

test    foo
   0      0

# fix bad col/index names with custom replacements - you MUST provide replacements for '(', ')' and '%':

# reset df
df = pd.DataFrame({'test()':[0],
                   'foo()%':[0]}).set_index('test()')

# clean cols, index w/ user-specified replacements
df_fixed = fix_psycopg2_bad_cols(df, replacements={'%':'percent', '(':'', ')':''})

print(df_fixed)
test    foopercent
   0             0

只会修复/更正一些坏字符：

替换“%”、“(”和“)”（不能很好地播放甚至根本不能播放的字符）

但是，有用的是它处理清理和更新插入。

（ps，我知道这篇文章已有 4 年多的历史了，但是当搜索“pangres upsert determine number inserts and updates”作为最重要的 SO 结果时，它仍然显示在 Google 结果中，日期为 2020 年 5 月 13 日。）

原文由 RUDelerius 发布，翻译遵循 CC BY-SA 4.0 许可协议