Skip to main content

Basic Insert data in SQLite

 Python's SQLite module provides multiple methods to insert data into database tables safely and efficiently. After creating a table, inserting data is the next fundamental operation for building functional database applications.

Basic Insert Operations

The most straightforward approach uses the execute() method with INSERT SQL statements:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Insert single record
cursor.execute('''
    INSERT INTO users (name, email, age)
    VALUES ('John Doe', '[email protected]', 25)
''')

conn.commit()
conn.close()

Parameterized Queries (Recommended)

Always use parameterized queries to prevent SQL injection attacks and handle special characters properly:

# Using question mark placeholders
user_data = ('Jane Smith', '[email protected]', 30)
cursor.execute('INSERT INTO users (name, email, age) VALUES (?, ?, ?)', user_data)

# Using named placeholders
cursor.execute('''
    INSERT INTO users (name, email, age) 
    VALUES (:name, :email, :age)
''', {'name': 'Bob Wilson', 'email': '[email protected]', 'age': 35})

Bulk Insert Operations

For inserting multiple records efficiently, use executemany():

users_list = [
    ('Alice Brown', '[email protected]', 28),
    ('Charlie Davis', '[email protected]', 32),
    ('Diana Miller', '[email protected]', 27)
]

cursor.executemany('INSERT INTO users (name, email, age) VALUES (?, ?, ?)', users_list)

Error Handling and Best Practices

Implement proper error handling for constraints violations:

try:
    cursor.execute('INSERT INTO users (name, email, age) VALUES (?, ?, ?)', 
                   ('Test User', '[email protected]', 25))
    conn.commit()
except sqlite3.IntegrityError as e:
    print(f"Insert failed: {e}")
    conn.rollback()

Key Points

  • Always use parameterized queries with ? or named placeholders to prevent SQL injection
  • Call conn.commit() to save changes permanently
  • Use executemany() for bulk operations to improve performance
  • Implement try-except blocks to handle constraint violations gracefully
  • Consider using context managers (with statements) for automatic connection management

These techniques ensure secure, efficient data insertion while maintaining database integrity and application reliability.

Labels:

Popular posts from this blog

How to Check if Someone is Connected to Your Machine in Linux

Picture this: you glance at your system monitor and notice your CPU is humming along even though you're not running anything demanding. Or maybe your internet feels sluggish for no obvious reason. A small, uneasy thought creeps in — is someone else on my machine right now? For Linux users, this isn't something you have to wonder about. Linux ships with a powerful set of built-in tools that let you see exactly who's connected, who's logged in, and what your network is doing at any given moment. You don't need to be a security expert to use them — you just need to know where to look. This guide walks you through the practical, no-nonsense steps to check for unauthorized connections on your Linux system, with real commands you can run right now. Why Monitoring Network Connections Matters Every device on a network — including your own Linux machine — communicates using an IP address. When another device or user connects to your system, that connection shows up as a trac...
Read more

How to Set Up a Linux Web Server and Host an HTML Page Easily

Setting up a web server on Linux means spending a fair amount of time in the terminal — Linux leans heavily on the command line rather than clicking through menus, so you'll be typing out instructions more often than not.  If you're new to this, it can feel a little intimidating at first, but the good news is you don't need to become a Linux wizard overnight. A handful of core commands will get you surprisingly far. A few you'll lean on constantly: cd — move between directories ls — see what's in the current directory mkdir — create a new folder nano or vim — edit files right there in the terminal sudo — run something with administrator privileges Get comfortable with these and you'll be able to navigate around, tweak configuration files, and install software without much trouble. You don't need to memorize everything — you just need to be confident enough to follow along with clear instructions, which is exactly what this guide aims to give you....
Read more

Linux Network Troubleshooting

If you've spent any time as a sysadmin — or honestly, just as someone who's had to fix their own home network at 11pm — you know that connectivity issues are one of the most common headaches out there. The good news is that a handful of core tools and a methodical approach can take you from "why isn't this working" to a root cause pretty quickly.  This guide walks through the essentials: configuring interfaces, managing routes, and diagnosing problems when things go sideways. Configuring Network Interfaces Your network interfaces are the actual bridge between your machine and the outside world, so getting them configured correctly is step one for any kind of reliable connectivity. Doing It Manually ifconfig is the old-school, tried-and-true tool for this on Unix-like systems. To see everything currently configured, run: ifconfig -a If you need to manually set up a specific interface — assigning an IP, a netmask, and bringing it online — it looks like this: ifconf...
Read more