Python sqlite3 insert into table

Python Insert into SQLite Table

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

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.

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

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.

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") 

Successfully Connected to SQLite Record inserted successfully into table The SQLite connection is closed

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) 

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

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) 

Connected to SQLite Total 3 Records inserted successfully into table The SQLite connection is closed

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.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Related Tutorial Topics:

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

Источник

Python sqlite3 – INSERT INTO Table

You can insert one or more rows into sqlite3 table using execute() method.

In this tutorial, we shall go through the sequence of steps required to insert one or more rows into a table in sqlite database using sqlite3 library. Also, we shall learn how to check if the row insertion is successful.

Steps to insert rows into Sqlite Table

To insert a row into sqlite3 table, follow these steps.

1. Create a connection to your sqlite3 database.
2. Get a cursor to the connection.
3. Create a table if not present, or check if the table is present.
4. If the table is present, use execute() method on the cursor, by passing SQL insert query to the execute() method.

You can check if the row is inserted successfully or not with the help of cursor.lastrowid(). We shall see this scenario in Example 2.

Examples

1. Insert row into sqlite3 table

In the following example, we have created a table, if it does not exist, and then used INSERT INTO query to insert a record into the table.

Python Program

import sqlite3 conn = sqlite3.connect('mysqlite.db') c = conn.cursor() #create table c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''') c.execute('''INSERT INTO students VALUES(1, 'Alex', 8)''') #commit the changes to db conn.commit() #close the connection conn.close()

2. Insert row into sqlite3 table and check if insertion is successful

Well, most often times, we do need to know if the INSERT INTO query has actually worked. We need to know if the record is inserted successfully.

To know that, we can check the last row id that is inserted by the sqlite3 connection cursor. If the lastrowid is not zero 0, then we can assure programmatically that the insertion is successful.

Python Program

import sqlite3 conn = sqlite3.connect('mysqlite.db') c = conn.cursor() #create table c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''') c.execute('''INSERT INTO students VALUES(1, 'Glen', 8)''') print(c.lastrowid) #commit the changes to db conn.commit() #close the connection conn.close()

If you see a non-zero id echoed to the console, then your INSERT INTO statement has worked for you. If you get a zero, you need to debug your program.

Summary

In this tutorial of Python Examples, we learned how to insert rows into sqlite3 table.

Источник