数据库已经创建(PostgreSQL)
有一个清单:
data =
['param_1', 0],
['amp', 0],
['voltage', 1],
['params', 1],
['antenna', 1],
['freq', 0.00011000000085914508]
我尝试过
import psycopg2
from psycopg2 import sql
with psycopg2.connect(dbname='db_name', user='postgres',
password='123', host='localhost') as conn:
conn.autocommit = True
with conn.cursor() as cur:
query = "INSERT INTO table_name (%s) VALUES (%s);"
cur.executemany(query, data)
我需要在包含名为以下字段的数据库的表中插入值:
'param_1': 'paRam_2' e.t.c.
如何生成查询字符串?
我将很高兴为您提供任何帮助,在此先感谢您。
最佳答案
参数替换只能用于传递列值,不能用于传递列名,因此我们需要构建一个列名列表以插入到SQL命令文本中。具体来说,我们需要
构建以逗号分隔的列名字符串
建立一个逗号分隔的参数占位符字符串
创建INSERT命令,包括上面的两项
创建(数值)参数值的元组
执行命令
看起来像这样:
# create environment
# ~~~~~~~~~~~~~~~~~~
data = (
['param_1', 0],
['amp', 0],
['voltage', 1],
['params', 1],
['antenna', 1],
['freq', 0.00011000000085914508]
)
# example code
# ~~~~~~~~~~~~
columns = ','.join([f'"{x[0]}"' for x in data])
print(columns)
# "param_1","amp","voltage","params","antenna","freq"
param_placeholders = ','.join(['%s' for x in range(len(data))])
print(param_placeholders)
# %s,%s,%s,%s,%s,%s
sql = f"INSERT INTO table_name ({columns}) VALUES ({param_placeholders})"
print(sql)
# INSERT INTO table_name ("param_1","amp","voltage","params","antenna","freq") VALUES (%s,%s,%s,%s,%s,%s)
param_values = tuple(x[1] for x in data)
print(param_values)
# (0, 0, 1, 1, 1, 0.00011000000085914508)
cur.execute(sql, param_values)
关于python - 如何将列表中的数据动态添加到数据库(PostgreSQL),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/58418007/