Python

Python SQLite Insert

A library that offers a compact disk-based database without the need for a separate/dedicated server process is called SQLite. This library is written in C programming.  The other database technologies like PostgreSQL or MySQL may be the ones that you are already familiar with. These employ a client-server architecture in which the database is set up as a server and is accessed by a client. In contrast, SQLite is referred to as an embedded DB because it is a library that is integrated into a program. Some functions enable you to execute the SQL statements or perform the other operations on the database and the data. In this article, you’ll discover how to use the sqlite3 module in a Python program to add or insert the rows to a table in an SQLite database.

How to Insert the Data in Python SQLite

SQLite is a C library as we mentioned in the introduction. However, there are interfaces created in many different languages, including Python. With a minimum requirement for SQLite 3.7.15, the sqlite3 module offers an SQL interface. The best part is that Python and sqlite3 are already included, so nothing needs to be installed.

The following steps are used to insert the data rows into a database table in an SQLite database:

  • The connection object’s cursor function should be called to create a Cursor object.
  • Run an INSERT statement. To pass the argument as an input to the INSERT statement, the question mark (?) is used as a placeholder for each argument.

The INSERT INTO command/statement can be used in two ways to insert the data:

  1. To insert the values only
  2. To insert both column names and values

First, it is required to connect to SQLite DB. The following are the steps to create or connect an SQLite database in Python.

SQLite Database Connection in Python

This section explains how to use Python’s sqlite3 module to create a connection to an SQLite DB. The following steps should be used to connect to SQLite:

  1. Import the Sqlite3 Module: To import sqlite3, we can use the “import sqlite3” statement. We may interact with the SQLite database using the functions and classes defined in the sqlite3 module.
  2. Use the Connect() Function: Use the database name in the connect() function of the connector class. You must input the database name of your choice to connect. It will establish a connection to the database file that you provide if it is already existent on the disk. However, SQLite creates a new database with the specified name if the file you requested for your SQLite database doesn’t exist yet. When a connection is established successfully, this function returns the Connection Object.
  3. Use the Cursor() Function: Create a cursor using the cursor() function of a “connection” class to run the SQLite commands or queries from Python.
  4. Use the Execute() Function: The SQL query is executed using the execute() functions and the output is returned.
  5. Extract Results: To read the results of a query, use the cursor.fetchall(), fetchmany(), or fetchone() function.
  6. Close the Cursor and Connection: After your job is finished, use the “cursor.close()” and “connection.close()” methods to close the cursor and the SQLite connections.

The following program script creates the new DB file “MyDB.db”, establishes a connection to it, and prints the information about the SQLite DB version:

Text Description automatically generated

Output:

As can be seen in the output, the database is created and the connection is now active. Using the defined functions, we obtained the version of our database.

We created our database. Now, we need a table so we can enter/insert the data rows inside the table.

Creating SQLite Table in Python

Now, we will explain how to use Python to construct a table in an SQLite database. In this example, the “db_table” is created inside the MyDB.db database. An SQLite table in Python can be created using the following steps:

  1. Use the sqlite3.connect() method to connect to SQLite.
  2. Create a table query.
  3. Use the cursor.execute(query) function to run the query.

Text Description automatically generated

Output:

In the previous script, we used a query to create a table. We created three columns inside the “id”, “name”, and “age” tables where “id” is specified as a primary key. Then, the cursor.execute() function is used to run the query.

Since we created both our SQLite database and table, we now insert the data inside the table using the following steps:

  1. Connect to an SQLite DB from Python using the sqlite3 module first.
  2. Create an SQL query (INSERT) to add the row data to a table. We specify the column names as well as their values to add to a table in the insert query.
  3. The next step is to build a cursor object using the connection.cursor() method. SQL queries can be run using cursor objects.
  4. The operation defined in the Insert query is carried out using the cursor.execute(query) method.
  5. Use the commit() method of the “connection” class to save the changes to a database when an insert operation is completed successfully.
  6. Use the cursor.rowcount method to determine the number of rows affected following a successful insert operation. The count is based on the total amount of rows that we insert.
  7. To observe the updated modifications, run an SQLite select query if needed.
  8. Cursor and SQLite connections should be closed using the “connection.close()” and “cursor.close()” methods once your work is finished.

Let’s insert some data to the MyDB database and db_table table since it is currently empty.

Example 1: Inserting the Values Only

We created the “MyDB” table with variables earlier. Now, we will add the values to the table only.

Syntax:

INSERT INTO table_name VALUES (va1, val2…);

Where:

table_name: Name of the table.

value1, value2.. : Values to insert in each column of table rows.

Output:

The example program on how to add the data into an SQLite table using only the values statements is shown. We first accessed and connected to the MyDB database. Then, we used the INSERT query’s syntax to add the values inside the db_table table. We called the execute() function multiple times with the query inside it to enter the multiple data rows inside the table. The table’s contents are then shown and committed to the database.

Example 2: Inserting Both Variables and Values

The following program  is similar to the first program. But instead of adding the values directly to the table, we reorder the column’s names that contain the values.

Syntax:

INSERT INTO table_name (col1, col2, col3,..) VALUES (val1, val2, val3,..);

Where:

table_name: Name of the table

col1, col2,…: Name of the columns

val1, val2,…: Values of the columns

Output:

Text Description automatically generated

In the previous script, we created a new table which is “RECORD”. The variables/column names are specified as Stu, Grade, and Marks. We called the execute() method multiple times to run the insert query. Finally, the stored data is displayed on the console.

Conclusion

In this tutorial, we learned about the introduction of SQLite. Then, we discussed how the data can be inserted inside the SQLite table in Pandas. First, we explained the steps to connect to the SQLite DB in Python. After creating the connection, we created an SQLite table. We implemented two examples to insert the data inside the table of an SQLite database. In the first example, we inserted the column values for each row while we learned how to add both variables and values in the second example.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.