title: Import CSV to database using sqlalchemy
body: <p>I am using <a href="https://stackoverflow.com/questions/31394998/using-sqlalchemy-to-load-csv-file-into-a-database">this</a> example to upload a csv file into a sqlite database:</p>
  
  <p>this is my code:</p>
  
  <pre><code>from numpy import genfromtxt
  from time import time
  from datetime import datetime
  from sqlalchemy import Column, Integer, Float, Date, String, VARCHAR
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy import create_engine
  from sqlalchemy.orm import sessionmaker
  
  def Load_Data(file_name):
      data = genfromtxt(file_name, delimiter=',')# skiprows=1, converters={0: lambda s: str(s)})
      return data.tolist()
  
  Base = declarative_base()
  
  class cdb1(Base):
      #Tell SQLAlchemy what the table name is and if there's any table-specific arguments it should know about
      __tablename__ = 'cdb1'
      __table_args__ = {'sqlite_autoincrement': True}
      #tell SQLAlchemy the name of column and its attributes:
      id = Column(Integer, primary_key=True, nullable=False) 
      name = Column(VARCHAR(40))
      shack = Column(VARCHAR)
      db = Column(Integer)
      payments = Column(Integer)
      status = Column(VARCHAR)
  
  
  if __name__ == "__main__":
      t = time()
      print 'creating database'
  
      #Create the database
      engine = create_engine('sqlite:///cdb.db')
      Base.metadata.create_all(engine)
  
      #Create the session
      session = sessionmaker()
      session.configure(bind=engine)
      s = session()
  
      try:
          file_name = 'client_db.csv'
          data = Load_Data(file_name)
  
          for i in data:
              record = cdb1(**{
                  'name' : i[0],
                  'shack' : i[1],
                  'db' : i[2],
                  'payments' : i[3],
                  'status' : i[4]
              })
              s.add(record) #Add all the records
  
          s.commit() #Attempt to commit all the records
      except:
          s.rollback() #Rollback the changes on error
          print 'error in reading'
      finally:
          s.close() #Close the connection
      print "Time elapsed: " + str(time() - t) + " s." #0.091s
  </code></pre>
  
  <p>and this is the first few rows of the csv file:</p>
  
  <pre><code>Name,Shack,DB,Payments,Status
  Loyiso Dwala,I156,13542,37,LightsOnly ON
  Attwell Fayo,I157,13077,32,LightsON
  David Mbhele,G25,13155,33,LightsON
  </code></pre>
  
  <p>The DB is created ok, but only some of the data is captured into the attributes: the 'payments' and 'db' column are populated correctly, but everything else comes out as NULL.</p>
  
  <p>UPDATED CORRECT CODE (using pandas dataframe):</p>
  
  <pre><code>from numpy import genfromtxt
  from time import time
  from datetime import datetime
  from sqlalchemy import Column, Integer, Float, Date, String, VARCHAR
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy import create_engine
  from sqlalchemy.orm import sessionmaker
  import csv
  import pandas as pd
  
  
  #def Load_Data(file_name):
      #data = csv.reader(file_name, delimiter=',')# skiprows=1, converters={0: lambda s: str(s)})
      #return data.tolist()
  
  Base = declarative_base()
  
  class cdb1(Base):
      #Tell SQLAlchemy what the table name is and if there's any table-specific arguments it should know about
      __tablename__ = 'cdb1'
      __table_args__ = {'sqlite_autoincrement': True}
      #tell SQLAlchemy the name of column and its attributes:
      id = Column(Integer, primary_key=True, nullable=False) 
      Name = Column(VARCHAR(40))
      Shack = Column(VARCHAR)
      DB = Column(Integer)
      Payments = Column(Integer)
      Status = Column(VARCHAR)
  
  engine = create_engine('sqlite:///cdb.db')
  Base.metadata.create_all(engine)
  file_name = 'client_db.csv'
  df = pd.read_csv(file_name)
  df.to_sql(con=engine, index_label='id', name=cdb1.__tablename__, if_exists='replace')
  </code></pre>