title: SQLAlchemy parse a large file
body: <p>How to parse a big file (50-100GB) into my database using SQLAlchemy? Let's say I have two tables.</p>
  
  <pre><code>import collections
  import re
  import Bio.SeqIO
  import sqlalchemy
  from sqlalchemy import ForeignKey, UniqueConstraint
  from sqlalchemy import Column, Float, Integer, String, Text, DateTime
  from sqlalchemy.sql import func
  from sqlalchemy.orm import relationship
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.sql.expression import ClauseElement
  
  Base = declarative_base()
  
  class Protein_sequence(Base):
      __tablename__ = 'protein_sequence'
      prot_seq_id = Column(Integer, primary_key=True)
      prot_seq = Column(Text, unique=True)
      protein_annotation = relationship('Protein', back_populates='protein_sequence')
  
  class Protein(Base):
      __tablename__ = 'protein_annotation'
      prot_id = Column(Integer, primary_key=True)
      prot_seq_id = Column(Integer, ForeignKey('protein_sequence.prot_seq_id'))
      prot_acc = Column(Text, unique=True)
      prot_name = Column(Text)
      protein_sequence = relationship('Protein_sequence', back_populates='protein_annotation')
  
  def parse_fasta(path, prot_db='unknown', taxon_name=None, taxon_id=None):
      """Parsing a fasta file (UniProt or NCBInr)."""
  
      prot = collections.OrderedDict()
  
      for record in Bio.SeqIO.parse(path, 'fasta'):
  
          prot['seq'] = str(record.seq)
          desc = record.description
  
          gi_num = re.findall('^gi\|([0-9]+)(?:\||\s|$)', desc)
          if gi_num:
              prot['prot_gi'] = gi_num[0]
          desc = re.sub('^gi\|([0-9]+)(?:\||\s|$)', '', desc)
  
          prot_db = re.findall('^([^|]+)\|', desc)
          if prot_db:
              prot_db = prot_db[0]
          prot['prot_db'] = prot_db
  
          prot_acc = re.findall('^[^|]+\|([^ ]+)', desc)[0]
          prot['prot_acc'] = prot_acc
  
          prot['prot_name'] = re.findall('^[^ ]+ (.+)', desc)[0]
  
          yield prot
  
  def prot_db_from_fasta():
      """Create tables in SQLite database. Input fasta file."""
  
      db = 'sqlite:///proteomic.db'
      engine = sqlalchemy.create_engine(db)
      Base.metadata.create_all(engine)
      Session = sqlalchemy.orm.sessionmaker(bind=engine)
      session = Session()
      conn = engine.connect()
  
      p = 'prot.fasta'
  
      for prot in parse_fasta(p):
  
          # prot is dictionary storing info about protein
          protein_sequence = Protein_sequence(prot_seq=prot['seq'])
          session.add(protein_sequence)
  
          try:
              session.commit()
          except:
              session.rollback()
  
          # choose only columns in table protein_annotation
          cols = [c.name for c in Protein.__table__.columns]
          annotation = {key: prot[key] for key in cols if key in prot}
  
          annotation['prot_seq_id'] = protein_sequence.prot_seq_id
          protein_annotation = Protein(**annotation)
          session.add(protein_annotation)
  
      session.commit()
      conn.close()
  
  # run function to insert data into database
  prot_db_from_fasta()
  </code></pre>
  
  <p>The problem is that I need information about sequence ID (for annotation table) and in the same time insert sequence into database unless it is already there. Using SQLAlchemyCore will not help, the problem is I am using session commit in every for cycle. And it is very slow. If I use this script for 70MB big file, it takes 17 sec. If I use sqlite3 instead of SQLAlchemy it takes only 0.3 sec.</p>
  
  <p>I know it is better to insert data in one big transaction but how to do it. I will not get back sequence ID to use them for my protein annotation.</p>
  
  <p>There is also example of fasta file.</p>
  
  <pre><code>&gt;gi|115646|sp|P02662.2|CASA1_BOVIN Alpha-S1-casein
  MKLLILTCLVAVALARPKHPIKHQGLPQEVLNENLLRFFVAPFPEVFGKEKVNELSKDIGSESTEDQAME
  DIKQMEAESISSSEEIVPNSVEQKHIQKEDVPSERYLGYLEQLLRLKKYKVPQLEIVPNSAEERLHSMKE
  GIHAQQKEPMIGVNQELAYFYPELFRQFYQLDAYPSGAWYYVPLGTQYTDAPSFSDIPNPIGSENSEKTT
  MPLW
  
  &gt;gi|115654|sp|P02663.2|CASA2_BOVIN Alpha-S2-casein
  MKFFIFTCLLAVALAKNTMEHVSSSEESIISQETYKQEKNMAINPSKENLCSTFCKEVVRNANEEEYSIG
  SSSEESAEVATEEVKITVDDKHYQKALNEINQFYQKFPQYLQYLYQGPIVLNPWDQVKRNAVPITPTLNR
  EQLSTSEENSKKTVDMESTEVFTKKTKLTEEEKNRLNFLKKISQRYQKFALPQYLKTVYQHQKAMKPWIQ
  PKTKVIPYVRYL
  </code></pre>
  
  <p>So what is good practice to insert data into database.</p>
  
  <p><a href="https://i.stack.imgur.com/IPS4d.png" rel="nofollow noreferrer">Picture of my proteomic database.</a></p>