获取记录并通过修改创建新记录

Question

使用python，psychopg2试图找出如何获取一条记录并通过修改该记录来创建一条新记录。我的一切都在运转，连接，创造新的记录。我被困在修改获取的记录的问题上

newrecord=cursor.fetchone（）

我现在被下一步的行动困住了。如何识别列以便修改它。Table是一个简单的表，具有id和true/false列。这似乎是一项简单的任务

import psycopg2

connection = psycopg2.connect(user = "postgres", 
password = "secret", database = "example")

cursor = connection.cursor()

cursor.execute('DROP TABLE IF EXISTS table3;')

cursor.execute ('''
CREATE TABLE table3 (
    id INTEGER PRIMARY KEY,
    completed BOOLEAN NOT NULL DEFAULT FALSE
);
''')

data0 = {
    'id': 1,
    'completed': True
}
data = {
    'id': 2,
    'completed': False
}
data2 = {
    'id': 3,
    'completed': False
}
data3 = {
    'id': 4,
    'completed': True
}
data4 = {
    'id': 5,
    'completed': True
}
SQL = 'INSERT INTO table3 (id,completed) values (%(id)s, %(completed)s);'

cursor.execute (SQL, data0)
cursor.execute (SQL, data)
cursor.execute (SQL, data2)
cursor.execute (SQL, data3)
cursor.execute (SQL, data4)

cursor.execute('SELECT * FROM table3;')
newrecord = cursor.fetchone()


print(newrecord)
# cursor.execute(SQL, newrecord)
# result = cursor.fetchall()
# for row in result:
#     print (row)

connection.commit()

connection.close()
cursor.close()

Answer 1

虽然还不清楚您面临的是什么问题，但我正在尝试回答。
您可以从SELECT中INSERT以防止获取记录并将其“重新发送”到数据库：

import psycopg2

connection = psycopg2.connect("dbname=mf port=5959 host=localhost user=mf_usr")
cursor = connection.cursor()
cursor.execute('DROP TABLE IF EXISTS table3;')

cursor.execute ('''
CREATE TABLE table3 (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price INT,
    completed BOOLEAN NOT NULL DEFAULT FALSE
);
''')

data0 = {
    'id': 1,
    'completed': True
}
data = {
    'id': 2,
    'completed': False
}
data2 = {
    'id': 3,
    'completed': False
}
data3 = {
    'id': 4,
    'completed': True
}
data4 = {
    'id': 5,
    'completed': True
}

# Insert 5 rows
SQL = 'INSERT INTO table3 (id,name,price,completed) values (%(id)s, %(name)s, %(price)s, %(completed)s);'
for i in range(0, 5):
    cursor.execute (SQL, {'id': i, 'price': (i+1)*1000, 'name': f"data_{i}", 'completed': True})


# COPY a record, just modifying id and completed.
COPY_SQL = """
    INSERT INTO table3 (id, name, price, completed)
    (SELECT %(id)s, name, price, %(completed)s from table3 where name=%(name)s)
"""

COPY_VALUES = {'id': 6, 'name': 'data_0', 'completed': False}
cursor.execute(COPY_SQL, COPY_VALUES)

# FETCH: We end up with 2 records for data_n with completed True and False!
cursor.execute("SELECT * FROM table3 where name = '%(name)s'" % COPY_VALUES)
print(cursor.fetchall())

connection.commit()
connection.close()
cursor.close()

输出：

[(0, 'data_0', 1000, True), (6, 'data_0', 1000, False)]