Insert and Select data from Database

Insert data into the database

Now let us discuss how to insert data into the table we have created earlier.

We are going to insert five values into our table at a time in the second try: and except: block that you see in the below picture. ID value of each and every single student's information should be different if not SQLite will give us error, because during table creation we made ID value as the primary key, it is designated to uniquely identify all table records and it cannot be null or empty.

And also if you see in the table creation code in our first try: and except: block I have made small modification which is, CREATE TABLE IF NOT EXISTS this modification will not create a table if there is a STUDENTINFO table that already exist.



Lets Code images

Lets Code Images

Code explanation

We are familiar with our try: and except: blocks as well as conn.execute(); function, our conn.execute(); function basically takes in the SQLite query as argument. And when interpreter come across conn.commit(); function, SQLite queries in our argument will be executed.

SQLite query to insert the data into our table is quite understandable but still below is the explanation.

"INSERT INTO STUDENTINFO VALUES (1, 'Alex', 'UCL', 'USA', 'BSc','male',25)"we are actually telling the SQLite to INSERT the following values into the name of the table we have created which is STUDENTINFO, do note that the string/text/char/varchar values should be within quotes as you see above.


SELECT data from the database

Since we are learning operation specific, I converted the operations we want to perform into functions and calling that functions at the end of the code.



Lets Code images

Code explanation

"SELECT id, Name, university, country, course, gender, age from STUDENTINFO" script will select all the data in the specified columns and return those values as array, so we get those values as array on a variable called cursor and iterate through that array to print out the values.

Below is the output of our code.

Lets Code Images

In the next article we will discuss how we can update the data in our table as well as delete selected data from our table.