SQL Alchemy insert no parameters supplied

Question

I am trying to insert values to SQL Server database using SQL Alchemy (?,?,?) syntax for supplying parameters. I am encountering two issues.

python = "3.10.2"
SQLAlchemy = "^1.4.32"
pyodbc = "^4.0.32"

SQL Server drivers = ODBC Driver 17 for SQL Server

# Connection is made with this string
conn_params = server, database, uid, pwd
con_string = "DRIVER={ODBC Driver 17 for SQL Server};" + "SERVER={};"
            "DATABASE={};"
            "UID={};"
            "PWD={}".format(*conn_params)

# engine is created with
engine = sqlalchemy.create_engine(
    url="mssql+pyodbc://",
    creator=pyodbc.connect(con_string),
    echo=False,
    fast_executemany=True,
)

table for testing:
CREATE TABLE testing (
   id INT,
   name NVARCHAR(255)
);

1st Issue: '<' not supported between instances of 'str' and 'int' error is thrown when executed following

query = sqlalchemy.sql.text('INSERT INTO testing (id,name) VALUES (?,?,?,?,?,?)')
insert_values = [
            [3, "a"],
            [3, "f"],
            [3, "e"],
            [3, "d"],
            [3, "c"],
            [3, "b"]
        ]
with engine.connect() as con:
            con.execute(
                query,
                *insert_values
            )

I can't figure at which point it makes this comparison and fails (and why it does it?). My id field is defined as INT so it doesn't make sense to convert to string. For sake of tries and errors I did it anyway. Then occurs:

2nd Issue (pyodbc.ProgrammingError) ('Expected 6 parameters, supplied 0', 'HY000') [SQL: INSERT INTO testing (id,name) VALUES (?,?,?,?,?,?)] [parameters: ((), (), (), (), (), ())]

Exact same code as above but with ids converted to strings

insert_values=[
    ["3", "a"],
    ["3", "f"],
    ["3", "e"],
    ["3", "d"],
    ["3", "c"],
    ["3", "b"],
]

According to SQLAlchemy docs on supplying *multiparamater it seems to me that I am doing it correctly. Apparently I don't

I know that I can supply **kwargs arguments but would rather like to go with *args

Answer 1

I found an explanation to it here

btw. HoneyBadger made a valid point that number of "?" in VALUES (?, ?, ?) should be equal to number of columns I am inserting to, not number of rows I am inserting

The text()function requires Bound parameters in the named colon format. They are consistent regardless of database backend

So (?) syntax for supplying parameters is not available with TextClause

It can be done it on string though. Following code inserts data as expected:

query = 'INSERT INTO testing (id,name) VALUES (?,?)'  # Changed to string and reduces no. '?'
insert_values = [
            [3, "a"],
            [3, "f"],
            [3, "e"],
            [3, "d"],
            [3, "c"],
            [3, "b"]
        ]
with engine.connect() as con:
            con.execute(
                query,
                *insert_values
            )