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

Question

I have 2 classes of models as shown 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 added only the necessary attributes needed here in the example. I want to populate the model with a script by reading the csv file for the data. The script table for the domain works well using Flask-SQLAlchemy, but the script table for emailaccount throws an exception. The script looks like this:

 #Populate domains from csv domain_file = "domain.csv" csv_file = csv.DictReader(open(domain_file, 'rb'), delimiter=',') for row in csv_file: #data type conversion from (csv)string before inserting to table for key, value in row.items(): #some code omitted print key, value domain = Domain(**row) db.session.add(domain) db.session.commit() #Populate accounts from csv accounts_file = "accounts.csv" csv_file = csv.DictReader(open(accounts_file, 'rb'), delimiter=',') for row in csv_file: mdomain_name = '' #data type conversion from (csv)string before inserting to table for 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() 

An exception:

File "data.py", line 55, in db.session.add (account)
File "... / local / lib / python2.7 / site-packages / sqlalchemy / orm / scoping.py", line 149, in do return getattr (self.registry (), name) (* args, ** kwargs )
File "... / local / lib / python2.7 / site-packages / sqlalchemy / orm / session.py", line 1397, 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: object 'str' does not have the attribute '_sa_instance_state'

Pl. come back with code changes to the script for data.py ie script to download data for the EmailAccount model, which has a foreign key of the Domain class. I want to use only Flask-SQLAlchemy.

Extract the accounts.csv file:

 Email Account,legacy_username,password,full_name,quota,is_domain_admin,is_catch_all,disabled_login,disabled_delivery [e-mail] ,,,,104857600,,,, [e-mail] ,,,Internal,102400000,,,, [e-mail] ,,,,102400000,,,, kishorepr,xyz.com,,,,209715200,,,, 

Answer 1

When the string contains the domain key, you get the domain to get its key, but you do not update your row with the domain identifier.

Then when you do:

 account = EmailAccount(**row) 

the row object still has the domain key associated with the domain name. Since your EmailAccount class uses the domain name for the relationship, db thinks it will receive the domain object when it actually gets a string (name). This is why you get the AttributeError: 'str' object has no attribute '_sa_instance_state' error message AttributeError: 'str' object has no attribute '_sa_instance_state' .

Update : this should work

 for row in csv_file: account_values = {} for 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()