title: add a bunch of rows from csv, then keep counting primary key where that left off (postgres, flask-sqlalchemy)
body: <p>I have a postgres database that I'm mainly interacting with in python using sqlalchemy (via flask-sqlalchemy).  I'd like to be able to add the initial data for my tables directly in psql from a CSV, and then be able to programmatically add new rows later.</p>
  
  <p>But I'm having problems with my primary key.  Specifically, if I include it in the csv file for the initial data, then when I try to add new rows programmatically, the new rows try to start the primary key at 1, and promptly fail because that key already exists.  </p>
  
  <p><strong>Details and code</strong></p>
  
  <p>The relevant part of my sqlalchemy code: </p>
  
  <pre><code>class Contracts(db.Model):
      __tablename__ = "contracts"
      id = db.Column(db.Integer, primary_key=True)
      contract = db.Column(db.Text())
      # a bunch of other columns that are irrelevant follow here
  
      def __init__(self, contract, url, firstadded, firstaddedby):
          self.contract = contract
          # and all the other irrelevant columns.  
          # not ID, because that's to be auto-incremented.
  </code></pre>
  
  <p>I then create the tables from the python shell with: </p>
  
  <pre><code>&gt;&gt;&gt; from app import db
  &gt;&gt;&gt; db.create_all()
  </code></pre>
  
  <p>Confirming that this produced what I expected, the relevant parts of the first two rows of <code>\d+ contracts</code> in psql: </p>
  
  <pre><code>id      | integer        | not null default nextval('contracts_id_seq'::regclass)
  contract| text           |
  </code></pre>
  
  <p>So then, I have a csv file as follows:</p>
  
  <pre><code>1,this is a word,http://foo.bar,never,test,f,,,,,
  2,this is a word or t20,http://foo.baz,never,test,f,,,,,
  </code></pre>
  
  <p>which I add to the contracts table from psql as follows: 
  <code>\copy contracts from 'path/to/csv/file' with (format csv);</code></p>
  
  <p>and, as expected, selecting from the table reveals two rows, with ids 1 and 2. </p>
  
  <p>But then suppose I open up a shell and try to enter some data with the sqlalchemy code: </p>
  
  <pre><code>&gt;&gt;&gt;from database import Contracts
  &gt;&gt;&gt; k = Contracts("here's some text", "url column", "firstadded column", "firstaddedby column")
  &gt;&gt;&gt; db.session.add(k)
  &gt;&gt;&gt; db.session.commit()
  </code></pre>
  
  <p>but it fails with the expected error (in relevant part) <code>(psycopg2.IntegrityError) duplicate key value violates unique constraint "contracts_pkey"
  DETAIL:  Key (id)=(1) already exists.</code></p>
  
  <p>I tried to just remove the first column from the csv, or leave it blank, on the theory that maybe posgres could be convinced to start an auto-incrementing index if it wasn't supplied.  But in both cases importing the csv failed--- removing failed because it treated the second column as the id and blew up with an <code>invalid input syntax for integer</code> --- adding an empty column failed because it violated the not-null constraint.</p>
  
  <p>Help?  Surely there's a straightforward way to add rows from a csv and then continue counting up the primary key where that left off?</p>