Insert Data Into an SQLite Database Using Python

Insert Data Into an SQLite Database Using Python

This tutorial educates about Python’s built-in sqlite3 module to create an SQLite database connection, create a table, and insert data into that table.

Use the sqlite3 Module to Insert Data Into an SQLite Database Using Python

We must follow the subsequent steps to insert data in the SQLite database table.

  • 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.

{{ % /step %}}

Complete Source Code

import sqlite3

connect= sqlite3.connect('test.db')
cursor = connect.cursor()

std_table ="""CREATE TABLE STUDENT( FIRSTNAME VARCHAR(255), LASTNAME VARCHAR(255));"""
cursor.execute(std_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')''')

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

connect.commit()
connect.close()

OUTPUT:

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

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook

Related Article - Python SQLite

  • Install Sqlite3 Using Pip
  • Show Tables in SQLite
  • Sqlite3.OperationalError: Unable to Open Database File
  • Unlock the SQLite Database
  • Install SQLite in Python