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

Question

在我的工作中，我有两个 SQL 表，一个称为 jobs，具有字符串属性、作业和代码。后者称为技能，带有字符串属性code和skill。

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

我的老板希望我做的是:从职位中的属性代码中获取值，将字符串拆分为一个数组，将该数组连接到代码上(来自技能表)，并以职位技能的格式返回查询，例如:

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 个属性的表，每个技能 1 个；他相信上述方法将导致更简单的查询，并且可能具有更好的内存管理。

Answer 1

我对 SQL 还很陌生，而且从技术上讲，无论如何我只使用 SQLite3，所以我能做的最好的可能就是 Python 解决方案。我将告诉你我将如何解决这个问题，并希望有人能够修复它，因为纯粹用 SQL 做事比使用 Python 快得多。

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

我假设 conn 是您与数据库 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

可能有更好的方法来做到这一点。如果它很重要，我建议研究它们。但如果这是一次性的事情，那么效果就很好。所有这一切都是为了提供一种简单的方法来查找给定代码的技能。您可以通过数十种方法来做到这一点。您没有提到它是一个特别大的数据库，所以这应该没问题。

在下一部分之前，应该提到一些关于 SQLite 的事情。它的表修改机制非常有限——这是我今天偶然发现的。推荐的方法是创建一个新表，而不是尝试使用旧表。但是有一些简单的方法可以使用 SQLiteBrowser 来修改它们——我强烈建议您使用它。至少对我来说查看其中的信息要容易得多，而且它可以在所有重要的操作系统上使用。

其次，我们需要将工作表和技能字典结合起来。有很多更好的方法可以解决这个问题，但我选择了简单的方法。用逗号分隔 job.skills 列并从那里开始。我还将创建一个新表，并直接插入其中。

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_array for 循环感到困惑，请进一步解释一下:拆分技能允许您查看每个单独的代码，这意味着您所要做的就是用相应的技能替换正在查找的代码的每个实例。

就是这样。上面的代码中可能有一两个错误，所以我会在尝试之前备份您的数据库/表，但这应该可行。您可能会发现有用的一件事是上下文管理器，这将使其更加Pythonic。如果您打算一致地使用它(出于某种奇怪的原因)，那么为了速度和可读性而进行重构也可能是明智的。

我还愿意相信存在一种仅限 SQLite 的方法，因为这正是数据库的用途。

希望这有帮助。如果是的话，请告诉我。 :>附:如果您对某些事情感到困惑/想要更多解释，请随时发表评论。