How to Connect to SQLite Database in Python Easily

To connect to an SQLite database in Python, use the sqlite3.connect() function with the database file name as an argument. This creates a connection object that you can use to interact with the database.

Syntax

The basic syntax to connect to an SQLite database in Python is:

• sqlite3.connect(database): Opens a connection to the SQLite database file named database. If the file does not exist, it will be created.
• The function returns a Connection object used to execute SQL commands.

python

import sqlite3

connection = sqlite3.connect('example.db')

Example

This example shows how to connect to an SQLite database, create a table, insert data, and query it.

python

import sqlite3

# Connect to database (creates file if not exists)
conn = sqlite3.connect('example.db')

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

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)''')

# Insert data
cursor.execute('INSERT INTO users (name) VALUES (?)', ('Alice',))
cursor.execute('INSERT INTO users (name) VALUES (?)', ('Bob',))

# Commit changes
conn.commit()

# Query data
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

# Print results
for row in rows:
    print(row)

# Close connection
conn.close()

Output

(1, 'Alice')
(2, 'Bob')

Common Pitfalls

Common mistakes when connecting to SQLite in Python include:

• Not closing the connection, which can lock the database file.
• Forgetting to commit changes after insert/update/delete operations.
• Using incorrect SQL syntax or not using parameterized queries, which can cause errors or security risks.

python

import sqlite3

# Wrong: forgetting to commit changes
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute('INSERT INTO users (name) VALUES ("Charlie")')
# Missing conn.commit() here
conn.close()

# Right: commit after changes
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute('INSERT INTO users (name) VALUES (?)', ('Charlie',))
conn.commit()
conn.close()

Quick Reference

Tips for working with SQLite in Python:

• Use sqlite3.connect() to open/create a database.
• Always use a cursor object to execute SQL commands.
• Use parameterized queries with ? placeholders to avoid SQL injection.
• Call commit() after changes to save them.
• Close the connection with close() when done.

Key Takeaways

Use sqlite3.connect('filename.db') to connect or create an SQLite database.

Always commit your changes with connection.commit() after inserts or updates.

Use cursor objects to execute SQL commands safely with parameterized queries.

Close the database connection with connection.close() to free resources.

Avoid SQL injection by never concatenating user input directly into SQL statements.