How to Insert Data Into an SQLite Database Using Python

Import the sqlite3 module.

import sqlite3

It is a built-in module; we don’t have to install it separately. We only need to import it and use it.

Create SQLite database connection.

connect = sqlite3.connect("test.db")

The .connect() method creates a connection to connect with the specified SQLite database; in our case, it is test.db. You may rename the database name keeping the following syntax in mind.

sqlite3.connect('database_name.db')

Get a cursor object to execute SQL queries.

cursor = connect.cursor()

The connect.cursor() method creates a cursor object that we can use to execute SQL queries to manipulate the specified database, whether it is about creating a table, inserting data, updating data, etc.

Create a STUDENT table.

std_table = """CREATE TABLE STUDENT( FIRSTNAME VARCHAR(255), LASTNAME VARCHAR(255));"""
cursor.execute(std_table)

Here, we first design our CREATE TABLE query and save it in the std_table. Next, we pass std_table to the cursor.execute() method, which executes the specified query.

Insert data into the STUDENT table.

cursor.execute("""INSERT INTO STUDENT VALUES ('Mehvish', 'Ashiq')""")
cursor.execute("""INSERT INTO STUDENT VALUES ('Raza', 'Tahir')""")
cursor.execute("""INSERT INTO STUDENT VALUES ('Hina', 'Mukhtar')""")

Here, we use the cursor.execute() method to run INSERT queries.

Note that we don’t have to store our query in a separate variable while creating the STUDENT table. Still, we can pass the SQL query to the cursor.execute() method as we did for the INSERT statements above.

Show the inserted data.

print("The 'STUDENT' Table Data:")
table_data = cursor.execute("""SELECT * FROM STUDENT""")
for row in table_data:
    print(row)

Again, we use the cursor.execute() method to run the SELECT query and save all the table data in table_data that we will use to loop over and print each row.

OUTPUT:

The 'STUDENT' Table Data:
('Mehvish', 'Ashiq')
('Raza', 'Tahir')
('Hina', 'Mukhtar')

Commit and close the connection.

connect.commit()
connect.close()

The .commit() will commit the latest changes in our currently selected database, while .close() will close the connection. The complete source code is given below.