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 moduleimport sqlite3 # Connecting to sqliteconn = sqlite3.connect('Lazyroar2.db') # Creating a cursor object using the # cursor() methodcursor = conn.cursor() # Creating tabletable ="""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 insertedprint("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 connectionconn.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 moduleimport sqlite3 # Connecting to sqliteconn = sqlite3.connect('geek.db') # Creating a cursor object using the # cursor() methodcursor = conn.cursor() # Creating tabletable ="""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 insertedprint("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 connectionconn.close() |
Output:
SQLite3:
