python sqlite how to insert some data from one row into another table with additional data from code

Question

so i need insert first_name and last_name from source table where row contains same uid from handler and insert to recordtable and add record_time.

cur.execute("""INSERT INTO recordtable (first_name,
last_name,
record_time)
SELECT first_name, last_name 
FROM sourcetable 
WHERE uid is ?""", ("some string from handler"))

Thats the best i can do but i still need add record_time from handler to that execute, how to do it?

here how tables look:

CREATE TABLE IF NOT EXISTS sourcetable (
uid TEXT NOT NULL UNIQUE,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL);

CREATE TABLE IF NOT EXISTS recordtable (
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
record_time DATETIME);

i mean i need just copy first_name, last_name from sourcetable and add record_time value to execute — JoinMOD, May 11 '21 at 19:37

forpas · Answer 1 · 2021-05-11T20:34:10.583

0

Use an INSERT INTO ... SELECT statement:

INSERT INTO recordtable(first_name, last_name, record_time) 
SELECT first_name, last_name, CURRENT_TIMESTAMP
FROM sourcetable
WHERE uid = ?

edited May 11 '21 at 20:34

answered May 11 '21 at 19:41

forpas

160,666
10
38
76

I dont need copy a whole entry from sourcetable... I have data some data from code like uid and record_time VALUE. So i say clearly, i need copy ONE row with uid what contains only first name and last name and insert to record table with record_time value frome code – JoinMOD May 11 '21 at 19:50
@JoinMOD your response to my comment under your question is: "i need just copy first_name, last_name from sourcetable and add record_time value". These are the columns of recordtable. What does uid have to do with what you want? – forpas May 11 '21 at 19:58
for that (i am on phone right now i have some trouble in typing new line) cur.execute("""INSERT INTO recordtable ( first_name, last_name, record_time ) SELECT first_name, last_name from sourcetable where uid is ?""", ("Some string from var from request"). Last thing i need is concat request time var but i dont know how to form this – JoinMOD May 11 '21 at 20:07
1

@JoinMOD edit your question and post sample data and expected results to clarify what you want. – forpas May 11 '21 at 20:08

score -1 · Accepted Answer · answered May 11 '21 at 21:26

never mind, i eventually found out that you can set a predefined values in SELECT operator

cur.execute("""INSERT INTO recordtable (first_name, last_name, record_time)
           SELECT first_name, last_name, ? FROM sourcetable WHERE uid is ?;""",
           (datetime.date(datetime.now()), self.__handle_uid))

this is my first experience with sql database for python

python sqlite how to insert some data from one row into another table with additional data from code

2 Answers2