PYTHON Tutorial

Executing SQL Commands

Key Concepts:

  • Executing queries: Retrieving data from a database.
  • Inserting data: Adding new rows to a table.
  • Updating data: Modifying existing rows in a table.
  • Deleting data: Removing rows from a table.

Steps:

  • Establish a connection to the database: Create a connection object using sqlite3.connect('database.db').
  • Create a cursor: A cursor is a way to execute SQL commands on the connection. Create a cursor object using connection.cursor().
  • Execute a command: Use the cursor's execute() method to execute SQL commands. For example, cursor.execute('SELECT * FROM table_name').
  • Get results: For queries, fetch results using the cursor's fetchall() method. For insert, update, and delete operations, use the cursor's rowcount property to get the number of rows affected.
  • Close the connection: Once finished, close the connection using connection.close().

Python Example:

import sqlite3

# Establish connection
connection = sqlite3.connect('database.db')
cursor = connection.cursor()

# Execute query
cursor.execute('SELECT * FROM table_name')
results = cursor.fetchall()

# Insert data
cursor.execute('INSERT INTO table_name VALUES (1, "John Doe")')

# Update data
cursor.execute('UPDATE table_name SET name="Jane Doe" WHERE id=1')

# Delete data
cursor.execute('DELETE FROM table_name WHERE id=2')

# Commit changes
connection.commit()

# Close connection
connection.close()