SQLite Forum

How to insert certain table_1 column value in table_2 along with new user input values
Login

How to insert certain table_1 column value in table_2 along with new user input values

(1.1) By Lalremruata Khiangte (Lalremruata) on 2021-06-25 00:52:46 edited from 1.0 [link] [source]

I am stuck and request help. I have an Sqlite3 table and code with PyQt5 in PyCharm. I want to insert id's latest row of table_1 into a "import" column in table_2 simultaneously when a button is click to make new record for/in table_2. [Fetch table_1 id's latest row number and insert into "import" column along with name1 and name2 (user input) text value in table_2 when a button is click.]

Table_1:
CREATE TABLE "table_1" ( "id" INTEGER UNIQUE, "first_name" TEXT, "last_name" TEXT, PRIMARY KEY("id" AUTOINCREMENT) )

id | first_name | last_name |
---|------------|-----------|
1  | Mike       |  Spiker   |
2  | John       |  lastname |

Table_2:
CREATE TABLE "table_2" ( "import" INTEGER, "name1" TEXT, "name2" TEXT )

import | name1      |  name2    |
-------|------------|-----------|
1      | apple      |  self     |
1      | mango      |  import   |
2      | tomato     |  stock    |
2      | potato     |  trans    |

I have google many website but could not find clue/answer to my problem. There are some code to insert value(s) into another table but they missed out on how to insert into along with capturing the txt_name1 & txt_name2 user's input values.

Thanks in advance.

(2) By Larry Brasfield (larrybr) on 2021-06-25 00:46:51 in reply to 1.0 [link] [source]

One change which will help is to create table_1 thusly: CREATE TABLE table_1 ( id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT ) , which will ensure that the "ROWID" is identical to the id. The utility of that identity should become obvious as you study the last_insert_rowid() function, which you will probably want to use for that 2nd insert operation.

You should perform both inserts within the same transaction unless you can be absolutely certain that no other inserts can be made to happen between your two, linked inserts.

You probably don't need the AUTOINCREMENT feature. The ROWID values are going to be unique and normally increment anyway except when deletions permit reuse of their old values. Likewise, there is no reason to declare a primary key to be unique, and sometimes there is reason not to do so. (That's another topic.)

(3) By Keith Medcalf (kmedcalf) on 2021-06-25 00:50:05 in reply to 1.0 [link] [source]

INSERT INTO table_2 SELECT max(id), :name1, :name2 FROM table_1;

where :name1 and :name2 are bound variables.

(4) By Lalremruata Khiangte (Lalremruata) on 2021-06-25 01:23:15 in reply to 3 [source]

Thanks for the quick response Keith Medcalf. I may not express my problem clearly because of my poor English knowledge. Let me try to explain more clearly (I hope):

I have two tables: table_1 & table_2. I successfully inserted into table_1 like this:

        conn = sqlite3.connect('new_db.db')
        c = conn.cursor()

        gst = self.txt_GST.text()
        vat = self.txt_VAT.text()
        oTax = self.txt_oTax.text()

        c.execute("INSERT INTO table_1(gst, vat, oTax) VALUES (?, ?,?)",
                  (gst, vat, oTax))
        conn.commit()
        self.label_notice.setText("<font color='blue'>Added successfully</font>")

table_1:
id | gst     | vat   | oTax  |
---|---------|-------|-------|
1  | 500     | 200   | 150   |
2  | 200     | 100   | 50    |

Now, I want to insert into table_2 several user input along with latest id of table_1

table_2:
import | item_name     | item_code   | cost_price  |
-------|---------------|-------------|-------------|
1      | apple         | app1        | 50          |
1      | mango         | mgo1        | 40          |
1      | jeans         | J1          | 500         |
2      | T-shirt       | TS123       | 480         |
2      | Baby Powder   | pwd3        | 850         |

How can I automatically insert into "import" column the value of table_1 latest row id and a new user input for item_name, item_code and cost_price..... etc., etc.

(5) By Keith Medcalf (kmedcalf) on 2021-06-25 01:36:55 in reply to 4 [link] [source]

c.execute('insert into table_2 select max(id), ?, ?, ? from table_1', (item_name, item_code, cost_price))

(6) By Lalremruata Khiangte (Lalremruata) on 2021-06-25 02:19:08 in reply to 5 [link] [source]

Thanks Keith Medcalf. It works like a charm. I am very grateful to you.

(7.1) By Lalremruata Khiangte (Lalremruata) on 2021-06-25 03:13:46 edited from 7.0 in reply to 5 [link] [source]

Here again Keith Medcalf, when I try in my project, I face a problem. I cannot make it to working.

I have 4 tables: (1) Daily_Transaction (2) cogs (3) goods (4) user

CREATE TABLE "cogs" (
"cogs_id"	INTEGER UNIQUE,
"VAT"	INTEGER,
"GST"	INTEGER,
"other_taxes"	INTEGER,
"labor_cost"	INTEGER,
"transportation_cost"	INTEGER,
"surcharge"	INTEGER,
PRIMARY KEY("cogs_id" AUTOINCREMENT)
);

A button_1 confirms:

conn = sqlite3.connect('dawr.db')
        c = conn.cursor()

        VAT = self.txt_vat.text()
        #GST = int(self.txt_gst.text())
        GST = self.txt_gst.text()
        other_taxes = self.txt_other_tax.text()
        labor_cost = self.txt_labor_cost.text()
        transportation_cost = self.txt_transport_cost.text()
        surcharge = self.txt_surcharge.text()

        c.execute(
            "INSERT INTO cogs(VAT, GST, other_taxes, labor_cost, transportation_cost, surcharge) VALUES (?,?,?,?,?,?)",
            (VAT, GST, other_taxes, labor_cost, transportation_cost, surcharge))


        conn.commit()
        conn.close()

It worked.

The other table:

CREATE TABLE "goods" (
"reference"	INTEGER,
"item_id"	INTEGER NOT NULL UNIQUE,
"item_name"	TEXT NOT NULL UNIQUE,
"item_code"	TEXT UNIQUE,
"item_description"	TEXT,
"purchase_date"	TEXT,
"quantity"	INTEGER NOT NULL,
PRIMARY KEY("item_id" AUTOINCREMENT)
);

Now, the button_2 (here is my problem)

conn = sqlite3.connect('dawr.db')
        c = conn.cursor()

        itemName = self.lineEdit_3.text()
        itemDesc = self.lineEdit_6.text()
        itemCode = self.lineEdit_11.text()
        itemCP = self.txt_cost_price.text()
        itemQnty = self.txt_quantity.text()

        c.execute("INSERT INTO goods select max(cogs_id), ?, ?, ?, ?, ?, ?, ? from cogs",
                  (itemName, itemDesc, itemCode, itemCP, itemQnty))
        conn.commit()
        conn.close()

        self.label_5.setText("<font color='red'>You've successfully records item. Please continue adding more item "
                             "until you finish.</font>")

I want to insert into reference column in goods table the value of cogs_id from table cogs.

I cannot point out my mistake and couldn't solve all alone. (I am a self learner.) Could you please help me out again?

(8) By MBL (RoboManni) on 2021-06-25 06:04:02 in reply to 7.1 [link] [source]

"INSERT INTO goods select max(cogs_id), ?, ?, ?, ?, ?, ?, ? from cogs", (itemName, itemDesc, itemCode, itemCP, itemQnty)

Count the number of parameters supplied and compare with the number of parameters expected: 7 expected but only 5 supplied. I guess this is your problem.