sqlalchemy: 'metadata.drop_all' does not work

Question

I tried to drop all the exist tables in the schema by using 'Base.metadata.drop_all(bind=engine)', and recreate a new table. However, the table did not be dropped as I thought, and records were just appended to the old table. I'm new to sqlalchemy, could anyone tell me what I was doing wrong here...thanks!

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://root:[email protected]:3306/bond?charset=utf8", echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base(bind=engine)
Base.metadata.drop_all(bind=engine)
table = Table('TradeHistory', Base.metadata,
     Column('Id', Integer, primary_key=True),
     Column('trade_date', String(50)),
     Column('rem_period', String(50)),
      Column('bond_code', String(50)),
      Column('bond_name', String(50)),
      Column('bond_rating', String(50)),
      Column('bond_yield', String(50)),
      Column('note', String(50)),
      Column('offer', String(50)),
      Column('bid', String(50)),
      Column('amount', String(50)))
Base.metadata.create_all(bind=engine)
conn.execute(table.insert(), listToWrite)
session.commit()
session.close()

Answer 1

Base.metadata.drop_all(bind=engine) will drop all tables that it knows about.

You are defining table 'TradeHistory' after that line, so that table is not going to be deleted.

So, first define your whole data model (that is usually done in a separate model module btw), then perform operations like drop_all and create_all.