Python Data Persistence – INSERT Statement

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 IDNamePrice
1Laptop25000
2TV40000
3Router2000
4Scanner5000
5Printer9000
6Mobile15000
CustIDNameGSTIN
1Ravikumar27AAJPL7103N1ZF
2Patel24ASDFG1234N1ZN
3Nitin27AABBC7895N1ZT
4Nair32MMAF8963N1ZK
5Shah24BADEF2002N1ZB
6Khurana07KABCS1002N1Z V
7Irfan05IIAAV 5103N1ZA
8Kiran12PPSDF22431ZC
9Divya15ABCDE1101N1ZA
10John29AAEEC4258E1ZK
Inv IDCustIDProduct IDQuantity
1112
21021
3963
4416
51053
6225
7214
85310
9752
10343