回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我在学习炼金术,我被卡住了。
我有一个SQL表(table1)有两个字段:“name”和“othernames”</p>
<p>我有一个包含两列的excel文件:</p>
<pre><code>first_name alias
paul patrick
john joe
simon simone
john joey
john jo
</code></pre>
<p>我想把excel文件读入table1,这样它看起来是这样的(即同一行的所有别名都在一行上):</p>
^{pr2}$
<p>这就是我想做的。我尝试的代码(带注释):</p>
<pre><code>for line in open('file.txt', 'r'): #for each line in the excel file
line = line.strip().split('\t') #split each line with a name and alias
first_name = line[0] #first name is the name before the tab
alias = line[1] #alias is the name after the tab
instance =
Session.query(session,tbs['table1'].name).filter_by(name=first_name) #look through the database table, by name field, and see if the first name is there
list_instance = [x[0] for x in instance] #make a list of first names already in database table
if first_name not in list_instance: #if the excel first name is not in the database table
alias_list = [] #make an empty list
alias_list.<a href="https://www.cnpython.com/list/append" class="inner-link">append</a>(alias) #append the alias
name_obj = lib.get_or_create( #small function to make db object
session,
tbs["table1"],
name = first_name, #add first name to the name field
other_names = alias_list # add alias list to the other_names field
)
elif first_name in list_instance: #elif first name already in db
alias_list.append(alias) #append the alias to the alias list made above
name_obj = lib.get_or_create(
session,
tbs["table1"],
name = first_name,
other_names = alias_list #create object as before, but use updated alias list
)
</code></pre>
<p>问题是,我可以让上面的代码运行没有错误,但输出不是一个附加的列表,它只是一个看起来像excel文件的数据库表;也就是说</p>
<pre><code>name alias
paul patrick
john joe
simon simone
john joey
john jo
</code></pre>
<p>有人能指出我哪里出了问题,具体地说,我该如何修改这一准则?如果问题不清楚,请告诉我,我试着把它作为一个简单的例子。具体来说,如何初始化列表并将其作为SQLalchemy db表中的字段条目添加到列表中。在</p>
<p>更新1:我已经根据下面的建议更新了我的代码。但是我仍然有这个问题。这是完整的目标、代码和测试文件:
目标:</p>
<p>我在数据库中有一个表(关于进入表的测试文件,见下文)。表有两个字段,name(拉丁名,如homo sapiens)和其他名称(常用名称,如human,man)。我想更新表中的字段(其他名称),因此不需要:</p>
<pre><code>Rana rugosa human
Rana rugosa man
Rana rugosa frog
Rana rugosa cow
</code></pre>
<p>我有:</p>
<pre><code>Rana rugosa human,man,frog,cow
</code></pre>
<p>测试数据文件如下所示:</p>
<pre><code>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 - -
</code></pre>
<p>代码:</p>
<pre><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
)
</code></pre>
<p>elif语句中的部分不起作用。我遇到了两个问题:</p>
<p>(1)我最近得到的错误:
名称错误:未定义名称“new_list”</p>
<p>(2)我得到的另一个错误是我有另一张桌子在前面</p>
<pre><code>map1 = lib.get_or_create(
session,
tbs["map1"],
age_id_id = age,
name_id_id = origin_species.id
)
</code></pre>
<p>…它说找不到起源物种,但我认为这与elif的声明有关,不知何故原始物种对象没有得到正确更新。在</p>
<p>如果有人能帮忙,我会很感激的。在</p>