How to insert multiple records in Sqlite3 database

By /
Adding Multiple Records At Once In SQLite Database Using Python

Hello everyone, in this article, we shall study how the Sqlite3 database works and how to add multiple rows at once using Python. Now a database works on four fundamental operations CRUD.

  1. Create
  2. Retrieve
  3. Update
  4. Delete

The most in-use type of DB’s today in technological fields is Relational Databases. Their name itself defines their functioning – Tables that are connected with some special keys. One of them is the Sqlite3 database. 

SQL vs SQLite3

Let’s quickly compare what SQL and SQLite have similar and different to each other.

What is SQL?

SQL is a language that helps us to communicate with our databases. The commands of SQL are specifically called “Queries”. Each query is for a special purpose. This interpreted language does not have any special compiler, rather it executes it from special SQL frameworks. There are various servers out there available on the internet. One of them is SQLite3. 

What is SQLite3?

SQLite3 framework is a lightweight server for SQL. Here are some features of SQLite3.

  1. Does not require a server connection for raw working.
  2. Requires less memory space.
  3. No installation is necessary, extract the zip file, and it is ready for use.
  4. Commands are easier to execute.
  5. Cross-platform – runs on Windows, Linux, and Mac systems.
  6. Integrated – can be operated using languages like C/C++, Java, Python, etc.

Steps to Insert multiple records in Sqlite3 database

This section of the article tells us about how we can use SQLite3 with Python and add multiple records in one single command in SQL.

Note: Skip to point #5 for the code to add multiple records in an SQLite3 database using Python.

1. Setting up the environment

We need to set up some points when we work with any database. This is a good practice when working with huge projects.

  1. Create a folder SQLITE that will hold all our databases and codes.
  2. In that folder create another folder as a database. This will contain all the databases.
  3. Download the slqite3 zip packet from the official website: https://sqlite.org/download.html and search for the Precompiled binaries for Windows. We can choose it according to our system whether it is 32 bit or 64 bit.
  4. Once downloaded extract the zip file into our SQLite folder. That’s done no need to install anything.
  5. Create a python file data.py that contains the whole code we will write.

It will look like this now:

Folderr Setup
Folder Setup

2. Creating a database in SQLite3 using Python

Python provides a special library namely sqlite3 which comes as a built-in package along with it. So, our task becomes easy. We just need to have Python 3.x.x version in our system. It is a recommendation to use 3.6.x versions for error-free programming.

Code:

import sqlite3

connection = sqlite3.connect('databases/student.db') # file path

# create a cursor object from the cursor class
cur = connection.cursor()

cur.execute('''
   CREATE TABLE customers(
       first_name text,
       last_name text,
       email text
       )''')

# committing our connection
connection.commit()

# close our connection
connection.close()

DATA TYPES IN SQLITE: There are five types of datatypes present in sqlite3

  1. NULL
  2. INTEGER
  3. REAL: decimal point digit
  4. TEXT: string
  5. Blob: images, mp3 file, video file etc.

Explanation:

  1. Import the sqlite3 module.
  2. Create a connection object using the connect() method. This method creates a database. Store this using a connection object.
  3. Create a cursor object using the cursor() function. Create a simple object for an easy workflow. The cursor object helps to connect to a new or existing database and perform operations on it.
  4. Then using the same cursor object call the execute() function. This function takes all the SQL queries in the form of string arguments.
  5. Create a simple database that holds three records ‘first_name’, ‘last_name’, and ’email’. Commit the action using commit() function. Call it with the connection object using the dot “.” operator.
  6. Close the connection using the close() method.

This command also inserts a “student.db” file into the database folder.

Student Database Created In The Database Folder
Student Database Created In The Database Folder

3. View the database in SQLite studio

Open the SQLite studio from the extracted zip folder and select the language as “American English”. Then, it will open an interface like this:

SQLite Studio Interface
SQLite Studio Interface

Click on the Database column and select Add a database option from it. We can also perform it using the “Ctrl + O” command. 

Adding A Database 1
Adding A Database
Adding It From The System Folder
To add the database from the system click on the folder icon
Select The Newly Created Student Db Database From The Database Folder
Select the appropriate database from the folder
View Of The Added Database
View of the added database

4. Inserting values into the database

This is one of the most important queries. Because there is no benefit of creating a table and leaving it empty. So, we will insert some sample data into our table. The SQLite module’s two methods provide us with help for doing the same.

  1. execute() – inserts only one record at a time.
  2. executemany() – inserts many records at a time.

Adding one record at a time

Code:

import sqlite3

connection = sqlite3.connect('databases/student.db') # file path

# create a cursor object from the cursor class
cur = connection.cursor()

cur.execute("INSERT INTO student_data VALUES ('Shree', 'Narayan', '[email protected]')")
# committing our connection

print('Command executed successfully!!!')
connection.commit()

# close our connection
connection.close()

Output:

To view the change in our database just open the Sqlite3 studio and view the Data option of the studio.

Checking The Inserted Data
Checking the inserted data

Explanation:

  1. First, connect with the database.
  2. Then create a cursor object.
  3. Then add the record of student having name = “Shree”, last_name = “Narayan” and email = “[email protected]” using the execute() function.
  4. Commit the changes and then close the connection.

5. Adding multiple records at a time

Code:

import sqlite3
 
# connection = sqlite3.connect(':memeory:')
connection = sqlite3.connect('databases/student.db') # file path
 
# create a cursor object from the cursor class
cur = connection.cursor()
 
# creating a list of items
 
multiple_columns = [('Walt', 'Diseny', '[email protected]'),
                    ('Ben', 'Parker', '[email protected]'),
                    ('Charlemagne', 'Butler', '[email protected]')]
 
cur.executemany("INSERT INTO student_data VALUES (?,?,?)", multiple_columns)
 
# committing our connection
 
print('Command executed successfully!!!')
connection.commit()
 
# close our connection
connection.close()

Explanation:

  1. First, connect with the database.
  2. Then create a cursor object.
  3. We need to create a list of tuples that holds data of three students. Name it multiple_records.
  4. Using the cursor object we will use the executemany() function. Using the (INSERT INTO student_data column VALUES (?, ?, ?), multiple_records) command.
  5. Here (?, ?, ?) is a placeholder that has question marks that are used according to the number of columns the particular table has. We have three columns so we use three placeholders.

Syntax of executemany() function:

cursor_object.executemany("INSERT INTO database_name VALUES (?,?,?,...?)", column_list)

Output:

Go to the studio and then click on the refresh button or press F5, we get the updated student_data.

Inserted Multiple Records At Once
Inserted multiple records at a time

Conclusion

Here we wrap up this article, I hope this helps everyone in making all the possible changes in their respective DBMS. SQLite with Python is easy to learn but revise this concerning every piece of code.