Inserting into Table
To insert data into the table we will again write the SQL command as a string and will use the execute() method.
Example 1: Inserting Data into SQLite3 table using Python
Python3
# Python code to demonstrate table creation and# insertions with SQL# importing moduleimport sqlite3# connecting to the databaseconnection = sqlite3.connect("gfg.db")# cursorcrsr = connection.cursor()# SQL command to insert the data in the tablesql_command = """INSERT INTO emp VALUES (23, "Rishabh",\"Bansal", "M", "2014-03-28");"""crsr.execute(sql_command)# another SQL command to insert the data in the tablesql_command = """INSERT INTO emp VALUES (1, "Bill", "Gates",\"M", "1980-10-28");"""crsr.execute(sql_command)# To save the changes in the files. Never skip this.# If we skip this, nothing will be saved in the database.connection.commit()# close the connectionconnection.close() |
Output:
Example 2: Inserting data input by the user
Python3
# importing moduleimport sqlite3# connecting to the databaseconnection = sqlite3.connect("gfg.db")# cursorcrsr = connection.cursor()# primary keypk = [2, 3, 4, 5, 6]# Enter 5 students first namesf_name = ['Nikhil', 'Nisha', 'Abhinav', 'Raju', 'Anshul']# Enter 5 students last namesl_name = ['Aggarwal', 'Rawat', 'Tomar', 'Kumar', 'Aggarwal']# Enter their gender respectivelygender = ['M', 'F', 'M', 'M', 'F']# Enter their joining data respectivelydate = ['2019-08-24', '2020-01-01', '2018-05-14', '2015-02-02', '2018-05-14']for i in range(5): # This is the q-mark style: crsr.execute('INSERT INTO emp VALUES ({pk[i]}, "{f_name[i]}", "{l_name[i]}", "{gender[i]}", "{date[i]}")')# To save the changes in the files. Never skip this.# If we skip this, nothing will be saved in the database.connection.commit()# close the connectionconnection.close() |
Output:
SQL using Python
In this article, integrating SQLite3 with Python is discussed. Here we will discuss all the CRUD operations on the SQLite3 database using Python. CRUD contains four major operations –
Note: This needs a basic understanding of SQL.
Here, we are going to connect SQLite with Python. Python has a native library for SQLite3 called sqlite3. Let us explain how it works.