In [1]:

    

from sqlalchemy import Column, Integer, String, Date, DateTime, create_engine, ForeignKey, func, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref, joinedload, subqueryload
import datetime

engine=create_engine('mssql+pyodbc://pilotfish:setinstone@pilotfishdb.c5zdfsvfmy5u.us-west-2.rds.amazonaws.com:1433/FishBase', echo=True)
engine.connect()
Base = declarative_base()
Session = sessionmaker(bind = engine)

class Person(Base):
	__tablename__ = 'Persons'
	PersonID = Column(Integer, primary_key = True, autoincrement=False)
	FirstName = Column(String(50))
	LastName = Column(String(50))
	Password = Column(String(20))
	Department = Column(String(20))
	Position = Column(String(20))
	Office = Column(String(20))
	PhoneNumber = Column(String(20))
	Email = Column(String(50))
	
	Skill1 = Column(String(20))
	Skill2 = Column(String(20))
	Skill3 = Column(String(20))
	
	Interest1 = Column(String(20))
	Interest2 = Column(String(20))
	
	Campaigns = relationship("Campaign", primaryjoin= "Campaign.Creator == Person.PersonID",  backref="Person")
	
	ContributedTo = relationship("Contribution", primaryjoin= "Contribution.ContributorID == Person.PersonID", backref="Contributor")

	Ventures = relationship("Venture", primaryjoin = "Venture.CreatorID == Person.PersonID", backref="Creator")
	
	Comments = relationship('Comment', primaryjoin = 'Comment.Author == Person.PersonID', backref='Commentator')
	
	def get_monthly_contribution(self):
		sum = int()
		today = datetime.datetime.now()
		for c in self.ContributedTo:
			if(c.SubTime.year == today.year and c.SubTime.month == today.month):
				sum += c.Contribution
		return sum
	
	def __init__(self, PersonID, FirstName, LastName, Password, Department, Position, Office, PhoneNumber, Email, Skill1, Skill2, Skill3, Interest1, Interest2):
		self.PersonID = PersonID
		self.FirstName = FirstName
		self.LastName = LastName
		self.Password = Password
		self.Department = Department
		self.Position = Position
		self.Office = Office
		self.PhoneNumber = PhoneNumber
		self.Email = Email
		
		self.Skill1 = Skill1
		self.Skill2 = Skill2
		self.Skill3 = Skill3
		
		self.Interest1 = Interest1
		self.Interest2 = Interest2

	def is_authenticated(self):
		return True

	def is_active(self):
		return True

	def is_anonymous(self):
		return False

	def get_id(self):
		return self.PersonID

	def __repr__(self):
		return self.FirstName

class Venture(Base):
	__tablename__ = 'Ventures'
	Title = Column(String(50), primary_key = True)
	ShortDesc = Column(String(300))
	Backers = Column(Integer)
	CreatorID = Column(Integer, ForeignKey('Persons.PersonID'))
	
class Campaign(Base):
	__tablename__ = 'Campaigns'
	CampaignTitle = Column(String(50), primary_key = True)
	ShortDesc = Column(String(300))
	DatePosted = Column(Date)
	Creator = Column(Integer, ForeignKey('Persons.PersonID'))
	
	IndividualContributions = relationship("Contribution", primaryjoin= "Contribution.CampaignName == Campaign.CampaignTitle", backref="ContributionTarget")
	
	Comments = relationship('Comment', primaryjoin = 'Comment.ParentPost == Campaign.CampaignTitle', backref='TopicCampaign')
	
	def getContributionSum(self):
		sum = int()
		for c in self.IndividualContributions:
			sum += c.Contribution
		return sum
	
	def getNumBackers(self):
		return len(self.IndividualContributions)
	
class Contribution(Base):
	__tablename__ = 'Contributions'
	ContributorID = Column(Integer, ForeignKey('Persons.PersonID'), primary_key = True)
	CampaignName = Column(String(20), ForeignKey('Campaigns.CampaignTitle'), primary_key = True)
	Contribution = Column(Integer)
	SubTime = Column(DateTime, primary_key = True)
	
	def __init__(self, ContributorID, CampaignName, Contribution, SubTime):
		self.ContributorID = ContributorID
		self.CampaignName = CampaignName
		self.Contribution = Contribution
		self.SubTime = SubTime
		
class Comment(Base):
	__tablename__ = 'Comments'
	ParentPost = Column(String(50), ForeignKey('Campaigns.CampaignTitle'), primary_key = True)
	Author = Column(String(20), ForeignKey('Persons.PersonID'), primary_key = True)
	SubTime = Column(DateTime, primary_key = True)
	Content = Column(Text)
	
	def __init__(self, ParentPost, Author, Content):
		self.ParentPost = ParentPost
		self.Author = Author
		self.SubTime = datetime.datetime.now()
		self.Content = Content


    

    




2014-07-14 21:50:25,618 INFO sqlalchemy.engine.base.Engine SELECT user_name()






    




INFO:sqlalchemy.engine.base.Engine:SELECT user_name()






    




2014-07-14 21:50:25,619 INFO sqlalchemy.engine.base.Engine ()






    




INFO:sqlalchemy.engine.base.Engine:()






    




2014-07-14 21:50:25,697 INFO sqlalchemy.engine.base.Engine 
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE name = ?
            AND type = 'S'
            






    




INFO:sqlalchemy.engine.base.Engine:
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE name = ?
            AND type = 'S'
            






    




2014-07-14 21:50:25,697 INFO sqlalchemy.engine.base.Engine (u'pilotfish',)






    




INFO:sqlalchemy.engine.base.Engine:(u'pilotfish',)






    




2014-07-14 21:50:25,996 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1






    




INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1






    




2014-07-14 21:50:25,996 INFO sqlalchemy.engine.base.Engine ()






    




INFO:sqlalchemy.engine.base.Engine:()






    




2014-07-14 21:50:26,072 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1






    




INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1






    




2014-07-14 21:50:26,072 INFO sqlalchemy.engine.base.Engine ()






    




INFO:sqlalchemy.engine.base.Engine:()

In [9]:

    

name = 'Alcohol'
session = Session()
result = session.query(Campaign).options(joinedload(Campaign.Person), joinedload(Campaign.IndividualContributions), subqueryload(Campaign.Comments).joinedload(Comment.Commentator)).filter(Campaign.CampaignTitle==name).first()
session.close()


    

    




2014-07-14 21:54:37,782 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)






    




INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)






    




2014-07-14 21:54:37,785 INFO sqlalchemy.engine.base.Engine SELECT anon_1.[Campaigns_CampaignTitle] AS [anon_1_Campaigns_CampaignTitle], anon_1.[Campaigns_ShortDesc] AS [anon_1_Campaigns_ShortDesc], anon_1.[Campaigns_DatePosted] AS [anon_1_Campaigns_DatePosted], anon_1.[Campaigns_Creator] AS [anon_1_Campaigns_Creator], [Contributions_1].[ContributorID] AS [Contributions_1_ContributorID], [Contributions_1].[CampaignName] AS [Contributions_1_CampaignName], [Contributions_1].[Contribution] AS [Contributions_1_Contribution], [Contributions_1].[SubTime] AS [Contributions_1_SubTime], [Persons_1].[PersonID] AS [Persons_1_PersonID], [Persons_1].[FirstName] AS [Persons_1_FirstName], [Persons_1].[LastName] AS [Persons_1_LastName], [Persons_1].[Password] AS [Persons_1_Password], [Persons_1].[Department] AS [Persons_1_Department], [Persons_1].[Position] AS [Persons_1_Position], [Persons_1].[Office] AS [Persons_1_Office], [Persons_1].[PhoneNumber] AS [Persons_1_PhoneNumber], [Persons_1].[Email] AS [Persons_1_Email], [Persons_1].[Skill1] AS [Persons_1_Skill1], [Persons_1].[Skill2] AS [Persons_1_Skill2], [Persons_1].[Skill3] AS [Persons_1_Skill3], [Persons_1].[Interest1] AS [Persons_1_Interest1], [Persons_1].[Interest2] AS [Persons_1_Interest2] 
FROM (SELECT TOP 1 [Campaigns].[CampaignTitle] AS [Campaigns_CampaignTitle], [Campaigns].[ShortDesc] AS [Campaigns_ShortDesc], [Campaigns].[DatePosted] AS [Campaigns_DatePosted], [Campaigns].[Creator] AS [Campaigns_Creator] 
FROM [Campaigns] 
WHERE [Campaigns].[CampaignTitle] = ?) AS anon_1 LEFT OUTER JOIN [Contributions] AS [Contributions_1] ON [Contributions_1].[CampaignName] = anon_1.[Campaigns_CampaignTitle] LEFT OUTER JOIN [Persons] AS [Persons_1] ON anon_1.[Campaigns_Creator] = [Persons_1].[PersonID]






    




INFO:sqlalchemy.engine.base.Engine:SELECT anon_1.[Campaigns_CampaignTitle] AS [anon_1_Campaigns_CampaignTitle], anon_1.[Campaigns_ShortDesc] AS [anon_1_Campaigns_ShortDesc], anon_1.[Campaigns_DatePosted] AS [anon_1_Campaigns_DatePosted], anon_1.[Campaigns_Creator] AS [anon_1_Campaigns_Creator], [Contributions_1].[ContributorID] AS [Contributions_1_ContributorID], [Contributions_1].[CampaignName] AS [Contributions_1_CampaignName], [Contributions_1].[Contribution] AS [Contributions_1_Contribution], [Contributions_1].[SubTime] AS [Contributions_1_SubTime], [Persons_1].[PersonID] AS [Persons_1_PersonID], [Persons_1].[FirstName] AS [Persons_1_FirstName], [Persons_1].[LastName] AS [Persons_1_LastName], [Persons_1].[Password] AS [Persons_1_Password], [Persons_1].[Department] AS [Persons_1_Department], [Persons_1].[Position] AS [Persons_1_Position], [Persons_1].[Office] AS [Persons_1_Office], [Persons_1].[PhoneNumber] AS [Persons_1_PhoneNumber], [Persons_1].[Email] AS [Persons_1_Email], [Persons_1].[Skill1] AS [Persons_1_Skill1], [Persons_1].[Skill2] AS [Persons_1_Skill2], [Persons_1].[Skill3] AS [Persons_1_Skill3], [Persons_1].[Interest1] AS [Persons_1_Interest1], [Persons_1].[Interest2] AS [Persons_1_Interest2] 
FROM (SELECT TOP 1 [Campaigns].[CampaignTitle] AS [Campaigns_CampaignTitle], [Campaigns].[ShortDesc] AS [Campaigns_ShortDesc], [Campaigns].[DatePosted] AS [Campaigns_DatePosted], [Campaigns].[Creator] AS [Campaigns_Creator] 
FROM [Campaigns] 
WHERE [Campaigns].[CampaignTitle] = ?) AS anon_1 LEFT OUTER JOIN [Contributions] AS [Contributions_1] ON [Contributions_1].[CampaignName] = anon_1.[Campaigns_CampaignTitle] LEFT OUTER JOIN [Persons] AS [Persons_1] ON anon_1.[Campaigns_Creator] = [Persons_1].[PersonID]






    




2014-07-14 21:54:37,786 INFO sqlalchemy.engine.base.Engine ('Alcohol',)






    




INFO:sqlalchemy.engine.base.Engine:('Alcohol',)






    




2014-07-14 21:54:37,983 INFO sqlalchemy.engine.base.Engine SELECT [Comments].[ParentPost] AS [Comments_ParentPost], [Comments].[Author] AS [Comments_Author], [Comments].[SubTime] AS [Comments_SubTime], [Comments].[Content] AS [Comments_Content], anon_1.[Campaigns_CampaignTitle] AS [anon_1_Campaigns_CampaignTitle], [Persons_1].[PersonID] AS [Persons_1_PersonID], [Persons_1].[FirstName] AS [Persons_1_FirstName], [Persons_1].[LastName] AS [Persons_1_LastName], [Persons_1].[Password] AS [Persons_1_Password], [Persons_1].[Department] AS [Persons_1_Department], [Persons_1].[Position] AS [Persons_1_Position], [Persons_1].[Office] AS [Persons_1_Office], [Persons_1].[PhoneNumber] AS [Persons_1_PhoneNumber], [Persons_1].[Email] AS [Persons_1_Email], [Persons_1].[Skill1] AS [Persons_1_Skill1], [Persons_1].[Skill2] AS [Persons_1_Skill2], [Persons_1].[Skill3] AS [Persons_1_Skill3], [Persons_1].[Interest1] AS [Persons_1_Interest1], [Persons_1].[Interest2] AS [Persons_1_Interest2] 
FROM (SELECT TOP 1 [Campaigns].[CampaignTitle] AS [Campaigns_CampaignTitle] 
FROM [Campaigns] 
WHERE [Campaigns].[CampaignTitle] = ?) AS anon_1 JOIN [Comments] ON [Comments].[ParentPost] = anon_1.[Campaigns_CampaignTitle] LEFT OUTER JOIN [Persons] AS [Persons_1] ON [Comments].[Author] = [Persons_1].[PersonID] ORDER BY anon_1.[Campaigns_CampaignTitle]






    




INFO:sqlalchemy.engine.base.Engine:SELECT [Comments].[ParentPost] AS [Comments_ParentPost], [Comments].[Author] AS [Comments_Author], [Comments].[SubTime] AS [Comments_SubTime], [Comments].[Content] AS [Comments_Content], anon_1.[Campaigns_CampaignTitle] AS [anon_1_Campaigns_CampaignTitle], [Persons_1].[PersonID] AS [Persons_1_PersonID], [Persons_1].[FirstName] AS [Persons_1_FirstName], [Persons_1].[LastName] AS [Persons_1_LastName], [Persons_1].[Password] AS [Persons_1_Password], [Persons_1].[Department] AS [Persons_1_Department], [Persons_1].[Position] AS [Persons_1_Position], [Persons_1].[Office] AS [Persons_1_Office], [Persons_1].[PhoneNumber] AS [Persons_1_PhoneNumber], [Persons_1].[Email] AS [Persons_1_Email], [Persons_1].[Skill1] AS [Persons_1_Skill1], [Persons_1].[Skill2] AS [Persons_1_Skill2], [Persons_1].[Skill3] AS [Persons_1_Skill3], [Persons_1].[Interest1] AS [Persons_1_Interest1], [Persons_1].[Interest2] AS [Persons_1_Interest2] 
FROM (SELECT TOP 1 [Campaigns].[CampaignTitle] AS [Campaigns_CampaignTitle] 
FROM [Campaigns] 
WHERE [Campaigns].[CampaignTitle] = ?) AS anon_1 JOIN [Comments] ON [Comments].[ParentPost] = anon_1.[Campaigns_CampaignTitle] LEFT OUTER JOIN [Persons] AS [Persons_1] ON [Comments].[Author] = [Persons_1].[PersonID] ORDER BY anon_1.[Campaigns_CampaignTitle]






    




2014-07-14 21:54:37,983 INFO sqlalchemy.engine.base.Engine ('Alcohol',)






    




INFO:sqlalchemy.engine.base.Engine:('Alcohol',)






    




2014-07-14 21:54:38,161 INFO sqlalchemy.engine.base.Engine ROLLBACK






    




INFO:sqlalchemy.engine.base.Engine:ROLLBACK

In [5]:

    

len(result.Comments)


    

    
Out[5]:





3

In [10]:

    

for instance in result.Comments:
    print instance.Content, instance.Commentator.FirstName


    

    




The first comment! Tomy
And another! Tomy
Try another one! Tomy

In [8]:

    

if result.Comments:
    print "True"


    

    




True