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

Question

在我的工作中，我有两张SQL表，一张叫做jobs，有字符串属性，job和code。后者称为具有字符串属性代码和技能的技能。

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

标签： pythonsql

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 的方法，因为这正是数据库的用途。

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