Insert rows into an SQLite table using Python

Overview:

Once the database design is complete and the tables are created in the SQLite database, populating the tables with data is an important next step.

Using the INSERT statement in SQL, a row of data can be inserted into an SQLite table.

INSERT SQL statement has several variants.

All those variants of the INSERT statement begin with the form INSERT INTO <table_name>.

Fig: Variants of INSERT statement supported by SQLite

Variant 1: INSERT INTO <table_name> values (<list of values>):

• The values clause of the INSERT statment takes a list of values for each column present in the table.
• The values are inserted into the table in the same order in which they are specified.
• A comma separates the list of values.
• If a value specified is not conforming to the type of the column, an error will be raised by the SQLite.

The above INSERT statement will add a row into the SQLite table "Student".  Since there is no column list specified in this form of INSERT statement, care should be taken to ensure the list of values provided by the "values" clause matches column count and type of a given table.

Variant 2: INSERT INTO <table_name> <column list> values (<list of values>):

• Values can be inserted into an SQLite table by specifying one are more column names as well in the INSERT statement.
• In this variant of the INSERT statement, the columns which are not specified will be given default values as specified in the CREATE TABLE statement of SQLite.

Assume a SQlite table named TEMPERATURE has the columns timestamp, reading and unit. The INSERT statement above, will insert a row into the TEMPERATURE table, taking values for the "timestamp" column and the "reading" column from the SQL statment and assigning a default value 1 for the "unit" column.

Variant 3: INSERT INTO <table_name> SELECT:

• In SQLite, variants one and two of the INSERT statement can be used along with a SQL SELECT query.
• The results of the SELECT query, which could be one or multiple rows is added into the SQLite table.
• If the INSERT statement consists of a column list, the results of the SELECT query should match the number of columns in the list.
• In case of no column list being specified in the INSERT statement, the number of columns present in the table and the number of columns present in the result of the SELECT query should match.

The above INSERT STATEMENT copies all the rows of SQLite table orders into another SQLite table orders_backup. The column definitions of orders_backup is a replica of column definitions present in the orders table.

Variant 4: INSERT INTO <table_name> DEFAULT VALUES:

• This variant of the INSERT statement does not take any value or a value list for one or more columns.
• Instead, the clause DEFAULT VALUES is specified which inserts a row with the default values as provided in the SQLite CREATE TABLE Statement.

The above INSERT statement will add a row into an SQLite table marker, for which all the values are taken as default values as specified in the SQLite CREATE TABLE statement.

Inserting a row into a SQLite table using Python:

• Using the module sqlite3, interactions can be made from a Python Program to an SQLite database.
• A database connection is opened to an existing SQLite database by specifying the database file name. For example, a database file called market.db.
• If the database file does not exist, a new database is created using the specified file name. This database is called the main database.
• Using the database connection object, a cursor object is obtained and any SQL statement supported by SQLite can be executed over the cursor object.
• In the Python example program given below an INSERT statement is executed over the cursor object obtained.
• The COMMIT command is executed followed by the INSERT statement so as to ensure the changes made to the SQLite database table are permanent.

Example:

Output: