title: SQLite and python - can't set primary keys
body: <p>I'm trying to create tables using python but when I inspect the data structure in SQLite, the primary keys aren't being assigned. Here's the code for one of the tables. It seems to work as intended except for the primary key part. I'm new to Python and SQLite so I'm probably missing something very obvious but can't find any answers.</p>
  <pre><code># Create a database and connect
  conn = sql.connect('Coursework.db')
  c = conn.cursor()
  
  # Create the tables from the normalised schema
  c.execute('CREATE TABLE IF NOT EXISTS room_host (room_ID integer PRIMARY KEY, host_ID integer)')
  
  
  c.execute(&quot;SELECT count(name) from sqlite_master WHERE type='table' AND name='room_host'&quot;)
  if c.fetchone()[0] == 1:
      c.execute(&quot;DROP TABLE room_host&quot;)
  else:
      c.execute('CREATE TABLE room_host (room_ID integer PRIMARY KEY, host_ID integer)')
  
  conn.commit()
  
  # read data from csv
  
  read_listings = pd.read_csv('listings.csv')
  room_host = pd.DataFrame(read_listings, columns=['id', 'host_id'])
  room_host.set_index('id')
  room_host.to_sql(&quot;room_host&quot;, conn, if_exists='append', index=False)
  
  c.execute(&quot;&quot;&quot;INSERT INTO room_host (id, host_ID)
  SELECT room_host.id, room_host.host_ID
  FROM room_host
  &quot;&quot;&quot;)
  </code></pre>