No problem. By the way, I noticed a bug in my code, on this line:
origin_species =
session.query(Species).filter_by(name=origin_latin_name).one()
You probably want ".first()" rather than ".one()". "first()" will
either return a matching Species object or None, whereas "one()"
checks that the database contains exactly 1 matching row and otherwise
raises an exception.
Simon
On Wed, Sep 26, 2018 at 8:38 AM Ioannes <[email protected]> wrote:
>
> 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_organism
>> Rana rugosa human -
>> Rana rugosa man -
>> Rana rugosa frog homo sapiens
>> Rana rugosa cow Rana rugosa
>> Rana rugosa frog Rana rugosa
>> Rana 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
