title: How to read and insert bytea columns using psycopg2?
body: <p>I am working on a <code>Python</code> script to replicate some <code>Postgresql</code> tables from one environment to another (which does a little more than <code>pg_dump</code>). It works except when I am copying a table that has <code>bytea</code> data type.</p>
  
  <p>I read the source table data in memory, then I dump the memory in the target database with concatenated inserts.</p>
  
  <p>Here is my method that produces an insert statement:</p>
  
  <pre><code>def generateInsert(self, argCachedRow):
  
      colOrd = 0;
  
      valClauseList = []
  
      hasBinary = False
  
      for colData in argCachedRow:
          colOrd += 1
          colName = self.colOrdLookup.get(colOrd)
          col = self.colLookup.get(colName)
          dataType = col.dataType
  
          insVal = None
  
          if colData is not None:
  
              strVal = str(colData)
              if dataType.useQuote:
  
                  if "'" in strVal:
                      strVal = strVal.replace("'", "''")
                  insVal = "'%s'" % strVal
              else:
                  if dataType.binary:
                      hasBinary = True
                      #insVal = psycopg2.Binary(colData)
                  #else:
  
                  insVal = strVal
          else:
              insVal = "NULL"
  
          valClauseList.append(insVal)
  
      valClause = ", ".join(valClauseList)
  
      if hasBinary:
          valClause = psycopg2.Binary(valClause)
  
      result = "INSERT INTO %s VALUES (%s)" % (self.name, valClause)
  
      return result
  </code></pre>
  
  <p>which works with every table that doesn't have binary data.</p>
  
  <p>I also tried (intuitively) to wrap just the binary column data in <code>psycopg2.Binary</code>, which is the commented out line and then not do it to the whole row value list but that didn't work either.</p>
  
  <p>Here is my simple DataType wrapper, which is loaded by reading Postgres' information_schema tables:</p>
  
  <pre><code>class DataType(object):
      def __init__(self, argDispName, argSqlName, argUseQuote, argBin):
          self.dispName = argDispName
          self.sqlName = argSqlName
          self.useQuote = argUseQuote
          self.binary = argBin
  </code></pre>
  
  <p>How do I read and insert bytea columns using <code>psycopg2</code>?</p>