How to insert data in database of SQLite3 database engine in Python

hi, how are you today




Till now we discussed
How  to create database in sqlite3 database engine in Python
How  to create table in database in sqlite3 database engine in Python.

So today, taking one more step further, we will discuss how to insert data in table inside database of SQLite3 database engine.
We are continuing with the same example of student database that we have discussed in previous post of creating table.

Inserting Data in database

Data to be inserted in the table is in form of  "Tuple". Inserting data in database means we are inserting a new row with fixed no of columns( columns created at time of table creation).

In brief, Tuple is a collection of heterogeneous data, written inside ().

Syntax/Query to insert data in database

As explained earlier, curs is optional not mandatory while creating table and inserting data but mandatory for reading, updating and deleting data.

Using cursor

 curs=conn.cursor()  
 curs.execute("insert into table_name(col_name , col_name, col_name,)values(?,?,?)",(data1,data2,data3))    

or

 curs=conn.cursor()  
 curs.execute("insert into table_name values(?,?,?)",(data1,data2,data3))  

Here I have mentioned two ways:

If you want to enter data in specific columns, means you don't want to enter all columns data, then use first option and mention the column names in which you want to insert data. No of "?" must be equal to no of columns in whose name you have mentioned followed by the same  no of data elements as tuple eg (data1,data2,data3) is a tuple and no of elements in tuple is equal to no of "?".

If you want to enter data in all columns the directly use the second method.No. of  "?" must be equal to no of  actual columns in the table followed by same count of data in tuple format.

 conn.execute("insert into table_name(col_name , col_name, col_name,)values(?,?,?)",(data1,data2,data3))  

or

 conn.execute("insert into table_name values(?,?,?)",(data1,data2,data3))  

The two ways have same functionality as described above
combine code for how to create database and table

1:  s_id=input("Roll No: ")  
2:  s_name=int(input("Name:"))  
3:  s_class=int(input("Class:"))  
4:  s_scholarship=float(input("Enter scholarship: "))  
5:  import sqlite3 as sql  
6:  conn=sql.connect("mydatabase.db")  
7:  conn.execute("insert into St_data values(?,?,?,?)",(s_id,s_name,s_class,s_scholarship))  
8:  print("data entered ")  
9:  conn.commit()  
10:  conn.close()  

or

1:  s_id=int(input("Roll No: "))  
2:  s_name=input("Name:")  
3:  s_class=int(input("Class:"))  
4:  s_scholarship=float(input("Enter scholarship: "))  
5:  import sqlite3 as sql  
6:  conn=sql.connect("mydatabase.db")  
7:  curs=conn.cursor()  
8:  curs.execute("insert into St_data values(?,?,?,?)",(s_id,s_name,s_class,s_scholarship))  
9:  print("data entered ")  
10:  conn.commit()  
11:  conn.close()  

So above example is about how to insert data in database of SQLite3 database engine.

[*NOTE: entering the data with same primary key will raise exception as already said, primary key is unique, so no duplication allowed. To overcome this issue we have to handle exception. We will handle exception later in a single program. Meanwhile visit Exception Handeling  ]

Do not forget to save the data if you do any type of modification in table of database, i.e. inserting,updating and deleting. And to save use

 conn.commit()  

Always remember to close the connection before closing or quitting your software

 conn.close()  

Hope you all like this post and I wish this proves useful to you.
Keep sharing & give your views about post in comments.
Comments are always welcomed for better improvement.

Thanks :)