Executing SQLite INSERT Query from Python
When it comes to databases, SQLite is the go-to option for many developers due to its simplicity and reliability. One of the most common operations we perform on databases is inserting data.
In this article, we will discuss how to execute SQLite INSERT query from Python. We will cover three different scenarios: inserting a single row, using Python variables in insert query, and inserting multiple rows with executemany().
Inserting Single Row with SQLite Database Connection
To insert a single row into SQLite database from Python, we need to create a connection first. We can do this using the sqlite3 module in Python.
Here is how we can create a connection and execute an SQL INSERT query:
“`
import sqlite3
connection = sqlite3.connect(‘example.db’)
cursor = connection.cursor()
# SQL INSERT Query
insert_query = “INSERT INTO Students (name, age, email) VALUES (‘John’, 23, ‘[email protected]’)”
# Execute the query
cursor.execute(insert_query)
# Commit the changes
connection.commit()
print(f”Row inserted successfully.”)
“`
In the above code, we first created a connection to the ‘example.db’ database, and then created a cursor object that will allow us to execute SQL queries. We then created an SQL INSERT query using the `INSERT INTO` syntax and specified the values we want to insert.
Once we have our query, we execute it using the `execute()` method of the cursor object. We then commit the changes to the database using the `commit()` method.
Finally, we print a message to confirm that the row has been inserted successfully.
Using Python Variables in Insert Query
When we insert data into a database, we often need to use values from variables rather than hard-coding them into the query. To do this, we can use a parameterized query that uses placeholders for the variable values.
Here’s how we can modify our code to use placeholders:
“`
import sqlite3
connection = sqlite3.connect(‘example.db’)
cursor = connection.cursor()
# Placeholder query
insert_query = “INSERT INTO Students (name, age, email) VALUES (?, ?, ?)”
# Values to insert
name = ‘Samantha’
age = 21
email = ‘[email protected]’
# Execute the query with values
cursor.execute(insert_query, (name, age, email))
# Commit the changes
connection.commit()
print(f”{cursor.rowcount} row(s) inserted successfully.”)
“`
In the above code, we have replaced the hard-coded values with placeholders (`?`). We then defined the values we want to insert using Python variables.
When we execute the query, we pass the values as a tuple to the `execute()` method of the cursor object. This way, the values are substituted into the placeholders in the correct order.
We also used the `rowcount` attribute of the cursor object to print the number of rows inserted.
Inserting Multiple Rows with executemany()
In cases where we need to insert multiple rows at once, we can use the `executemany()` method of the cursor object. This method allows us to execute the same SQL query multiple times with different values.
Here’s an example:
“`
import sqlite3
connection = sqlite3.connect(‘example.db’)
cursor = connection.cursor()
# Query to insert multiple rows
insert_query = “INSERT INTO Students (name, age, email) VALUES (?, ?, ?)”
# Records to insert
records = [
(‘Alice’, 20, ‘[email protected]’),
(‘Bob’, 22, ‘[email protected]’),
(‘Charlie’, 19, ‘[email protected]’)
]
# Execute the query with many values
cursor.executemany(insert_query, records)
# Commit the changes
connection.commit()
print(f”{cursor.rowcount} row(s) inserted successfully.”)
“`
In the above code, we have defined an SQL INSERT query with placeholders, just like in the previous example. We have also defined a list of records to insert.
Each record is a tuple of values that correspond to the placeholders in the query. To execute the query with multiple records, we use the `executemany()` method and pass in the query and the list of records.
The method will execute the query once for each record, substituting the placeholders with the values in each record.
Conclusion
Inserting data into an SQLite database from Python is a common operation, and there are different ways to achieve it. In this article, we have covered three scenarios for executing an SQL INSERT query: inserting a single row, using Python variables in the insert query, and inserting multiple rows with executemany().
These examples should give you a good starting point for working with SQLite databases in Python, and hopefully, you’ll find them useful in your projects.
Inserting Data into SQLite Table using Python
SQLite is a popular database management system that is known for its simplicity, reliability, and ease of use. Python is a versatile and powerful programming language that is widely used among developers.
In this article, we will focus on inserting data into an SQLite table using Python. We will cover three different scenarios: inserting a single row, using Python variables in the INSERT query, and inserting multiple rows.
Inserting a Single Row into SQLite Table
To insert a single row into an SQLite table, you will need to establish a connection with your SQLite database using Python’s built-in sqlite3 module. You will then use SQL to create an insert statement that will populate the fields of your table with the appropriate data.
For example, suppose you have the following SQLite table:
“`
CREATE TABLE SqliteDb_developers(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
joining_date DATE NOT NULL,
salary REAL NOT NULL
);
“`
To insert a single row into this table using Python, follow these steps:
“`
import sqlite3
import datetime
# Establish a connection to the SQLite database
connection = sqlite3.connect(‘SQLite_Python.db’)
# Create a cursor object
cursor = connection.cursor()
# Define the row to be inserted
name = ‘John Doe’
email = ‘[email protected]’
joining_date = datetime.date(2021, 9, 21)
salary = 5000.0
# Create the SQL query with placeholders
query = ”’INSERT INTO SqliteDb_developers (name, email, joining_date, salary)
VALUES (?, ?, ?, ?)”’
# Execute the SQL query with the row data
cursor.execute(query, (name, email, joining_date, salary))
# Commit the transaction to the database
connection.commit()
# Print the confirmation message
print(f”The row with id {cursor.lastrowid} was inserted successfully.”)
“`
Here, we first established a connection to the SQLite database. We then created a cursor object to execute our SQL queries.
We defined the data that we want to insert as variables (name, email, joining_date, and salary) and created an SQL query that included placeholders for each field. We then used the cursor to execute the query with the row data, committed the transaction to the database, and printed a confirmation message that includes the id of the last inserted row.
Inserting Data Using Python Variables in SQLite Insert Query
Parameterized queries allow us to use Python variables in our SQL statements. When we use parameterized queries, we replace the literal values in our SQL statement with placeholders.
We can then supply the values for the placeholders using Python variables when we execute the query. Here’s an example:
“`
import sqlite3
connection = sqlite3.connect(‘SQLite_Python.db’)
cursor = connection.cursor()
# Define the row data using a Tuple
row_data = (‘Jane Doe’, ‘[email protected]’, 6000.0)
# Create the SQL query with placeholders
query = ”’INSERT INTO SqliteDb_developers (name, email, salary)
VALUES (?, ?, ?)”’
# Execute the SQL query with the row data
cursor.execute(query, row_data)
# Commit the transaction to the database
connection.commit()
# Print the confirmation message
print(f”The row with id {cursor.lastrowid} was inserted successfully.”)
“`
In this example, we defined the data in a Tuple (row_data), instead of creating separate variables for each field. We then created an SQL query that included placeholders and used the cursor to execute the query with the row data that we defined earlier.
We again committed the changes to the database and printed a confirmation message.
Inserting Multiple Rows into SQLite Table
If you have a large dataset, you might need to insert multiple rows into an SQLite table. One way to do this is to create a list of tuples, where each tuple represents a row to be inserted.
You can then pass this list to the `executemany()` method of the cursor object. Here’s an example:
“`
import sqlite3
connection = sqlite3.connect(‘SQLite_Python.db’)
cursor = connection.cursor()
# Define the rows to be inserted
rows_to_insert = [
(‘Jason Smith’, ‘[email protected]’, datetime.date(2021, 9, 20), 4000.0),
(‘Julia Brown’, ‘[email protected]’, datetime.date(2021, 9, 20), 4500.0),
(‘Max Rogers’, ‘[email protected]’, datetime.date(2021, 9, 21), 6500.0),
(‘Sarah White’, ‘[email protected]’, datetime.date(2021, 9, 22), 5500.0)
]
# Create the SQL query with placeholders
query = ”’INSERT INTO SqliteDb_developers (name, email, joining_date, salary)
VALUES (?, ?, ?, ?)”’
# Execute the SQL query with the multiple rows
cursor.executemany(query, rows_to_insert)
# Commit the transaction to the database
connection.commit()
# Print the confirmation message
print(f”{cursor.rowcount} rows were inserted successfully.”)
“`
In this example, we first defined the rows to be inserted as a list of tuples (rows_to_insert). We then created an SQL query with placeholders and used the `executemany()` method to execute the query with the multiple rows.
Remember to use the `commit()` method to save the changes to the database, and the `rowcount` attribute of the cursor object to print the number of rows inserted.
Conclusion
In this article, we have discussed three different scenarios for inserting data into an SQLite table using Python. We have covered inserting a single row, using Python variables in the INSERT query, and inserting multiple rows.
These examples provide a good starting point for working with SQLite databases using Python. Once you have learned these techniques, you can customize them to suit your specific needs.
In this article, we discussed the process of inserting data into an SQLite table using Python. We covered a range of topics, including inserting a single row, using Python variables in an INSERT query, and inserting multiple rows.
These techniques are critical since inserting data is a vital operation in working with databases. By using Python variables to execute parameterized queries and creating lists of tuples for inserting multiple rows, programmers can easily optimize their workflows.
As developers continue to rely on databases to store, manipulate, and retrieve data, mastering the insertion process can offer an important foundation for success.