Camkode
Camkode

How to Connect to SQLite with Python

Posted by Kosal

How to Connect to SQLite with Python

To connect to a SQLite database with Python, you can use the built-in sqlite3 module, which provides an easy way to work with SQLite databases. Below is a step-by-step guide to connecting to a SQLite database using Python:

Step 1: Install SQLite

There's no need to install SQLite separately, as it's already included in the Python standard library.

Step 2: Connect to the SQLite database

import sqlite3

# Connect to the SQLite database file (creates it if it doesn't exist)
conn = sqlite3.connect('example.db')

# Create a cursor object to execute SQL commands
cursor = conn.cursor()

# Example: Creating a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    age INTEGER
                )''')

# Commit the changes
conn.commit()

# Close the connection
conn.close()

In the above code:

  • sqlite3.connect('example.db') connects to the SQLite database file named 'example.db'. If the file does not exist, SQLite will create it.
  • conn.cursor() creates a cursor object that can execute SQL commands.
  • cursor.execute() executes SQL commands. In this example, it creates a table named users with columns id, name, and age.
  • conn.commit() commits the changes made to the database.
  • conn.close() closes the connection to the database.

Step 3: Perform Database Operations

After establishing a connection, you can perform various database operations like inserting, updating, deleting, or querying data from the database using SQL commands executed through the cursor object.

import sqlite3

# Reconnect to the database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Insert data into the table
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))

# Query data from the table
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Update data in the table
cursor.execute("UPDATE users SET age = 32 WHERE name = 'Alice'")

# Delete data from the table
cursor.execute("DELETE FROM users WHERE name = 'Alice'")

# Commit changes and close the connection
conn.commit()
conn.close()

In this code, we insert a record into the users table, query all records, update the age of the user named 'Alice', and then delete the user with the name 'Alice' from the table.

Additional Notes:

  • Always handle exceptions and errors appropriately, especially when dealing with databases.
  • Make sure to sanitize user inputs to prevent SQL injection attacks.
  • Consider using context managers (with statement) for handling connections and cursors to ensure resources are properly managed and automatically closed after use.
  • For more complex applications, you might want to use ORM libraries like SQLAlchemy to abstract away SQL queries and interact with the database more easily.
#python #sqlite