Thank you so much for this detailed response. I really appreciate it, the explanation and example really furthered my understanding and where I was going wrong.
On Sunday, September 23, 2018 at 9:05:36 PM UTC+1, Ioannes wrote: > > Hi all, > > I had originally asked a question here: > https://stackoverflow.com/questions/52391072/creating-and-appending-to-a-list-in-sqlalchemy-database-table/52391339#52391339. > > However as I am still really stuck, I thought asking to a group more > specifically focussed on SQLAlchemy would be better. > > I have a table in the database (see below for test file going into > table).The table has two fields, name (the latin name e.g. homo sapiens) > and other names (the common names e.g. human, man). I want to update a > field (other names) in the table, so instead of having: > > Rana rugosa human Rana rugosa man Rana rugosa frog Rana rugosa cow > > In the database table, It will return this: > > Rana rugosa human,man,frog,cow > > The test_data file looks like this: > > origin_organism common_name tested_organismRana rugosa > human -Rana rugosa man -Rana > rugosa frog homo sapiensRana rugosa cow > Rana rugosaRana rugosa frog Rana > rugosaRana rugosa frog -Rana rugosa > frog -Rana rugosa frog homo > sapiens- - -- > - homo sapiens- - > -- - > -- - -- > - - > streptococcus pneumoniae - - > > The code: > > import sys from sqlalchemy.orm import * from sqlalchemy import * from > dbn.sqlalchemy_module import lib import pd > > engine = lib.get_engine(user="user", psw="pwd", db="db", > db_host="111.111.111.11")Base = lib.get_automapped_base(engine) > session = Session(engine) > tbs = lib.get_mapped_classes(Base) > session.rollback() > df = pd.read_excel('test_data.xlsx', sheet_name = 'test2') > > > > for index, row in df.iterrows(): > origin_latin_name = row['origin_organism'].strip().lower() > other_names_name = row['common_name'].strip().lower() > tested_species = row['tested_organism'].strip().lower() > > if origin_latin_name not in [None, "None", "", "-"]: > instance = [x[0] for x in > Session.query(session,tbs['species'].name).filter_by(name=origin_latin_name).all()] > if origin_latin_name not in instance: > origin_species = lib.get_or_create( > session, > tbs["species"], > name = origin_latin_name, > other_names = other_names_name > ) > > elif origin_latin_name in instance: > other_names_query = > Session.query(session,tbs['species'].other_names).filter_by(name=origin_latin_name) > other_names_query_list = [x for x in other_names_query] > original_list2 = list(set([y for y in x[0].split(',') for x in > other_names_query_list])) > if other_names_name not in original_list2: > original_list2.append(other_names_name) > new_list = ','.join(original_list2) > new_names = {'other_names':','.join(original_list2)} > > origin_species = lib.get_or_create( > session, > tbs["species"], > name = origin_latin_name, > other_names = new_list > ) > > The part from the elif statement doesn't work. I've ran into two problems: > > (1) The most recent error I got: NameError: name 'new_list' is not defined > > (2) another error I got is that I have another table further on > > map1 = lib.get_or_create( > session, > tbs["map1"], > age_id_id = age, >> >> name_id_id = origin_species.id >> > > ) > > ...and it said that origin_species object cannot be found, but I think > this is linked to the elif statement, that somehow the origin_species > object is not being updated properly. > > If anyone could help I would appreciate it. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
