Invalid Parameter Type Error from SQLAlchemy Insert in SQL Server

Question

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.

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))

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)
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)

When I run this code, everything works correctly until the second insert. This means that my first insert statement logInsert works. Here's the data that's input to that statement:

{'referrer_anchor': '"site.com"', 'referrer_hostname': '"site.com"', 'referrer_path':
 '"/clothing/mens/shoes/6114"'}

And here's what the logInsert statement looks like:

INSERT INTO activate (referrer_anchor, referrer_hostname, referrer_path) VALUES 
(:referrer_anchor, :referrer_hostname, :referrer_path)

My second insert, conn.execute(ins) gives the following error:

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'4
3d9f', [2L])

I've printed out the newDict, and its format exactly matches what's listed on the SQLAlchemy Tutorial Page

[{'name': u'url', 'value': None,     'foreign_key': [2L]}, 
 {'name': u'pid', 'value': u'43d9f', 'foreign_key': [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

Nothing has worked so far, same error each time. Can someone point me in the right direction?

Jonathan Vanasco · Accepted Answer · 2014-11-13 20:03:48Z

Notice the error:

 'Invalid parameter type.  param-index=2 param-type=list

And then the values you're putting in on param-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]

Watch out for the term "scalar value" in the docs. SqlAlchemy often returns a "list" when you might expect otherwise. For example: if you select a single column, like session.query(Model.id).filter(id==1).first(), your result will be (1, ), not 1.

I put "list" in quotes, because the response on that column query is a "list-like" or "tuple-like" object called a "KeyedTuple" that is similar to the collections.namedtuple from teh standard library, and allows you to access the value like result[0] and result.id

Collectives™ on Stack Overflow

Invalid Parameter Type Error from SQLAlchemy Insert in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related