title: Python with postgres using named variables and bulk inserts
body: <p>I need some help understanding how Python and postgres handle transactions and bulk inserts specifically when inserting several data sets in a single transaction.
  Environment:</p>
  
  <ul>
  <li>Windows 7 64bit</li>
  <li>Python 3.2</li>
  <li>Postgresql 9.1</li>
  <li>psycopg2</li>
  </ul>
  
  <p>Here is my scenario: 
  I am converting data from one database(oracle) into xml strings and inserting that data into a new database(postgres). This is a large dataset so I'm trying to optimize some of my inserts. A lot of this data I'm considering library type objects, so I have a library table and then tables for my xml metadata and xml content, the fields for this data are text types in the database. I pull the data out of oracle and then I am creating dictionaries of the data I need to insert. I have 3 insert statements, the first insert creates a record in the library table using a serial id, and that id is necessary for the relationship in the next two queries that insert the xml into the metadata and content tables. Here is an example of what I'm talking about:</p>
  
  <pre><code>for inputKey in libDataDict.keys():
    metaString = libDataDict[inputKey][0]
    contentString = libDataDict[inputKey][1]
    insertLibDataList.append({'objIdent':"%s" % inputKey, 'objName':"%s" % inputKey, objType':libType})
    insertMetadataDataList.append({'objIdent':inputKey,'objMetadata':metaString}) 
    insertContentDataList.append({'objIdent':inputKey, 'objContent':contentString})
  
  dataDict['cmsLibInsert'] = insertLibDataList
  dataDict['cmsLibMetadataInsert'] = insertMetadataDataList
  dataDict['cmsLibContentInsert'] = insertContentDataList
  
  sqlDict[0] = {'sqlString':"insert into cms_libraries (cms_library_ident, cms_library_name, cms_library_type_id, cms_library_status_id) \
                values (%(objIdent)s, %(objName)s, (select id from cms_library_types where cms_library_type_name = %(objType)s), \
                (select id from cms_library_status where cms_library_status_name = 'active'))", 'data':dataDict['cmsLibInsert']}
  
  sqlDict[1] = {'sqlString':"insert into cms_library_metadata (cms_library_id, cms_library_metadata_data) values \
                ((select id from cms_libraries where cms_library_ident = %(objIdent)s), $$%(objMetadata)s$$)", \
                'data':dataDict['cmsLibMetadataInsert']}
  
  sqlDict[2] = {'sqlString':"insert into cms_library_content (cms_library_id, cms_library_content_data) values \
                ((select id from cms_libraries where cms_library_ident = %(objIdent)s), $$%(objContent)s$$)", \
                'data':dataDict['cmsLibContentInsert']}
  
  bulkLoadData(myConfig['pgConn'], myConfig['pgCursor'], sqlDict)
  </code></pre>
  
  <p>The problem I have is when I run the first query(sqlDict[0]) and do the insert everything works fine as long as I do it separate and commit before I run the next two. Ideally I would like all these queries in the same transaction, but it fails because it can't find the id from cms_libraries table for the 2nd and 3rd queries. 
  Here is my current insert code:</p>
  
  <pre><code>def bulkLoadData(dbConn, dbCursor, sqlDict):
   try:
     libInsertSql = sqlDict.pop(0)
     dbSql = libInsertSql['sqlString']
     data = libInsertSql['data']
     dbCursor.executemany(dbSql, data)
     dbConn.commit()
     for sqlKey in sqlDict:
       dbSql = sqlDict[sqlKey]['sqlString']
       data = sqlDict[sqlKey]['data']
       dbCursor.executemany(dbSql, data)
  
     dbConn.commit()
  </code></pre>
  
  <p>Previously I was appending the values into the query and then running a query for each insert. When I do that I can put it all in the same transaction and it finds the generated id and everything is fine. I don't understand why it doesn't find the id when I do the bulk insert with executemany()? Is there a way to do the bulk insert and the other two queries in the same transaction?</p>
  
  <p>I have been reading this documentation and searching stackoverflow and the internet but have not found an answer to my problem:
  <a href="http://initd.org/psycopg/docs/advanced.html#adapting-new-python-types-to-sql-syntax" rel="nofollow">pyscopg docs</a>
  as well as postgres's:
  <a href="http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html" rel="nofollow">Postgresql string docs</a></p>
  
  <p>Any help, suggestions, or comments would be appreciated.
  Thanks,
  Mitch</p>