# Python Sqlite3 Module

The `sqlite3` module provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249. It allows you to interact with SQLite databases, which are lightweight, disk-based databases that do not require a separate server process.

## Importing the Module

```python
import sqlite3
```

## Basic Usage

### Connecting to a Database

To use the module, you must first create a connection object that represents the database.

```python
import sqlite3

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

# You can also create an in-memory database
# con = sqlite3.connect(':memory:')
```

### Creating a Cursor

Once you have a connection, you can create a cursor object and call its `execute()` method to perform SQL commands.

```python
cur = con.cursor()
```

### Creating Tables

```python
# Create table
cur.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')
```

### Inserting Data

```python
# Insert a row of data
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
con.commit()
```

### Closing the Connection

We can also close the connection if we are done with it.

```python
con.close()
```

## Retrieving Data

To retrieve data after executing a SELECT statement, you can treat the cursor as an iterator, call the cursor's `fetchone()` method to retrieve a single matching row, or call `fetchall()` to get a list of the matching rows.

```python
for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
    print(row)
```

## Parameter Substitution

Use `?` as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor's `execute()` method. **Never** use string operations to assemble your SQL statements, as it makes your program vulnerable to SQL injection attacks.

```python
# Incorrect (Vulnerable to SQL Injection)
symbol = 'RHAT'
# cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Correct
cur.execute("SELECT * FROM stocks WHERE symbol=?", (symbol,))
```

## Row Objects

By default, `sqlite3` returns rows as tuples. You can use `sqlite3.Row` to access columns by name.

```python
con.row_factory = sqlite3.Row
```

[[programming/python/python]]