Python SQLite - Insert Data
This article explains how to insert data into an SQLite table from Python using the sqlite3 module. The INSERT INTO statement is used to insert new rows into a table. There are two main methods for inserting data:
- Only values: Inserting data by specifying the values without column names.
- Column names and values: Specifying both column names and their corresponding values for insertion.
Let's discuss both these methods in detail with example:
1. Insert Data Using Only Values
In this approach, we insert data by specifying the values for all columns without mentioning column names.
Syntax:
INSERT INTO table_name VALUES (value1, value2, value3,...);
Example
Below is a program that depicts how to insert data in an SQLite table using only values. Then display the content of the table and commit it to the database.
import sqlite3
# Connect to SQLite database
conn = sqlite3.connect('geeks2.db')
cursor = conn.cursor()
# Create table
cursor.execute("""CREATE TABLE STUDENT(NAME VARCHAR(255), CLASS VARCHAR(255), SECTION VARCHAR(255))""")
# Insert data into the table
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 inserted data
print("Data Inserted in the table: ")
cursor.execute("SELECT * FROM STUDENT")
for row in cursor.fetchall():
print(row)
# Commit changes and close connection
conn.commit()
conn.close()
Output:
SQLite3:
2. Insert Data Using Column Names and Values
In the second method we will specify both the columns which we want to fill and their corresponding values.
Syntax:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Example
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.
import sqlite3
# Connect to SQLite database
conn = sqlite3.connect('geek.db')
cursor = conn.cursor()
# Create table
cursor.execute("""CREATE TABLE STUDENT(NAME VARCHAR(255), CLASS VARCHAR(255), SECTION VARCHAR(255))""")
# Insert data into the table using column names
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 inserted data
print("Data Inserted in the table: ")
cursor.execute("SELECT * FROM STUDENT")
for row in cursor.fetchall():
print(row)
# Commit changes and close connection
conn.commit()
conn.close()
Output:
SQLite3:
