title: How to convert a table stored remotely fetched by asyncpg and convert it to a sqlite3 one?
body: <p><strong>TLDR / Summmary</strong> :<br />
  How to convert / Cast remote database (and constituent tables) fetched by <code>asyncpg</code> into <code>sqlite3</code> ?</p>
  <p><br />
  I have a remote database (the wrapper connecting to this database is <code>asyncpg</code>) and wanted to clone it locally in <code>sqlite3</code> such that both table structure and data are cloned completely.</p>
  <p>My initial idea was to do something like :</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE new_table LIKE ?;
  INSERT INTO new_table SELECT * FROM ?;
  </code></pre>
  <p>but this runs into a problem of what object should be passed as an arg in sqlite3 cursor, since this dilemma couldn't be solved by myself, I thought of the next solution to be get the table name and the constituent column data to reconstruct the SQL query which could then be passed onto sqlite3, if worked correctly we could at least clone the table structure and then pass in the adequate data.</p>
  <p>To proceed with this notion of thinking, I first got all the tables in the remote database,</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE'
  </code></pre>
  <p>(Albeit this gets some tables that weren't even defined by me but this works)</p>
  <p>Now we have the table name (of every table), let's say there was a table fetched named <code>foo</code> so that we can work with it. Now we are interested in cloning this table locally into <code>sqlite3</code>, we <em>attempt</em> to do that by first getting the column info</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT column_name, data_type, character_maximum_length, column_default, is_nullable
  FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'foo`
  </code></pre>
  <p>Here is the code for &quot;reconstructing&quot; the table :</p>
  <pre class="lang-py prettyprint-override"><code>import asyncpg
  import asyncio
  import os
  from asyncpg import Record
  from typing import List
  
  
  def make_sql(table_name: str, column_data: List[Record]) -&gt; str:
      sql = f&quot;CREATE TABLE {table_name} (\n&quot;
      for i in column_data:
          sql += f&quot;{i['column_name']} {i['data_type'].upper()} {'NOT NULL' if i['is_nullable'].upper() == 'NO' else ''} {'' if i['column_default'] is None else i['column_default']},\n&quot;
      sql = sql[:-2] + &quot;\n);&quot;
      return sql
  
  
  async def main():
      con = await asyncpg.connect(os.getenv(&quot;DATABASE_URL&quot;))
      # In the actual case we will be getting every table name from the database
      # for now let's only focus on the table foo
      response = await con.fetch(
          &quot;SELECT * FROM information_schema.columns WHERE table_name = 'foo'&quot;
      )
      print(make_sql(&quot;foo&quot;, response))
      await con.close()
  
  
  asyncio.get_event_loop().run_until_complete(main())
  </code></pre>
  <p>Here is how the table <code>foo</code> was actually created :</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE foo (
                  bar bigint NOT NULL,
                  baz text NOT NULL,
                  CONSTRAINT foo_pkey PRIMARY KEY (bar)
              );
  </code></pre>
  <p>Here is how my code attempted to reconstruct the previous query :</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE foo (
  bar BIGINT NOT NULL ,
  baz TEXT NOT NULL 
  );
  </code></pre>
  <p>This very clearly has lost its structure (referring to <code>constraints</code>) while trying to reconstruct the query and I am sure there will be other edge cases that this didn't cover, and I think sewing and stitching strings around like this is extremely vulnerable, I think some other approach to this would be better because this seems fundamentally wrong.</p>
  <p>Which leads me to pose the question, how do I make it so that the exact table structure and data is cloned done to <code>sqlite3</code> properly?</p>