Type matching problem when trying to add rows using SQLAlchemy with 'sqlite' driver

Question

I have simple example from a book about using SQLAlchemy on 1st level, which is close to DB-API. Since this book came out it's syntax changed and I have to modify code to have it worked. I have a problem inserting row in a table, using positional placeholders in a pre-writen string. Here is the code:

import sqlalchemy as sa
from sqlalchemy import text

engine = sa.create_engine('sqlite://')

with engine.connect() as conn:

    print (conn)
    conn.execute(text('''CREATE TABLE zoo (
                 critter VARCHAR(20) PRIMARY KEY,
                 count INT,
                 damage FLOAT)'''))

    ins = text('INSERT INTO zoo (critter, count, damage) VALUES (?,?,?)')
    conn.execute (ins, [('bear', 2, 1000.0)])
    conn.execute (ins, [('weasel', 1, 2000.0)])

    rows = conn.execute('SELECT * FROM zoo')
    for row in rows:
       print (row)

for some reason, it raises this:

'<' not supported between instances of 'int' and 'str'

...although I do not matching anything and values order seems to be right (for the corresponding columns). Please, help to understand what is wrong here?

Answer 1

The issue seems related to how you pass the parameters to the execute method.

Try this one

import sqlalchemy as sa
from sqlalchemy import text

engine = sa.create_engine('sqlite://')

with engine.connect() as conn:
    # Create table
    conn.execute(text('''CREATE TABLE zoo (
                 critter VARCHAR(20) PRIMARY KEY,
                 count INT,
                 damage FLOAT)'''))

    # Fix 1: Pass parameters as a dictionary or direct values, not as a list of tuples
    ins = text('INSERT INTO zoo (critter, count, damage) VALUES (:critter, :count, :damage)')
    
    # Option 1: Using named parameters
    conn.execute(ins, {"critter": "bear", "count": 2, "damage": 1000.0})
    conn.execute(ins, {"critter": "weasel", "count": 1, "damage": 2000.0})
    
    # OR Option 2: Using positional parameters with proper syntax
    # ins = text('INSERT INTO zoo (critter, count, damage) VALUES (?, ?, ?)')
    # conn.execute(ins, ["bear", 2, 1000.0])  # Pass values directly, not as a list of tuples
    
    # Fetch results
    result = conn.execute(text('SELECT * FROM zoo'))
    for row in result:
        print(row)