# Test Insert Operations


<!-- WARNING: THIS FILE WAS AUTOGENERATED! DO NOT EDIT! -->

## Setup

``` python
from fastlite import *
```

Note: Make sure to use fastlite’s `database()` here

``` python
db = database(':memory:')
```

``` python
class People: id: int; name: str
```

``` python
people = db.create(People, pk='id')
```

## Test Single Inserts

Here we test `insert()`

### Test Cases for `insert()` Where Nothing Is Inserted

Test that calling `insert()` without any parameters doesn’t change
anything, and returns nothing

``` python
people.insert()
```

    {}

Test None doesn’t change anything.

``` python
count = people.count
assert people.insert(None) == {}
assert people.count == count
```

Test empty dict doesn’t change anything

``` python
count = people.count
assert people.insert({}) == {}
assert people.count == count
```

``` python
# Test empty dataclass doesn't change anything
PersonDC = people.dataclass()
count = people.count
assert people.insert(PersonDC()) == {}
assert people.count == count
```

``` python
# Test empty class instance doesn't change anything
class EmptyPerson: pass
count = people.count
assert people.insert(EmptyPerson()) == {}
assert people.count == count
```

### Single Insert Types

Test insert with keyword argument. Result should be the inserted item.

``` python
assert people.insert(name='Alice').name == 'Alice'
```

Test insert with dataclass

``` python
assert people.insert(People(name='Bobba')).name == 'Bobba'
```

Test with regular class

``` python
class Student: pass
student = Student()
student.name = 'Charlo'

assert people.insert(student).name == 'Charlo'
```

Verify count is 3

``` python
assert people.count == 3
```

### None and Empty String Handling

SQLite makes a clear distinction between NULL (represented as None in
Python) and an empty string (’’). Unlike some popular Python ORMs,
fastlite preserves this distinction because:

1.  NULL represents “unknown” or “missing” data
2.  Empty string represents “known to be empty”

These are semantically different concepts, and maintaining this
distinction allows users to make appropriate queries
(e.g. `WHERE name IS NULL` vs `WHERE name = ''`). The fact that fastlite
preserves this distinction in both directions (Python-\>SQLite and
SQLite-\>Python) is good database design.

Test inserting a record with name set to None

``` python
result = people.insert(name=None)
assert result.name is None
```

Test with empty string

``` python
result = people.insert(name='')
assert result.name == ''
```

``` python
assert people.get(pk_values=4).name == None
```

Remember, `get()` is for getting single items. The following would not
work here. `pk_values` can be a list only for tables with compound
primary keys.

``` python
# people.get(pk_values=[4,5])
```

### Other Cases

Test with special characters

``` python
assert people.insert(name='O\'Connor').name == "O'Connor"
assert people.insert(name='José').name == 'José'
```

Test id auto-increment

``` python
p1 = people.insert(name='Test1')
p2 = people.insert(name='Test2') 
assert p2.id == p1.id + 1
```

Test dict insert

``` python
assert people.insert({'name': 'Dict Test'}).name == 'Dict Test'
```

Test that extra fields raise `apsw.SqlError`

``` python
from sqlite3 import OperationalError
```

``` python
try:
    p = people.insert(name='Extra', age=25, title='Dr')
except SQLError as e:
    assert e.args[0] == 'table people has no column named age'
```

## Test Multiple Inserts

Here we test `insert_all()`

### Test cases for `insert_all()` where nothing is changed

Test empty list doesn’t change anything

``` python
count = people.count
people.insert_all([])
assert people.count == count
```

Test other empty iterables don’t change anything

``` python
count = people.count
people.insert_all(iter([]))  # empty iterator
people.insert_all(set())     # empty set
people.insert_all(tuple())   # empty tuple
assert people.count == count
```

Test that lists of `None` don’t change anything.

``` python
count = people.count
assert people.insert_all([None, None]) == people
assert people.result == []
assert people.count == count
```

### Test cases for `insert_all()` where records are inserted

Test that a list containing both None and a valid records only inserts
the valid record.

``` python
count = people.count
people.insert_all([None, None, None, None, None, dict(name='Dermot')])
assert people.count == count + 1
```

Test list of dicts

``` python
count = people.count
data = [{'name': 'Bulk1'}, {'name': 'Bulk2'}, {'name': 'Bulk3'}]
people.insert_all(data)
assert people.count == len(data) + count
```

Test `insert_all` with a list of dataclass instances to insert

``` python
count = people.count
Person = people.dataclass()
data = [Person(name=f'DC{i}') for i in range(3)]
people.insert_all(data)
assert people.count == count + 3
```

Test list of regular class instances

``` python
count = people.count
class Student:
    def __init__(self, name): self.name = name
students = [Student(f'Student{i}') for i in range(3)]
people.insert_all(students)
assert people.count == count + 3
```

### Edge Cases

Test mixed types in list

``` python
count = people.count
Person = people.dataclass()
mixed_data = [
    {'name': 'Dict1'},
    Person(name='DC1'),
    Student('Student1')
]
people.insert_all(mixed_data)
assert people.count == count + 3
```

Test None/empty strings in bulk insert

``` python
count = people.count
null_data = [
    {'name': None},
    {'name': ''},
    {'name': 'Regular'}
]
people.insert_all(null_data)
assert people.count == count + 3
```

Test with special characters in bulk

``` python
count = people.count
special_data = [
    {'name': "O'Brien"},
    {'name': 'José'},
    {'name': '张伟'}
]
res = people.insert_all(special_data)
assert people.count == count + 3
```

Test error on invalid column

``` python
try:
    people.insert_all([{'name': 'Valid'}, {'invalid_col': 'Bad'}])
except SQLError as e:
    assert 'no column named invalid_col' in str(e)
```