如何将字符串转换为数组并连接到sq中的另一个表

Question

在我的工作中，我有两个SQL表，一个叫做jobs，带有字符串属性、job和代码。后者称为带字符串属性的技能代码和技能。你知道吗

job code
--- ----
j1  s0001,s0003
j2  s0002,20003
j3  s0003,s0004

code  skills
----- ------
s0001 python programming language
s0002 oracle java
s0003 structured query language sql
s0004 microsoft excel

我的老板要我做的是：从jobs中的属性代码中获取值，将字符串拆分为一个数组，在codes（from skills table）上加入这个数组，并以job skills的格式返回查询，如：

job skills
--- ------
j1  python programming language,structured query language sql

在这一点上，我只想知道（A）这是否可行，（B）是否有一个更好的替代方法。我在下面列出了我的python解决方案，使用字典来说明我的概念：

jobs = {'j1':'s0001,s0003',
        'j2':'s0002,20003',
        'j3':'s0003,s0004'}

skills = {'s0001':'python programming language',
          's0002':'oracle java',
          's0003':'structured query language sql',
          's0004':'microsoft excel'}

job_skills = {k:[] for k in jobs.keys()}
for j,s in jobs.items():
    for code,skill in skills.items():
        for i in s.split(','):
            if i == code:
                job_skills[j].append(skill)

for k,v in job_skills.items():
    job_skills[k] = ','.join(v)

以及输出：

{'j1': 'python programming language,structured query language sql',
 'j2': 'oracle java',
 'j3': 'structured query language sql,microsoft excel'}

这个问题的真正症结在于，我们的数据中不仅仅有4种不同的技能。我们公司的数据包括约5000项技能。我的老板非常希望避免创建一个包含5000个属性的表，每个技能对应一个属性；他相信上述方法将导致更简单的查询，并有可能实现更好的内存管理。你知道吗

Answer 1

我对SQL还是相当陌生的，而且技术上只做SQLite3，所以我能做的最好的可能就是Python解决方案。我将告诉您我将如何解决它，并且希望有人能够来解决它，因为纯粹用SQL做事情比使用Python要快得多。你知道吗

我假设这是SQLite，因为您标记了Python。如果不是这样，如果您喜欢这个解决方案，可能有一些方法可以将数据库转换为.db格式，以便使用它。你知道吗

我假设conn是您到数据库conn = sqlite3.connect(your_database_path)的连接或它的游标。我不使用光标，但使用它们几乎肯定是更好的做法。你知道吗

首先，我将获取“skills”表并将其转换为dict。我将使用：

skills_array = conn.execute("""SELECT * FROM skills""")
skills_dict = dict()
#replace i with something else. I just did it so that I could use 'skill' as a variable
for i in skills_array:
    #skills array is an iterator of tuples, which means the first position is the code number, and the second position is the skill itself
    code = i[0]
    skill = i[1] 
    skills_dict[code] = skill

可能有更好的方法。如果这很重要，我建议你研究一下。但如果这是一次性的事情，这将工作得很好。所有这一切都是做一个简单的方法来查找给定代码的技能。你可以用很多种方法。你没有提到它是一个特别大的数据库，所以这应该是好的。你知道吗

在下一部分之前，应该先介绍一下SQLite。我今天碰巧发现了一件事。推荐的方法是创建一个新表，而不是试图用一个旧表来欺骗。但是有一些简单的方法可以用SQLiteBrowser修改它们，我强烈建议您使用。至少对我来说，在它里面查看信息要容易得多，而且它在所有重要的操作系统上都是可用的

第二，我们需要把工作表和技能表结合起来，有更好的方法，但我选择了简单的方法。界定工作技能以逗号表示的列并从那里开始。我还将创建一个新表，并直接插入其中。你知道吗

conn.execute("""CREATE TABLE combined (job TEXT PRIMARY KEY, skills text)""")
conn.commit()
job_array = conn.execute("""SELECT * FROM jobs""")


for i in job_array:
    job = i[0]
    skill = i[1]
    for code in skill.split(","):
        skill.replace(code, skills_dict[code])

    conn.execute("""INSERT INTO combined VALUES (?, ?)""", (job, skill,))
    conn.commit()

把这一切结合起来。。。你知道吗

import sqlite3

conn = sqlite3.connect(your_database_path)

skills_array = conn.execute("""SELECT * FROM skills""")
skills_dict = dict()
#replace i with something else. I just did it so that I could use 'skill' as a variable
for i in skills_array:
    #skills array is an iterator of tuples, which means the first position is the code number, and the second position is the skill itself
    code = i[0]
    skill = i[1] 
    skills_dict[code] = skill

conn.execute("""CREATE TABLE combined (job TEXT PRIMARY KEY, skills text)""")
conn.commit()
job_array = conn.execute("""SELECT * FROM jobs""")

for i in job_array:
    job = i[0]
    skill = i[1]
    for code in skill.split(","):
        skill.replace(code, skills_dict[code])

    conn.execute("""INSERT INTO combined VALUES (?, ?)""", (job, skill,))
    conn.commit()

如果您/某人对job\u array for loop感到困惑，请进一步解释： 拆分技能允许您查看每个单独的代码，这意味着您所要做的就是用相应的技能替换正在查找的代码的每个实例。你知道吗

就这样。上面的代码中可能有一两个错误，所以我会在尝试之前备份您的数据库/表，但这应该是可行的。您可能会发现上下文管理器很有帮助，这会使它更具python风格。如果您计划一致地使用它（出于某些奇怪的原因），那么为提高速度和可读性而进行重构也可能是谨慎的。你知道吗

我还想相信有一种只使用SQLite的方法，因为这正是数据库的用途。你知道吗

希望这有帮助。如果有，告诉我。：>； 另外，如果你对某事/想要更多的解释感到困惑，请随意评论。你知道吗