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 [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.
