[sqlalchemy] Invalid Parameter Type Error on SQL Server Insert

[Chris Leonard]

I'm struggling with inserting one-to-many relationship in SQL Server via
SQLAlchemy. I'm getting an Invalid parameter type error no matter what I
try. My input data is a line of JSON.

The error message is:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('Invalid parameter
type. param-index=2 param-type=list', 'HY105') u'INSERT INTO refQuery
(name, value, foreign_key) OUTPUT inserted.[rQ_id] VALUES (?, ?, ?), (?, ?,
?)' (u'url', None, [2L], u'pid', u'43d9f', [2L])

I've tried:
- deleting my database & recreating from scratch
- moving the insert statement inside the for-loop so that there's only one
insert at a time
- issuing the insert statement as conn.execute(refQuery.insert(), newDict)
- encoding all of my strings so that I'm not mixing strings & unicode
- set implicit_returning to False to disable the return of of the refQuery
primary key

I can get the insert statements to work within the ORM, but I'm working
with log files that have ~500k rows so ORM is crazy slow. So far no luck
duplicating my work within core.

My code is below. The first insert statement works as expected. I'd
appreciate any help.

import pyodbc
from sqlalchemy import BigInteger, Column, create_engine, ForeignKey,
String, MetaData, Table
meta = MetaData()
logFile = Table('logFile', meta,
Column('id', BigInteger, primary_key=True),
Column('referrer_anchor', String(900), nullable=True),
Column('referrer_hostname', String(900), nullable=True),
Column('referrer_path', String(900), nullable=True))
refQuery = Table('refQuery', meta,
Column('rQ_id', BigInteger, primary_key=True),
Column('name', String(8000)),
Column('value', String(8000)),
Column('foreign_key', None, ForeignKey('logFile.id'), nullable=False),
implicit_returning = False)
engine = create_engine(...)
conn = engine.connect()

# create dictionary
logKeys = ['referrer_anchor', 'referrer_hostname', 'referrer_path']
logVals = [myRefAnchor, myRefHost, myRefPath]
logDict = dict(zip(logKeys, logVals))

# insert
logInsert = logFile.insert().values(logDict)
result = conn.execute(logInsert) # THIS INSERT WORKS
ins_id = result.inserted_primary_key

if refQueryPairs:
newDict = []
names = ['name', 'value', 'foreign_key']
for k, v in refQueryPairs.items():
vals = [k, v, ins_id]
tempDict = dict(zip(names, vals))
newDict.append(tempDict)
ins = refQuery.insert().values(newDict)
conn.execute(ins)

[Jonathan Vanasco]

Notice the error:
'Invalid parameter type. param-index=2 param-type=list

And then the values you're putting in on index 2:
(u'url', None, [2L]

You're submitting a list, instead of single value.

Your problem is this line:

ins_id = result.inserted_primary_key

according to the docs

http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=inserted_primary_key#sqlalchemy.engine.ResultProxy.inserted_primary_key

"Return the primary key for the row just inserted.
The return value is a list of scalar values corresponding to the list of
primary key columns in the target table."

so you want:

ins_id = result.inserted_primary_key[0]

[Chris Leonard]

That solved it, thank you! I'd been struggling with this for 2 days,
awesome to move on to other things. In hindsight it seems so simple.

I posted this question on SO yesterday with no response. If you have an
account there & care to make a few extra points, copy/paste your answer and
I'll accept it as the solution.

http://stackoverflow.com/questions/26898670/invalid-parameter-type-error-from-sqlalchemy-insert-in-sql-server

--
Thanks,
Chris

Post by Jonathan Vanasco
'Invalid parameter type. param-index=2 param-type=list
(u'url', None, [2L]
You're submitting a list, instead of single value.
ins_id = result.inserted_primary_key
according to the docs
http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=inserted_primary_key#sqlalchemy.engine.ResultProxy.inserted_primary_key
"Return the primary key for the row just inserted.
The return value is a list of scalar values corresponding to the list of
primary key columns in the target table."
ins_id = result.inserted_primary_key[0]