Python SQLite – Insert Data

To insert data into a SQLite database table using Python, you can execute SQL `INSERT INTO` statements. These statements specify the table name and the values to be inserted into each column. Here’s how you can insert data into a table named `employees` with columns `id`, `name`, and `salary`:

1. Establish Connection: First, establish a connection to the SQLite database file using the `connect()` function from the `sqlite3` module.

2. Create Cursor: After establishing the connection, create a cursor object using the `cursor()` method.

3. Execute SQL Statement: Use the cursor’s `execute()` method to execute an SQL `INSERT INTO` statement. This statement specifies the table name and the values to be inserted into each column.

4. Commit Changes: After executing the SQL statement, commit the changes to the database using the `commit()` method of the connection object.

5. Close Connection: Finally, close the cursor and connection using the `close()` method to release any resources associated with them.

Here’s an example of how to insert data into the `employees` table:

“`python
import sqlite3

# Establish connection to SQLite database
connection = sqlite3.connect(‘example.db’)

# Create a cursor object
cursor = connection.cursor()

# Define data to be inserted
data = [
(1, ‘John Doe’, 50000.0),
(2, ‘Jane Smith’, 60000.0),
(3, ‘Alice Johnson’, 75000.0)
]

# Execute SQL statement to insert data
cursor.executemany(‘INSERT INTO employees VALUES (?, ?, ?)’, data)

# Commit changes to the database
connection.commit()

# Close cursor and connection
cursor.close()
connection.close()
“`

In this example:

– We establish a connection to an SQLite database file named `example.db`.
– We create a cursor object using the `cursor()` method.
– We define the data to be inserted as a list of tuples, where each tuple represents a row to be inserted into the table.
– We execute an SQL `INSERT INTO` statement using the cursor’s `executemany()` method to insert multiple rows of data into the `employees` table.
– We commit the changes to the database using the `commit()` method.
– Finally, we close the cursor and connection.