title: Docker POSTGRESQL - Insert multiple rows with string and bytea data
body: <p>I am facing some difficulties in inserting binary values (<code>bytea</code>) in a PostgreSQL DB using <code>psycopg2</code>. I am new to Postgres so pardon my ignorance, please.</p>
  <p>So, here's the scenario:</p>
  <ol>
  <li>I am scraping my university website (using <code>BeautifulSoup</code>) and am retrieving the notification they post in the Notification section.</li>
  <li>It's a shitty site, so the notification is posted in just a webpage, with the notification text hyperlinked to a pdf document.</li>
  <li>There are 7 sub-pages that I scrape. I know I am not using the right web-dev terminology, so here's what I mean: The root URL is <a href="https://exampleuniversity.com/" rel="nofollow noreferrer">https://exampleuniversity.com/</a> and the sub-pages are (in bold) 'https://exampleuniversity.com/<strong>univ-circulars/</strong>', 'https://exampleuniversity.com/<strong>univ-notification/</strong>' etc - and there are 7 of those</li>
  <li>These 7 sub-pages are placed in a list (called <code>paths</code>) and I iterate over each of them (and append it with the root URL) and scrape for the text of the notification, the URL and the pdf file itself. This all works like a charm. The issue is when I need to write them into the (docker) PostgreSQL table.</li>
  </ol>
  <p>FYI - the docker PostgreSQL container part by itself works fine.</p>
  <p>So as you can imagine, when I iterate over the list of the <em>sub-pages</em> I get a batch of (scraped) texts + URLs + pdf file that I need to write into the DB.</p>
  <p>Following <a href="https://www.postgresqltutorial.com/postgresql-python/blob/" rel="nofollow noreferrer">https://www.postgresqltutorial.com/postgresql-python/blob/</a>, I learnt that I will have to <code>open(file, 'rb').read()</code>, and do <code>psycopg2.Binary(&lt;variable_name&gt;)</code> when inserting it.</p>
  <p>All right. But this has to be done per iteration. So there's a list of columns (like, <em>texts</em> is a column, <em>urls</em> is column etc). So following <a href="https://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query">psycopg2: insert multiple rows with one query</a> (answer by <code>ant32</code>), I learnt that I will have to <code>args_str = ','.join(cur.mogrify(&quot;(%s,%s,%s,%s,%s,%s,%s,%s,%s)&quot;, x) for x in tup)</code> then <code>cur.execute(&quot;INSERT INTO table VALUES &quot; + args_str)</code> - Please assume I have the variables and the placeholders placed correctly.</p>
  <p>SO, I zip the retrieved text + URLs + <code>open('file', 'rb').read()</code> (this is per iteration) and make a tuple and try to follow the above example, and (naturally) it fails (FYI: every item in the tuple is a group of three elements now, which forms the <strong>row</strong> that needs to be inserted into the DB - as opposed to the columns that were discussed earlier). And that's the issue I am having.</p>
  <p>The error thrown is: <code>sequence item 0: expected str instance, bytes found</code></p>
  <p>In the code below, some comments have been marked with &quot;TO StackOverflow&quot;. That'd explain to you a bit more of what I was thinking of doing.</p>
  <p>Here's the code:</p>
  <pre><code>for path in paths:
      for i in range(retry_attempts):
          try:
              #Creating directoty to save the pdf files
              dir_to_create = f'./app/{today}/{path}'
              if not os.path.exists(dir_to_create):
                  os.makedirs(dir_to_create)
  
              print(f'\n****** Scraping - {path} ******'.upper())
              univ_notification_url = univ_url + path
              univ_notification_page = requests.get(univ_notification_url)
              soup = BeautifulSoup(univ_notification_page.content, 'html.parser')
              items = soup.find_all('div', {'class': 'entry'})
              desired_p_tags = items[0].find_all('p')
  
              #Removing &lt;p&gt; tags with no href (only with the string values as mentioned below)
              for _ in desired_p_tags:
                  if _.text == 'University Circulars' or 'Circulars, Notifications, Letters' in _.text:
                      desired_p_tags.remove(_)
  
              #Only retaining &lt;p&gt; tags that point to a pdf files (so the ones linking to another webpage is omitted)
              desired_p_tags = [_ for _ in desired_p_tags if '.pdf'  in (_.find('a'))['href']]
  
              texts = [_.text for _ in desired_p_tags]
              urls = [(_.find('a'))['href'] for _ in desired_p_tags]
  
              url_hash = [] #TO STACKOVERFLOW: I am also creating a blake2b hash for the files that I download and save it in the db. So the zipped tuple will contain this too
              pdf_obj = []
              for url in urls:
                  filename = os.path.basename(url) # To get the name of the downloaded file 
                  urllib.request.urlretrieve(url, f'{dir_to_create}/{filename}') # Downloading the file
                  pdf_obj.append(open(f'{dir_to_create}/{filename}', 'rb').read()) # Appending into the list the binary form of the downloaded file
                  url_hash.append(hashlib.blake2b(open(f'{dir_to_create}/{filename}', 'rb').read()).hexdigest()) # Hashing the file
                  print(f&quot;Downloaded {filename}\n&quot;)
                  
              data_to_insert = tuple(zip(texts, urls, pdf_obj, url_hash)) #Zipping the variables into a tuple
  
              # DATABASE PART
              conn = psycopg2.connect(
                  host = 'pg_db',
                  port = '5432',
                  database = 'bu_notifications', 
                  user = 'superuser', 
                  password = 'User1234!'
              )
  
              cur = conn.cursor()
  
              args_str = ','.join(cur.mogrify(&quot;(%s,%s,%s,%s)&quot;, x) for x in data_to_insert)
              cur.execute(&quot;INSERT INTO table00 (notification_text, item_url, pdf_file, pdf_hash) VALUES (%s, %s, %s, %s)&quot; + args_str)
              conn.commit()
              //Close connection and cursor (also not writing down the except clause that follows after a try)
  </code></pre>