Python SQLite - Insert Data

Python SQLite - Insert Data

To insert data into an SQLite database using Python, you'd typically follow these steps:

  • Connect to the SQLite database (or create one if it doesn't exist).
  • Create a cursor object to interact with the database.
  • Execute an SQL INSERT command.
  • Commit the changes.
  • Close the connection.

Here's a step-by-step example:

  • Setup:

First, you'll need the sqlite3 module, which comes with Python's standard library, so you don't need to install anything.

  • Example:

Assuming you have a table named users with columns id, name, and age, here's how you can insert data into it:

import sqlite3 # Connect to the database (it will create "mydatabase.db" if it doesn't exist) conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() # Create a table (only for the first run, you can comment it out later) cursor.execute(''' CREATE TABLE IF NOT EXISTS users( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER ) ''') # Insert a single row of data cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("John Doe", 25)) # Insert multiple rows of data users_data = [ ("Jane Smith", 30), ("Bob Johnson", 35), ("Lucy Liu", 40) ] cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users_data) # Commit the changes conn.commit() # Close the connection conn.close()

A few things to note:

  • We use ? as placeholders for the data to prevent SQL injection attacks. The actual data values are then passed as a tuple to the execute() function.
  • If you're inserting many rows, executemany() is more efficient than calling execute() in a loop.

Now, the data has been inserted into the users table in the SQLite database mydatabase.db.

More Tags

gnuplot ngx-bootstrap-modal milliseconds escaping centos7 motion-detection barcode bc zend-framework2 try-except

More Programming Guides

Other Guides

More Programming Examples