1

I am having an issue with placing the right qoutes in the following sqlite statement , which places the customer id(c_id) , a primary key of customers table , into the foreign key c_id of place table.

I could extract the c_id by un-nesting the code , and executing the portion 'select c_id from customers where fname =?" separately . However I want more conciseness in my code .

t = (fname,)
place = (postal_code , place_name)
cur.execute(""insert into place(postal_code ,place_name,c_id) values(?,?,("select c_id from customers where fname =?"),t)""",place)

The quotes are all out of place , help is appreciated for a fix .

Btw: This question is a similar duplicate of this question Insert Data Into Tables Linked by Foreign Key , from which I copied the idea of nesting .

Community
  • 1
  • 1
motiur
  • 1,640
  • 9
  • 33
  • 61

2 Answers2

0

I'm completely guessing here but have you tried.

cur.execute("insert into place(postal_code ,place_name,c_id) values(?,?,(select c_id from customers where fname = ?))", (postal_code , place_name, fname))

Edit See this question Insert into ... values ( SELECT ... FROM ... )

INSERT INTO TableA (colA, colB, colC) SELECT ?, ?, (SELECT c_id FROM customers WHERE fname = ?)
Community
  • 1
  • 1
John
  • 13,197
  • 7
  • 51
  • 101
  • Its an issue with the inverted commas in python . I have to put them correctly so that the compiler understands that the select clause inside the values is a sql statement not something else . – motiur Nov 01 '12 at 04:15
  • Well it gives the error: cur.execute("insert into place(postal_code ,place,c_id) values(?,?,(select c_id from customers where fname =?))",place,fname) TypeError: function takes at most 2 arguments (3 given) – motiur Nov 01 '12 at 04:20
0

The solution for this problem lies not in putting the right amount of quotation but in the form of the sqlite statement .

One solution is to design the variable place with three values, one of them with the foreign key of the customer id(c_id)

place = (postal_code , place , f_name) 

cur.execute("insert into place(postal_code ,place,f_name) values(?,?,(select c_id from customers where fname =?))",place)
motiur
  • 1,640
  • 9
  • 33
  • 61