Python and SQLite: insert into table

python

bulk-inserts

parameterized-queries

sql-injections

byNikita Barsukov·Aug 26, 2024

Incorporate SQLite data operations into your Python workflow using the sqlite3 module. The process involves creating a database connection and a cursor, followed by running a parameterized insert operation for security, a commit to save changes, and finally a close the connection. The anchor code for these steps is as follows:

import sqlite3

# Connect to the database like it's your new Tinder match
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Insert a statement with placeholders to tell SQL injection 'Not today!'
cursor.execute("INSERT INTO table_name (col1, col2) VALUES (?, ?)", ("value1", "value2"))

# Commit - 'cos no one likes flaky daters or databases
conn.commit()
# Close - after ensuring you've taken all you need from this relationship
conn.close()

Change table_name, col1, col2, and ("value1", "value2") with your table name, column names and data you want to insert.

Efficient and safe database operations

Let’s delve deeper into additional scenarios and best practices when working with Python and SQLite.

Bulk inserts: All the data, in one shot

Python’s executemany function allows you to insert multiple rows at once, optimizing your performance and minimizing database I/O time.

# Gathers the crowd
rows_to_insert = [
    ("value1", "value2"),
    ("value3", "value4")
]

# Drops the beat
cursor.executemany("INSERT INTO table_name (col1, col2) VALUES (?, ?)", rows_to_insert)
# Crowd goes wild!

Context managers: The unsung heroes

Use the context manager to handle your transactions automatically, dealing with committing or rolling back in case of hiccups.

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    # The context manager has your back!
    cursor.execute("INSERT ...")

Understanding your schema: The Matchmaker

Make sure your tuple elements correspond to the order and type of your table's columns. Let's avoid some awkward dates, shall we?

Error-proofing: Catch'em All

Wrap your SQL operations in a try-except block to gracefully handle exceptions and manage potential disasters.

try:
    # Insert or bust!
    cursor.executemany("INSERT ...")
except sqlite3.DatabaseError as e:
    # Well, we tried.
    print(f"Database error: {e}")
except Exception as e:
    # Who knows what happened here.
    print(f"Exception in _insert: {e}")
finally:
    # Time to say goodbye.
    conn.close()

Using dictionaries: Find a better path

Use sqlite3.Row to work with query results as if they were dictionaries. Now, isn’t that convenient!

# Fancy iterator for the classy coder
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM table_name")
for row in cursor:
    print(dict(row))  # Well, isn't that neat?

Tackling duplicates: Dealing with Déjà Vu?

Learn to catch and handle duplicate entries by catching unique constraints errors during insertion.

Visualistion

Think of inserting data into a database like assembling a LEGO set:

Before: 🧱 (Your pile of bricks or data)

INSERT INTO lego_castle (brick_color, brick_size) VALUES ('Blue', '2x2');

After:  🏰 (A beautiful castle made by combining your bricks/data!)

Parameterized queries: Be the bouncer!

Use placeholders, denoted by ?, to guard against SQL injections. It's like the velvet rope outside an exclusive club—only the right people get in.

# A real bouncer would say something scarier!
unsafe_query = f"INSERT INTO table_name VALUES ('{user_input}')"
# The bouncer is on duty!
safe_query = "INSERT INTO table_name (col1) VALUES (?)"

Design dynamic queries like a pro!

Although string formatting for query building is looked down upon, it could be safely used for constructing column lists or other non-data parts of the SQL statement.

col_list = ", ".join(["name", "age", "profession"])
placeholders = ", ".join(['?'] * len(col_list.split(", ")))
query = f"INSERT INTO my_table ({col_list}) VALUES ({placeholders})"