Test Insert Operations

Setup

from fastlite import *

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

db = database(':memory:')

class People: id: int; name: str

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

people.insert()

{}

Test None doesn’t change anything.

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

Test empty dict doesn’t change anything

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

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

# 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.

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

Test insert with dataclass

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

Test with regular class

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

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

Verify count is 3

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

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

Test with empty string

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

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.

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

Other Cases

Test with special characters

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

Test id auto-increment

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

Test dict insert

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

Test that extra fields raise apsw.SqlError

from sqlite3 import OperationalError

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

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

Test other empty iterables don’t change anything

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.

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.

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

Test list of dicts

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

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

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

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

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

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

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