python - How to populate a model having foreign key using csv using Flask-SQLAlchemy? -

i have 2 model classes below:

class domain(db.model):     __tablename__ = 'domain'     id = db.column(db.integer, primary_key=true)     domain_name = db.column(db.string(30), unique=true)     mailboxes = db.column(db.integer, default=0)      def __init__(self, **kwargs):         self.__dict__.update(kwargs)      def __repr__(self):         return '%s'  % self.domain_name   class emailaccount(db.model):     __tablename__ = 'email_account'     __table_args__ = (         db.uniqueconstraint('username', 'domain_id',                              name='_uq_username_domain'),{}     )     id = db.column(db.integer, primary_key=true)     username = db.column(db.string(30))     domain_id = db.column(db.integer, db.foreignkey('domain.id'))     domain = db.relationship('domain', backref=db.backref('emailaccounts',                             lazy='dynamic'))     def __init__(self,**kwargs):         self.__dict__.update(kwargs)      def __repr__(self):          return  '%s@%s ' % (self.username, self.domain) 

i have added relevant attributes required here in example. wish populate model using script reading csv file data. script domain table works using flask-sqlalchemy, script emailaccount table throws out exception. script follows:

#populate domains csv domain_file = "domain.csv" csv_file = csv.dictreader(open(domain_file, 'rb'), delimiter=',') row in csv_file:     #data type conversion (csv)string before inserting table     key, value in row.items():           #some code omitted         print key, value         domain = domain(**row)     db.session.add(domain)     db.session.commit()  #populate accounts csv accounts_file = "accounts.csv" csv_file = csv.dictreader(open(accounts_file, 'rb'), delimiter=',')  row in csv_file:     mdomain_name = ''     #data type conversion (csv)string before inserting table     key, value in row.items():         print key, value         if key == 'domain':             mdomain = domain.query.filter_by(domain_name = value).first()             mdomain_name = mdomain.domain_name             mdomain_id = mdomain.id         if key == 'domain_id':             value = mdomain_id     account = emailaccount(**row)     db.session.add(account)     db.session.commit() 

exception thrown is:

file "data.py", line 55, in db.session.add(account)
file ".../local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 149, in return getattr(self.registry(), name)(*args, **kwargs)
file ".../local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1397, in add self._save_or_update_state(state)
file ".../local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1415, in _save_or_update_state halt_on=self._contains_state):
file ".../local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1986, in cascade_iterator parent_dict, visited_states, halt_on))
file ".../local/lib/python2.7/site-packages/sqlalchemy/orm/properties.py", line 930, in cascade_iterator get_all_pending(state, dict_)
file ".../local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 761, in get_all_pending ret = [(instance_state(current), current)] attributeerror: 'str' object has no attribute '_sa_instance_state'

pl. revert changes in code in script data.py i.e script uploading data emailaccount model has foreignkey of domain class. wish use flask-sqlalchemy only.

extract of accounts.csv file:

email account,legacy_username,password,full_name,quota,is_domain_admin,is_catch_all,disabled_login,disabled_delivery info@abc.com,,,,104857600,,,, internal@abc.com,,,internal,102400000,,,, kiran.rs@abc.com,,,,102400000,,,, kishorepr,xyz.com,,,,209715200,,,, 

when row contains domain key, retrieve domain key, don't update row domain id.

then when do:

account = emailaccount(**row) 

the row object still has key domain associated domain name. since emailaccount class uses name domain relationship, db thinks domain object when in fact, it's getting string (the name). why error attributeerror: 'str' object has no attribute '_sa_instance_state'.

update: should work

for row in csv_file:     account_values = {}     key, value in row.items():         if key == 'domain':             mdomain = domain.query.filter_by(domain_name = value).first()             account_values['domain'] = mdomain         else:             account_values[key] = value     account = emailaccount(account_values)     db.session.add(account)     db.session.commit()