PYnative

Python Programming

Home » Python » Databases » Python Insert into SQLite Table

Python Insert into SQLite Table

Updated on:  | 9 Comments

Learn to execute the SQLite INSERT Query from Python to add new rows to the SQLite table using a Python sqlite3 module.

Goals of this lesson: –

  • Insert single and multiple rows into the SQLite table
  • Insert Integer, string, float, double, and datetime values into SQLite table
  • Use a parameterized query to insert Python variables as dynamic data into a table

Also Read:

Table of contents

Prerequisites

Before executing the following program, please make sure you know the SQLite table name and its column details.

For this lesson, I am using the ‘SqliteDb_developers’ table present in my SQLite database.

sqlitedb_developers table
sqlitedb_developers table

If a table is not present in your SQLite database, then please refer to create SQLite table from Python.

Python example to insert a single row into SQLite table

Follow the below steps: –

How to Insert Into SQLite table from Python

  1. Connect to SQLite from Python

    Refer to Python SQLite database connection to connect to SQLite database from Python using sqlite3 module.

  2. Define a SQL Insert query

    Next, prepare a SQL INSERT query to insert a row into a table. in the insert query, we mention column names and their values to insert in a table.
    For example, INSERT INTO mysql_table (column1, column2, …) VALUES (value1, value2, …);

  3. Get Cursor Object from Connection

    Next, use a connection.cursor() method to create a cursor object. using cursor object we can execute SQL queries.

  4. Execute the insert query using execute() method

    The cursor.execute(query) method executes the operation stored in the Insert query.

  5. Commit your changes

    After successfully executing an insert operation, make changes persistent into a database using the commit() of a connection class.

  6. Get the number of rows affected

    After a successful insert operation, use a cursor.rowcount method to get the number of rows affected. The count depends on how many rows you are Inserting.

  7. Verify result using the SQL SELECT query

    If required, execute SQLite select query from Python to see the new changes.

  8. Close the cursor object and database connection object

    use cursor.clsoe() and connection.clsoe() method to close the cursor and SQLite connections after your work completes.

As of now, the SqliteDb_developers table is empty, so let’s insert data into it.

Example

import sqlite3

try:
    sqliteConnection = sqlite3.connect('SQLite_Python.db')
    cursor = sqliteConnection.cursor()
    print("Successfully Connected to SQLite")

    sqlite_insert_query = """INSERT INTO SqliteDb_developers
                          (id, name, email, joining_date, salary) 
                           VALUES 
                          (1,'James','james@pynative.com','2019-03-17',8000)"""

    count = cursor.execute(sqlite_insert_query)
    sqliteConnection.commit()
    print("Record inserted successfully into SqliteDb_developers table ", cursor.rowcount)
    cursor.close()

except sqlite3.Error as error:
    print("Failed to insert data into sqlite table", error)
finally:
    if sqliteConnection:
        sqliteConnection.close()
        print("The SQLite connection is closed")

Output

Successfully Connected to SQLite Record inserted successfully into table
The SQLite connection is closed
sqlitedb_developers table after single row from Python
sqlitedb_developers table after single row from Python

Using Python variables in SQLite INSERT query

Sometimes we need to insert a Python variable value into a table’s column. This value can be anything, including integer, string, float, and DateTime. For example, in the registration form person enter his/her details. You can take those values in Python variables and insert them into the SQLite table.

We use a parameterized query to insert Python variables into the table. Using a parameterized query, we can pass python variables as a query parameter in which placeholders (?)

import sqlite3

def insertVaribleIntoTable(id, name, email, joinDate, salary):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sqlite_insert_with_param = """INSERT INTO SqliteDb_developers
                          (id, name, email, joining_date, salary) 
                          VALUES (?, ?, ?, ?, ?);"""

        data_tuple = (id, name, email, joinDate, salary)
        cursor.execute(sqlite_insert_with_param, data_tuple)
        sqliteConnection.commit()
        print("Python Variables inserted successfully into SqliteDb_developers table")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to insert Python variable into sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

insertVaribleIntoTable(2, 'Joe', 'joe@pynative.com', '2019-05-19', 9000)
insertVaribleIntoTable(3, 'Ben', 'ben@pynative.com', '2019-02-23', 9500)

Output: 

Connected to SQLite Python Variables inserted successfully into table
sqlite connection is closed 

Connected to SQLite Python Variables inserted successfully into table The SQLite connection is closed
sqlitedb_developers table after inserting Python variable as a column value
sqlitedb_developers table after inserting Python variable as a column value

Note: If you have a date column in the SQLite table, and you want to insert the Python DateTime variable into this column then please refer to working with SQLite DateTime values in Python.

Python Insert multiple rows into SQLite table using the cursor’s executemany()

In the above example, we have used execute() method of cursor object to insert a single record. Still, sometimes we need to insert multiple rows into the table in a single insert query.

For example, You wanted to add all records from the CSV file into the SQLite table. Instead of executing the INSERT query every time to add each record, you can perform a bulk insert operation in a single query using a cursor’s executemany() function.

The executemany() method takes two arguments SQL query and records to update.

import sqlite3

def insertMultipleRecords(recordList):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sqlite_insert_query = """INSERT INTO SqliteDb_developers
                          (id, name, email, joining_date, salary) 
                          VALUES (?, ?, ?, ?, ?);"""

        cursor.executemany(sqlite_insert_query, recordList)
        sqliteConnection.commit()
        print("Total", cursor.rowcount, "Records inserted successfully into SqliteDb_developers table")
        sqliteConnection.commit()
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to insert multiple records into sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

recordsToInsert = [(4, 'Jos', 'jos@gmail.com', '2019-01-14', 9500),
                   (5, 'Chris', 'chris@gmail.com', '2019-05-15', 7600),
                   (6, 'Jonny', 'jonny@gmail.com', '2019-03-27', 8400)]

insertMultipleRecords(recordsToInsert)

Output

Connected to SQLite
Total 3 Records inserted successfully into table
The SQLite connection is closed
sqlitedb_developers table after inserting multiple rows from Python
sqlitedb_developers table after inserting multiple rows from Python

 verify the result by selecting data from SQLite table from Python.

Let’s understand the above example

  • After connecting to SQLite, We prepared a list of records to insert into the SQLite table. Each entry in the list is nothing but a table tuple (row)
  • SQL INSERT statement contains the parameterized query, which uses the placeholder (?) for each column value.
  • Next, Using cursor.executemany(sqlite_insert_query, recordList) , we inserted multiple rows into the table.
  • To get to know the number of records inserted, we used a cursor.rowcount method.

Next Steps:

To practice what you learned in this article, Solve a Python Database Exercise project to Practice Database operations.

TweetF  sharein  shareP  Pin

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Comments

  3. prasad says 

    stream=stream.read()
        sortedKey=sorted(template_config.get('Template_attribute').keys(),key=lambda x: int(x))
        for i in sortedKey:
            print('________________________________________')
            print('Processing : {}'.format(i))
            for k,v in template_config['Template_attribute'][i].items():
               print('{0} : {1}'.format(k,v))

    from this code iam generating output like this
    Processing : 1
    sql_column_name : id
    bussness_column_name : id
    description : test column
    gbd_flag : Y
    filter_flag : Y
    llk_flag : Y
    column_width : 5
    ________________________________________
    Processing : 2
    sql_column_name : name
    bussness_column_name : cl name
    description : test column
    gbd_flag : Y
    filter_flag : Y
    llk_flag : Y
    column_width : 5
    ________________________________________
    Processing : 3
    sql_column_name : site
    bussness_column_name : site name
    description : test column
    gbd_flag : Y
    filter_flag : Y
    llk_flag : Y
    column_width : 15
    ________________________________________
    Processing : 4
    sql_column_name : pbm_load_log_key
    bussness_column_name : load_log_key
    description : test column
    gbd_flag : Y
    filter_flag : Y
    now how i can insert this data into sqlite

    Reply

  4. Theo Lockefeer says

    Great site ! great examples ! Thanks a lot !.

    This works ok in Pycharm :
    Python Insert multiple rows into SQLite table using the cursor’s executemany()

    but i have the following questions :

    1) i want to see the database after the insertions (but it is closed ?)
    2) i want to print the database but i donot know howto : can you please explain with an example ?

    Thanks again a lot in advance !

    Reply

  6. Nikhilesh Sandela says

    How to auto-generate primary key IDs as and when you upload a new file in the DB?
    Bcoz there is no point entering values manually when a server is running(if connected). 🙂

    Reply

    • Vishal says

      Hi Nikilesh,

      You need to configure the table in such a way that the primary key gets auto-incremented. either you can use create new table or alter the existing table.

      CREATE TABLE new_employee ( INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, photo BLOB NOT NULL, resume BLOB NOT NULL);

      And while inserting data don’t enter this column value in insert query.

      for example,

      sqlite_insert_blob_query = """ INSERT INTO 'new_employee'
                                  ('name', 'photo', 'resume') VALUES (?, ?, ?)"""

      Referance: https://www.sqlite.org/autoinc.html

      Reply

Leave a Reply

your email address will NOT be published. all comments are moderated according to our comment policy.

Use <pre> tag for posting code. E.g. <pre> Your code </pre>

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

Legal Stuff

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.