Python Data Persistence – INSERT Statement

by

Python Data Persistence – INSERT Statement

Now that we have created tables in our database, let us add few records to them. SQL provides an INSERT statement for the purpose. Its standard syntax is as follows:

Example

INSERT INTO table name (coll, col2, ...) VALUES (vail, val2 , val3 , ...) ;

Name of the table in which a new record (row) is to be added, follows mandatory keywords INSERT INTO. The column list is given after the name in parentheses, which is followed by the VALUES clause. The data corresponding to each column is given in another set of parentheses. The following statement adds one record in the Products table:

sqlite> INSERT INTO Products (Name, Price) VALUES ('Laptop1, 40000);

We insert a row in the ‘Customers’ table by executing the following statement in the SQLite console:

sqlite> INSERT INTO Customers (Name, GSTIN) VALUES ('Ravikumar', 127AAJPL7103N1ZF');

Similarly, the following statement adds a record in the ‘Invoices’ table:

sqlite> INSERT INTO Invoices (CUSTID, PRODUCTID, Quantity) VALUES (1, 1, 2);

Note that, in the above INSERT statements, we have not included ProductID, CustID, and InvID columns in respective column list parentheses because they have been defined as autoincrement fields. The column list may be omitted altogether if you intend to provide values for all columns in the table (excluding autoincrement fields). They must be given in the VALUES list exactly in the same order in which their fields have been defined. You may add a few more records to these three tables. Sample data for these tables is given below: (table 7.3, table 7.4, and table 7.5)

Product ID Name Price
1 Laptop 25000
2 TV 40000
3 Router 2000
4 Scanner 5000
5 Printer 9000
6 Mobile 15000
CustID Name GSTIN
1 Ravikumar 27AAJPL7103N1ZF
2 Patel 24ASDFG1234N1ZN
3 Nitin 27AABBC7895N1ZT
4 Nair 32MMAF8963N1ZK
5 Shah 24BADEF2002N1ZB
6 Khurana 07KABCS1002N1Z V
7 Irfan 05IIAAV 5103N1ZA
8 Kiran 12PPSDF22431ZC
9 Divya 15ABCDE1101N1ZA
10 John 29AAEEC4258E1ZK
Inv ID CustID Product ID Quantity
1 1 1 2
2 10 2 1
3 9 6 3
4 4 1 6
5 10 5 3
6 2 2 5
7 2 1 4
8 5 3 10
9 7 5 2
10 3 4 3