我正在编写一个脚本,从MongoDB中获取数据并将其转储到PostgreSQL中。一切都很顺利,但当我尝试插入MongoDB的ObjectId
由psycopg2生成的查询如下所示。
_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列表是要插入的记录列表。