Python SQLite Insert Data

In this article, we will show how to insert data into a SQLite database table.

We use the SQL INSERT INTO statement to insert a new row in a table.

Syntax

The first method is to specify only the values of data to be inserted without the column names specified.

INSERT INTO table_name VALUES (value1, value2, value3,…);

tablename: name of the table.
value1, value2, value3 : value of first column, second column, and so on for the new record

In the second method is to specify the columns which we want to fill and their corresponding values.

INSERT INTO tablename (column1, column2, column3,..) VALUES ( value1, value2, value3,..);

tablename: the name of the table.
column1: name of first column, second column and so on…
value1, value2, value3 : value of first column, second column, and so on for the new record

Inserting Data

To add records to an existing table in SQLite database you do the following

1. Import the sqlite3 package.
2. Create a connection object using the connect() method by passing the name of the database as a parameter to it.
3. Create a cursor object by invoking the cursor() object on the (above created) Connection object.
4. Then, invoke the execute() method on the cursor object, by passing an INSERT statement as a parameter to it.

Lets see that in action

Examples

Basic example

Lets specify the columns which we want to fill and their corresponding values.

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('country1.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#insert data
cursor.execute('''INSERT INTO COUNTRY(NAME, CAPITAL, DOMAIN, POPULATION) 
   VALUES ('France', 'Paris', 'fr', 67413000)''')

#Commit your changes in the database
conn.commit()

print("Records inserted........")

#Closing the connection
conn.close()

Which displayed the following

>>> %Run sqliteinsertdata1.py
Records inserted……..

Example 2

This is an example that specifies only the values of data to be inserted without the column names specified

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('country1.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

# Queries to INSERT records.
cursor.execute('''INSERT INTO COUNTRY VALUES ('Germany', 'Berlin', 'de', 83190556)''')
cursor.execute('''INSERT INTO COUNTRY VALUES ('Spain', 'Madrid', 'es', 47450795)''')
cursor.execute('''INSERT INTO COUNTRY VALUES ('Italy', 'Rome', 'it', 60317116)''')

#Commit your changes in the database
conn.commit()

print("3 Records inserted........")

#Closing the connection
conn.close()

This displayed the following

>>> %Run sqliteinsertdata2.py
3 Records inserted……..