# Python SQLite (sqlite3)

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. Python comes with a built-in module `sqlite3` to interact with SQLite databases.

## Importing the Module

```python
import sqlite3
```

## Connecting to a Database

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

```python
import sqlite3

# Connect to a database (or create one if it doesn't exist)
conn = sqlite3.connect('example.db')

# Create a cursor object
c = conn.cursor()
```

## Creating a Table

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

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

## Inserting Data

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

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

### Using Variables

To insert variables safely (preventing SQL injection), use the `?` placeholder.

```python
t = ('2006-01-06', 'BUY', 'IBM', 1000, 45.00)
c.execute('INSERT INTO stocks VALUES (?,?,?,?,?)', t)
conn.commit()
```

## Querying Data

```python
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())

# Select all
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print(row)
```

## Closing the Connection

```python
# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()
```

## Using Context Managers

It is best practice to use context managers to handle the connection and transactions automatically.

```python
import sqlite3

# Using 'with' automatically closes the connection
# However, for transactions (commit/rollback), you might need to handle them explicitly or use the connection as a context manager for transactions specifically.

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

with conn:
    # Automatically commits if no exception, otherwise rolls back
    conn.execute("INSERT INTO stocks VALUES ('2006-01-07', 'BUY', 'MSFT', 100, 65.10)")

conn.close()
```

[[programming/python/python]]