title: psycopg2 sql.SQL: Composed elements must be Composable
body: <pre class="lang-py prettyprint-override"><code>import psycopg2
  from psycopg2 import sql
  import datetime
  
  
  def connect(conn):
      &quot;&quot;&quot; Connect to the PostgreSQL database server &quot;&quot;&quot;
      # create a cursor
      cur = conn.cursor()
  
      # https://www.psycopg.org/docs/sql.html
  
      col_names = sql.SQL(', ').join([
          sql.Identifier('lookup'),
          sql.Identifier('LOL1'),
          sql.Identifier('LOL2'),
          sql.Identifier('identifier1'),
          sql.Identifier('identifier2'),
          sql.Identifier('timestamp_first_entry'),
          sql.Identifier('timestamp_last_entry'),
          sql.Identifier('some_number')
      ]),
  
      values = sql.SQL(', ').join([
          &quot;hash&quot;,
          [  # LOL1
              [[1.0, 2.0],  # list of lists of lists 1.1
               [3.0, 0.5]],
              [[-1.0, -2.0], # list of lists of lists 1.2
               [-3.0, -4.0]]
          ], [  # LOL2
              [[1.0, 2.0],  # list of lists of lists 2.1
               [3.0, 0.5]],
              [[-1.0, -2.0], # list of lists of lists 2.2
               [-3.0, -4.0]]
          ],
          &quot;identifier1&quot;,
          &quot;identifier2&quot;,
          datetime.datetime(2021, 5, 10),
          datetime.datetime(2021, 5, 12),
          20
      ])
  
      query_base = sql.SQL(&quot;insert into {table_name}({col_names} values ({values}))&quot;.format(
          table_name=sql.Identifier(&quot;raw_orderbook&quot;),
          col_names=col_names,
          values=values
      ))
  
      cur.execute(query_base)
  
      cur.close()
  
  
  # https://www.psycopg.org/docs/sql.html
  
  if __name__ == '__main__':
      conn = psycopg2.connect(
          host=&quot;localhost&quot;,
          database=&quot;test&quot;,
          user=&quot;ian&quot;,
          password=&quot;&quot;)
  
      connect(conn)
  </code></pre>
  <p>results in an error of</p>
  <pre class="lang-py prettyprint-override"><code>Traceback (most recent call last):
    File &quot;/home/ian/PycharmProjects/panthera/src/database/tester.py&quot;, line 73, in &lt;module&gt;
      connect(conn)
    File &quot;/home/ian/PycharmProjects/panthera/src/database/tester.py&quot;, line 33, in connect
      values = sql.SQL(', ').join([
    File &quot;/home/ian/anaconda3/envs/panthera/lib/python3.9/site-packages/psycopg2/sql.py&quot;, line 288, in join
      return Composed(rv)
    File &quot;/home/ian/anaconda3/envs/panthera/lib/python3.9/site-packages/psycopg2/sql.py&quot;, line 109, in __init__
      raise TypeError(
  TypeError: Composed elements must be Composable, got 'lookup' instead
  </code></pre>
  <p>Given that <code>composable</code> is the base class, I'm not really sure what the error is because of. As far as I know it <em>seems</em> correct based on the examples from the <a href="https://www.psycopg.org/docs/sql.html" rel="nofollow noreferrer">docs</a>. My <code>sql_schema</code></p>
  <pre><code>DROP TABLE IF EXISTS &quot;raw_orderbooks&quot;;
  CREATE TABLE &quot;raw_orderbooks&quot;
  (
  
      lookup                text, -- essentially our lookup key comprising: hash(exchange_pair_timestapOfFirstEntry)
      LOL1                  DOUBLE PRECISION[][][],
      LOL2                  DOUBLE PRECISION[][][],
      identifier1                  text,
      identifier2            text,
      timestamp_first_entry TIMESTAMP,
      timestamp_last_entry  TIMESTAMP,
      some_number       int
  );
  </code></pre>
  <p>one other idea I had was to wrap the non-datetime values in <code>sql.Literal</code> but then I run into the datetime issue.</p>
  <p>Looking around, I've seen other people using different notation but it feels less &quot;clean&quot; which is definitely subjective but IDK.</p>
  <h1>Edit</h1>
  <p>Based on the comments from the answer, my updated script is</p>
  <pre><code>import psycopg2
  from psycopg2 import sql
  import datetime
  
  
  def connect(conn):
      &quot;&quot;&quot; Connect to the PostgreSQL database server &quot;&quot;&quot;
      # create a cursor
      cur = conn.cursor()
  
      # https://www.psycopg.org/docs/sql.html
  
      col_names = sql.SQL(', ').join([
          sql.Identifier('lookup'),
          sql.Identifier('LOL1'),
          sql.Identifier('LOL2'),
          sql.Identifier('identifier1'),
          sql.Identifier('identifier2'),
          sql.Identifier('timestamp_first_entry'),
          sql.Identifier('timestamp_last_entry'),
          sql.Identifier('some_number')
      ]),
  
      values = sql.SQL(', ').join([
          &quot;hash&quot;,
          [  # LOL1
              [[1.0, 2.0],  # list of lists of lists 1.1
               [3.0, 0.5]],
              [[-1.0, -2.0], # list of lists of lists 1.2
               [-3.0, -4.0]]
          ], [  # LOL2
              [[1.0, 2.0],  # list of lists of lists 2.1
               [3.0, 0.5]],
              [[-1.0, -2.0], # list of lists of lists 2.2
               [-3.0, -4.0]]
          ],
          &quot;identifier1&quot;,
          &quot;identifier2&quot;,
          datetime.datetime(2021, 5, 10),
          datetime.datetime(2021, 5, 12),
          20
      ])
      table_col_names = ['lookup','LOL1','LOL2','identifier1','identifier2','timestamp_first_entry','timestamp_last_entry','some_number']
  
      col_names = sql.SQL(', ').join(sql.Identifier(n) for n in table_col_names )
  
      place_holders = sql.SQL(', ').join(sql.Placeholder() * len(table_col_names ))
  
      query_base = sql.SQL(&quot;insert into {table_name} ({col_names}) values ({values})&quot;).format(
          table_name=sql.Identifier(&quot;raw_orderbook&quot;),
          col_names=col_names,
          values=place_holders
      )
  
      print(query_base.as_string(conn))
      cur.execute(query_base,values)
  
      cur.close(
  </code></pre>
  <p>but I'm still running into the issue <code>TypeError: Composed elements must be Composable, got 'lookup' instead</code></p>