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)
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
