SQL Server中SQLAlchemy插入的参数类型错误无效

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.

我正在努力通过SQLAlchemy在SQL服务器中插入一对多关系。无论如何，我都会得到一个无效的参数类型错误。

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:

当我运行这段代码时，直到第二次插入时，一切都正常工作。这意味着我的第一个insert语句logInsert是有效的。这是输入到语句的数据:

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

And here's what the logInsert statement looks like:

下面是logInsert语句的样子:

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:

我的第二个插入，conn.execute(ins)给出了以下错误:

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

我已经打印出了newDict，它的格式与SQLAlchemy教程页面上列出的内容完全匹配。

[{'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
将insert语句移动到for循环中，以便每次只有一个insert
issuing the insert statement as conn.execute(refQuery.insert(), newDict)
以conn.execute(refQuery.insert()、newDict类型)的形式发出insert语句
encoding all of my strings so that I'm not mixing strings & unicode
编码所有的字符串，这样就不会混合字符串和unicode了
set implicit_returning to False to disable the return of of the refQuery primary key
将implicit_return设置为False，以禁用refQuery主键的返回

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

到目前为止，一切都没有进展，每次都是同样的错误。有人能给我指出正确的方向吗?

1 个解决方案

#1

Notice the error:

注意到错误:

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

And then the values you're putting in on param-index 2:

然后你在参数指数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.

注意文档中的术语“标量值”。SqlAlchemy通常会返回一个“列表”，而您可能会有其他的期望。例如:如果您选择一个列，如session.query(Model.id).filter(id= 1).first()，您的结果将是(1，)，而不是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

我将“list”放在引号中，因为该列查询的响应是一个“类列表”或“类tuple”对象，名为“KeyedTuple”，类似于集合.namedtuple，来自teh标准库，允许您访问结果[0]和result.id之类的值

#1