In [1]:

    

import os

import sqlalchemy
from sqlalchemy import orm
from sqlalchemy import Column, Integer, String, Float, Date, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

Base = declarative_base()

db_location = r"C:\Users\dsx.AD3\Projects\scratch\test.sqlite"

db_engine = sqlalchemy.create_engine('sqlite:///{}'.format(db_location))

Session = orm.sessionmaker(bind=db_engine, autoflush=False)
Session.configure(bind=db_engine)


    

In [2]:

    

def make_tables():
	print("Creating tables")
	Base.metadata.create_all(db_engine)


def recreate_tables():
	try:
		os.remove(db_location)
	except WindowsError:
		pass  # likely that the file doesn't exist. Try to create tables now
	make_tables()


    

In [3]:

    

class Site(Base):
	__tablename__ = "sites"

	id = Column(Integer, primary_key=True)
	name = Column(String)
	code = Column(String, unique=True)


    

In [4]:

    

class WaterQuality(Base):
	"""
		Each instance of this class is an observation in the database
	"""
	__tablename__ = 'water_quality'

	id = Column(Integer, primary_key=True)

	site_id = Column(Integer, ForeignKey('sites.id'))
	site = relationship("Site",
						backref="water_quality_records")


    

In [5]:

    

recreate_tables()
session1 = Session()


    

    




Creating tables

In [6]:

    

s = Site()
s.code = "wqt"
session1.add(s)
len(session1.new)
session1.commit()


    

In [7]:

    

wq = WaterQuality()
session1.add(wq)
len(session1.new)


    

    
Out[7]:





1

In [11]:

    

for i in range(1000):
    wq = WaterQuality()
    session1.query(Site).filter(Site.code == "wqt").one()
    session1.add(wq)


    

In [12]:

    

len(session1.new)


    

    
Out[12]:





1002

In [ ]: