如何将 ObjectId 插入到 PostgreSQL 数据库中

Question

我正在编写一个脚本，从MongoDB中获取数据并将其转储到PostgreSQL中。一切都很顺利，但当我尝试插入MongoDB的ObjectId _id时，它会出现以下错误。

(psycopg2.errors.SyntaxError) trailing junk after numeric literal at or near "63711d"

我想将_id插入PostgreSQL数据库作为主键，以便不会存在重复行。

由psycopg2生成的查询如下所示。

[SQL: INSERT INTO employees (_id, candidate_last_name, candidate_first_name, candidate_state, candidate_experience, candidate_relocation, candidate_skills, candidate_specialty)VALUES (6375364d1ad809ab6108a544, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (6375364d1ad809ab6108a545, NULL, NULL, NULL, NULL, NULL, NULL, NULL)]

在PostgreSQL中，_id字段是VARCHAR类型。

我正在使用的代码如下：

def insert_psql(db, table_name: str, fields: dict, data: list):

    new_fields = {}
    for field in fields:
        new_fields[field.replace('.', '_')] = fields[field]
    insert_query = f'INSERT INTO {table_name} ('
    insert_query += ', '.join(new_fields.keys()) + ')'
    insert_query += 'VALUES '
    for i, row in enumerate(data):
        insert_query += '('
        for j, field in enumerate(fields):
            if not row.get(field):
                insert_query += 'NULL'
            else:
                insert_query += f'{str(row.get(field))}'
            if not j == len(fields) - 1:
                insert_query += ', '
        insert_query += ')'
        if not i == len(data) - 1:
            insert_query += ', '
    # print(insert_query)
    try:
        db.execute(insert_query)
        db.commit()
    except Exception as e:
        print(e)

fields字典是一个包含列名和它们的数据类型作为值的字典。data列表是要插入的记录列表。

- Abdulaziz Ibrahim

---

你如何生成插入语句？如果在PostgreSQL中这是一个VARCHAR，那么在生成的语句中该值应该用引号括起来。 - Mark Rotteveel

你能在帖子中添加生成此查询的代码吗？ - Pompedup

我正在动态生成插入查询。我已经在帖子中添加了此代码。 - Abdulaziz Ibrahim

@MarkRotteveel，你有解决这个错误的想法吗？ - Abdulaziz Ibrahim

你不应该通过字符串拼接来创建语句。应该使用带参数的预处理语句。你当前的代码极易受到 SQL 注入攻击。 - Mark Rotteveel

Answer 1

你的错误代码是：trailing junk after numeric literal at or near "63711d" 具体来说，它警告数字文字后出现意外字符。在输出文本中，我们可以看到五个数字（63711）后跟着字符a。似乎代码试图将此组字符解析为数字，并在找到第一个字母字符后无法执行此操作。

实际上，当我们查看生成的SQL语句时，可以看到：

VALUES (6375364d1ad809ab6108a544,

如果您正在尝试插入字符串（VARCHAR），那么@Mark Rotteveel在这个问题的第一个评论中说了您需要做的事情：

如果这是PostgreSQL中的VARCHAR，则生成的语句中的值应该用引号括起来。

您的INSERT语句应该像这样：

VALUES ('6375364d1ad809ab6108a544',

已编辑为使用单引号而非双引号。此外，Mark提出了关于这种方法的另一个重要警告，请在此处查看。

- user20042973

---

它应该看起来像 VALUES ('6375364d1ad809ab6108a544',（单引号，而不是双引号）。 - Mark Rotteveel

糟糕，已修复！同时在评论中指出了你关于整个方法的警告。 - user20042973