Python SQLite – Insert Data

In this article, we will discuss how can we insert data in a table in the SQLite database from Python using the sqlite3 module. The SQL INSERT INTO statement of SQL is used to insert a new row in a table. There are two ways of using the INSERT INTO statement for inserting rows:

• Only values: The first method is to specify only the value of data to be inserted without the column names.

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

table_name: name of the table.

value1, value2,.. : value of first column, second column,… for the new record

• Column names and values both: In the second method we will specify both the columns which we want to fill and their corresponding values as shown below:

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

table_name: name of the table.

column1: name of first column, second column …

value1, value2, value3 : value of first column, second column,… for the new record

Example 1: Below is a program that depicts how to insert data in an SQLite table using only values. In the program, we first create a table named STUDENT and then insert values into it using the 1st syntax of the INSERT query. Finally, we display the content of the table and commit it to the database.

Python3

# Import module
import sqlite3

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

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

# Creating table
table ="""CREATE TABLE STUDENT(NAME VARCHAR(255), CLASS VARCHAR(255),
SECTION VARCHAR(255));"""
cursor.execute(table)

# Queries to INSERT records.
cursor.execute('''INSERT INTO STUDENT VALUES ('Raju', '7th', 'A')''')
cursor.execute('''INSERT INTO STUDENT VALUES ('Shyam', '8th', 'B')''')
cursor.execute('''INSERT INTO STUDENT VALUES ('Baburao', '9th', 'C')''')

# Display data inserted
print("Data Inserted in the table: ")
data=cursor.execute('''SELECT * FROM STUDENT''')
for row in data:
    print(row)

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

# Closing the connection
conn.close()

Output:

SQLite3:

Example 2: The below program is similar to that of the 1st program, but we insert values into the table by reordering the names of the columns with values as in the 2nd syntax.

Python3

# Import module
import sqlite3

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

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

# Creating table
table ="""CREATE TABLE STUDENT(NAME VARCHAR(255), CLASS VARCHAR(255),
SECTION VARCHAR(255));"""
cursor.execute(table)

# Queries to INSERT records.
cursor.execute(
  '''INSERT INTO STUDENT (CLASS, SECTION, NAME) VALUES ('7th', 'A', 'Raju')''')

cursor.execute(
  '''INSERT INTO STUDENT (SECTION, NAME, CLASS) VALUES ('B', 'Shyam', '8th')''')

cursor.execute(
  '''INSERT INTO STUDENT (NAME, CLASS, SECTION ) VALUES ('Baburao', '9th', 'C')''')

# Display data inserted
print("Data Inserted in the table: ")
data=cursor.execute('''SELECT * FROM STUDENT''')
for row in data:
    print(row)

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

# Closing the connection
conn.close()

Output:

SQLite3: