import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker

Engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
Session = scoped_session(sessionmaker(bind=Engine))
Base = declarative_base()

#sqlalchemy vanilla model
from sqlalchemy import Column, Integer, String, Text, Date, TIMESTAMP

from sqlalchemy.orm import relationship, synonym
from sqlalchemy import Column, ForeignKey, Integer, String, Text, Date, DateTime, TIMESTAMP, Boolean


#These models represent a Google account object  
class GoogleAccount_websites(Base):
    #SQLAlchemy Table Definition
    __tablename__ = 'googleAccountsWebsites'
    
    #website_id = Column('website_id', Integer, primary_key=True)
    googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
    type = Column('type', String, primary_key=True)
    value = Column('value', String)
    
    
class GoogleAccount_emails(Base):
    #SQLAlchemy Table Definition
    __tablename__ = 'googleAccountsEmails'
    
    #email_id = Column('email_id', Integer, primary_key=True)
    googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
    address = Column('address', String, primary_key=True)
    primary = Column('primary', Boolean)
    
    
class GoogleAccount_externalIds(Base):
    #SQLAlchemy Table Mapping
    __tablename__ = 'googleAccountsExternalIds'
    
    #external_id = Column('external_id', Integer, primary_key=True)
    googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
    customType = Column('customType', String, primary_key=True)
    type = Column('type', String)
    value = Column('value', String)
    
    
class GoogleAccount_name(Base):
    #SQLAlchemy Table Mapping
    __tablename__ = 'googleAccountsNames'    
    
    #name_id = Column('name_id', Integer, primary_key=True)
    googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
    familyName = Column('familyName', String)
    fullName = Column('fullName', String)
    givenName = Column('givenName', String)   
    
    
class GoogleAccount_phones(Base):
    #SQLAlchemy Table Mapping
    __tablename__ = 'googleAccountsPhones'
        
    #phone_id = Column('phone_id', Integer, primary_key=True)
    googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
    type = Column('type', String, primary_key=True)
    customType = Column('customType', String)
    value = Column('value', String)
    
    
class GoogleAccount_aliases(Base):
    #SQLAlchemy Table Definition
    __tablename__ = 'googleAccountsAliases'
    
    #alias_id = Column('alias_id', Integer, primary_key=True)
    googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
    emailAddress = Column('emailAddress', String, primary_key=True)
    
    
class GoogleAccount_nonEditableAliases(Base):
    #SQLAlchemy Table Definition
    __tablename__ = 'googleAccountsNoneditableAliases'
    
    #noneditable_alias_id = Column('noneditableAliasId', Integer, primary_key=True)
    googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
    emailAddress = Column('emailAddress', String, primary_key=True)
    
    
class GoogleAccountSAModel(Base):
    #SQLAlchemy Table Definition
    __tablename__ = 'googleAccounts'
    
    id = Column('id', String, primary_key=True, nullable=False)
    agreedToTerms = Column('agreedToTerms', Boolean)
    changePasswordAtNextLogin = Column('changePasswordAtNextLogin', Boolean)
    creationTime = Column('creationTime', String)
    customerId = Column('customerId', String)
    etag = Column('etag', String)
    includeInGlobalAddressList = Column('includeInGlobalAddressList', Boolean)
    ipWhitelisted = Column('ipWhitelisted', Boolean)
    isAdmin = Column('isAdmin', Boolean)
    isDelegatedAdmin = Column('isDelegatedAdmin', Boolean)
    isMailboxSetup = Column('isMailboxSetup', Boolean)
    kind = Column('kind', String)
    lastLoginTime = Column('lastLoginTime', String)
    orgUnitPath = Column('orgUnitPath', String)
    primaryEmail = Column('primaryEmail', String)
    suspended = Column('suspended', Boolean)
    thumbnailPhotoEtag = Column('thumbnailPhotoEtag', String)
    thumbnailPhotoUrl = Column('thumbnailPhotoUrl', String)
    
    websites = relationship('GoogleAccount_websites', collection_class=list,
                             cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
    
    emails = relationship('GoogleAccount_emails', collection_class=list,
                             cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
    
    externalIds = relationship('GoogleAccount_externalIds', collection_class=list,
                             cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
    
    #Name is an Object, not a List type, it must have uselist=False
    name = relationship('GoogleAccount_name', uselist=False,
                             cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
    
    phones = relationship('GoogleAccount_phones', collection_class=list,
                             cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
    
    aliases = relationship('GoogleAccount_aliases', collection_class=list,
                             cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
    
    nonEditableAliases = relationship('GoogleAccount_nonEditableAliases', collection_class=list,
                             cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
    
Base.metadata.create_all(Engine) 

#Marshmallow-SQLAlchemy Schema - Builds Schema based on ORM Model
from marshmallow_sqlalchemy import ModelSchema
from marshmallow import fields

class GoogleAccountSASchema(ModelSchema):
    aliases = fields.Method(deserialize='processAliases')
    nonEditableAliases = fields.Method(deserialize='processNonEditableAliases')
        
    class Meta:
        model = GoogleAccountSAModel

    def deserializeListType(self, value, saType):
        saListOut = []
        
        for item in value:
            newAlias = saType()
            newAlias.emailAddress = item
            saListOut.append(newAlias)
            
        return saListOut

    def processAliases(self, value):
        return self.deserializeListType(value, GoogleAccount_aliases)
    
    def processNonEditableAliases(self, value):
        return self.deserializeListType(value, GoogleAccount_nonEditableAliases) 

Base.metadata.create_all(Engine)

#start of our test
googleAccountJson = '''{
    "agreedToTerms": true,
    "aliases": ["jd@testd.com",
                "j.e.doe@testd.com",
                "zz.dd@testd.com",
                "ff.qwerty@testd.com"
                ],
    "changePasswordAtNextLogin": false,
    "creationTime": "",
    "customerId": "C02fmcj1i",
    "emails": [{
        "address": "john.doe@testd.com",
        "primary": true
        }],
    "etag": "XRsypGOPUmlmxokHB51cC07Vb3s/nnW4dSQR5PacZne6JsXkOrTfeBM",
    "externalIds": [{
        "customType": "GID",
        "type": "custom",
        "value": "336168"
        }],
    "id": "108488927407595610966",
    "includeInGlobalAddressList": true,
    "ipWhitelisted": false,
    "isAdmin": false,
    "isDelegatedAdmin": true,
    "isMailboxSetup": true,
    "kind": "admin#directory#user",
    "lastLoginTime": "",
    "name": {
        "familyName": "Doe",
        "fullName": "John E Doe",
        "givenName": "John E"
        },
    "nonEditableAliases": ["john.doe@webmail.testd.com",
                           "doe@webmail.testd.com",
                           "john.doe2@webmail.testd.com",
                           "john_doe3@webmail.testd.com",
                           "john.doe4@webmail.testd.com"
                           ],
    "orgUnitPath": "/Ops",
    "phones": [{
        "type": "work",
        "value": "+1 839-439-7484"
        }, {
            "type": "mobile",
            "value": "+1 407-393-5048"
            }, {
                "customType": "Ztel",
                "type": "custom",
                "value": "4-439-7484"

                }],
    "primaryEmail": "john.e.doe@testd.com",
    "suspended": false,
    "thumbnailPhotoEtag": "XRGOPUm7Vb3s/mVS-OxFB0Dyxo8",
    "thumbnailPhotoUrl": "https://plus.google.com/_/focus/photos/public/AI0YzcyZjRhNWZjOWExNDkwARP7Z8ODD5GJ",
    "websites": [{
        "type": "profile",
        "value": "https://employees.testd.com/seto/336168"
    }]
}'''

from sqlalchemy.orm.attributes import get_history, instance_state


def GetUncommittedChangesDict(saRow):
    #{'columnName' : {'newValue' : '2', 'oldValue' : '1'}
    #for key in z:   
    
    changed = False
    changedDict = {}
    unchangedDict = {}    
    outDict = {}
    
    for column in saRow._sa_instance_state.attrs:
        if (column.history):
            if (column.history.added) and (not column.history.deleted):
                changed = True
                changedDict[column.key] = {'newValue' : column.history.added, 'oldValue' : None}
                outDict[column.key] = {'newValue' : column.history.added, 'oldValue' : None}
                
                
            if (column.history.unchanged):
                unchangedDict[column.key] = {'newValue' : None, 'oldValue' : column.history.unchanged}
                outDict[column.key] = {'newValue' : None, 'oldValue' : column.history.unchanged}
            
            if (column.history.deleted):
                changed = True
                changedDict[column.key] = {'newValue' : column.history.added, 'oldValue' : None}
                outDict[column.key] = {'newValue' : column.history.added, 'oldValue' : column.history.deleted}
    return changed, changedDict, unchangedDict, outDict

def hasChanged(saRow):
    changed, changedDict, unchangedDict, outDict = GetUncommittedChangesDict(saRow)
    
    print('Change detected: {}'.format(changedDict))
    
    return changed

#First pass
x = GoogleAccountSASchema(strict=True, session=Session).loads(googleAccountJson)
firstRow = x.data

print('1.1- Session.dirty: {}\n\n'.format(Session.dirty))
print('1.1- hasChanged(firstRow): {}\n\n'.format(hasChanged(firstRow)))

Session.add(firstRow)

#Expected hasChanged == True
print('1.2- Session.dirty: {}\n'.format(Session.dirty))
print('1.2- hasChanged(firstRow): {}\n\n'.format(hasChanged(firstRow)))

Session.commit()

#Expected hasChanged == False
print('1.3- Session.dirty: {}\n'.format(Session.dirty))
print('1.3- hasChanged(firstRow): {}\n\n'.format(hasChanged(firstRow)))

print()

y = GoogleAccountSASchema(strict=True, session=Session).loads(googleAccountJson_CHANGED)
secondRow = y.data

print('2.1- Session.dirty: {}\n'.format(Session.dirty))
print('2.1- hasChanged(secondRow): {}\n\n'.format(hasChanged(secondRow)))

print(get_history(secondRow, 'websites'))

#Session.merge(secondRow)

#print('2.2- Session.dirty: {}\n'.format(Session.dirty))
#print('2.2- hasChanged(firstRow): {}\n\n'.format(hasChanged(secondRow)))


#z = GoogleAccountSASchema(strict=True, session=Session).loads(googleAccountJson)
#thirdRow = z.data

#print('3.1- Session.dirty: {}\n'.format(Session.dirty))
#print('3.1- hasChanged(firstRow): {}\n\n'.format(hasChanged(secondRow)))


print()

"""
Test Output-

1.1- Session.dirty: IdentitySet([])


Change detected: {'phones': {'newValue': [<__main__.GoogleAccount_phones object at 0x10a44bf50>, <__main__.GoogleAccount_phones object at 0x10a463b50>, <__main__.GoogleAccount_phones object at 0x10a44b890>], 'oldValue': None}, u'isDelegatedAdmin': {'newValue': [True], 'oldValue': None}, u'thumbnailPhotoEtag': {'newValue': [u'XRGOPUm7Vb3s/mVS-OxFB0Dyxo8'], 'oldValue': None}, u'suspended': {'newValue': [False], 'oldValue': None}, u'id': {'newValue': [u'108488927407595610966'], 'oldValue': None}, 'aliases': {'newValue': [<__main__.GoogleAccount_aliases object at 0x10a44b990>, <__main__.GoogleAccount_aliases object at 0x10a44b810>, <__main__.GoogleAccount_aliases object at 0x10a44be50>, <__main__.GoogleAccount_aliases object at 0x10a44bed0>], 'oldValue': None}, 'nonEditableAliases': {'newValue': [<__main__.GoogleAccount_nonEditableAliases object at 0x10a44bd90>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a44bcd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463bd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463dd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463c50>], 'oldValue': None}, u'thumbnailPhotoUrl': {'newValue': [u'https://plus.google.com/_/focus/photos/public/AI0YzcyZjRhNWZjOWExNDkwARP7Z8ODD5GJ'], 'oldValue': None}, u'includeInGlobalAddressList': {'newValue': [True], 'oldValue': None}, u'isAdmin': {'newValue': [False], 'oldValue': None}, u'etag': {'newValue': [u'XRsypGOPUmlmxokHB51cC07Vb3s/nnW4dSQR5PacZne6JsXkOrTfeBM'], 'oldValue': None}, u'lastLoginTime': {'newValue': [u''], 'oldValue': None}, u'orgUnitPath': {'newValue': [u'/Ops'], 'oldValue': None}, u'agreedToTerms': {'newValue': [True], 'oldValue': None}, 'externalIds': {'newValue': [<__main__.GoogleAccount_externalIds object at 0x10a463f90>], 'oldValue': None}, u'ipWhitelisted': {'newValue': [False], 'oldValue': None}, u'primaryEmail': {'newValue': [u'john.e.doe@testd.com'], 'oldValue': None}, u'isMailboxSetup': {'newValue': [True], 'oldValue': None}, 'emails': {'newValue': [<__main__.GoogleAccount_emails object at 0x10a471fd0>], 'oldValue': None}, u'kind': {'newValue': [u'admin#directory#user'], 'oldValue': None}, 'name': {'newValue': [<__main__.GoogleAccount_name object at 0x10a477bd0>], 'oldValue': None}, u'creationTime': {'newValue': [u''], 'oldValue': None}, 'websites': {'newValue': [<__main__.GoogleAccount_websites object at 0x10a47d810>], 'oldValue': None}, u'changePasswordAtNextLogin': {'newValue': [False], 'oldValue': None}, u'customerId': {'newValue': [u'C02fmcj1i'], 'oldValue': None}}
1.1- hasChanged(firstRow): True


1.2- Session.dirty: IdentitySet([])

Change detected: {'phones': {'newValue': [<__main__.GoogleAccount_phones object at 0x10a44bf50>, <__main__.GoogleAccount_phones object at 0x10a463b50>, <__main__.GoogleAccount_phones object at 0x10a44b890>], 'oldValue': None}, u'isDelegatedAdmin': {'newValue': [True], 'oldValue': None}, u'thumbnailPhotoEtag': {'newValue': [u'XRGOPUm7Vb3s/mVS-OxFB0Dyxo8'], 'oldValue': None}, u'suspended': {'newValue': [False], 'oldValue': None}, u'id': {'newValue': [u'108488927407595610966'], 'oldValue': None}, 'aliases': {'newValue': [<__main__.GoogleAccount_aliases object at 0x10a44b990>, <__main__.GoogleAccount_aliases object at 0x10a44b810>, <__main__.GoogleAccount_aliases object at 0x10a44be50>, <__main__.GoogleAccount_aliases object at 0x10a44bed0>], 'oldValue': None}, 'nonEditableAliases': {'newValue': [<__main__.GoogleAccount_nonEditableAliases object at 0x10a44bd90>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a44bcd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463bd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463dd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463c50>], 'oldValue': None}, u'thumbnailPhotoUrl': {'newValue': [u'https://plus.google.com/_/focus/photos/public/AI0YzcyZjRhNWZjOWExNDkwARP7Z8ODD5GJ'], 'oldValue': None}, u'includeInGlobalAddressList': {'newValue': [True], 'oldValue': None}, u'isAdmin': {'newValue': [False], 'oldValue': None}, u'etag': {'newValue': [u'XRsypGOPUmlmxokHB51cC07Vb3s/nnW4dSQR5PacZne6JsXkOrTfeBM'], 'oldValue': None}, u'lastLoginTime': {'newValue': [u''], 'oldValue': None}, u'orgUnitPath': {'newValue': [u'/Ops'], 'oldValue': None}, u'agreedToTerms': {'newValue': [True], 'oldValue': None}, 'externalIds': {'newValue': [<__main__.GoogleAccount_externalIds object at 0x10a463f90>], 'oldValue': None}, u'ipWhitelisted': {'newValue': [False], 'oldValue': None}, u'primaryEmail': {'newValue': [u'john.e.doe@testd.com'], 'oldValue': None}, u'isMailboxSetup': {'newValue': [True], 'oldValue': None}, 'emails': {'newValue': [<__main__.GoogleAccount_emails object at 0x10a471fd0>], 'oldValue': None}, u'kind': {'newValue': [u'admin#directory#user'], 'oldValue': None}, 'name': {'newValue': [<__main__.GoogleAccount_name object at 0x10a477bd0>], 'oldValue': None}, u'creationTime': {'newValue': [u''], 'oldValue': None}, 'websites': {'newValue': [<__main__.GoogleAccount_websites object at 0x10a47d810>], 'oldValue': None}, u'changePasswordAtNextLogin': {'newValue': [False], 'oldValue': None}, u'customerId': {'newValue': [u'C02fmcj1i'], 'oldValue': None}}
1.2- hasChanged(firstRow): True


1.3- Session.dirty: IdentitySet([])

Change detected: {}
1.3- hasChanged(firstRow): False


2.1- Session.dirty: IdentitySet([<__main__.GoogleAccountSAModel object at 0x10a47d890>, <__main__.GoogleAccount_websites object at 0x10a4d2c10>])

Change detected: {'websites': {'newValue': [<__main__.GoogleAccount_websites object at 0x10a4c3d10>], 'oldValue': None}}
2.1- hasChanged(firstRow): True

History(added=[<__main__.GoogleAccount_websites object at 0x106f38c10>], unchanged=[], deleted=[<__main__.GoogleAccount_websites object at 0x106f38210>])
"""